Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources]

Answer: Your answer should look similar to the following:

PROMPT Creating Table 'CHAP4'
CREATE TABLE chap4
(id NUMBER,
name VARCHAR2(20));

Answer: Your answer should look similar to the following:

PROMPT Creating Sequence 'CHAP4_SEQ'
CREATE SEQUENCE chap4_seq
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE;

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;