DELIMITER // CREATE PROCEDURE p_studentinfobyclass (IN classname VARCHAR(10)) BEGIN
SELECT students.Sno, students.Sname, stc.Cno, stc.Score
FROM students
INNER JOIN stc ON students.Sno = stc.Sno
WHERE stc.Cno IN (
SELECT courses.Con FROM courses WHERE courses.cname = classname
);
END// DELIMITER ;
Explanation:
- The procedure name is “p_studentinfobyclass”, and it takes one input parameter “classname” of type VARCHAR(10).
- The procedure selects student information from the “students” table and course information from the “stc” table.
- It uses an inner join to match records in both tables based on the student ID (Sno) column.
- The where clause filters the results by selecting only those records whose course number (Cno) is included in a subquery that selects all course numbers for a given class name.
- The result set includes columns for student ID (Sno), student name (Sname), course number (Cno), and score.
