Consider the following table structures related to a university for the below…
2020
Consider the following table structures related to a university for the below question
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 result by employee ID (refer 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
Choose the correct answer from the options given below:
- A.
(A) and (B) Only
- B.
(A) and (C) Only
- C.
(B) Only
- D.
(C) Only
Attempted by 264 students.
Show answer & explanation
Correct answer: D
Final answer: Only the query that uses SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO = 20) and includes ORDER BY EID is correct.
Why the other queries are incorrect:
Using SALARY > (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO = 20) is invalid when the subquery returns more than one salary. A comparison operator with a subquery that returns multiple rows causes a runtime error.
Even if the comparison were valid, the version that omits ORDER BY does not satisfy the problem requirement to order results by employee ID.
Correct approaches:
Use ALL to compare against every salary in department 20 and include ordering:
SELECT EID, NAME FROM EMPLOYEE WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO = 20) ORDER BY EID
Or compare to the maximum salary in department 20 and include ordering:
SELECT EID, NAME FROM EMPLOYEE WHERE SALARY > (SELECT MAX(SALARY) FROM EMPLOYEE WHERE DEPTNO = 20) ORDER BY EID
Summary:
The > ALL form correctly enforces "greater than every salary" semantics.
A plain scalar comparison to a subquery that returns multiple rows is not valid; use an aggregate (MAX) or ALL/ANY operators as appropriate.
Remember to include ORDER BY EID to satisfy the ordering requirement.
A video solution is available for this question — log in and enroll to watch it.