Solutions Chapter 7: Subqueries Test Your Thinking

  1. Using a subquery construct, determine which sections the student Henry Masser is enrolled in.


    SELECT section_id
      FROM enrollment
     WHERE student_id IN
           (SELECT student_id
              FROM student
             WHERE last_name = 'Masser'
               AND first_name = 'Henry')
    no rows selected

    Note: Henry Masser is not enrolled in any section at all. The query returns no rows.

  2. Write the question for the following SELECT statement.

    SELECT zip
      FROM zipcode z
           (SELECT '*'
              FROM student
             WHERE = zip)
           (SELECT '*'
              FROM instructor
             WHERE = zip)


    Display the zip codes not found in either the STUDENT table or the INSTRUCTOR table.

  3. Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.


    SELECT course_no, description
      FROM course c
           (SELECT NULL
              FROM section s
             WHERE c.course_no = s.course_no)
       OR course_no IN
           (SELECT course_no
              FROM section s2
             WHERE NOT EXISTS
                   (SELECT NULL
                      FROM enrollment e
                     WHERE s2.section_id = e.section_id))
    --------- ---------------------------------
           25 Intro to Programming
           80 Structured Programming Techniques
          350 JDeveloper Lab
          430 JDeveloper Techniques
    16 rows selected.
  4. Can the ANY and ALL operators be used on the DATE datatype? Write a simple query to prove your answer.


    Yes, the ANY and ALL operators work on the DATE datatype. There are many different possible sample queries. Here is one for each operator. The queries produce the correct result with no error.

    SELECT 'Hello'
      FROM dual
     WHERE TO_DATE('12-MAR-2001', 'DD-MON-YYYY') < ANY
           (TO_DATE('13-MAR-2001', 'DD-MON-YYYY'), 
            TO_DATE('14-MAR-2001', 'DD-MON-YYYY'))
    1 row selected.
    SELECT 'Hello again'
       FROM dual
      WHERE TO_DATE('12-MAR-2001', 'DD-MON-YYYY') >ALL
            (SELECT created_date
               FROM grade)
    Hello again
    1 row selected.
  5. If you have a choice to write either a correlated subquery or a simple subquery, which one would you choose and why?


    The correlated subquery using the NOT EXISTS operator tests for NULL values which the NOT IN operator does not. Another consideration is the number of records returned by the outer query and the inner query. If the outer query returns a large number of records, the correlated subquery must test for each of these outer rows, which is very time-consuming. If the inner query returns only a very few records, the simple subquery is typically best. To determine the most efficient statement, test against realistic data volumes and properly indexed tables. For more information about this topic see Chapter 16, "SQL Optimization."

  6. Determine the top three zip codes where most of the students live.


    SELECT s.*, ROWNUM ranking
      FROM (SELECT zip, COUNT(*)
              FROM student
             GROUP BY zip
             ORDER BY 2 DESC) s
     WHERE ROWNUM <= 3
    ----- --------- ---------
    07024         9         1
    07010         6         2
    11368         6         3
    3 rows selected.

    Note, if you execute the inline view query, you notice that there are actually three zip codes with six students enrolled each. Below is a partial listing of the query.

    SELECT zip, COUNT(*)
      FROM student
     GROUP BY zip
    ZIP    COUNT(*)
    ----- ---------
    07024         9
    07010         6
    11373         6
    11368         6
    07042         5
    06605         1
    06798         1
    145 rows selected.

    The zip code 07024 has the largest number of students. Three zip codes follow with an equal number of enrollments. But only the first two are included in the query, because the ROWNUM pseudocolumn picks a maximum of three rows. In Chapter 15, "Advanced SQL Queries," you will learn more about top-n queries.

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