Oracle PL/SQL Interactive Workbook, 2/e

Test Your Thinking Solutions Chapter 4 SQL in PLSQL

1. Create a table called CHAP4 with two columns, one is ID (a number) and the second is NAME which is a varchar2(20).

   Answer: Your answer should look similar to the following:

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

2. Create a sequence called CHAP4_SEQ that increments by units of 5.

   Answer: Your answer should look similar to the following:

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

3. Write a PL/SQL block that performs the following in this order:

   1. Declares two variables one for the v_name and one for v_id. The v_name variable can be used throughout the block for holding the name that will be inserted, realize that the value will change in the course the block.
   2. The block then inserts into the table the name of the student that is enrolled in the most classes and uses a sequence for the ID; afterwards there is SAVEPOINT A.
   3. Then the student with the least enrollments is inserted; afterwards there is SAVEPOINT B.
   4. Then the instructor who is teaching the maximum number of courses is inserted in the same way. Afterwards there is SAVEPOINT C.
   5. Using a SELECT INTO statement, hold the value of the instructor in the variable v_id.
   6. Undo the instructor insert by use of rollback.
   7. Insert the instructor teaching the least amount of courses but do not use the sequence to generate the ID; instead use the value from the first instructor whom you have since undone.
   8. Now insert the instructor teaching the most number of courses and use the sequence to populate his ID.

   Add DBMS_OUTPUT throughout the block to display the values of the variables as they change. (This is good practice for debugging) 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;
   

Select a Chapter for Test Your Thinking Solutions

1. Programming Concepts
2. PLSQL Concepts
3. General Programming Language Fundamentals
4. SQL in PLSQL
5. Conditional Control: IF Statements
6. Conditional Control: CASE Statements
7. Error Handling and Built-In Exceptions
8. Iterative Control
9. Introduction to Cursors
10. Exceptions
11. Exceptions: Advanced Concepts
12. Procedures
13. Functions
14. Packages
15. Advanced Cursors
16. Stored Code
17. Triggers
18. Collections
19. Records