Top SQL Interview Queries — Solved and Explained

These queries appear in almost every SQL round for developer, analyst and support roles. Each one is solved on our sample company database.

CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(30));
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'HR'), (40, 'Marketing');
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(30), dept_id INT, salary INT, city VARCHAR(20), manager_id INT, joined DATE);
INSERT INTO employees VALUES
 (1, 'Asha', 10, 95000, 'Jaipur', NULL, '2019-04-01'), (2, 'Ravi', 10, 72000, 'Pune', 1, '2021-07-15'),
 (3, 'Neha', 20, 58000, 'Jaipur', 1, '2020-01-10'), (4, 'Kabir', 20, 58000, 'Delhi', 3, '2022-03-01'),
 (5, 'Om', 30, 45000, 'Pune', 1, '2023-06-20'), (6, 'Meera', 10, 88000, 'Bengaluru', 1, '2018-11-05'),
 (7, 'Arjun', NULL, 40000, 'Delhi', 3, '2024-02-12');

1. Second highest salary (without window functions)

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

second_highest
88000

For the Nth highest, use DENSE_RANK() = N (see the window functions lesson), or ORDER BY salary DESC LIMIT 1 OFFSET N-1 on SELECT DISTINCT salary.

2. Find duplicate values

Which salaries appear more than once?

SELECT salary, COUNT(*) AS times
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

salary | times
58000 | 2

Delete duplicates, keeping the lowest id

CREATE TABLE signups (id INT PRIMARY KEY, email VARCHAR(40));
INSERT INTO signups VALUES (1, 'a@x.in'), (2, 'b@x.in'), (3, 'a@x.in'), (4, 'a@x.in');
DELETE FROM signups
WHERE id NOT IN (SELECT MIN(id) FROM signups GROUP BY email);
SELECT * FROM signups ORDER BY id;

id | email
1 | a@x.in
2 | b@x.in

3. Employees earning more than their department's average

SELECT name, dept_id, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

name | dept_id | salary
Asha | 10 | 95000
Meera | 10 | 88000

4. Department-wise count, including empty departments

SELECT d.dept_name, COUNT(e.emp_id) AS staff
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY staff DESC, d.dept_name;

dept_name | staff
Engineering | 3
Sales | 2
HR | 1
Marketing | 0

COUNT(e.emp_id) (not COUNT(*)) gives 0 for Marketing, because the unmatched row has NULL there.

5. Highest salary in each department, with the name

SELECT d.dept_name, e.name, e.salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE dept_id = e.dept_id)
ORDER BY d.dept_name, e.name;

dept_name | name | salary
Engineering | Asha | 95000
HR | Om | 45000
Sales | Kabir | 58000
Sales | Neha | 58000

(Both Sales employees are returned because they tie — mention this in the interview!)

6. Managers with the number of people reporting to them

SELECT m.name AS manager, COUNT(*) AS reports
FROM employees e JOIN employees m ON e.manager_id = m.emp_id
GROUP BY m.name
ORDER BY reports DESC;

manager | reports
Asha | 4
Neha | 2

7. Employees who joined in the last N years / a given year

SELECT name, joined FROM employees
WHERE joined >= '2022-01-01' AND joined < '2024-01-01'
ORDER BY joined;

name | joined
Kabir | 2022-03-01
Om | 2023-06-20

(A date range keeps the query index-friendly — better than YEAR(joined) IN (2022, 2023).)

8. Percentage of payroll per department

SELECT dept_id, SUM(salary) AS payroll,
       ROUND(100.0 * SUM(salary) / (SELECT SUM(salary) FROM employees), 1) AS pct
FROM employees
GROUP BY dept_id
ORDER BY payroll DESC;

dept_id | payroll | pct
10 | 255000 | 55.9
20 | 116000 | 25.4
30 | 45000 | 9.9
NULL | 40000 | 8.8

100.0 * forces decimal division (integer division would give 0 in some databases).

How to answer SQL questions in interviews

1. Restate the question with the tables and columns.
2. Say the approach first ("group by department, then filter with HAVING").
3. Mention edge cases: ties, NULLs, empty groups.
4. Write clean SQL with aliases and line breaks.

Common mistakes

• Using COUNT(*) after a LEFT JOIN when you want zeros for empty groups.
• Forgetting ties in "highest per group" questions.
• Integer division in percentages.

Quick recap

• Second highest: MAX below MAX, or DENSE_RANK.
• Duplicates: GROUP BY … HAVING COUNT(*) > 1; delete keeping MIN(id).
• Above-average: correlated subquery.
• Include empty groups: LEFT JOIN + COUNT(column).