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

SELECT section_id, student_id
FROM enrollment
WHERE (student_id, section_id) IN
(SELECT student_id, section_id

SELECT section_id, student_id
FROM enrollment e
WHERE EXISTS
(SELECT 1
WHERE e.section_id = g.section_id
AND e.student_id = g.student_id)

SELECT DISTINCT e.section_id, e.student_id
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.
```