Skip to content

Database Management System

CSE · Quiz · Spring 26

Paper 8 of 8Section 63_LSwitch

Section 63_L

Paper 8 of 8

This browser can’t show the PDF here

Open it, and your phone’s PDF viewer takes it, or download it.

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,