To solve this type of question, we will take a sample table which includes all possible cases. In this case, we have to find out the employees whose salary is greater than salary of any employee in department 5. So we have taken two employees in department 5 with salary 5000 and 10000 and three other employees:
empId 1 whose salary is less than both employees of department 5.
empId 2 whose salary is greater than both employees of department 5.
empId 4 whose salary is less than one employee of department 5(empId 5) and greater than other employee of department 5(empId 3).
So we will see whether two queries given in question work for these scenarios or not.
Q1 Select e.empId from employee e where not exists
(Select * from employee s where s.department = “5” and s.salary >=e.salary)
This is a correlated nested query. For every row in outer query, inner query is executed and result of inner query is used as an input of outer query. First row of employee table from outer query having e.empId=1 is passed to inner query, it will execute the inner query like:
select * from employee s where s.department=”5” and s.salary>=2000(1.salary)
It will return rows with empId 3 and 5. But the outer query will not return anything for this row because not exists condition is failed.
For second row of employee table, inner query will return no row, and outer query will retun empId 2.
Similarly, we can do the same for other rows and the final output will be:
Q2 Select e.empId from employee e where e.salary > any
(Select distinct salary From employee s Where s.department = “5”)
This type of query is independent nested query in which inner query is executed independently and its result is used in execution of outer query. Inner query will fetch distinct salaries of employees in department “5” as:
Select distinct salary From employee s Where s.department = “5”
its output will be 5000 and 10000.
The inner query will fetch those empId whose salary is greater than any salary from this set like:
Select e.empId from employee e where e.salary > any(5000,10000)
The final output will be:
So the required output is drawn from Q2 but not Q1. Q1 is missing those rows where employee salary is greater than one employee of department 5 but less than other employee of department 5(empId 4). So answer will be (B).