ace 2017 test series

Question

How will this query execute?

Q1: Select pno from project

Q2: select pno from works where employee.eno=works.eno

Now Q1 except Q2 will all projects which do not have an employee working on it. So the outer query should give: All employees who are working on some project.

But answer is: Employees working on all projects.

Consider the following relations schema
employee (eno, ename, salary)
project (pno, pname, duration)
works (eno, pno)

Select ename from employee where not exists
(select pno from project)
except
(select pno
from works_on
where employee eno = works_on. eno):

The above query finds name of the employee who works_on $\qquad$

• A. Exactly one project
  
• B. Atleast one project
  
• C. Atmost one project
  
• D. Every project.
  Your Answer: B
  Correct Answer :D

Answer 1

Query1  selects all the PNo from project table.

Query2 is a correlated query that returns that Pno in which an employee works.

Query1 except Query2. returns in which project that particular employee did not work.

 if Query1 except Query2 is an empty set , the outer query returns the employee name.
So we can say, Query1 except Query2 is an empty set if an employee works in all project.