Complete the following exercises:
| a) |
Create a companion procedure to the example procedure LOG_USER_COUNT, name your proceudre READ_LOG. This procedure will read a text file and display each line via DBMS_OUTPUT.PUT_LINE. |
| b) |
Run the procedure LOG_USER_COUNT and then run the procedure READ_LOG for the same file. |
Complete the following exercises:
| a) |
Create a procedure DELETE_ENROLL that will delete all student enrollments if there are no grades in the GRADE table for that student's enrollment and the start date of the section is already one month past. |
| b) |
Submit the procedure DELETE_ENROLL to execute one a month. |
Complete the following exercises:
| a) |
Find out if your schema has a table named PLAN_TABLE that matches the DDL in the Plan table script ch21_1a.sql. If it does not, then use the above script to create the PLAN_TABLE. |
| b) |
Compute statistics on all tables in your schema using a single SQL statement to generate the command. |
| c) |
The following SQL statement generates a list of the open sections in courses that the student with the ID of 214 is not enrolled in. There are many different SQL statements that would produce the same result. Since various in-line views are required, it is important to examine the execution plan to determine which plan will produce the result with the least COST to the database. Run the SQL as follows to generate an SQL plan. -- ch21_1b.sql EXPLAIN PLAN FOR SELECT c.course_no course_no, c.description description, b.section_no section_no, s.section_id section_id, i.first_name first_name, i.last_name last_name FROM course c, instructor i, section s, (SELECT a.course_no course_no, MIN(a.section_no) section_no FROM (SELECT count(*) enrolled, se.CAPACITY capacity, se.course_no course_no, se.section_no section_no, e.section_id section_id FROM section se, enrollment e WHERE se.section_id = e.section_id AND e.student_id <> 214 GROUP BY se.CAPACITY, se.course_no, e.section_id, se.section_no HAVING count(*) < se.CAPACITY) a GROUP BY a.course_no) b WHERE c.course_no = b.course_no AND b.course_no = s.course_no AND s.section_no = b.section_no AND s.instructor_id = i.instructor_id; |
| d) |
Use the DBMS_XPLAN package as a means to see the execution plan of the SQL statement. |
| e) |
Generate an alternative SQL that will produce the same results and then examine the explain plan. |
Complete the following exercises:
| a) |
Create a PL/SQL procedure that will generate the following HTML page. <HTML> <HEAD> <TITLE>Section Location Update Form</TITLE> </HEAD> <BODY> <H1>Change Section Location</H1> <FORM ACTION="update_section" METHOD="GET"> Section ID: <INPUT TYPE="text" NAME="p_section" SIZE="8" MAXLENGTH="8" VALUE="150"> Course No: <INPUT TYPE="text" NAME=" SIZE="8" VALUE="120"> <SELECT NAME="p_location" SIZE="10"> <OPTION VALUE=H310>H310 <OPTION VALUE=L206>L206 <OPTION SELECTED VALUE=L210>L210 <OPTION VALUE=L211>L211 <OPTION VALUE=L214>L214 <OPTION VALUE=L500>L500 <OPTION VALUE=L507>L507 <OPTION VALUE=L509>L509 <OPTION VALUE=L511>L511 <OPTION VALUE=M200>M200 <OPTION VALUE=M311>M311 <OPTION VALUE=M500>M500 </SELECT> <INPUT TYPE="submit" VALUE="Change the location"> </FORM> </BODY> </HTML> |
| b) |
Generate an update page for the form action in the last HTML page. This update will be the form handler for the SUBMIT button, it will commit the changes to the database and then refresh the page. |