3.28

Using the university schema, write an SQL query to find the names and IDs of those instructors who teach every course taught in his or her department (i.e., every course that appears in the course relation with the instructor’s department name). Order result by name.

---

SELECT id, name
FROM instructor AS i
WHERE NOT EXISTS (
    (SELECT course_id FROM course WHERE dept_name = i.dept_name)
    EXCEPT 
    (SELECT course_id FROM teaches WHERE teaches.id = i.id)
)
ORDER BY name ASC