- All Questions Are Based On The Following Tables And Data Student Sno Not Null Number 10 Sname Varchar2 20 Age Number 1 (32.06 KiB) Viewed 114 times
- All Questions Are Based On The Following Tables And Data Student Sno Not Null Number 10 Sname Varchar2 20 Age Number 2 (45.15 KiB) Viewed 114 times
Marks Write ONE PL/SQL program (anonymous block) that provides a report showing student name and information regarding their ordering history. The detailed specification is as follows: • Using an explicit cursor, retrieve all students (SNO) from the STUDENT table. Your cursor will need to group by students together for processing • In the appropriate looping structure you will need to • Call the function created in question 3. Store the total count in a local variable. • Processing block will need to make the following decisions. If the total number of students in a faculty is more than 200, we classify the action as "Large Faculty". If the total number of students in a faculty is between 100 and 200, we classify the action as "Medium Faculty". For all other students in a faculty less than 100, we classify them as "Small Faculty". Declare another local variable to store the classification. • You need to process all the information for a student to produce the following output for each student. Finally, create an exception handler which fires when no rows are found. The exception handler should output the following message to the screen: "No rows found". Important Note: The program must use ONE block only. Do NOT write a block to perform each task of the specification above. Question 5 (10 marks) Marks Write an appropriate trigger fires after a DML statement on the table STUDENT. The trigger should check whether the faculty number (FNO) has been changed. If there has been a change, insert the old and the new values into the LOGLINE table. Use the mtext 1 column.