The Gateway to Computer Science Excellence
0 votes


in Databases by Loyal (7k points) | 103 views
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?

2 Answers

+2 votes
$\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.}$
by Loyal (5.9k points)
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
+2 votes

$\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 }$

by Loyal (5.9k points)
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

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,292 answers
104,917 users