1. Consider that we have the following tables in a database (primary keys are underlined):
Student (roll_no int, student_name varchar(30) not null, year_of_admission int, dept_cd char(2) not null, cgpa decimal(5,2), percentage_marks decimal(5,2), hall_cd char(2))
Course (course_cd char(5), course_name varchar(30) not null, credits int not null, max_marks decimal(5,2) not null, dept_cd char(2) not null)
Registration (roll_no int, course_cd char(5), grade_point int, marks_obtained decimal(5,2))
Write a trigger on the Registration table so that whenever there is a change in the rows (insert, update, delete), the column value for CGPA will be updated in the Student table. [10]
2. Consider the same tables used in Problem 1.
Write a database procedure that will take a roll_no as input and update the column value for CGPA in
the Student table. It will also return the CGPA. [10]
3. Consider that we have the following table in a database (primary key is underlined):
Employee (emp_cd int, manager_emp_cd int)
Assume that the seniormost manager will have his/her own emp_cd as his/her manager_emp_cd
Write a recursive query which will take an emp_cd as input (i.e., somewhere in your SELECT, there will be a clause “AND emp_cd = 5” or “WHERE emp_cd = 5”) and return one row for each reporting manager higher in the hierarchy starting from the input emp_cd. [10]
Through Moodle, submit a single text file containing all your SQL and PL/SQL statements. (Name it as Lab6_<Roll_no>.txt).
