A3: | Answer: Your answer should look similar to the following: DECLARE v_name student.last_name%TYPE; v_id student.student_id%TYPE; BEGIN BEGIN -- A second block is used to capture the possibility of -- multiple students meeting this requirement. -- The exception section will handles this situation SELECT s.last_name INTO v_name FROM student s, enrollment e WHERE s.student_id = e.student_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM student s, enrollment e WHERE s.student_id = e.student_id GROUP BY s.student_id) GROUP BY s.last_name; EXCEPTION WHEN TOO_MANY_ROWS THEN v_name := 'Multiple Names'; END; INSERT INTO CHAP4 VALUES (CHAP4_SEQ.NEXTVAL, v_name); SAVEPOINT A; BEGIN SELECT s.last_name INTO v_name FROM student s, enrollment e WHERE s.student_id = e.student_id HAVING COUNT(*) = (SELECT MIN(COUNT(*)) FROM student s, enrollment e WHERE s.student_id = e.student_id GROUP BY s.student_id) GROUP BY s.last_name; EXCEPTION WHEN TOO_MANY_ROWS THEN v_name := 'Multiple Names'; END; INSERT INTO CHAP4 VALUES (CHAP4_SEQ.NEXTVAL, v_name); SAVEPOINT B; BEGIN SELECT i.last_name INTO v_name FROM instructor i, section s WHERE s.instructor_id = i.instructor_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM instructor i, section s WHERE s.instructor_id = i.instructor_id GROUP BY i.instructor_id) GROUP BY i.last_name; EXCEPTION WHEN TOO_MANY_ROWS THEN v_name := 'Multiple Names'; END; SAVEPOINT C; BEGIN SELECT instructor_id INTO v_id FROM instructor WHERE last_name = v_name; EXCEPTION WHEN NO_DATA_FOUND THEN v_id := 999; END; INSERT INTO CHAP4 VALUES (v_id, v_name); ROLLBACK TO SAVEPOINT B; BEGIN SELECT i.last_name INTO v_name FROM instructor i, section s WHERE s.instructor_id = i.instructor_id HAVING COUNT(*) = (SELECT MIN(COUNT(*)) FROM instructor i, section s WHERE s.instructor_id = i.instructor_id GROUP BY i.instructor_id) GROUP BY i.last_name; EXCEPTION WHEN TOO_MANY_ROWS THEN v_name := 'Multiple Names'; END; INSERT INTO CHAP4 VALUES (v_id, v_name); BEGIN SELECT i.last_name INTO v_name FROM instructor i, section s WHERE s.instructor_id = i.instructor_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM instructor i, section s WHERE s.instructor_id = i.instructor_id GROUP BY i.instructor_id) GROUP BY i.last_name; EXCEPTION WHEN TOO_MANY_ROWS THEN v_name := 'Multiple Names'; END; INSERT INTO CHAP4 VALUES (CHAP4_SEQ.NEXTVAL, v_name); END; |