Classic SQL interview queries
55 min · Advanced
A small set of classic SQL queries appears again and again in placement tests and interviews, almost always on an employee table. Every query here runs on the same eight-employee table, so you can compare approaches; Section 12 (SQL & Database Design) teaches each clause in full if one feels new.
Topics in this lesson
- Nth highest salary: LIMIT/OFFSET, correlated subquery and DENSE_RANK
- Finding and deleting duplicate rows
- Employees earning more than their manager (self join)
- Department-wise max salary and counts with GROUP BY/HAVING
- Second highest without LIMIT; DELETE vs TRUNCATE vs DROP
Interview questions this lesson answers
- How do you find the second highest salary?
- How do you find the Nth highest salary?
- Why is DISTINCT important in the Nth highest salary query?
- What is the difference between RANK, DENSE_RANK and ROW_NUMBER?
- How does the correlated subquery for the Nth highest salary work?
- How do you find duplicate rows?
- How do you delete duplicates but keep one copy?
- Why does MySQL need an extra derived table when deleting with a subquery on the same table?
- What is a self join? Give an example.
- How do you find employees who earn more than their managers?
Sign in free to continue this lesson
Create a free account to read every lesson, practise with the exercises and keep track of your progress.