MADE EASY TEST SERIES

Question

Consider the following relations and query given below:
Emp( Id, Name, Sex, Salary)
Proj(No, Name, Dno) and
Works_on(No, IDs) contains atleast one tuples.

Consider the following query:

Select Name
From Emp
Where NOT Exists ((Select No
                 From Proj where Dno = 106)
             EXCEPT (select No from
                 works_on where Id = IDs));

 

What does this query return?

Comments

@learner what the options given?

Answer 1

Employee name who works on all the project of department number 106

Comments

source?

Answer 2

Let's break down the query step by step:

1. Select Name From Emp: This selects all the names from the Emp table.

2. Where NOT Exists: This filters the results to only include employees for whom the following subquery evaluates to FALSE.

3. (Select No From Proj where Dno = 106) EXCEPT (select No from works_on where Id = IDs): This subquery compares the project numbers of projects managed by department 106 (Dno = 106) to the project numbers of projects that the employee with ID 'IDs' is working on.

4. The EXCEPT operator removes any project numbers that are common to both sets, leaving only the project numbers that are unique to either set.

If the result of this EXCEPT operation is empty, it means that the employee with ID 'IDs' is not working on any projects managed by department 106.

So, the query returns the names of employees who do not work on any projects managed by department 106.