Retrieve the name of each employee who works on all the projects controlled by department number 5
20:18 26 Jul 2014

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.

mysql