SQL Query

Question

 

Comments

I got option D can't be the Answer Please Help me with option A and B

---

C should be the answer

---

I am getting option b

---

can we use columns of main query in sub-query without prefixing the table?

Answer 1

$\text{answer should be b)}$$$\text{NOT EXISTS returns TRUE if there are no tuples in the result of nested query , and it returns }$$$\text{FALSE otherwise.}$

$\text{first subquery selects allprojects controlled by department 5, and the second subquery}$
$\text{selects all projects on which particular employee is working and the }$
$\text{difference of the first subquery result EXCEPT the second subquery result is }$
$\text{empty, it means that the employee works on all the projects and is therefore selected.}$

Comments

Answer is Correct. But Please Elaborate :)

I got that in option B inner query will be Empty. But how its giving the answer i dont understand ??

Please elaborate on option A too

Answer 2

$\text{just go through it ,what I have done for each }$
$\text{employee the inner subquery will run inner query returns null }$
$\text{means that particular employee is working on all the project that are controlled}$
$\text{by department no. 5 and the query will return some value only when that particular employee}$

$\text{is not working on all projects controlled by dept. 5 }$

Comments

As, i have shown that employee b
Will not be selected as it is not working on all the projects that are controlled by dept. 5 but in first query ,
 IN is there  which compares a
value ssn with a set (or multiset) of values essn and evaluates to TRUE if ssn is one of the elements in essn as when inner most query project return {10,25,15} then due to IN the essn 2 of employee name b will also be considered as it is working on project 10 and 15 as result of which the ssn will be match with essn 2 and the row with employee name b get printed