'Retrieve the name of each employee who works on all the projects controlled by department number 5

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


Solution 1:[1]

SELECT fname, lname FROM employee
WHERE NOT EXISTS
(SELECT pnumber FROM project WHERE dnum=5
AND pnumber NOT IN
(SELECT pno FROM works_on WHERE ssn=essn));

Please note Except doesnot work in mysql

Solution 2:[2]

SELECT FNAME, LNAME
FROM EMPLOYEE 
WHERE NOT EXISTS 
((  SELECT PNUMBER 
        FROM PROJECT 
        WHERE DNUM = 5) 
     EXCEPT 
    (   SELECT PNO 
        FROM WORKS ON 
        WHERE ESSN = SSN));

Solution 3:[3]

I had to modify the query in order to find all employees having ALL the projects that Smith was working on. So taking the code as written but reformatted:

SELECT fname, lname FROM employee
WHERE NOT EXISTS  --single EXISTS looking for Emp OUTER QUERY
(SELECT pnumber FROM project WHERE dnum=5  -- ALL Dept 5 Projects
    --add next line to exclude project 3 for dept 5 to return 2 employees (pNo 1&2)
    --AND EXISTS (SELECT * FROM WORKS_ON WHERE Pno=Pnumber and ESSN='123456789')
    AND pnumber NOT IN  -- with ea dept proj matching pno assigned to SSN·
    (SELECT pno FROM works_on WHERE ssn=essn)
);

Adding the line which is commented returns ALL employees having ALL dept5 projects (1,2) assigned to Smith, which is the same output if Project 3 is deleted from projects. So the CONFUSION is the NOT EXISTS which is saying return all records for which it is not true that there is not a match for every project in the dept.

Solution 4:[4]

Please try the following code:

SELECT fname,
       lname
FROM   employee
WHERE  EXISTS(SELECT pnumber
              FROM   project
              WHERE  dnum = 5
                     AND pnumber IN (SELECT pno
                                     FROM   works_on
                                     WHERE  essn = ssn));

Solution 5:[5]

SELECT FNAME,LNAME
FROM   EMPLOYEE
WHERE  EXISTS   ( SELECT *
                  FROM DEPENDENT
                  WHERE SSN=ESSN)
AND EXISTS 
                ( SELECT *
                  FROM DEPARTMENT
                  WHERE SSN=MGR_SSN);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Nikhil Taneja
Solution 2
Solution 3 L00_Cyph3r
Solution 4 azbarcea
Solution 5 geisterfurz007

Related Questions