Skip to content
Tutorials Center
My learning

Topics

Lesson 122 of 125 ยท Practice and Review

SQL Practice Exercises

30 graded SQL exercises with solutions on filtering, functions, GROUP BY, joins, subqueries, CTEs, window functions and changing data. Run them in your browser.

Reading queries and writing queries are different skills, and only practice builds the second one. This page collects 30 exercises that cover the whole course, grouped by topic and ordered from beginner to advanced. Each one has a short task, an editor that already runs a starter query on the right sample database, and a hidden solution.

Here's how to get the most out of them:

  • Look before you write. Every starter query shows you the table you need, usually with LIMIT so it stays short. Run it first and read the column names.
  • Build in small steps. Start with FROM and WHERE, run, then add grouping, joins or sorting one clause at a time. Errors are much easier to fix that way.
  • Try first, peek later. Give each exercise an honest attempt before you open the solution. Your query may look different from ours and still be correct: compare the results, then read ours for ideas.

Every run starts from a fresh copy of the database, so you can't break anything, even in the exercises that change data. Each group links to the lessons it practices, and the cheat sheet is handy to keep open in another tab. When you finish, the 50 classic query problems are the natural next challenge.

Filtering and sorting

These practice SELECT, WHERE, AND, OR and NOT, IN and BETWEEN, NULL, ORDER BY and LIMIT.

1. Price list (Beginner)

In the bookstore database, list every book's title and price, cheapest first. Books with the same price should be sorted by title.

SQL Exercise 1: Price list ยท database: bookstore

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

Show solution
SQL
SELECT title, price
FROM books
ORDER BY price, title;

ORDER BY sorts ascending by default, and the second column only matters when two rows tie on the first.

2. Out of stock (Beginner)

Which books have no copies left in stock? Show the title and genre.

SQL Exercise 2: Out of stock ยท database: bookstore

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

Show solution
SQL
SELECT title, genre
FROM books
WHERE stock = 0;

You can filter on a column without selecting it: stock is in the WHERE but not in the result.

3. Kitchen or outdoor, mid-priced (Beginner)

In the shop database, list products in category 1 (Kitchen) or 2 (Outdoor) that cost between 20 and 50, cheapest first.

SQL Exercise 3: Kitchen or outdoor ยท database: shop

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

Show solution
SQL
SELECT name, category_id, price
FROM products
WHERE (category_id = 1 OR category_id = 2)
  AND price BETWEEN 20 AND 50
ORDER BY price;

The parentheses are essential: AND binds tighter than OR, so without them you would get every Kitchen product plus the mid-priced Outdoor ones. category_id IN (1, 2) is a shorter way to write the same test.

4. Recent customers in two cities (Beginner)

List the shop customers from Toronto or Berlin who signed up on or after January 1, 2024, oldest sign-up first.

SQL Exercise 4: Recent customers ยท database: shop

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

Show solution
SQL
SELECT first_name, last_name, city, signed_up
FROM customers
WHERE city IN ('Toronto', 'Berlin')
  AND signed_up >= '2024-01-01'
ORDER BY signed_up;

Dates stored as YYYY-MM-DD text sort and compare correctly as strings, which is why SQLite databases use that format.

5. Unknown or early birth years (Beginner)

In bookstore, list the authors whose birth year is unknown or earlier than 1970.

SQL Exercise 5: Unknown birth years ยท database: bookstore

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

Show solution
SQL
SELECT name, country
FROM authors
WHERE born IS NULL OR born < 1970;

born < 1970 alone would miss Daniel Brook: comparing NULL with anything is unknown, never true, so you have to ask for IS NULL explicitly.

6. Newest products (Beginner)

Show the three products that were added to the shop most recently.

SQL Exercise 6: Newest products ยท database: shop

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

Show solution
SQL
SELECT name, added_on
FROM products
ORDER BY added_on DESC
LIMIT 3;

LIMIT without ORDER BY returns some three rows, in whatever order the database finds them. Sorting first is what makes it a top-N query.

Functions and expressions

These practice calculated columns, string functions, numeric functions, date functions, CASE and COALESCE.

7. Name badges (Beginner)

For the shop customers in France, show a customer column like Rosa BAUER (last name in capitals), their initials (RB) and the number of letters in their first and last names together. Sort by last name.

SQL Exercise 7: Name badges ยท database: shop

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

Show solution
SQL
SELECT
  first_name || ' ' || upper(last_name) AS customer,
  substr(first_name, 1, 1) || substr(last_name, 1, 1) AS initials,
  length(first_name || last_name) AS letters
FROM customers
WHERE country = 'France'
ORDER BY last_name;

|| joins strings, substr(text, 1, 1) takes the first character (positions start at 1), and length counts characters.

8. Sale prices (Beginner)

The shop is running a 15% sale on Audio (category 4). Show each Audio product's name, normal price and sale price rounded to two decimals, most expensive first.

SQL Exercise 8: Sale prices ยท database: shop

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

Show solution
SQL
SELECT
  name,
  price,
  ROUND(price * 0.85, 2) AS sale_price
FROM products
WHERE category_id = 4
ORDER BY price DESC;

Multiplying by 0.85 removes 15%, and ROUND(x, 2) keeps two decimal places. Real shops store money as integer cents or DECIMAL to avoid floating-point surprises.

9. Weekend orders (Intermediate)

List the orders placed on a Saturday or Sunday in January and February 2025, with a weekday column that says Saturday or Sunday.

SQL Exercise 9: Weekend orders ยท database: shop

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

Show solution
SQL
SELECT
  id,
  ordered_at,
  CASE strftime('%w', ordered_at)
    WHEN '0' THEN 'Sunday'
    ELSE 'Saturday'
  END AS weekday
FROM orders
WHERE ordered_at >= '2025-01-01'
  AND ordered_at < '2025-03-01'
  AND strftime('%w', ordered_at) IN ('0', '6')
ORDER BY ordered_at;

strftime('%w', ...) returns text, so compare it with '0' and '6' in quotes. The half-open date range covers both months, including times on February 28.

10. Price bands (Intermediate)

For Stationery and Home Office products (categories 3 and 5), add a price_band column: budget under 20, standard under 60, otherwise premium. Sort by price.

SQL Exercise 10: Price bands ยท database: shop

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

Show solution
SQL
SELECT
  name,
  price,
  CASE
    WHEN price < 20 THEN 'budget'
    WHEN price < 60 THEN 'standard'
    ELSE 'premium'
  END AS price_band
FROM products
WHERE category_id IN (3, 5)
ORDER BY price;

A searched CASE returns the first branch whose condition is true, so the second test doesn't need price >= 20.

11. Fill in the blanks (Intermediate)

In company, list every project with its budget and end date. Show not set when the budget is missing and ongoing when there is no end date.

SQL Exercise 11: Fill in the blanks ยท database: company

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

Show solution
SQL
SELECT
  name,
  COALESCE(CAST(budget AS TEXT), 'not set') AS budget,
  COALESCE(ends_on, 'ongoing') AS ends_on
FROM projects
ORDER BY id;

COALESCE returns its first non-NULL argument. Casting the number to text keeps the column one type, which stricter databases such as PostgreSQL require.

Aggregation

These practice aggregate functions, GROUP BY, HAVING and grouping in practice.

12. Books per genre (Beginner)

In bookstore, show each genre with its number of books and average price (two decimals). Sort by the count, largest first, then by genre.

SQL Exercise 12: Books per genre ยท database: bookstore

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

Show solution
SQL
SELECT
  genre,
  COUNT(*) AS books,
  ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY genre
ORDER BY books DESC, genre;

GROUP BY genre makes one row per genre, and every other selected column must be an aggregate over that group.

13. Busiest weekday (Intermediate)

How many shop orders were placed on each day of the week? Show the day as Sun, Mon, ... and sort by the number of orders, busiest first.

SQL Exercise 13: Busiest weekday ยท database: shop

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

Show solution
SQL
SELECT
  CASE strftime('%w', ordered_at)
    WHEN '0' THEN 'Sun' WHEN '1' THEN 'Mon' WHEN '2' THEN 'Tue'
    WHEN '3' THEN 'Wed' WHEN '4' THEN 'Thu' WHEN '5' THEN 'Fri'
    ELSE 'Sat'
  END AS weekday,
  COUNT(*) AS orders
FROM orders
GROUP BY strftime('%w', ordered_at)
ORDER BY orders DESC;

You can group by an expression. Grouping by the day number rather than the label also lets you add ORDER BY strftime('%w', ordered_at) for calendar order.

14. Big payrolls (Intermediate)

In company, show the departments (by department_id) whose total salaries exceed 450,000, with their head count and payroll.

SQL Exercise 14: Big payrolls ยท database: company

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

Show solution
SQL
SELECT
  department_id,
  COUNT(*) AS employees,
  SUM(salary) AS payroll
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING SUM(salary) > 450000
ORDER BY payroll DESC;

The condition is on an aggregate, so it goes in HAVING. The WHERE drops the two people without a department before grouping.

15. Average grade per course (Intermediate)

In school, show each course_id with the number of graded enrollments and the average grade (one decimal), highest average first. Some grades are NULL because the course is still running.

SQL Exercise 15: Average grade per course ยท database: school

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

Show solution
SQL
SELECT
  course_id,
  COUNT(grade) AS graded,
  ROUND(AVG(grade), 1) AS avg_grade
FROM enrollments
GROUP BY course_id
ORDER BY avg_grade DESC;

COUNT(grade) and AVG(grade) both skip NULLs, so missing grades neither count as zero nor drag the average down. COUNT(*) would count every enrollment.

16. Busiest month (Intermediate)

Which month of 2025 had the most shop orders (any status)?

SQL Exercise 16: Busiest month ยท database: shop

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

Show solution
SQL
SELECT
  strftime('%Y-%m', ordered_at) AS month,
  COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY orders DESC
LIMIT 1;

Group, sort by the aggregate, keep one row. If two months could tie, use RANK() instead of LIMIT 1 to return both.

Joins

These practice joins, INNER JOIN, LEFT JOIN and joining many tables.

17. Books with authors (Beginner)

In bookstore, list each book's title, its author's name and the year it was published, sorted by author and then year.

SQL Exercise 17: Books with authors ยท database: bookstore

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

Show solution
SQL
SELECT b.title, a.name AS author, b.published
FROM books AS b
JOIN authors AS a ON a.id = b.author_id
ORDER BY a.name, b.published;

The join condition connects the foreign key books.author_id to the primary key authors.id. Short table aliases keep the column references readable.

18. Never ordered (Intermediate)

Which books have never appeared in an order?

SQL Exercise 18: Never ordered ยท database: bookstore

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

Show solution
SQL
SELECT b.title, b.stock
FROM books AS b
LEFT JOIN orders AS o ON o.book_id = b.id
WHERE o.id IS NULL;

The LEFT JOIN keeps every book; books without orders get NULL in the order columns, and the WHERE keeps only those. Both happen to be out of stock as well.

19. Course list with teachers (Intermediate)

In school, list every course code and title with its teacher's name. One course has no teacher yet: show TBA for it instead of dropping it.

SQL Exercise 19: Courses and teachers ยท database: school

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

Show solution
SQL
SELECT
  c.code,
  c.title,
  COALESCE(t.name, 'TBA') AS teacher
FROM courses AS c
LEFT JOIN teachers AS t ON t.id = c.teacher_id
ORDER BY c.code;

An inner join would silently drop CS300 because its teacher_id is NULL. LEFT JOIN plus COALESCE keeps it and labels it.

20. The Berlin office (Intermediate)

In company, list the employees who work in a department based in Berlin, with the department name. Sort by department, then last name.

SQL Exercise 20: The Berlin office ยท database: company

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

Show solution
SQL
SELECT
  e.first_name,
  e.last_name,
  d.name AS department
FROM employees AS e
JOIN departments AS d ON d.id = e.department_id
WHERE d.office = 'Berlin'
ORDER BY d.name, e.last_name;

You can filter on any column of a joined table, here the department's office.

21. Project staffing (Intermediate)

For every project, show how many people are assigned and the total hours per week they put in. Sort by hours, highest first.

SQL Exercise 21: Project staffing ยท database: company

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

Show solution
SQL
SELECT
  p.name AS project,
  COUNT(a.employee_id) AS people,
  COALESCE(SUM(a.hours_per_week), 0) AS hours_per_week
FROM projects AS p
LEFT JOIN assignments AS a ON a.project_id = p.id
GROUP BY p.id, p.name
ORDER BY hours_per_week DESC;

Starting from projects with a LEFT JOIN guarantees that a project with nobody assigned would still appear with 0. COUNT(a.employee_id) counts matches only, while COUNT(*) would count 1 for an unmatched project.

22. Revenue per category (Advanced)

Using only delivered orders, show each category's units sold and revenue (two decimals), highest revenue first.

SQL Exercise 22: Revenue per category ยท database: shop

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

Show solution
SQL
SELECT
  c.name AS category,
  SUM(oi.quantity) AS units,
  ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items AS oi
JOIN orders AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
JOIN categories AS c ON c.id = p.category_id
WHERE o.status = 'delivered'
GROUP BY c.id, c.name
ORDER BY revenue DESC;

Four tables, three joins: the order supplies the status, the product links to the category, and the line item has the money. Use unit_price from the line item, not the product's current price, because prices change over time.

Subqueries and CTEs

These practice subqueries, correlated subqueries, EXISTS and common table expressions.

23. Pricier than average (Beginner)

List the books that cost more than the average book price, most expensive first.

SQL Exercise 23: Pricier than average ยท database: bookstore

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

Show solution
SQL
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books)
ORDER BY price DESC;

WHERE price > AVG(price) is an error because WHERE can't use aggregates; the scalar subquery computes the average first.

24. Loyal customers (Intermediate)

Show the first and last names of shop customers who placed six or more orders.

SQL Exercise 24: Loyal customers ยท database: shop

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

Show solution
SQL
SELECT first_name, last_name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(*) >= 6
)
ORDER BY last_name;

The subquery returns a list of customer ids, and IN keeps the customers on that list. A join to the grouped subquery would work too and could also show the count.

25. Not on any project (Intermediate)

In company, list the employees who are not assigned to any project, in id order.

SQL Exercise 25: Not on any project ยท database: company

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

Show solution
SQL
SELECT e.first_name, e.last_name, e.title
FROM employees AS e
WHERE NOT EXISTS (
  SELECT 1
  FROM assignments AS a
  WHERE a.employee_id = e.id
)
ORDER BY e.id;

NOT EXISTS is the safest anti-join: unlike NOT IN, it gives the right answer even if the subquery could return NULLs.

26. Honor roll (Advanced)

In school, compute every student's average grade in a CTE, then list the students whose average is 75 or more, with how many graded courses it's based on. Highest average first, one decimal.

SQL Exercise 26: Honor roll ยท database: school

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

Show solution
SQL
WITH student_averages AS (
  SELECT
    student_id,
    COUNT(grade) AS graded,
    AVG(grade) AS avg_grade
  FROM enrollments
  GROUP BY student_id
)
SELECT
  s.first_name,
  s.last_name,
  sa.graded,
  ROUND(sa.avg_grade, 1) AS avg_grade
FROM student_averages AS sa
JOIN students AS s ON s.id = sa.student_id
WHERE sa.avg_grade >= 75
ORDER BY sa.avg_grade DESC;

The CTE gives the per-student numbers a name, so the outer query reads like a sentence. Filtering and sorting on the unrounded average keeps ties such as 81.7 in a consistent order.

Window functions

These practice window functions and ranking.

27. Class ranking (Advanced)

Rank the students in Cell Biology (course_id 8) for the Spring 2025 term (term_id 2) by grade, best first. Students with the same grade must share a position, and the next position must skip accordingly.

SQL Exercise 27: Class ranking ยท database: school

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

Show solution
SQL
SELECT
  s.first_name,
  s.last_name,
  e.grade,
  RANK() OVER (ORDER BY e.grade DESC) AS position
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
WHERE e.course_id = 8
  AND e.term_id = 2
ORDER BY position;

RANK gives ties the same number and skips the next ones, like a sports table. DENSE_RANK wouldn't skip, and ROW_NUMBER would break the tie arbitrarily.

28. Compared with the team (Advanced)

For the Research department (department_id 6), show each employee's salary, the department average (rounded) and the difference between the two, highest difference first. Keep every employee row.

SQL Exercise 28: Compared with the team ยท database: company

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

Show solution
SQL
SELECT
  first_name,
  last_name,
  salary,
  ROUND(AVG(salary) OVER (PARTITION BY department_id)) AS dept_avg,
  salary - ROUND(AVG(salary) OVER (PARTITION BY department_id)) AS difference
FROM employees
WHERE department_id = 6
ORDER BY difference DESC;

A window aggregate puts the group's average on every row without collapsing them, which GROUP BY can't do. Only the head of the department earns above the average.

Changing data and design

These practice CREATE TABLE, constraints, INSERT, UPDATE, DELETE and transactions.

29. A to-do table (Intermediate)

Starting from an empty database, create a tasks table with an id primary key, a required title, a priority from 1 to 3 that defaults to 2, and a done flag (0 or 1) that defaults to 0. Insert four tasks, one of them without a priority, and list them by priority.

SQL Exercise 29: A to-do table

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

Show solution
SQL
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  priority INTEGER NOT NULL DEFAULT 2 CHECK (priority BETWEEN 1 AND 3),
  done INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1))
);

INSERT INTO tasks (title, priority) VALUES
  ('Write report', 1),
  ('Book flights', 2),
  ('Water plants', 3);
INSERT INTO tasks (title) VALUES ('Call the bank');

SELECT * FROM tasks ORDER BY priority, id;

DEFAULT fills in columns you leave out, and the CHECK constraints reject bad values: try inserting a priority of 5 and the run stops with a constraint error.

30. Change it, then undo it (Advanced)

In the shop database, start a transaction. Raise every Fitness price (category 6) by 10%, rounded to two decimals, and delete all line items that belong to cancelled orders. Check the new prices and the line-item count, then roll back and prove that both are back to their original values.

SQL Exercise 30: Change it, then undo it ยท database: shop

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

Show solution
SQL
BEGIN;

UPDATE products
SET price = ROUND(price * 1.10, 2)
WHERE category_id = 6;

DELETE FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled');

SELECT name, price FROM products WHERE category_id = 6;
SELECT COUNT(*) AS line_items FROM order_items;

ROLLBACK;

SELECT name, price FROM products WHERE category_id = 6;
SELECT COUNT(*) AS line_items FROM order_items;

The update changes 4 rows and the delete removes 38, but ROLLBACK undoes everything since BEGIN, so the final queries show the original prices and all 292 line items. Testing risky changes this way is a habit worth keeping.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In exercise 3, what goes wrong if you drop the parentheses: WHERE category_id = 1 OR category_id = 2 AND price BETWEEN 20 AND 50?

    AND binds tighter than OR, so the condition reads category_id = 1 OR (category_id = 2 AND price BETWEEN 20 AND 50). All Kitchen products pass the first part.
  2. 2. Why does exercise 15 use COUNT(grade) instead of COUNT(*)?

    COUNT(*) counts rows; COUNT(column) counts non-NULL values. Courses still in progress have NULL grades, which shouldn't count as graded.
  3. 3. In exercise 19, which join keeps the course that has no teacher?

    A LEFT JOIN keeps every row of the left table, courses, and fills the teacher columns with NULL when there is no match.
  4. 4. What do the final SELECTs in exercise 30 show after ROLLBACK?

    ROLLBACK discards every change made since BEGIN, so both the price update and the delete are undone.

Summary

  • Filtering exercises practice WHERE with AND/OR (and parentheses), IN, BETWEEN, IS NULL, sorting and LIMIT.
  • Function exercises build strings, round numbers, read dates with strftime, classify rows with CASE and fill gaps with COALESCE.
  • Aggregation exercises group rows, count and average them, filter groups with HAVING and remember that aggregates skip NULLs.
  • Join exercises connect foreign keys to primary keys, use LEFT JOIN to keep unmatched rows, find missing matches and chain several tables.
  • Subquery and CTE exercises compare with an average, filter by lists, use NOT EXISTS and name intermediate results.
  • Window exercises rank rows with ties and compare each row with its group, and the last exercises create a table with constraints and test changes inside a transaction.

Next: SQL Interview Questions, where you'll review the concepts interviewers ask about most, from keys and joins to indexes and isolation levels.