Practice SQL Online: A 12-Exercise Workbook

SQL is learned by querying, not reading. This workbook is twelve exercises on one small database, ordered from a first SELECT to window functions, with every answer verified against the real data. Open the playground in another tab and type each query yourself before peeking.

The database you'll practice on

Four tables, small enough to hold in your head, rich enough to ask real questions — a shop with 6 customers, 8 products in 3 categories, 9 orders (one cancelled), and 16 line items:

That shape — customers to orders one-to-many, orders to products many-to-many through order_items — is the same shape as most business data you'll ever meet.

Level 1: Getting rows out

Exercise 1 — most expensive products. List the three priciest products with name and price.

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

Standing Desk $549.00, 4K Monitor $349.99, Ergonomic Chair $329.00. ORDER BY sorts descending, LIMIT truncates — the workhorse pair for "top N" questions.

Exercise 2 — cheapest product. Same table, other direction: Desk Mat, $25.00.

Exercise 3 — count with a filter. How many products are Hardware?

SELECT COUNT(*) AS n FROM products WHERE category = 'Hardware';

Answer: 3. COUNT(*) counts rows; the WHERE happens before it.

Exercise 4 — pattern matching. How many orders were placed in June 2025?

SELECT COUNT(*) AS june_orders FROM orders WHERE order_date LIKE '2025-06%';

Answer: 5 of the 9. LIKE with a trailing % is the quick range-over-text pattern; for real date work you'd use BETWEEN '2025-06-01' AND '2025-06-30'.

Level 2: Aggregation

Exercise 5 — revenue by category. The classic. Join items to products for prices and to orders for status, skip the cancelled order, group, and sum:

SELECT p.category, COUNT(*) AS items_sold,
       ROUND(SUM(p.price * oi.qty), 2) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o   ON o.id = oi.order_id
WHERE o.status != 'cancelled'
GROUP BY p.category
ORDER BY revenue DESC;

Furniture: 6 items, $1,831.00. Hardware: 6 items, $1,155.98. Audio: 3 items, $518.97. This one query exercises joins, filtering, grouping, and arithmetic in seven lines.

Exercise 6 — average order value. Orders first, then the average of averages:

SELECT ROUND(AVG(sub.total), 2) AS avg_order_value,
       COUNT(*) AS orders,
       ROUND(MIN(sub.total), 2) AS smallest,
       ROUND(MAX(sub.total), 2) AS largest
FROM (SELECT o.id, SUM(p.price * oi.qty) AS total
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.id
      JOIN products p ON p.id = oi.product_id
      WHERE o.status != 'cancelled'
      GROUP BY o.id) sub;

$438.24 average across 8 orders, ranging from $154.00 to $697.50. The subquery computes each order's total; the outer query treats that result as a table. Every "average of a sum" question has this two-stage shape.

Exercise 7 — orders per month.

SELECT substr(order_date, 1, 7) AS month, COUNT(*) AS orders
FROM orders GROUP BY month ORDER BY month;

2025-06: 5 orders, 2025-07: 4. substr chops the ISO date to year-month.

Level 3: Joins on relationships

Exercise 8 — order count per customer. A LEFT JOIN keeps customers with zero orders visible:

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY orders DESC, c.name;

Ada Lovelace, Grace Hopper, and Margaret Hamilton have 2 each; the other three have 1. Note COUNT(o.id), not COUNT(*) — counting the joined side's column turns missing matches into zeros instead of ones.

Exercise 9 — repeat customers only. Add HAVING COUNT(o.id) > 1 and you get exactly those three names. WHERE filters rows before grouping; HAVING filters groups after — the distinction interviewers love.

Exercise 10 — who never ordered?

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

On this data: zero rows — every customer ordered. That empty result is itself the lesson: the anti-join pattern is correct even when it has nothing to report.

Level 4: Composition and windows

Exercise 11 — leaderboard with a CTE. Rank customers by total spend:

WITH spend AS (
  SELECT o.customer_id, SUM(p.price * oi.qty) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status != 'cancelled'
  GROUP BY o.customer_id)
SELECT c.name, ROUND(s.total, 2) AS total_spent
FROM spend s JOIN customers c ON c.id = s.customer_id
ORDER BY s.total DESC;

Grace Hopper $998.49, Linus Torvalds $697.50, Margaret Hamilton $578.00, then the rest. A CTE names the intermediate result so the final read is two clean stages.

Exercise 12 — rank without sorting the output. Window functions aggregate without collapsing rows:

SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS price_rank,
       ROUND(AVG(price) OVER (PARTITION BY category), 2) AS cat_avg
FROM products;

Each product keeps its row and gains its rank plus its category's average price — impossible with plain GROUP BY, which would fold the eight products into three. Windows are the last big idea in everyday SQL.

How to actually practice

Three habits separate practice that sticks from practice that evaporates. First, type the queries — copying teaches your clipboard, not you. Second, predict the output before running; the moment of "wait, why is it 8 and not 9?" is where understanding forms (in this data, it's usually the cancelled order). Third, break working queries on purpose: drop the WHERE from exercise 5 and see Furniture jump by one cancelled chair — $1,831.00 becomes $2,160.00. Understanding what a clause contributes is easier when you watch it leave.

When your queries outgrow the playground, keep them readable with the SQL formatter, and when you have CSV data of your own to query, the CSV to SQL converter turns it into CREATE TABLE plus INSERTs you can paste straight into a database like this one.

Frequently Asked Questions

Related Tools