Top SQL Interview Questions with Answers

Developer, analyst and tester interviews tend to ask the same core SQL questions, because each one tests a specific skill: grouping, joins, subqueries, ranking or NULL handling. Here are the most common ones, solved against the practice database, with the reasoning interviewers want to hear. Try each one yourself before opening the answer.

1. Second highest salary

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

+----------------+
| second_highest |
+----------------+
|         180000 |
+----------------+

Why it works: the subquery finds the maximum; the outer query takes the maximum of what's left. It returns NULL (not an empty result) if there's no second salary – a detail interviewers like.

2. Nth highest salary (handles ties)

WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
)
SELECT DISTINCT salary AS fourth_highest FROM ranked WHERE rnk = 4;

+----------------+
| fourth_highest |
+----------------+
|         120000 |
+----------------+

The distinct salaries are ₹2,50,000, ₹1,80,000, ₹1,40,000, ₹1,20,000 and ₹95,000. DENSE_RANK gives both ₹1,20,000 salaries rank 4 and ₹95,000 rank 5. With ROW_NUMBER, rows 4 and 5 would both be ₹1,20,000 and "5th highest" would wrongly return it again – explain this choice in the interview.

3. Highest-paid employee in each department

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r = 1
ORDER BY department;

+-------------+-------------+--------+
| department  | name        | salary |
+-------------+-------------+--------+
| Engineering | Rohit Mehta | 180000 |
| HR          | Kavya Reddy |  90000 |
| Management  | Anita Desai | 250000 |
| Sales       | Farhan Ali  | 140000 |
+-------------+-------------+--------+

4. Employees earning more than their department's average

SELECT name, department, salary, ROUND(dept_avg) AS dept_avg
FROM (
  SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg
  FROM employees
) t
WHERE salary > dept_avg
ORDER BY department;

+-------------+-------------+--------+----------+
| name        | department  | salary | dept_avg |
+-------------+-------------+--------+----------+
| Rohit Mehta | Engineering | 180000 |   128750 |
| Kavya Reddy | HR          |  90000 |    77500 |
| Farhan Ali  | Sales       | 140000 |   101000 |
+-------------+-------------+--------+----------+

5. Find duplicate values

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

+--------+-------+
| salary | times |
+--------+-------+
| 120000 |     2 |
+--------+-------+

A follow-up is often "delete the duplicates but keep one": number the rows in each duplicate group with ROW_NUMBER() OVER (PARTITION BY dup_columns ORDER BY id) and delete rows where the number is greater than 1.

6. Customers who never ordered

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

+------------+
| name       |
+------------+
| Vikram Das |
+------------+

Mention the alternatives (NOT EXISTS) and the NOT IN + NULL trap from chapter 6.

7. Employee and manager names

SELECT e.name AS employee, COALESCE(m.name, '— (top)') AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
WHERE e.department IN ('Management', 'HR')
ORDER BY e.emp_id;

+--------------+-------------+
| employee     | manager     |
+--------------+-------------+
| Anita Desai  | — (top)     |
| Kavya Reddy  | Anita Desai |
| Imran Shaikh | Kavya Reddy |
+--------------+-------------+

8. Top customer by spend

SELECT c.name, SUM(i.quantity * i.unit_price) AS spent
FROM customers c
JOIN orders o      ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.name
ORDER BY spent DESC
LIMIT 1;

+-----------+---------+
| name      | spent   |
+-----------+---------+
| Kiran Rao | 5795.00 |
+-----------+---------+

Good follow-up answer: LIMIT 1 hides ties – use RANK() if two customers could share the top spot.

9. Running total of delivered revenue

SELECT o.order_id, o.order_date,
       SUM(i.quantity * i.unit_price) AS order_total,
       SUM(SUM(i.quantity * i.unit_price)) OVER (ORDER BY o.order_date, o.order_id) AS running_total
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'delivered'
GROUP BY o.order_id, o.order_date
ORDER BY o.order_date, o.order_id;

+----------+------------+-------------+---------------+
| order_id | order_date | order_total | running_total |
+----------+------------+-------------+---------------+
|      101 | 2025-08-02 |      839.00 |        839.00 |
|      102 | 2025-08-05 |     2499.00 |       3338.00 |
|      103 | 2025-08-19 |      800.00 |       4138.00 |
|      105 | 2025-09-07 |     3697.00 |       7835.00 |
|      107 | 2025-10-03 |     1299.00 |       9134.00 |
|      110 | 2025-11-02 |      700.00 |       9834.00 |
|      112 | 2025-11-06 |     1000.00 |      10834.00 |
+----------+------------+-------------+---------------+

SUM(SUM(…)) OVER looks odd but is correct: the inner SUM is the GROUP BY aggregate per order, the outer SUM is a window function over those order totals.

10. Each category's share of revenue

SELECT COALESCE(cat.name, 'Uncategorised') AS category,
       SUM(i.quantity * i.unit_price) AS revenue,
       ROUND(100 * SUM(i.quantity * i.unit_price) / SUM(SUM(i.quantity * i.unit_price)) OVER (), 1) AS pct
FROM order_items i
JOIN products p ON p.product_id = i.product_id
LEFT JOIN categories cat ON cat.category_id = p.category_id
GROUP BY cat.name
ORDER BY revenue DESC;

+---------------+----------+------+
| category      | revenue  | pct  |
+---------------+----------+------+
| Electronics   | 11691.00 | 69.3 |
| Books         |  3050.00 | 18.1 |
| Stationery    |  1230.00 |  7.3 |
| Uncategorised |   899.00 |  5.3 |
+---------------+----------+------+

11. Orders per month, including months with none

WITH RECURSIVE months AS (
  SELECT DATE('2025-07-01') AS m
  UNION ALL
  SELECT m + INTERVAL 1 MONTH FROM months WHERE m < '2025-11-01'
)
SELECT DATE_FORMAT(months.m, '%Y-%m') AS month, COUNT(o.order_id) AS orders
FROM months
LEFT JOIN orders o ON o.order_date >= months.m AND o.order_date < months.m + INTERVAL 1 MONTH
GROUP BY months.m
ORDER BY months.m;

+---------+--------+
| month   | orders |
+---------+--------+
| 2025-07 |      0 |
| 2025-08 |      3 |
| 2025-09 |      3 |
| 2025-10 |      3 |
| 2025-11 |      3 |
+---------+--------+

12. Theory questions to prepare

Keep practising

• SQL queries asked in interviews
• Advanced SQL coding questions
• SQL tricky output questions
• Indexing and query optimisation
• NoSQL and MongoDB

Summary