You are to create the following queries on the SCE tables as described below using only syntax and recommended techniques we have covered in class or on SLATE.

You are to create the following queries on the SCE tables as described below using only syntax and recommended techniques we have covered in class.

1. Write a stored procedure that can be used to find (1) the total number of sections of a specified course (course_code) that a specified faculty member (fac_id) is teaching, (2) the combined total seats still available in all sections for a specified course (course_code) taught by that same faculty member, and (3) the campus name of that faculty member. This procedure will be used in question 2, but it should be able to be tested with any faculty member and any course code. Use only parameters to pass/receive data. Do not use DBMS_OUTPUT.PUT_LINE to display data in the stored procedure. Be sure to handle any exceptions appropriately and return appropriate results. (Marks: 3 for code; 2 for query output; 2.5 for exception handling)

2. Write an anonymous block that prompts the user to enter a faculty_id and course_code and then invokes the procedure created previously (in #1) then displays the total number of sections a faculty member is teaching, the total seats still available combined in all sections for the course taught by that faculty member, and the campus name of the faculty member. Display the output. (Marks: 5 for PL/Script; 2.5 for query output)

3. Create a trigger for the course_section table that will reject inserting or updating values for CURR_ENRL that are either negative or greater than 37. The trigger should raise an appropriate exception. (Marks: 3 for code; 2 for output showing trigger was created)

4. Write a script with three commands.

- An INSERT statement that inserts a new record in the course_section table. You will have to deal with referential integrity constraints caused by COURSE_CODE, TERM_ID and CAMPUS_ID etc. Insert values for all columns in the course_section table and make sure that the value for CURR_ENRL is between 0 and 37.

- Write an UPDATE statement and update the CURR_ENRL to a value that is either negative or greater than 37.

- Follow by a COMMIT.

- Execute the script and show the response from the serve.