Which of the following query/queries

return the employee ID and name of
employees whose salary is greater than

the salary of all employees in department number 20 of university? Order results by employee ID (refer to table structures given above).

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

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

1. A(A) and (B) only
2. B(A) and (C) only
3. C(B) only
4. D(C) only

Pick an option to check yourself, or open the answer below.

Show answer & explanation

Correct answer: D. (C) only

Explanation

A plain > comparison with a subquery that returns many rows is an error, so A and B fail. > ALL (subquery) compares with every department-20 salary, and C also orders by EID.