Table Emp(id, salary) has rows (1,3000), (2,5000), (3,5000), (4,4000). SELECT MAX(salary) FROM Emp WHERE salary < (SELECT MAX(salary) FROM Emp); What value is returned?
Correct answer A. 4000
Explanation
The inner query finds the highest salary, 5000. The outer WHERE keeps only salaries strictly below 5000, which are 3000 and 4000, and MAX picks 4000. So the query returns the second-highest distinct salary even though 5000 appears twice. A version that sorts descending and takes the second row would wrongly return 5000 here because of the tie.
Answer checked How we write and check questions
Discussion