Skip to content
Tutorials Center
My learning

Topics

Lesson 121 of 125 ยท Practice and Review

SQL Queries: 50 Classic Problems

50 classic SQL query problems with runnable answers, from the second-highest salary and duplicates to top-N per group, running totals, gaps and pivots.

Some SQL questions come up again and again: in interviews, on the job, in every reporting tool ever built. Find the second-highest salary. List the customers who never ordered. Show each employee next to their manager. Rank the best sellers in every category. Fill the missing days in a report. Once you have solved each of these patterns once, you will recognize them everywhere.

This page collects 50 of those classic problems, grouped by technique. Every problem has a one-line task, a live editor with a working answer on one of the course's sample databases, and a short explanation with a link to the lesson that teaches the technique in depth. Try to write your own query before you read ours: change the code, press Run, and compare. Many problems have more than one good answer, and several mention the alternatives.

The databases are company (departments, employees with manager_id, projects, assignments), shop (categories, products, customers, orders, order_items) and school (students, courses, teachers, enrollments, terms). A few problems create a tiny table of their own. Keep the cheat sheet open if you need a syntax reminder.

Filtering and sorting

1. Second-highest salary

Return the second-highest distinct salary in the company.

SQL 1. Second-highest salary ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

DISTINCT matters: without it, two people sharing the top salary would make the "second" row equal the first. For the nth value change the offset to n โˆ’ 1, or see problem 33 for the DENSE_RANK version. See LIMIT and OFFSET.

2. Second-highest salary with a subquery

Answer the same question without LIMIT, so it works in any database.

SQL 2. Second-highest salary with MAX ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The inner query finds the top salary, and the outer one takes the maximum of everything below it. A nice property: if there is no second value, you get NULL instead of an empty result. See Subqueries.

3. Hired in a given year

List the employees hired in 2019, oldest hire first.

SQL 3. Hired in 2019 ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

A half-open range (>= start, < next start) is correct for dates and timestamps and can use an index, unlike strftime('%Y', hire_date) = '2019'. See WHERE and Date Functions.

4. Missing values

Find the employees who have no email address.

SQL 4. Employees without email ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

email = NULL would return nothing, because comparing with NULL is never true. See Working with NULL.

5. Pattern matching

Find the shop customers whose last name ends in "son".

SQL 5. Last names ending in son ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

% matches any run of characters, so '%son' means "ends with son". A leading % can't use a normal index, which matters on big tables. See LIKE.

6. Top N rows

Show the five most expensive products.

SQL 6. Five most expensive products ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Server writes SELECT TOP 5, and the standard form is FETCH FIRST 5 ROWS ONLY. Add a tie-breaker column (ORDER BY price DESC, id) when the order must be stable. See ORDER BY.

7. One page of results

Show page 3 of the product list sorted by name, 5 products per page.

SQL 7. Page 3 of products ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The offset is (page - 1) * page_size. For deep pages, keyset pagination (WHERE name > :last_name) is much faster because the database doesn't read and discard the skipped rows. See Pagination.

8. Custom sort order

List the orders placed from June 15 to June 20, 2025: pending first, then shipped, then the rest.

SQL 8. Custom sort order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A CASE in ORDER BY maps each value to a sort key, which is how you sort by business rules instead of alphabetically. See CASE Expressions.

Aggregation

9. Count per group

How many customers does each country have?

SQL 9. Customers per country ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This is the most common report shape in SQL: group, count, sort by the count. See GROUP BY.

10. Find duplicates

Which first names are shared by more than one shop customer?

SQL 10. Duplicate first names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Group by the column (or columns) that should be unique, then keep the groups with more than one row. To see the duplicate rows themselves, join this result back to the table or use COUNT(*) OVER (PARTITION BY first_name). See HAVING.

11. Average per group with names

Show each department's head count and average salary.

SQL 11. Department averages ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The inner join drops the CEO and the contractor, who have no department. Use a LEFT JOIN from departments if empty departments must appear too. See Aggregate Functions.

12. Groups above a threshold

Which departments have more than five employees?

SQL 12. Departments with more than 5 people ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

WHERE removes rows before grouping, HAVING removes groups after counting. See HAVING.

13. Monthly revenue

Total the revenue of non-cancelled orders per month.

SQL 13. Revenue per month ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

COUNT(DISTINCT o.id) is needed because the join repeats each order once per line item. In PostgreSQL you would group by date_trunc('month', ordered_at). See Grouping in Practice.

14. Percentage of total

What share of all orders does each status have?

SQL 14. Share of orders per status ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SUM(COUNT(*)) OVER () adds up the group counts across the whole result, giving the grand total on every row. The 100.0 avoids integer division. A scalar subquery (SELECT COUNT(*) FROM orders) works too. See Window Functions.

15. Relational division: took all of them

Which students have taken every Mathematics course?

SQL 15. Students who took all math courses ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

"For all" questions become counting questions: a student qualifies when the number of distinct math courses they took equals the number of math courses that exist. DISTINCT stops a retaken course from counting twice. See Subqueries.

16. Min and max in the same row

For each category, show the cheapest and most expensive price and the spread between them.

SQL 16. Price range per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Several aggregates over the same group cost one pass over the data. See Aggregate Functions.

Joins

17. Rows with their parent's name

List the first five orders with the customer's full name.

SQL 17. Orders with customer names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Join the foreign key (orders.customer_id) to the primary key it references (customers.id). See INNER JOIN.

18. Rows with no match

Which customers have never placed an order?

SQL 18. Customers who never ordered ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The LEFT JOIN keeps every customer, and customers without orders get NULL in the order columns. NOT EXISTS (problem 29) is an equally good answer. See LEFT JOIN.

19. Employees who earn more than their managers

The most famous self-join problem, plus its twin: who was hired before their manager?

SQL 19. Compared with the manager ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The same table appears twice under two aliases: e for the employee and m for the manager, and the join pairs each row with its manager's row. In this company nobody out-earns their boss, so the first result is empty, which is a correct answer, not a bug. The second query uses the same pattern on hire dates and finds Felix Demir, who joined before his current manager. See Self Joins.

20. Everyone with their manager, including the top

List every employee with their manager's name, showing "(none)" for the CEO.

SQL 20. Employees and managers ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

An inner self join would drop the CEO, whose manager_id is NULL. See Self Joins.

21. Things nobody uses

Which courses have no enrollments at all?

SQL 21. Courses without enrollments ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The same anti-join pattern as problem 18, on another schema. Check the join column (or the primary key) of the right table for NULL, never a nullable data column. See LEFT JOIN.

22. Totals per customer

Who are the five biggest spenders (non-cancelled orders)?

SQL 22. Top 5 customers by spend ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Join down the chain customer โ†’ order โ†’ line item, then group back up to the customer. See Joining Many Tables.

23. Every combination

Generate every pairing of term and computer science course for a planning grid.

SQL 23. Terms x CS courses ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Three terms times three CS courses gives nine rows. A CROSS JOIN followed by a LEFT JOIN to real data is how you build reports that show zeros for missing combinations. See CROSS JOIN.

24. Pairs within one table

Find pairs of employees who were hired on the same day.

SQL 24. Same hire date pairs ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

b.id > a.id keeps each pair once and stops people from pairing with themselves. With <> you would get every pair twice. See Self Joins.

25. Line items with all the names

Show order 6 line by line with product and category names and a line total.

SQL 25. Order 6 in detail ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each join follows one foreign key. Start from the table that has one row per thing you want to show (here, one row per line item). See Joining Many Tables.

Subqueries and set operations

26. Above the average

Which employees earn more than the company average?

SQL 26. Above-average salaries ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

A scalar subquery returns one value that you can use anywhere a constant would go. See Subqueries.

27. Above their group's average

Which employees earn more than the average of their own department?

SQL 27. Above department average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The subquery refers to e from the outer query, so it is evaluated per employee: a correlated subquery. The window version, AVG(salary) OVER (PARTITION BY department_id) in a CTE, usually scales better. See Correlated Subqueries.

28. Highest earner in each group

Show the best-paid employee of each department.

SQL 28. Top earner per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

This returns every tied top earner. Problem 34 does the same with ROW_NUMBER, which makes it easy to pick exactly one per group or the top n. See Correlated Subqueries.

29. Has no matching rows: NOT EXISTS

Which students have never scored below 50 in any graded enrollment?

SQL 29. Never below 50 ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

EXISTS asks "is there at least one row?" and stops at the first match. The first condition removes students with no grades at all, who would otherwise pass the NOT EXISTS test trivially. See EXISTS.

30. Bought a specific product

List the customers who ever ordered the Ultralight Tent.

SQL 30. Who bought the tent ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

IN (subquery) returns each customer once, even if they bought the tent several times, which a plain join wouldn't. See Subqueries.

31. Average of a per-group number

On average, how many orders does a customer who ordered at all place?

SQL 31. Average orders per customer ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You can't nest aggregates (AVG(COUNT(*))), so compute the inner number in a derived table first. See Subqueries.

32. In one set but not the other

Which products sold in the first quarter of 2025 but never in the fourth?

SQL 32. Sold in Q1 but not Q4 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

EXCEPT (Oracle: MINUS) removes the second result from the first and also removes duplicates. Use INTERSECT for "in both" and UNION for "in either". See UNION, INTERSECT and EXCEPT.

Window functions

33. Nth highest with DENSE_RANK

Find the employees with the third-highest distinct salary.

SQL 33. Third-highest salary ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

DENSE_RANK gives tied salaries the same rank without gaps, so rank 3 is always the third distinct value, and the query returns every person who earns it. See Ranking.

34. Top N per group

Show the two best-paid employees of every department.

SQL 34. Top 2 per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

You can't use a window function in WHERE, so rank in a CTE and filter outside. Use RANK() instead of ROW_NUMBER() if ties should all be included. See Ranking.

35. Rank with ties

Rank the well-stocked products (55 units or more) by stock, showing all three ranking functions side by side.

SQL 35. ROW_NUMBER vs RANK vs DENSE_RANK ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Two products have 78 units and three have 57. ROW_NUMBER still counts 1, 2, 3 (breaking ties arbitrarily), RANK repeats and then skips (1, 1, 3), and DENSE_RANK repeats without skipping (1, 1, 2). The WINDOW clause names a window once so all three functions can reuse it. See Ranking and Window Frames.

36. Running total

Show monthly revenue with a running total for the year.

SQL 36. Running total ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SUM(...) OVER (ORDER BY month) adds up everything from the first row to the current one. The last running total equals the year's revenue. See Running Totals.

37. Moving average

Smooth the monthly revenue with a three-month moving average.

SQL 37. Three-month moving average ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW covers the current month and the two before it. January and February average fewer months because there is nothing earlier. See Running Totals and Moving Averages.

38. Change from the previous period

Show each month's revenue next to the previous month's, with the percentage change.

SQL 38. Month-over-month change ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

LAG(x) reads x from the previous row of the window; the first row has no previous value, so it gets NULL. LEAD looks forward instead. See LAG and LEAD.

39. Time between events

How many days passed between each order and the same customer's previous one? Show customer 5.

SQL 39. Days between orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

PARTITION BY customer_id restarts the window for every customer, so LAG never reaches into another customer's orders. julianday turns dates into day numbers you can subtract. See LAG and LEAD.

40. Share of the group

Show each Design employee's salary as a percentage of the department's payroll.

SQL 40. Share of department payroll ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

A window aggregate without ORDER BY covers the whole partition, so every row sees the department total. See Window Functions.

41. First event per group

Find each customer's first order (show the first eight customers).

SQL 41. First order per customer ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The same pattern answers "latest status per ticket" or "most recent price per product": number the rows per group in the order you care about and keep number 1. See Ranking.

42. Median

Compute the median salary (SQLite has no MEDIAN or PERCENTILE_CONT).

SQL 42. Median salary ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

With 36 employees, integer division picks rows 18 and 19 and averages them; with an odd count both expressions point to the same middle row. PostgreSQL writes PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary). See Percentiles and Medians.

43. Buckets

Split the products into four price quartiles and show each bucket's range.

SQL 43. Price quartiles ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

NTILE(4) deals the 26 products into four groups as evenly as possible: the first two get 7, the last two get 6. See Ranking.

Gaps, islands and series

44. Fill in missing days

Count the orders per day for the first ten days of 2025, including days with zero orders.

SQL 44. Orders per day with zeros ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A recursive CTE generates the calendar; the LEFT JOIN keeps days with no orders, and COUNT(o.id) counts them as 0. PostgreSQL has generate_series() for this. See Recursive CTEs.

45. Find gaps in a sequence

Which invoice numbers are missing?

SQL 45. Missing invoice numbers

Press Run (or Ctrl + Enter) to see the result here.

Compare each number with the next one: a jump of more than one means a gap. The result shows three gaps: 4, 7โ€“8 and 12โ€“14. See LAG and LEAD.

46. Islands: consecutive streaks

Find each user's streaks of consecutive login days.

SQL 46. Login streaks

Press Run (or Ctrl + Enter) to see the result here.

The trick: inside a run of consecutive days, the date and the row number grow together, so their difference stays constant. Each distinct difference is one island. See Percentiles, Medians and Statistics.

47. Walk a hierarchy

Show the whole reporting chain above employee 30 (Farah Petrov).

SQL 47. Chain of command ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The anchor selects the starting employee, and each recursive step joins to the manager of the previous row until manager_id is NULL. Reverse the join (e.manager_id = b.id) to walk down to all reports instead. See Recursive CTEs.

Reshaping and changing data

48. Pivot rows into columns

Show the number of orders per status for each quarter of 2025, one column per status.

SQL 48. Orders by quarter and status ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Conditional aggregation turns values into columns. FILTER (WHERE ...) is standard SQL (SQLite, PostgreSQL); SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) works in every database. See Pivoting Rows into Columns.

49. Delete duplicates, keep one

Remove duplicate sign-ups, keeping the earliest row for each email.

SQL 49. Delete duplicate rows

Press Run (or Ctrl + Enter) to see the result here.

The subquery picks one survivor per email (the lowest id), and everything else goes: three rows deleted. With a ROW_NUMBER() CTE you can keep the newest row instead. Run the subquery as a SELECT first on real data. See DELETE.

50. Insert or update

Record stock counts: insert new products, update the ones that already exist.

SQL 50. Upsert stock counts

Press Run (or Ctrl + Enter) to see the result here.

excluded refers to the row that failed to insert, so the update copies the new values in. MySQL writes ON DUPLICATE KEY UPDATE, and SQL Server and Oracle use MERGE. See Upserts with ON CONFLICT.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does problem 1 use DISTINCT before LIMIT 1 OFFSET 1?

    OFFSET skips rows, not values. If two people share the top salary, the second row would hold the same value, so DISTINCT collapses ties first.
  2. 2. Which pattern finds rows in table A that have no match in table B?

    The LEFT JOIN keeps every row of A and fills B's columns with NULL when there is no match; filtering on B's key being NULL keeps exactly the unmatched rows. NOT EXISTS is the other standard answer.
  3. 3. To get the top 3 rows per group, why can't you write WHERE ROW_NUMBER() OVER (...) <= 3?

    WHERE runs before the SELECT list, where window functions are computed. Rank in a CTE or derived table, then filter the result.
  4. 4. In the islands technique (problem 46), what stays constant inside a run of consecutive days?

    Both the date and the row number go up by one per row within a streak, so their difference doesn't change. A new streak starts with a different difference.
  5. 5. Why does problem 13 use COUNT(DISTINCT o.id) instead of COUNT(*)?

    Joining orders to order_items produces one row per line item, so COUNT(*) would count line items. Counting distinct order ids counts each order once.

Summary

  • Filtering classics: the nth highest value (DISTINCT + OFFSET, MAX below MAX, or DENSE_RANK), half-open date ranges, IS NULL, LIKE and custom sort orders with CASE.
  • Aggregation classics: counts per group, duplicates with HAVING COUNT(*) > 1, monthly reports, percentages of a total and "for all" questions solved by counting.
  • Join classics: anti-joins for missing matches, self joins for managers and pairs, cross joins for grids, and chains of joins for detailed reports.
  • Subquery classics: comparisons with an overall or per-group average, EXISTS/NOT EXISTS, derived tables for aggregates of aggregates, and set operations.
  • Window classics: top-N per group, rankings with ties, running totals, moving averages, LAG-based changes, shares of a group, first rows per group, medians and buckets.
  • Series and reshaping: recursive calendars and hierarchies, gaps and islands, pivots with conditional aggregation, de-duplication and upserts.

Next: SQL Practice Exercises, where you'll write 30 queries yourself, from your first filter to window functions and recursive CTEs.