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