SQL Interview Questions Explained

Almost every interview for a developer, data analyst or QA role in India includes SQL: a written test, a shared screen, or a whiteboard. The questions repeat across companies because each one checks a specific skill: handling ties and NULL, joining a table to itself, counting rows after a join, or grouping consecutive values.

This lesson works through the classics on the practice database. For each one you will see a clear solution, the edge case the interviewer is really testing, and how to explain your answer. Treat it as revision: every technique links back to the lesson where it is taught in full.

How to answer any SQL question

Interviewers judge your thinking as much as your final query. A good routine:

1. Restate the question and ask about edge cases. "If two people share the highest salary, is the second highest the next different salary?" "Do cancelled orders count?"
2. Name the tables and the grain, meaning what one row of your result represents: "one row per department".
3. Build in steps. Write a small query, check it, then wrap it in a CTE and add the next step.
4. Test the edge cases aloud: ties, NULL, empty groups, a group with one row.
5. Mention performance briefly: which index would help on a large table.

Second highest salary

The most famous question. Three common answers:

-- 1. The largest salary below the maximum
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. Distinct salaries, skip one, take one
SELECT (SELECT DISTINCT salary FROM employees
        ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_highest;

-- 3. The same idea when no answer exists
SELECT (SELECT DISTINCT salary FROM employees
        WHERE department = 'Management'
        ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_in_management;

second_highest
--------------
180000
(1 row)

second_highest
--------------
180000
(1 row)

second_in_management
--------------------
NULL
(1 row)

The traps being tested:

• Ties. Without DISTINCT, two people on ₹2,50,000 would make the "second highest" also ₹2,50,000.
• No answer. Management has one person. A bare SELECT ... LIMIT 1 OFFSET 1 would return no rows, which many applications handle badly. Wrapping it in SELECT (...) AS ... turns "no row" into a single NULL, which is usually what the question wants.
• Nth highest. Answer 2 generalises to OFFSET n - 1. In MySQL, LIMIT and OFFSET accept only numbers, placeholders or (inside stored programs) integer variables, not an expression such as n - 1, so calculate the offset first or use the window version below.

Nth highest in each group

"Show the second highest earner in each department." This is a top N per group problem. DENSE_RANK() handles ties the way the question usually means:

WITH ranked AS (
  SELECT department, first_name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS pos
  FROM employees
)
SELECT department, first_name, salary
FROM ranked
WHERE pos = 2
ORDER BY department;

department  | first_name | salary
------------+------------+-------
Engineering | Amit       | 120000
Sales       | Rahul      | 45000
Support     | Tanvi      | 38000
(3 rows)

Management has no second earner, so it does not appear. If the interviewer wants every department listed with NULL for missing ones, LEFT JOIN the result to the list of departments. Explain your choice of ranking function: ROW_NUMBER() would pick one person arbitrarily among ties, RANK() could skip position 2 entirely after a tie for first, and DENSE_RANK() never skips.

Employees who earn more than their manager

The question tests a self join: the same table used twice under two aliases (see Self & Cross Joins). Nobody in the practice data out-earns their manager, so this example first gives Amit a raise:

UPDATE employees SET salary = 190000 WHERE employee_id = 3;

SELECT e.first_name AS employee, e.salary,
       m.first_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary;

employee | salary | manager | manager_salary
---------+--------+---------+---------------
Amit     | 190000 | Neha    | 180000
(1 row)

Read the join aloud: "for each employee e, find the row m whose ID is e's manager ID". The inner join quietly drops Rajesh, who has no manager. That is correct here, but say it out loud: it shows you know what an inner join removes.

How many rows does this join return?

A favourite trick question gives two tiny tables and asks for row counts without running anything. It tests duplicates and NULL in join keys:

CREATE TABLE a (val INTEGER);
CREATE TABLE b (val INTEGER);
INSERT INTO a (val) VALUES (1), (1), (2), (NULL);
INSERT INTO b (val) VALUES (1), (1), (3), (NULL);

SELECT (SELECT COUNT(*) FROM a JOIN b ON a.val = b.val) AS inner_rows,
       (SELECT COUNT(*) FROM a LEFT JOIN b ON a.val = b.val) AS left_rows,
       (SELECT COUNT(*) FROM a CROSS JOIN b) AS cross_rows;

inner_rows | left_rows | cross_rows
-----------+-----------+-----------
4          | 6         | 16
(1 row)

• Inner join: 4. Each of the two 1s in a matches each of the two 1s in b: 2 times 2. The NULLs do not match each other, because NULL = NULL is unknown (see NULL Values).
• Left join: 6. The four matches, plus the 2 and the NULL from a, each kept once with NULL on the right.
• Cross join: 16. Every row with every row: 4 times 4.

The real-world lesson: joining on a column with duplicates multiplies rows, which is why reports use COUNT(DISTINCT ...) after joins.

Customers who bought from both categories

"Find customers who bought at least one Accessories product and at least one Storage product." A beginner writes WHERE category = 'Accessories' AND category = 'Storage', which can never be true for one row. The standard pattern filters to the wanted categories, groups by customer, and requires both to be present:

SELECT c.first_name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
  AND p.category IN ('Accessories', 'Storage')
GROUP BY c.customer_id, c.first_name
HAVING COUNT(DISTINCT p.category) = 2
ORDER BY c.first_name;

first_name
----------
Aarav
(1 row)

Aarav bought a mouse and a pen drive in the same order. COUNT(DISTINCT p.category) = 2 scales to "all five categories" by changing the list and the number. Database people call this relational division. An alternative uses two EXISTS conditions (see EXISTS, ANY & ALL).

Gaps and islands: consecutive months

"For each customer, find streaks of consecutive months with a purchase." Problems about consecutive values are called gaps and islands: the islands are runs of consecutive values, the gaps are the holes between them. The trick: number each customer's active months with ROW_NUMBER(). Inside a streak, the month number and the row number both go up by one, so their difference stays constant, and that difference labels the island:

WITH active AS (
  SELECT DISTINCT customer_id,
         SUBSTR(order_date, 1, 7) AS month,
         SUBSTR(order_date, 1, 4) * 12 + SUBSTR(order_date, 6, 2) AS month_no
  FROM orders
  WHERE status <> 'cancelled'
)
SELECT customer_id, month, month_no,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month_no) AS rn,
       month_no - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month_no) AS island
FROM active
WHERE customer_id IN (1, 2)
ORDER BY customer_id, month_no;

customer_id | month   | month_no | rn | island
------------+---------+----------+----+-------
1           | 2025-01 | 24301    | 1  | 24300
1           | 2025-02 | 24302    | 2  | 24300
1           | 2025-05 | 24305    | 3  | 24302
2           | 2025-01 | 24301    | 1  | 24300
2           | 2025-03 | 24303    | 2  | 24301
(5 rows)

Aarav's January and February share island 24300, so they form one streak; May starts a new island. year * 12 + month turns each month into a number that increases by one each month, as in Cohort & Retention Analysis. To list the streaks, group by customer_id, island and take MIN(month), MAX(month) and COUNT(*). With daily data, use day numbers instead, for example TO_DAYS(order_date) in MySQL.

Quick-fire concept questions

Key points

• Clarify ties, NULL and cancelled rows before writing a query; interviewers test edge cases.
• Second highest: DISTINCT with LIMIT 1 OFFSET 1 inside a scalar subquery returns NULL when there is no answer.
• Nth per group: DENSE_RANK() in a CTE, then filter in the outer query.
• Self joins compare rows of one table, such as employees and their managers.
• Duplicate join keys multiply rows; NULL keys never match.
• "Has both X and Y": filter to X and Y, group, and require COUNT(DISTINCT ...) to equal 2.
• Gaps and islands: value minus ROW_NUMBER() is constant within a run of consecutive values.

SELECT department, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC
LIMIT 1;

SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  JOIN customers c ON c.customer_id = o.customer_id
  WHERE oi.product_id = p.product_id
    AND o.status <> 'cancelled'
    AND c.state = 'Haryana'
)
ORDER BY p.product_name;