GATE CS 2007 Q61 — Tuple Calculus and SQL

To find the employees who earn a higher salary than anyone in department 5, we need to find employees whose salary is strictly greater than the maximum salary of any employee in department 5.

Analyzing Query $Q_1$:

Select e.empId
From employee e
Where not exists
    (Select * From employee s Where s.department = "5" and s.salary >= e.salary)

• The subquery searches for any employee $s$ in department 5 whose salary is greater than or equal to employee $e$'s salary ($s.\text{salary} \ge e.\text{salary}$).
• The NOT EXISTS clause ensures that no such employee exists in department 5.
• Mathematically, this means for every employee $s$ in department 5, $e.\text{salary} > s.\text{salary}$.
• Thus, $Q_1$ correctly identifies employees who have a higher salary than everyone in department 5.

Analyzing Query $Q_2$:

Select e.empId
From employee e
Where e.salary > Any
    (Select distinct salary From employee s Where s.department = "5")

• The > ANY operator returns true if $e.\text{salary}$ is greater than at least one salary in department 5.
• This is equivalent to finding employees whose salary is greater than the minimum salary in department 5, which does not satisfy the requirement of being higher than anyone in department 5.

Therefore, only $Q_1$ is the correct query.