Which SQL query will retrieve the second highest salary

Solution

This query correctly retrieves the second-highest salary by first finding the maximum salary and then searching for the maximum salary that is less than the first maximum. The subquery (SELECT MAX(Salary) FROM Employee) retrieves the highest salary, and the main query finds the maximum salary that is smaller than that value, effectively returning the second-highest salary. This method ensures that the second-highest salary is retrieved by eliminating the highest salary and then finding the next largest value. Why Other Options Are Incorrect:

• B) This query would retrieve the third-highest salary, not the second-highest, due to the OFFSET 2 . It skips the first two salaries, resulting in the third.
• C) This query retrieves the highest salary, not the second-highest, by selecting the maximum salary without any condition.
• D) This query retrieves all salaries greater than the average salary, which does not necessarily provide the second-highest salary. It is based on a statistical average, not ranking.
• E) This query correctly retrieves the second-highest salary but uses LIMIT 2,1 , which might be seen as non-standard across different SQL systems. The approach is less elegant than option A for finding the second-highest salary.