Using a subquery construct, determine which sections the student Henry Masser is enrolled in.
Solution:
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.
Write the question for the following SELECT statement.
SELECT zip
FROM zipcode z
WHERE NOT EXISTS
(SELECT '*'
FROM student
WHERE z.zip = zip)
AND NOT EXISTS
(SELECT '*'
FROM instructor
WHERE z.zip = zip)
Solution:
Display the zip codes not found in either the STUDENT table or the INSTRUCTOR table.
Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.
Solution:
SELECT course_no, description
FROM course c
WHERE NOT EXISTS
(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))
COURSE_NO DESCRIPTION
--------- ---------------------------------
25 Intro to Programming
80 Structured Programming Techniques
...
350 JDeveloper Lab
430 JDeveloper Techniques
16 rows selected.
Can the ANY and ALL operators be used on the DATE datatype? Write a simple query to prove your answer.
Solution:
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'))
'HELL
-----
Hello
1 row selected.
SELECT 'Hello again'
FROM dual
WHERE TO_DATE('12-MAR-2001', 'DD-MON-YYYY') >ALL
(SELECT created_date
FROM grade)
'HELLOAGAIN
-----------
Hello again
1 row selected.
If you have a choice to write either a correlated subquery or a simple subquery, which one would you choose and why?
Solution:
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."
Determine the top three zip codes where most of the students live.
Solution:
SELECT s.*, ROWNUM ranking
FROM (SELECT zip, COUNT(*)
FROM student
GROUP BY zip
ORDER BY 2 DESC) s
WHERE ROWNUM <= 3
ZIP COUNT(*) RANKING
----- --------- ---------
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
ORDER BY 2 DESC
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.