Question

Using the Class database, answer the following questions:

1. Create a stored procedure that for any student id entered, a list of absences for that

student will be displayed. Display the student id, student name, and the dates the

student has missed. Display the total number of days missed for this student. Name

this stored procedure

StudentAbsences

.

a. What did you write for your stored procedure? Insert the snip of the contents of the

stored procedure here:

b. Run your stored procedure for student 17. Insert your snip showing your stored

procedure call and results here:

Answer 1

Stored procedure :

DELIMITER //
CREATE PROCEDURE StudentAbsences( IN studentID int)
BEGIN
select student.student_id , name,date,count(date) as ' Missed days' from student,absence where
student.student_id=absence.student_id and student.student_id=studentID group by student.student_id , name;
END //
DELIMITER ;

Executing stored procedure :

call StudentAbsences(17);

Explanation :

• Stored procedure is compile only once and when call every time it will execute.
• Above stored procedure will join two table student and absence based on student_id
• and will return the details for the passed student id as 17