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.
SELECT zip FROM zipcode MINUS SELECT zip FROM student MINUS SELECT zip FROM instructor
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.
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.
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.
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.