Scalar subqueries
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — A query inside a query
Which Codémons are stronger than average? You could run SELECT AVG(level) FROM codemon (11.5), then type that number into a second query. Or you can let SQL do both steps at once:
SELECT name, level
FROM codemon
WHERE level > (SELECT AVG(level) FROM codemon)
ORDER BY level DESC;The query in parentheses is a subquery. It runs first, returns a single value (11.5), and the outer query uses that value exactly as if you had typed it. Four Codémons are above average.
Why not just type 11.5?
Because the average changes as soon as the data changes. The subquery stays correct forever; the hard-coded number is wrong after the first new Codémon.
Exercise 2 — Scalar: one row, one column
A subquery that returns exactly one row and one column is called a scalar subquery. It can go anywhere a single value can go — in WHERE, but also in SELECT:
SELECT name, level,
(SELECT MAX(level) FROM codemon) - level AS gap_to_best
FROM codemon
ORDER BY gap_to_best;(SELECT MAX(level) FROM codemon) is computed once (15) and level is subtracted from it on every row. To be sure you get a single value, use an aggregate (MAX, COUNT, AVG…) or a filter that can only match one row, like a primary key.
When the subquery returns several rows
SQLite silently keeps the first row and ignores the others; most other databases raise an error. Either way, write scalar subqueries so that they can only return one value.
Exercise 3 — What does scalar mean?
What makes a subquery scalar?
Hint : Think of a value you could type in place of the subquery.
Exercise 4 — Below average
Complete the query so that it lists the Codémons whose level is below the average level.
Hint : A comparison operator, then the aggregate that computes an average.
Exercise 5 — The strongest, without LIMIT
You already know how to find the strongest Codémon: ORDER BY level DESC LIMIT 1. A scalar subquery gives another way — and it handles ties:
SELECT name, level
FROM codemon
WHERE level = (SELECT MAX(level) FROM codemon);If two Codémons shared the top level, LIMIT 1 would drop one of them; the subquery keeps both. The same pattern finds the row behind any aggregate — the trainer with the most badges, for instance:
SELECT name, badges
FROM trainers
WHERE badges = (SELECT MAX(badges) FROM trainers);Exercise 6 — What does each subquery return?
5 gyms, 6 battles, Codémon levels from 8 to 15. Match each scalar subquery with its value.
(SELECT COUNT(*) FROM gyms)(SELECT MIN(level) FROM codemon)(SELECT COUNT(*) FROM battles)(SELECT MAX(level) FROM codemon)58615Exercise 7 — Percent of the total
The levels of all Codémons add up to 92. What does this query return for Aquachu (level 15)?
SELECT name,
ROUND(level * 100.0 / (SELECT SUM(level) FROM codemon), 1) AS pct
FROM codemon
WHERE name = 'Aquachu';16.315.0100.0Hint : The subquery is just the number 92.
Exercise 8 — Stronger than Morgan's team
Complete the query so that it lists the Codémons stronger than every Codémon of Morgan (trainer_id 2).
Hint : Stronger than all of them means stronger than the strongest one.
Exercise 9 — Above the average trainer
Professor Pi wants the trainers who hold more badges than the average trainer.
Return name and badges, sorted by badges descending. Use a scalar subquery — do not type the average by hand.
-- Write your query below