This question is taken from the book Fundamentals of Database systems 6th edition (Elmasri,Navathe) Chapter 5 query 3b. One way the authors answered this is by using the existential quantifiers:
SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM WORKS_ON B
WHERE ( B.Pno IN ( SELECT Pnumber
FROM PROJECT
WHERE Dnum=5 )
AND
NOT EXISTS ( SELECT *
FROM WORKS_ON C
WHERE C.Essn=Ssn
AND C.Pno=B.Pno )));
The authors have explained the solution as:
Select each employee such that there does not exist a project controlled by department 5 that the employee does not work on
I cannot understand how the second inner correlated sub-query relates to the other uncorrelated subquery and the outer query to give the right result.
Any help is appreciated.