Nawathe Elmsari

Question

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

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 )));

Can someone plz explain how this query works?? please?? This query is on page number 270 nawathe book.

Comments

Can u plz share d screen shot?Tat ways it'll be still better. :)

---

This is the query. sorry I have cut it just out of frustration. just go through it plz.

---

I think it's more clear.

Answer 1

You can go through it actually first inner subquery is non correlated means you can execute it one time and leave it but second inner subquery is correlated means for each row in employee table you have to go through works on table and see that whether particular employee is working on that project or not as you can see employee a works on all project of dept 5 so inner subquery result will be null and hence not exist returns true so a will be printed

And employee b not working on any project controlled by dept no. 5 so inner subquery return 25 so not exist return false so that particular b will not be printed

 

Comments

@prince is there any other way to write the same query

---

can we write it using joins or subqueries?