Intro to Git — Snapshots, Branches & Sharing
WorkshopyOfficial10 stepsQuiz9 interactiveDiagram
gitversion controlbeginners
SQL subquery exercises you run as you learn: put a query inside a query, use IN, EXISTS and WITH (CTEs), and see the inner result feed the outer one.

WorkshopyOfficial
78 lessons · used by 2 teachers
Sign in to copy it to your workspace. It’s free.
11 steps · 2 quizzes · open any step, or read it here
In this lesson you practise SQL subqueries and CTEs on three small tables from a sales team: you put one query inside another, use what the inner query returns as a value, a list or a table, and name the steps of a long query with WITH. Every exercise runs here, and you check your answers as you go.
Here is the whole idea in one picture. The part in brackets is a query of its own. It runs first, its result goes where the brackets were, and only then does the outer query run. Pick a question and watch the inner result travel into the outer query.
Interactive · The inner query runs first and feeds the outer one — try it in the lesson
About 85 minutes, with a short check after the first four ideas, a playground, a practice set and a final quiz.
Your manager asks: which orders are bigger than our average order? It sounds like one question, but it hides two:
The first idea most people try is WHERE amount > AVG(amount). The database refuses it: WHERE looks at one row at a time, before any grouping, so there is no average yet when it runs. "aggregate functions are not allowed in WHERE" is the error.
You could run the two questions as two queries: get 633, then type WHERE amount > 633. That works today and is wrong tomorrow, when new orders move the average. A subquery does both in one statement: write the first question in brackets where the number would go.
Try it: press each way in turn. Watch the third one: the bracketed query runs first, returns 633, and that value lands in the outer WHERE.
Interactive · From two queries to one: the subquery — try it in the lesson
The subquery and the typed number give the same four orders today. Only the subquery stays right when the orders change.
Check your work · Run way 2a. Paste the average order amount it returns. — check it in the lesson
Takeaway: when a question needs a number you don't have yet, ask for that number in a subquery, in brackets, where the number goes.
A subquery that returns one row and one column is a scalar subquery: to the outer query it is just a number (or a text, or a date). So it can go anywhere a value can go:
WHERE amount > (SELECT AVG(amount) FROM orders)amount - (SELECT AVG(amount) FROM orders) AS diffamount * 100.0 / (SELECT SUM(amount) FROM orders) is each order's share of all sales, in per centIt runs once, before the outer query, and the same value is used for every row.
It must return one value. If it returns two rows the database stops with "more than one row returned by a subquery used as an expression": it can't compare one amount with two numbers. If it returns no rows, the value is NULL.
Try it: run the presets, then do the three tasks. Each one needs a subquery, not a typed number.
Interactive · Scalar subqueries on the orders table — try it in the lesson
Takeaway: a scalar subquery is one value: it works in WHERE, in SELECT and in arithmetic, and it must never return more than one row.
Now a question across two tables: which orders came from customers in Leeds? The orders table has no city; the customers table does. Split it:
SELECT customer_id FROM customers WHERE city = 'Leeds' gives the list (1, 3).WHERE customer_id IN (…).IN (subquery) is IN (1, 3) with the list worked out for you. The inner query must return one column (the values to match), any number of rows. NOT IN keeps the rows whose value is not in the list.
A subquery with IN never repeats a row of the outer table. Cobalt Inc ordered twice in March; "customers who ordered in March" with IN lists Cobalt once, where a JOIN would list it twice (once per matching order).
Try it: watch the list (1, 3) form, then the orders table keep the matching rows. Then do the tasks.
Interactive · IN and NOT IN with a subquery — try it in the lesson
Takeaway: IN (subquery) matches each row against a list another query makes; the inner query returns one column.
Four questions on what you've seen so far.
Question 1. In `SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders)`, what happens first?
Question 2. A query stops with "more than one row returned by a subquery used as an expression". What went wrong?
Question 3. `WHERE customer_id IN (SELECT …)`: what must the inner query return?
Question 4. Why does `WHERE amount > AVG(amount)` fail?
Queries nest more than one level. Which orders came from customers whose rep is on team North? Three tables, three questions:
SELECT order_id, amount
FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE rep_id IN (
SELECT rep_id
FROM reps
WHERE team = 'North'
)
);
Read it the way the database runs it, from the inside out:
Write long queries the same way: start with the innermost question, run it alone, check its result, then wrap the next question around it. When a nested query gives a strange answer, run its inner parts one at a time; the wrong one shows itself.
Try it: press Start. Before each part runs, pick what it returns. The columns and the tables are in the query; open the tables to look.
Interactive · Predict each level, from the inside out — try it in the lesson
Takeaway: read and write nested queries from the inside out: each inner query becomes a value or a list, and the query around it gets simpler.
So far every inner query could run on its own. Now look at this one: which customers have placed an order of 1000 or more?
SELECT name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.amount >= 1000
);
The inner query mentions c.customer_id, a column of the outer row. It can't run alone: it has no customer until the outer query hands it one. This is a correlated subquery, and the database runs it (in principle) once for each outer row: for Acme Ltd it looks for Acme's big orders, for Birch & Co it looks for Birch's, and so on.
EXISTS (subquery) is TRUE when the inner query returns at least one row, FALSE when it returns none. What it selects doesn't matter, so people write SELECT 1. NOT EXISTS is the opposite: keep the customer when nothing is found.
Correlated subqueries also work in SELECT, as a value per row: (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders counts each customer's orders, zero included.
Try it: run the presets and watch the list of runs, one per customer. Then do the tasks.
Interactive · Correlated subqueries: the inner query runs for each row — try it in the lesson
The "Above own average" preset compares each order with the average of its own customer's orders: 101 is above Acme's average of 683.33, 105 above Birch's 800, 104 above Cobalt's 566.67. One number for the whole table couldn't say that.
Takeaway: a subquery that reads the outer row is correlated and runs once per row; EXISTS asks "is there at least one?".
Which reps have no customers yet? Dev has none, so the natural query looks right:
SELECT name FROM reps
WHERE rep_id NOT IN (SELECT rep_id FROM customers);
It returns no rows at all. The reason is one customer: Ember Labs has no rep yet, so its rep_id is NULL, and the list is (11, 12, 13, 11, NULL, 12).
14 NOT IN (11, 12, 13, 11, NULL, 12) means 14 <> 11 AND 14 <> 12 AND … AND 14 <> NULL. Any comparison with NULL is unknown (NULL means "we don't know the value", so the database can't say 14 differs from it). TRUE AND unknown is unknown, and WHERE keeps only rows where the test is TRUE. So Dev is dropped, and so is every other rep. One NULL in a NOT IN list empties the result. IN doesn't have this problem: a match is still TRUE.
Two fixes, both correct:
NOT EXISTS with a correlated subquery: it asks "is there a customer with this rep?", and a NULL rep_id matches nobody.WHERE rep_id IS NOT NULL inside the subquery.Prefer NOT EXISTS: it stays right even if someone later adds NULLs to the column.
Try it: run the NOT IN preset and look at Dev's row in the reps table: a question mark, unknown, dropped. Switch Ember's NULL off and run it again. Then fix the query both ways.
Interactive · NOT IN, NULL and NOT EXISTS — try it in the lesson
Takeaway: NOT IN returns nothing when its list holds a NULL; use NOT EXISTS, or filter the NULLs out of the list.
A query's result is a table, so a subquery can stand where a table stands: in FROM. The outer query reads it like any other table. It needs a name after the closing bracket (AS t); MySQL, SQL Server and PostgreSQL before version 16 refuse it without one, so always give one.
The classic use is an aggregate of aggregates. What does an average customer spend in total? First total each customer, then average the totals:
SELECT AVG(total)
FROM (SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id) AS t;
AVG(SUM(amount)) in one query fails: "aggregate function calls cannot be nested". The subquery in FROM is the way to do it in two steps.
Try it: run the inner query alone first and read its five rows, then the whole query. Then do the tasks.
Interactive · A subquery in FROM: totals first, then the average — try it in the lesson
Check your work · Run task 1. Paste the average customer total. — check it in the lesson
Takeaway: a subquery in FROM is a table made on the spot; name it, then query it like any table.
A subquery in FROM reads from the middle out, and long ones get hard to follow. WITH moves the step to the top and gives it a name. That named step is a common table expression, a CTE:
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT AVG(total)
FROM customer_totals;
Same answer, 1266, but now it reads top to bottom: first the totals, then their average. Three more things WITH gives you:
WITH a AS (…), b AS (SELECT … FROM a) SELECT … FROM b.customer_totals says what the step is; t doesn't.A CTE exists only while its statement runs: it isn't saved like a table or a view.
Try it: run the presets and watch each named step run in order, then feed the next. Then do the tasks.
Interactive · WITH: named steps that feed each other — try it in the lesson
Takeaway: WITH names each step of a long query; later steps and the final query read the earlier ones like tables.
Now choose the tool yourself. A rough guide, not a rule:
The playground knows the three tables and understands SELECT, DISTINCT, JOIN and LEFT JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, subqueries anywhere and WITH. Each task says which columns to show.
Interactive · Subquery and CTE playground — try it in the lesson
If you get an error, read it: it names the problem, and the usual fixes are an alias for a subquery in FROM, one column inside IN, or one row from a scalar subquery.
Takeaway: pick the form that reads most clearly; many questions can be answered with a subquery, a CTE or a JOIN.
Work out each answer from the three tables (open them in any playground above), then check. A hint costs nothing.
SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);SELECT name FROM reps WHERE rep_id NOT IN (SELECT rep_id FROM customers);SELECT MAX(total) FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) AS t;SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);SELECT name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);SELECT name FROM customers WHERE customer_id <> (SELECT customer_id FROM orders);WITH t AS (SELECT city, COUNT(*) AS n FROM customers GROUP BY city) SELECT city FROM t WHERE n = (SELECT MAX(n) FROM t);Six questions on the whole lesson.
Question 1. What makes a subquery correlated?
Question 2. `customers.rep_id` holds 11, 12, 13, 11, NULL and 12. What does `SELECT name FROM reps WHERE rep_id NOT IN (SELECT rep_id FROM customers)` return?
Question 3. Where can a subquery go? (pick all that apply)
Question 4. Which is true of `WITH a AS (…), b AS (…) SELECT …`?
Question 5. In `… WHERE customer_id IN (SELECT customer_id FROM customers WHERE rep_id IN (SELECT rep_id FROM reps WHERE team = 'North'))`, which part do you read first, because it runs on its own?
Question 6. `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)`. What does the 1 do?
Workshopy shares this lesson under CC BY 4.0. Anyone may use and change it, with credit to you. Anyone can learn it here, free. A copy in your workspace is yours to change and run live; your students never need an account.
Version 1Current
· First published
Copies are made from the current version. A copy you already have doesn’t change.
WorkshopyOfficial10 stepsQuiz9 interactiveDiagram
gitversion controlbeginners
WorkshopyOfficial16 steps2 quizzes15 interactiveDiagram
workshopyproduct touronboarding
WorkshopyOfficial10 steps2 quizzes10 interactive
data-literacystatisticsmean and median
WorkshopyOfficial11 steps2 quizzes10 interactive
data-literacycorrelationcausation
WorkshopyOfficial9 steps2 quizzes7 interactive
pythonpandasdataframe
WorkshopyOfficial10 steps2 quizzes9 interactive
excelsumifcountif
Comments
No comments yet. Be the first to say how it went.