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.
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)
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.
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.
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?
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.
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.