Database Management System
CSE · Quiz · Spring 26
Paper 8 of 8Section 63_LSwitch
Section 63_L
Paper 8 of 8
Read the questions as text
Read off the PDF by AI, so it may have mistakes. The PDF is the original.
Daffodil International University
CSE-311 (63_L) Class Test-3
Time : 40 min, Marks : 15
Name :
ID :
1. Design a Trigger named “grade_change_log” that mainly occurs when you are updating a grade of a student. For instance if a student’s grade is changing from “” to “” a trigger will save the data of the student's previous grade and new grade alongside the student’s id in the updated_grade table as a log. Assume that the updated_grade table and student_grade table is already created.
student_grade: { std_id, course_id, grade}
updated_grade : { std_id, course_id, previous_grade, new_grade} 5 CO3
NB: If there is no change in grade your trigger should not work.
CREATE TRIGGER grade_change_log
AFTER UPDATE ON student_grade
FOR EACH ROW
BEGIN
IF OLD.grade <> NEW.grade THEN
Insert the change log into the updated_grade table INSERT INTO updated_grade (std_id, course_id, previous_grade, new_grade) VALUES (OLD.std_id, OLD.course_id, OLD.grade, NEW.grade);
END IF;
END ;
2. Create a stored procedure named All_students_CGPA() to calculate the CGPA of all the students from the Student_grade table. Output Format : std_id, CGPA.
CGPA_formula : ∑(gpa * credit)/ ∑(credit)
Student_grade : {std_id, course_id, c_credit, grade} 5 CO3
CREATE PROCEDURE All_students_CGPA()
BEGIN
SELECT std_id, SUM(c_credit * grade) / SUM(c_credit) FROM Student_grade
Group By std_id;
END ;
3. Create a SQL view named Avg_Result that displays the average CGPA of students for each department using the given schema. In the Output it will display
dept_id | dept_name | avg(cgpa)
Department : { dept_id (PK), dept_name }
Students : { student_id (PK), student_name, dept_id (FK), Cgpa } 05
CREATE VIEW Avg_Result AS
SELECT Department.dept_id, Department.dept_name, AVG(s.Cgpa) AS avg_cgpa
FROM Department
Left JOIN
Students
ON Department.dept_id = Students.dept_id
GROUP BY
Department.dept_id,