Analyzing SQL Queries for Employee Salary Comparison

The question asks us to find the employee ID and name of employees whose salary is strictly greater than the salary of all employees in department number 20. The result should be ordered by employee ID (EID).

We are given three potential SQL queries and need to determine which one(s correctly achieve this goal based on the provided database table structures, particularly the EMPLOYEE table which contains EID, EMPLOYEENAME, DEPTNO, and SALARY.

Let's examine each query:

Query (A) Analysis

The first query is:

SELECT EID, NAME
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20)
ORDER BY EID;

This query uses a subquery (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20). This subquery returns a set of all salaries for employees in department 20. The outer query then attempts to compare an employee's SALARY with this entire set using the simple > operator.

• In most SQL dialects, directly comparing a single value (SALARY) with a set of values using only the > operator is either syntactically incorrect or implicitly interpreted in a way that doesn't match "greater than all". For instance, it might be treated like > ANY, meaning greater than at least one salary in the set, which is not the requirement.
• The query includes the required ORDER BY EID clause.

However, due to the incorrect use of the > operator with a set to mean "greater than all", this query is likely incorrect.

Query (B) Analysis

The second query is:

SELECT EID, NAME
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20);

This query is very similar to query (A), using the same subquery and the simple > operator to compare a single salary against a set of salaries from department 20.

• It suffers from the same issue as query (A) regarding the comparison of a single value with a set using > to mean "greater than all".
• Crucially, this query does not include the required ORDER BY EID clause.

Therefore, this query is incorrect for two reasons: the improper comparison operator for the required logic and the missing ordering.

Query (C) Analysis

The third query is:

SELECT EID, NAME
FROM EMPLOYEE
WHERE SALARY > ALL(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20)
ORDER BY EID;

This query also uses the subquery (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20) to get the set of salaries for department 20 employees. However, it uses the > ALL operator.

• The > ALL operator is specifically designed to compare a single value with every value in a set. The condition SALARY > ALL(...) is true only if the SALARY from the outer query is greater than every single salary returned by the subquery. This precisely matches the requirement "greater than the salary of all employees in department number 20".
• The query includes the required ORDER BY EID clause.

This query correctly implements the required logic using the appropriate SQL operator and includes the necessary ordering.

Conclusion on Correct Query

Based on the analysis, only query (C) correctly uses the > ALL operator to ensure that an employee's salary is greater than every salary in department 20 and includes the required sorting by EID.

Therefore, only query (C) correctly answers the question.

Revision Table - SQL Subqueries and ALL Operator

Additional Information - Comparing with Subquery Results

When using a subquery in a WHERE clause, especially one that returns multiple rows, you often need operators that can handle set-based comparisons:

• Comparing with a single value: If the subquery is guaranteed to return only one row and one column, you can use standard comparison operators like =, <>, >, <, >=, <=. For example, comparing a salary to the *average* salary of a department.
• Comparing with a set of values: If the subquery can return multiple rows, you must use operators designed for set comparisons:
  • IN or NOT IN: Checks if a value is present or not present within the set.
  • ANY (or SOME): Used with comparison operators (=, <>, >, etc.). Condition is true if the comparison holds for *any* value in the set.
  • ALL: Used with comparison operators (=, <>, >, etc.). Condition is true if the comparison holds for *all* values in the set.

In this specific question, the requirement "greater than the salary of all employees" explicitly demands the use of the > ALL operator.