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.