Solutions Chapter 8: Set Operators Test Your Thinking

  1. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables. Write two different solutions, using set operators for both.

    Solution:

    SELECT zip
      FROM zipcode
    MINUS
    SELECT zip
      FROM student
    MINUS
    SELECT zip 
      FROM instructor
    

    or:

    SELECT zip
      FROM zipcode
    MINUS
    (SELECT zip
       FROM student
     UNION
     SELECT zip
       FROM instructor)
    ZIP
    -----
    00914
    06401
    ...
    30342
    33431
    
    79 rows selected.
    
  2. Write a SQL statement, using a set operator, to show which students enrolled in a section on the same day they registered.

    Solution:

    SELECT student_id, TRUNC(registration_date)
      FROM student
    INTERSECT
    SELECT student_id, TRUNC(enroll_date)
      FROM enrollment
    
    no rows selected
    
  3. Find the students that are not enrolled in any classes. Write three solutions: a set operation, a subquery, and a correlated subquery.

    Solution:

    SELECT student_id
      FROM student
    MINUS
    SELECT student_id
      FROM enrollment
    
    SELECT student_id
      FROM student
     WHERE student_id NOT IN
           (SELECT student_id
              FROM enrollment)
    
    SELECT student_id
      FROM student s
     WHERE NOT EXISTS
           (SELECT ÔxÕ
              FROM enrollment e
             WHERE s.student_id = e.student_id)
    STUDENT_ID
    ----------
           284
           285
    ...
           397
           399
    
    103 rows selected.
    
  4. Show the students who have received grades for their class. Write four solutions: a set operation, a subquery, a correlated subquery, and a join.

    Solution:

    SELECT section_id, student_id
      FROM enrollment
    INTERSECT
    SELECT section_id, student_id
      FROM grade
    
    SELECT section_id, student_id
      FROM enrollment
     WHERE (student_id, section_id) IN
           (SELECT student_id, section_id
              FROM grade)
    
    SELECT section_id, student_id
      FROM enrollment e
     WHERE EXISTS
           (SELECT 1
              FROM grade g
             WHERE e.section_id = g.section_id
               AND e.student_id = g.student_id)
    
    SELECT DISTINCT e.section_id, e.student_id
      FROM enrollment e, grade g
     WHERE e.section_id = g.section_id
       AND e.student_id = g.student_id
    SECTION_ID STUDENT_ID
    ---------- ----------
            80        128
            81        103
    ...
           156        214
           156        215
    
    226 rows selected.
    

Select Another Chapter

  1. SQL and Data
  2. SQL: The Basics
  3. Character and Number Functions
  4. Date, Conversion, and Miscellaneous Functions
  5. Aggregate Functions, GROUP BY, and HAVING
  6. Equijoins
  7. Subqueries
  8. Set Operators
  9. Complex Joins
  10. Insert, Update, and Delete
  11. Create, Alter, and Drop Tables
  12. Views, Indexes, and Sequences
  13. The Data Dictionary and Dynamic SQL Scripts
  14. Security
  15. Advanced SQL Queries
  16. SQL Optimization