Chapter 35 of 35

Top SQL Interview Queries

This final chapter brings the whole tutorial together. We will solve the SQL questions that come up again and again in placement tests, technical interviews and online assessments: the Nth highest salary, the top earner per department, employees who earn more than their manager, customers with no orders, duplicates, running totals, month-over-month growth, pivots, hierarchies and more.

For each question you will see a clean solution, the real MySQL 8.4 output on our company_db sample database, a short explanation of why it works, and, where it matters, the alternative approaches and traps an interviewer is looking for. Each question names the chapter where the underlying idea was taught, so you can go back if something feels unfamiliar.

Interview tip: before writing a query, say your assumptions out loud. "Should duplicates count once?", "Should employees without a department be included?", "What should I return if there is no answer?" Clarifying edge cases is often worth as much as the query itself.

A reminder of the data: 10 employees (Priya and Rohan both earn 95,000; Ishita has no department), 5 departments (Research has no employees), 5 customers (Siddharth has no orders), and 8 orders (order 1008 is a guest order with customer_id NULL; order 1003 is cancelled).


Q1. Find the Second-Highest Salary

The most famous SQL interview question. There are three standard answers.

Approach 1: MAX below the MAX (Subqueries chapter)

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
+----------------+
| second_highest |
+----------------+
|      110000.00 |
+----------------+
1 row in set (0.00 sec)

The inner query finds 150,000; the outer query finds the largest salary below it. It handles duplicates naturally, and returns NULL if everyone earns the same, because MAX of no rows is NULL.

Approach 2: DISTINCT + LIMIT/OFFSET (ORDER BY & LIMIT, DISTINCT chapters)

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

This also returns 110,000. But if there is no second salary it returns no row instead of NULL. Many judges (such as LeetCode) expect NULL, which you get by wrapping it as a scalar subquery:

SELECT (SELECT DISTINCT salary FROM employees
        ORDER BY salary DESC LIMIT 1 OFFSET 10) AS eleventh_highest;
+------------------+
| eleventh_highest |
+------------------+
|             NULL |
+------------------+
1 row in set (0.00 sec)

Approach 3: DENSE_RANK (Ranking Functions chapter) generalises best, as Q2 shows.


Q2. Find the Nth Highest Salary

Take N = 4. First, the classic trap: forgetting DISTINCT.

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 3;   -- wrong
+----------+
| salary   |
+----------+
| 95000.00 |
+----------+
1 row in set (0.00 sec)

The sorted salaries are 150000, 110000, 95000, 95000, 82000, ... The fourth row is the second 95,000, but the fourth highest distinct salary is 82,000. Adding DISTINCT fixes it:

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 3;
+----------+
| salary   |
+----------+
| 82000.00 |
+----------+
1 row in set (0.00 sec)

The window-function version, which also returns who earns it:

WITH ranked AS (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT name, salary FROM ranked WHERE dr = 4;     -- Arjun Reddy, 82000.00

And the version that works even without window functions or LIMIT (asked to test your understanding of correlated subqueries). "A salary is the Nth highest if exactly N distinct salaries are greater than or equal to it." With N = 3:

SELECT DISTINCT e1.salary
FROM employees e1
WHERE 3 = (SELECT COUNT(DISTINCT e2.salary)
           FROM employees e2
           WHERE e2.salary >= e1.salary);
+----------+
| salary   |
+----------+
| 95000.00 |
+----------+
1 row in set (0.00 sec)

For 95,000, the distinct salaries ≥ it are 150000, 110000 and 95000: three of them. This runs the subquery for every row (O(n²)), so mention that DENSE_RANK is the efficient choice.


Q3. Highest-Paid Employee in Each Department

Returning just MAX(salary) per department is easy with GROUP BY. The real question is to return the employee's name too, and you cannot simply add name to a GROUP BY query (ONLY_FULL_GROUP_BY rejects it, as the GROUP BY & HAVING chapter explained). Two correct solutions:

-- Correlated subquery
SELECT e.dept_id, e.name, e.salary
FROM employees e
WHERE e.salary = (SELECT MAX(x.salary) FROM employees x WHERE x.dept_id = e.dept_id)
ORDER BY e.dept_id;

-- Window function
WITH r AS (
  SELECT name, dept_id, salary,
         RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
  FROM employees
  WHERE dept_id IS NOT NULL
)
SELECT dept_id, name, salary FROM r WHERE rk = 1 ORDER BY dept_id;

Both return:

+---------+--------------+-----------+
| dept_id | name         | salary    |
+---------+--------------+-----------+
|      10 | Aarav Sharma | 150000.00 |
|      20 | Sneha Kapoor | 110000.00 |
|      30 | Karan Patel  |  70000.00 |
|      40 | Arjun Reddy  |  82000.00 |
+---------+--------------+-----------+
4 rows in set (0.00 sec)

Both return all employees who tie for the top salary in a department. Use RANK (or the subquery) when ties should all appear, and ROW_NUMBER when exactly one row per department is required. "Top 3 per department" is the same query with rk <= 3.


Q4. Employees Who Earn More Than Their Manager

The manager is just another row in the same table, so this is a self join (SELF JOIN & CROSS JOIN chapter): one copy of the table plays "employee", the other plays "manager".

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

On our data this returns Empty set: every manager earns more than their reports. To see it working, we gave Meera a raise to 75,000 in a copy of the database (her manager Karan earns 70,000) and ran the same query:

+------------+----------+-------------+----------------+
| employee   | salary   | manager     | manager_salary |
+------------+----------+-------------+----------------+
| Meera Nair | 75000.00 | Karan Patel |       70000.00 |
+------------+----------+-------------+----------------+
1 row in set (0.00 sec)

An inner join is right here: Aarav has no manager, so he cannot earn more than one. To list everyone with their manager's name, including Aarav (manager shown as NULL), use LEFT JOIN instead.


Q5. Customers Who Never Placed an Order

This question hides one of the nastiest traps in SQL. The obvious answer:

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
Empty set (0.00 sec)

Empty! But Siddharth (105) has no orders. The cause is the guest order 1008, whose customer_id is NULL. The subquery's list contains a NULL, and:

SELECT 105 NOT IN (101, 102, NULL) AS result;
+--------+
| result |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL, and x <> NULL is unknown (NULL Values chapter). So the condition is never TRUE for any customer, and NOT IN returns nothing as soon as the subquery contains one NULL.

The safe solutions are NOT EXISTS (Correlated Subqueries & EXISTS chapter) or a LEFT JOIN anti-join:

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- or
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
+-------------+---------------+
| customer_id | name          |
+-------------+---------------+
|         105 | Siddharth Rao |
+-------------+---------------+
1 row in set (0.00 sec)

(If you must use NOT IN, add WHERE customer_id IS NOT NULL inside the subquery.) The same pattern answers "departments with no employees": a LEFT JOIN from departments to employees with WHERE e.emp_id IS NULL returns Research.


Q6. Employee Count per Department, Including Empty Departments

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY headcount DESC, d.dept_name;
+-------------+-----------+
| dept_name   | headcount |
+-------------+-----------+
| Engineering |         3 |
| Sales       |         3 |
| HR          |         2 |
| Marketing   |         1 |
| Research    |         0 |
+-------------+-----------+
5 rows in set (0.00 sec)

Two details make this correct. The LEFT JOIN keeps Research even though no employee matches it. And COUNT(e.emp_id) counts only non-NULL values. If you write COUNT(*) instead, Research shows 1, because the LEFT JOIN produced one row for it (with NULLs in the employee columns) and COUNT(*) counts rows. We ran it: Research came out as 1. This difference between COUNT(*) and COUNT(column) is a favourite follow-up question.


Q7. Find Duplicate Values

Which salaries appear more than once? (GROUP BY & HAVING chapter)

SELECT salary, COUNT(*) AS how_many
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;
+----------+----------+
| salary   | how_many |
+----------+----------+
| 95000.00 |        2 |
+----------+----------+
1 row in set (0.00 sec)

To list the actual rows that are duplicated, join back to the result:

SELECT e.name, e.salary
FROM employees e
JOIN (SELECT salary FROM employees GROUP BY salary HAVING COUNT(*) > 1) d
  ON e.salary = d.salary;
+-------------+----------+
| name        | salary   |
+-------------+----------+
| Priya Iyer  | 95000.00 |
| Rohan Mehta | 95000.00 |
+-------------+----------+
2 rows in set (0.00 sec)

For duplicates across several columns (for example the same name and phone), group by all of those columns.


Q8. Delete Duplicate Rows, Keeping One

We use a small demo table in a copy of the database, where Rahul appears three times and Kavya twice:

CREATE TABLE contacts (id INT PRIMARY KEY, name VARCHAR(50), phone VARCHAR(15));
INSERT INTO contacts VALUES
 (1,'Rahul','98450-11111'), (2,'Kavya','98450-22222'), (3,'Rahul','98450-11111'),
 (4,'Neha','98450-33333'),  (5,'Kavya','98450-22222'), (6,'Rahul','98450-11111');

Approach 1: self-join DELETE (MySQL's multi-table DELETE syntax). Delete every row for which an identical row with a smaller id exists:

DELETE c1
FROM contacts c1
JOIN contacts c2
  ON  c1.name  = c2.name
  AND c1.phone = c2.phone
  AND c1.id    > c2.id;
Query OK, 3 rows affected (0.01 sec)
SELECT * FROM contacts;
+----+-------+-------------+
| id | name  | phone       |
+----+-------+-------------+
|  1 | Rahul | 98450-11111 |
|  2 | Kavya | 98450-22222 |
|  4 | Neha  | 98450-33333 |
+----+-------+-------------+
3 rows in set (0.00 sec)

The row with the lowest id in each group has no smaller twin, so it survives. Rows 3, 5 and 6 are deleted.

Approach 2: ROW_NUMBER, shown in the Ranking Functions chapter: number rows with PARTITION BY name, phone ORDER BY id and delete those with a number above 1.

The MySQL trap: the version many people write first fails:

DELETE FROM contacts
WHERE id NOT IN (SELECT MIN(id) FROM contacts GROUP BY name, phone);
ERROR 1093 (HY000): You can't specify target table 'contacts' for update in FROM clause

MySQL does not allow a DELETE or UPDATE to read the same table in a subquery. Wrapping the subquery in a derived table (SELECT id FROM (SELECT MIN(id) AS id ... ) AS keep) makes MySQL materialise it first, which is allowed. In an interview, the best final step is also to suggest a UNIQUE constraint so duplicates cannot come back.


Q9. Employees Earning More Than Their Department Average

SELECT name, dept_id, salary, ROUND(dept_avg, 2) AS dept_avg
FROM (
  SELECT name, dept_id, salary,
         AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
  FROM employees
  WHERE dept_id IS NOT NULL
) t
WHERE salary > dept_avg
ORDER BY dept_id;
+--------------+---------+-----------+-----------+
| name         | dept_id | salary    | dept_avg  |
+--------------+---------+-----------+-----------+
| Aarav Sharma |      10 | 150000.00 | 113333.33 |
| Sneha Kapoor |      20 | 110000.00 |  76666.67 |
| Karan Patel  |      30 |  70000.00 |  59000.00 |
+--------------+---------+-----------+-----------+
3 rows in set (0.00 sec)

The window function puts each department's average on every row without collapsing rows (Window Functions chapter), so we can compare and also display it. The correlated-subquery version gives the same three people:

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

Marketing does not appear because Arjun is its only employee and cannot be above his own average.


Q10. Departments Whose Average Salary Is Above the Company Average

SELECT d.dept_name, ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees);
+-------------+------------+
| dept_name   | avg_salary |
+-------------+------------+
| Engineering |  113333.33 |
+-------------+------------+
1 row in set (0.00 sec)

The company average is 825,000 / 10 = 82,500. Marketing's average (82,000) is just below it. The point being tested: a condition on an aggregate belongs in HAVING, not WHERE.


Q11. Running Total

A cumulative sum of order amounts in date order, excluding the cancelled order:

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total
FROM orders
WHERE status <> 'Cancelled';
+----------+------------+---------+---------------+
| order_id | order_date | amount  | running_total |
+----------+------------+---------+---------------+
|     1001 | 2024-01-15 | 2500.00 |       2500.00 |
|     1002 | 2024-01-20 | 4800.00 |       7300.00 |
|     1004 | 2024-02-14 | 7300.00 |      14600.00 |
|     1005 | 2024-03-01 |  950.00 |      15550.00 |
|     1006 | 2024-03-11 | 3100.00 |      18650.00 |
|     1007 | 2024-03-28 | 5600.00 |      24250.00 |
|     1008 | 2024-04-02 |  800.00 |      25050.00 |
+----------+------------+---------+---------------+
7 rows in set (0.00 sec)

With an ORDER BY inside OVER, SUM adds up all rows from the start up to the current row. We added order_id as a tie-breaker: with the default frame, rows sharing the same order_date would otherwise all receive the same total. Add PARTITION BY customer_id for a running total per customer.


Q12. Month-over-Month Revenue Change

WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS revenue
  FROM orders
  WHERE status <> 'Cancelled'
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))
                 / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM m
ORDER BY month;
+---------+---------+------------+------------+
| month   | revenue | prev_month | pct_change |
+---------+---------+------------+------------+
| 2024-01 | 7300.00 |       NULL |       NULL |
| 2024-02 | 7300.00 |    7300.00 |        0.0 |
| 2024-03 | 9650.00 |    7300.00 |       32.2 |
| 2024-04 |  800.00 |    9650.00 |      -91.7 |
+---------+---------+------------+------------+
4 rows in set (0.00 sec)

The CTE aggregates by month (Date & Time Functions, CTE chapters); LAG() fetches the previous row's value. Check March: (9650 − 7300) / 7300 × 100 = 32.2%. January has no previous month, so LAG returns NULL and the arithmetic stays NULL rather than failing. Note that a month with no orders at all would simply be missing; a full report would join to a calendar of months.


Q13. Pivot: Rows into Columns

Show, for each customer, how many orders are in each status, one column per status. MySQL has no PIVOT keyword; we use conditional aggregation (CASE Expression chapter):

SELECT customer_id,
  SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'Shipped'   THEN 1 ELSE 0 END) AS shipped,
  SUM(CASE WHEN status = 'Pending'   THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
  COUNT(*) AS total
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY customer_id;
+-------------+-----------+---------+---------+-----------+-------+
| customer_id | delivered | shipped | pending | cancelled | total |
+-------------+-----------+---------+---------+-----------+-------+
|         101 |         1 |       0 |       1 |         1 |     3 |
|         102 |         2 |       0 |       0 |         0 |     2 |
|         103 |         1 |       0 |       0 |         0 |     1 |
|         104 |         0 |       1 |       0 |         0 |     1 |
+-------------+-----------+---------+---------+-----------+-------+
4 rows in set (0.00 sec)

Each CASE turns a row into a 1 or 0 for its column, and SUM adds them up per customer. Replace THEN 1 with THEN amount to pivot money instead of counts.


Q14. Percentage of Total

What share of the total payroll does each department take?

SELECT COALESCE(d.dept_name, 'No department') AS department,
       SUM(e.salary) AS total_salary,
       ROUND(100 * SUM(e.salary) / SUM(SUM(e.salary)) OVER (), 1) AS pct_of_payroll
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY total_salary DESC;
+---------------+--------------+----------------+
| department    | total_salary | pct_of_payroll |
+---------------+--------------+----------------+
| Engineering   |    340000.00 |           41.2 |
| Sales         |    230000.00 |           27.9 |
| HR            |    118000.00 |           14.3 |
| Marketing     |     82000.00 |            9.9 |
| No department |     55000.00 |            6.7 |
+---------------+--------------+----------------+
5 rows in set (0.00 sec)

SUM(SUM(e.salary)) OVER () looks strange but is logical: the inner SUM is the GROUP BY aggregate (one value per department), and the outer SUM is a window function over all the grouped rows, giving the grand total 825,000 on every row. Engineering: 340,000 / 825,000 = 41.2%. The LEFT JOIN from employees keeps Ishita's salary in the total. The alternative is a scalar subquery: SUM(e.salary) / (SELECT SUM(salary) FROM employees).


Q15. Customers Whose Orders Are All Delivered

"All" questions are tricky, because SQL conditions check one row at a time. Turn it around: a customer qualifies if they have zero orders that are not delivered.

SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
HAVING SUM(o.status <> 'Delivered') = 0;
+-------------+--------------+
| customer_id | name         |
+-------------+--------------+
|         102 | Kavya Menon  |
|         103 | Aditya Joshi |
+-------------+--------------+
2 rows in set (0.00 sec)

In MySQL a comparison returns 1 (true) or 0 (false), so SUM(o.status <> 'Delivered') counts the non-delivered orders. (A portable version is SUM(CASE WHEN o.status <> 'Delivered' THEN 1 ELSE 0 END).) The inner JOIN matters: Siddharth, who has no orders, would otherwise "vacuously" qualify. Rahul has a cancelled and a pending order, and Neha's order is only shipped, so both are excluded.

A related favourite, "customers with more than one order", is a plain GROUP BY ... HAVING COUNT(*) > 1; on our data it returns Rahul (3) and Kavya (2).


Q16. Show the Reporting Hierarchy

"Print the management chain of every employee." The depth of the tree is not known in advance, so a fixed number of self joins cannot do it. A recursive CTE can (Common Table Expressions chapter):

WITH RECURSIVE chain AS (
  -- anchor: the top of the tree
  SELECT emp_id, name, manager_id, 0 AS level,
         CAST(name AS CHAR(200)) AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- recursive step: people who report to someone already found
  SELECT e.emp_id, e.name, e.manager_id, c.level + 1,
         CONCAT(c.path, ' > ', e.name)
  FROM employees e
  JOIN chain c ON e.manager_id = c.emp_id
)
SELECT emp_id, level, path FROM chain ORDER BY path;
+--------+-------+--------------------------------------------+
| emp_id | level | path                                       |
+--------+-------+--------------------------------------------+
|      1 |     0 | Aarav Sharma                               |
|      9 |     1 | Aarav Sharma > Arjun Reddy                 |
|     10 |     1 | Aarav Sharma > Ishita Gupta                |
|      7 |     1 | Aarav Sharma > Karan Patel                 |
|      8 |     2 | Aarav Sharma > Karan Patel > Meera Nair    |
|      2 |     1 | Aarav Sharma > Priya Iyer                  |
|      3 |     1 | Aarav Sharma > Rohan Mehta                 |
|      4 |     1 | Aarav Sharma > Sneha Kapoor                |
|      6 |     2 | Aarav Sharma > Sneha Kapoor > Ananya Das   |
|      5 |     2 | Aarav Sharma > Sneha Kapoor > Vikram Singh |
+--------+-------+--------------------------------------------+
10 rows in set (0.00 sec)

The anchor finds Aarav (level 0). Each recursive step adds the employees whose manager was found in the previous step, extending the path. It stops when a step finds nobody new. The CAST(... AS CHAR(200)) matters: a recursive CTE's column types are fixed by the anchor query. Without the cast, path would be only as wide as name (VARCHAR(50)); our longest path (42 characters) happens to fit, but in a deeper tree MySQL stops with ERROR 1406 (22001): Data too long for column 'path' at row 1, which we confirmed by making the paths longer.


Q17. Quick-Fire Questions

These short ones appear in written tests:

Question

Query idea

Rows with even ids

SELECT * FROM employees WHERE emp_id % 2 = 0; (returns ids 2, 4, 6, 8 and 10; add ORDER BY emp_id if you need them in order, because without it MySQL may return them in any order). For "even-numbered rows" when ids have gaps, use ROW_NUMBER() instead of the id.

First and last person hired

WHERE hire_date = (SELECT MIN(hire_date) FROM employees) OR hire_date = (SELECT MAX(hire_date) FROM employees) → Sneha Kapoor (2017-11-20) and Ishita Gupta (2024-01-10)

Hires per year

SELECT YEAR(hire_date) AS yr, COUNT(*) FROM employees GROUP BY yr ORDER BY yr; (2019 and 2020 have 2 each)

Names starting with 'A'

WHERE name LIKE 'A%'

Employees without an email

WHERE email IS NULL (never = NULL) → Meera Nair

Copy a table's structure without data

CREATE TABLE t2 LIKE t1; (or CREATE TABLE t2 AS SELECT * FROM t1 WHERE 1 = 0;, which does not copy indexes)

Latest order per customer

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1 (Ranking Functions chapter)


Patterns to Recognise

Most interview questions are one of a handful of patterns in disguise. Once you spot the pattern, the query almost writes itself:

The question says...

Reach for...

"Nth highest / top N"

DENSE_RANK (or DISTINCT + LIMIT/OFFSET)

"... in each group / per department"

PARTITION BY with a ranking function, or GROUP BY

"... compared with their manager / another row of the same table"

Self join

"... who never / with no ..."

NOT EXISTS or LEFT JOIN ... IS NULL (avoid NOT IN with nullable columns)

"... more than the average"

Subquery, correlated subquery or AVG() OVER

"duplicates"

GROUP BY ... HAVING COUNT(*) > 1; delete with self join or ROW_NUMBER

"running / cumulative / previous / next"

Window functions: SUM() OVER, LAG, LEAD

"one column per category"

Conditional aggregation: SUM(CASE ...)

"all of their ... are ..."

Count the exceptions and require zero (HAVING), or NOT EXISTS

"hierarchy / chain / tree"

Recursive CTE


Common Mistakes in Interviews

  • Forgetting DISTINCT in Nth-highest queries, so duplicate values shift the answer.

  • Using NOT IN with a subquery that can return NULL.

  • Using COUNT(*) after a LEFT JOIN when you need COUNT(column).

  • Putting an aggregate condition in WHERE instead of HAVING.

  • Filtering on a window function in the same query level instead of an outer query.

  • Selecting a non-grouped column (like name) together with MAX(salary) in a GROUP BY query.

  • Forgetting that inner joins drop unmatched rows, such as Ishita (no department) or the guest order.

  • Not considering ties and empty results, and not saying what the query returns in those cases.


Summary

  • Nth highest: DENSE_RANK() = N, or DISTINCT ... LIMIT 1 OFFSET N-1 wrapped in a scalar subquery to return NULL when there is no answer.

  • Top per group: RANK() or ROW_NUMBER() with PARTITION BY, filtered in an outer query.

  • Same-table comparisons (employee vs manager) use a self join; hierarchies use a recursive CTE.

  • "Never / no" questions: NOT EXISTS or LEFT JOIN ... IS NULL; NOT IN breaks on NULLs.

  • Duplicates: find with GROUP BY ... HAVING; delete with a self-join DELETE or ROW_NUMBER, remembering MySQL error 1093.

  • Running totals, previous-period comparisons and percentages of total are window-function problems.

  • Pivots use SUM(CASE ...); "all" questions count exceptions and require zero.

Congratulations on completing the SQL tutorial! You now know how to design tables, query and combine data, summarise it, use window functions, protect it with transactions, constraints and triggers, and package logic in stored routines. The best way to make it stick is practice: take any question from this chapter, change one detail (a different N, a different group, a different table) and write the query again from scratch.