Write a SQL query to find the second highest salary from a table.

Explanation:

Finding the second highest salary in a table is a common SQL interview question. It tests your understanding of SQL queries and your ability to manipulate data using subqueries or other methods. The key here is to think about how you can exclude the highest salary to identify the next highest one.

One way to solve this problem is by using a subquery to first find the highest salary and then exclude it in your main query to find the second highest.

Key Talking Points:

• Understand the use of subqueries: Subqueries can be used to filter out results from the main query.
• Focus on order and limit: Sorting and limiting results help in identifying specific records like the highest or second highest.
• Practice writing queries with different SQL functions: Functions like MAX(), LIMIT, and ORDER BY are often used in such scenarios.

Pseudocode:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Here is a brief breakdown:

• Use a subquery (SELECT MAX(salary) FROM employees) to find the highest salary.
• Use this subquery in the WHERE clause to exclude the highest salary from your main query.
• Use MAX(salary) again in the main query to find the next highest salary.

Follow-Up Questions and Answers:

1. Question: How would you modify your query if there are ties in the salary, and you want the second distinct highest salary?
   • Answer: You can use DISTINCT in the subquery to ensure that only unique salaries are considered.

   SELECT MAX(salary) AS second_highest_salary
   FROM employees
   WHERE salary < (SELECT DISTINCT MAX(salary) FROM employees);

2. Question: How would you find the Nth highest salary?
   • Answer: You can use the ROW_NUMBER() window function to assign a rank to each unique salary and then select the one with the desired rank.

   SELECT salary
   FROM (
       SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
       FROM employees
   ) ranked_salaries
   WHERE rank = N;

NOTES:

Reference Table: (Subquery vs. Window Function)

This explanation and solution should guide you in understanding how to approach this problem and be prepared for variations of this question during your interview.