In SQL, which query correctly returns the second highest salary from an Employee table with a salary column?

Introduction / Context:
Finding the second highest salary from an Employee table is a classic SQL interview question. It tests whether you can combine aggregate functions with subqueries or ranking techniques to obtain a specific order statistic. The query must exclude the highest salary and then find the maximum of the remaining values. This question uses a simple and widely understood pattern that does not require advanced window functions, making it suitable for many databases.

Given Data / Assumptions:

- There is a table named Employee with at least one numeric column called salary.

- Salaries are numeric values where higher numbers represent higher pay.

- We want a query that returns a single value representing the second highest salary.

- We assume that there is at least one salary lower than the absolute maximum.

Concept / Approach:
One common approach to find the second highest salary is to first identify the maximum salary and then look for the highest salary that is strictly less than that maximum. In SQL, you can use a subquery to get the maximum salary and then filter the Employee table to exclude that value. The outer query then takes the maximum of the remaining salaries. This pattern works even if there are multiple employees with the same highest salary, because the condition salary < (SELECT MAX(salary) FROM Employee) removes all of them from consideration and the new maximum among the remaining rows becomes the second highest distinct salary.

Step-by-Step Solution:
Step 1: Compute the highest salary using SELECT MAX(salary) FROM Employee. Step 2: Exclude this highest salary from the candidate set by adding a WHERE clause salary < (SELECT MAX(salary) FROM Employee). Step 3: On this filtered set, compute the maximum salary again to obtain the second highest distinct salary. Step 4: Combine these parts into one query: SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee). Step 5: Compare this query with the options and select option A, which matches the constructed query.

Verification / Alternative check:
Consider a simple data set with salaries 3000, 4000, 5000, and 7000. The inner subquery MAX(salary) returns 7000. The condition salary < 7000 filters out the row with 7000, leaving 3000, 4000, and 5000. The outer MAX on this filtered set returns 5000, which is the second highest salary. This reasoning holds even if there are multiple employees with salary 7000, because they are all excluded. This confirms that option A works correctly for finding the second highest distinct salary.

Why Other Options Are Wrong:
Option B returns the minimum salary, which is unrelated to the second highest. Option C returns the highest salary, not the second highest; it would be correct for the maximum but not for the second maximum. Option D returns the average salary, which again does not address the requirement for ordering by value. Therefore, none of these options implement the intended logic.

Common Pitfalls:
One common pitfall is to use ORDER BY salary DESC LIMIT 2 and then manually pick the second row, but forget to handle ties or portability across different database systems. Another issue is not considering the case where there is only one distinct salary, in which case the query might return null. In interviews, mention that the subquery pattern used in option A is widely accepted and that, in databases which support window functions, you could also use dense rank or row number for more advanced scenarios while the core idea remains to exclude the highest salary and then find the maximum of the remaining values.

Final Answer:
The correct SQL query for the second highest salary is SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee).