What is the result of the given query?

Question

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

Works_On table has details of projects the employee works on and project contains details of various projects running in various department.

What details does the above query fetches? Please explain the steps to solve this query.

Answer 1

1.  Select *from works_on c...... query run nd select those tuple which fullfil givn conditn now not exist means disply those tuple which r not selected

2. Now query select pnumber........run nd disply those pnumbr whose dnum = 5

3. Now query select * from works_ on b......nd select those tuple whose b.pno is belongs to above selected tuple

4. Now query select lname..... run nd bcz of not exist it disply those tuple from employee table which r not selected by above 3rd point and it is our final result..

Comments

@Mayuri Tiwari , can u plz explain once again the working of

 AND
NOT EXISTS ( SELECT *
FROM WORKS_ON C
WHERE C.Essn=Ssn
AND C.Pno=B.Pno )

I am nt getting what's the use of AND here since we have no such connection between the statements above AND and after it 

---

Hello radha,

Sorry I knw I m too late bcz of some problems but now I again xplain u

Before AND query select the pnumbr whose dnum is 5 frm project

And after AND query select the whole table which essn=ssn and c.bpno=b.bpno frm works_on c and not exist means gives those tuple which are not in selected by this (after AND) query

Now as we knw result of AND is true only when both condition (after AND nd befor AND) is true

Now it will result those bpno whose  dnum is 5 but it is not equal to b.bpno and its c.essn is not equal to ssn

Thanks

Answer 2

Gives details of employees who either don't work on a project in department no. 5 OR works on a project with essn=ssn (social security number?)

Comments

Sir , just one confusion in your answer 

  NOT EXISTS ( SELECT *  FROM WORKS_ON C  WHERE C.Essn=Ssn  AND C.Pno=B.Pno )));

Here it is Not Exists and therefore it must be OR who do not work on a project with essn=ssn (social security number?) since when NOT EXISTS would be true then only the those tuples would be selected .