4.17
Express the following query in SQL using no subqueries and no set operations.
SELECT id FROM student EXCEPT SELECT s_id FROM advisor WHERE i_id IS NOT NULL
The above query is going to get the ids of students that don’t have an advisor. That means, those students that have ids that don’t appear in the advisor relation or their advisor’s id is set to null.
SELECT s.id
FROM student s LEFT OUTER JOIN advisor a
ON s.id = a.s_id
WHERE a.i_id IS NULL
OR a.s_id IS NULL;
Note that we are assuming, the primary key of advisor to be s_id. That is each student can have at most one advisor.