SQL Interview Questions
Master joins, aggregations, window functions, and real-world database querying. Prepare with these 1 real-world questions covering beginner to advanced scenarios.
How do you find the second highest salary in a table?
Simple Answer
You can find it by using the `LIMIT` and `OFFSET` clauses, or by using a subquery that finds the maximum salary that is strictly less than the absolute maximum salary.
Detailed Answer
In MySQL/PostgreSQL, the simplest way is to order by salary descending, skip the first row (`OFFSET 1`), and take the next row (`LIMIT 1`). However, if there are multiple employees with the identical highest salary, this approach fails. The safer, standard approach is using a subquery: `SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);`. An even more robust modern approach is using the `DENSE_RANK()` window function.
Interview Scenario Walkthrough
My Thinking Process:
"1. I need the second highest distinct value. 2. A simple LIMIT might return the highest value again if there's a tie. 3. I should use a subquery or a Window Function."
Possible Causes:
- Duplicate maximum salaries.
- Only one employee exists (should return NULL).
How I Would Answer:
"The classic answer is a subquery: `SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)`. But in a modern environment, I prefer using a CTE with a window function like `DENSE_RANK()`. This handles ties perfectly and makes it easy to change 'second highest' to 'Nth highest' without writing complex nested subqueries."
Interview Tip
Always mention that `LIMIT/OFFSET` breaks when there are duplicate highest values. Pointing this out shows you've encountered real-world dirty data.
Common Mistake
Using `LIMIT 1, 1` without considering duplicate salaries at the top.
Real World Example
A company wants to generate a report of runner-up salespeople for a bonus. You must account for ties (e.g., if two people tied for first place, the 'second highest' is the next distinct value).
Test Your SQL Knowledge
Take this quick interactive quiz to see if you retained the key concepts.
SQL Mastery Quiz
Question 1 of 1How do you find the second highest salary in a table?
Keep Building Your SQL Skills
Interview prep works best when combined with hands-on practice. Use these resources to deepen your understanding and build portfolio projects.