SQL practice: 9 exercises with answers

The fastest way to learn SQL is to write queries against real tables and check the results. This page is SQL practice online: two small tables from a running club, then nine exercises that build from simple SELECT and WHERE queries to GROUP BY, HAVING and JOINs. Try each one first, then compare your query with the answer and the result shown below it.

The practice tables

runners: one row per club member.

races: one row per race result. runner_id is a foreign key pointing at runners.id, and minutes is the finishing time.

If you want to run the queries yourself, these statements create the tables. They work in most SQL databases. SQLite, for example, is a self-contained database engine whose code is in the public domain, so it is free to use.

CREATE TABLE runners (id INTEGER PRIMARY KEY, name TEXT, city TEXT, age INTEGER);
INSERT INTO runners VALUES
  (1, 'Maya Chen', 'Denver', 34), (2, 'Luis Ortega', 'Austin', 27),
  (3, 'Priya Nair', 'Denver', 41), (4, 'Sam Brooks', 'Boston', 22),
  (5, 'Ana Silva', 'Austin', 30), (6, 'Theo Grant', 'Chicago', 38);

CREATE TABLE races (id INTEGER PRIMARY KEY, runner_id INTEGER REFERENCES runners(id),
                    event TEXT, minutes INTEGER);
INSERT INTO races VALUES
  (1, 1, '10K', 48), (2, 2, '10K', 41), (3, 1, 'Half marathon', 105),
  (4, 3, '10K', 52), (5, 4, 'Half marathon', 98), (6, 2, 'Half marathon', 92),
  (7, 5, '10K', 45);

Beginner SQL practice: SELECT, WHERE and ORDER BY

Exercise 1: filter by a value

List the name and age of every runner from Denver.

SELECT name, age
FROM runners
WHERE city = 'Denver';

Result: Maya Chen, 34 and Priya Nair, 41. Text values go in single quotes; the column list after SELECT picks which columns you see.

Exercise 2: compare and sort

List runners aged 30 or over, oldest first.

SELECT name, age
FROM runners
WHERE age >= 30
ORDER BY age DESC;

Result: Priya Nair 41, Theo Grant 38, Maya Chen 34, Ana Silva 30. >= includes 30 itself; DESC sorts from largest to smallest, and leaving it out sorts smallest first.

Exercise 3: the top results

Show the runner_id and time for the two fastest half marathons.

SELECT runner_id, minutes
FROM races
WHERE event = 'Half marathon'
ORDER BY minutes
LIMIT 2;

Result: runner 2 in 92 minutes, then runner 4 in 98. The PostgreSQL documentation stresses why ORDER BY matters here: SQL doesn't promise any particular order of rows unless you ask for one, so LIMIT without ORDER BY returns an unpredictable subset. LIMIT works in PostgreSQL, MySQL and SQLite; some other databases use different keywords for the same job.

Intermediate SQL practice: aggregates and GROUP BY

Exercise 4: an average

Find the average 10K time.

SELECT AVG(minutes) AS avg_10k
FROM races
WHERE event = '10K';

Result: 46.5, from (48 + 41 + 52 + 45) ÷ 4. AS gives the result column a readable name. Other aggregates work the same way: COUNT, SUM, MIN and MAX.

Exercise 5: count per group

Count how many runners there are in each city, in alphabetical order of city.

SELECT city, COUNT(*) AS runners
FROM runners
GROUP BY city
ORDER BY city;

Result: Austin 2, Boston 1, Chicago 1, Denver 2. In PostgreSQL's words, GROUP BY combines rows with the same values into one group row. Any column in the SELECT list that isn't in GROUP BY has to be inside an aggregate like COUNT.

Exercise 6: filter groups with HAVING

List only the cities with more than one runner.

SELECT city, COUNT(*) AS runners
FROM runners
GROUP BY city
HAVING COUNT(*) > 1;

Result: Austin 2 and Denver 2. WHERE can't do this, because it filters individual rows before they are grouped. PostgreSQL describes the pipeline: rows pass the WHERE filter, then GROUP BY forms groups, then HAVING removes groups.

Advanced SQL practice: JOINs

Exercise 7: an inner join

List each 10K runner's name and time, fastest first.

SELECT r.name, x.minutes
FROM races AS x
JOIN runners AS r ON r.id = x.runner_id
WHERE x.event = '10K'
ORDER BY x.minutes;

Result: Luis Ortega 41, Ana Silva 45, Maya Chen 48, Priya Nair 52. JOIN (an inner join) pairs each race with the runner whose id matches its runner_id. The short names x and r are aliases that save typing.

Exercise 8: join and group

Count how many races each runner has run, listing names.

SELECT r.name, COUNT(*) AS races_run
FROM races AS x
JOIN runners AS r ON r.id = x.runner_id
GROUP BY r.id, r.name
ORDER BY r.id;

Result: Maya Chen 2, Luis Ortega 2, Priya Nair 1, Sam Brooks 1, Ana Silva 1. Theo Grant is missing: there are no race rows for Theo, and an inner join drops anything without a partner in the other table.

Exercise 9: find the rows with no match

Find any runner who hasn't run a race.

SELECT r.name
FROM runners AS r
LEFT JOIN races AS x ON x.runner_id = r.id
WHERE x.id IS NULL;

Result: Theo Grant. PostgreSQL explains how a LEFT JOIN works: it does an inner join, then adds every left-table row with no match, filling the right table's columns with nulls. Testing for IS NULL keeps just those. Use IS NULL: = NULL gives null (unknown), never true.

Now check your understanding of joins in the databases course: pick a join, then press Mark.

Common mistakes

• Clauses in the wrong order. Write SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
• Double quotes for text. As the PostgreSQL documentation puts it, a string constant is bounded by single quotes; double quotes make a quoted identifier, the name of a column or table.
• WHERE on an aggregate. WHERE COUNT(*) > 1 fails; filter groups with HAVING.
• Forgetting the join condition. Without ON, every row is paired with every other row.
• = NULL instead of IS NULL. PostgreSQL notes that a comparison with null gives null (unknown), not true, so the row is never kept.
• LIMIT without ORDER BY. You'll get some rows, but not reliably the ones you meant.

For more on the language itself, see what is SQL and CASE WHEN in SQL. The databases course covers tables, keys, SELECT, joins and more, with practice in each section, and what is a relational database explains why data is split across tables like these.

Read next

Computer Science · DatabasesWhat is SQL? What it is used for, with simple examplesSQL is the language for asking databases questions. Learn what SQL is, what it is used for, the main commands and how queries work, with examples.