Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Which one is scalar?
Which subquery is guaranteed to return exactly one row and one column?
(SELECT MAX(level) FROM codemon)(SELECT level FROM codemon)(SELECT trainer_id, gym_id FROM badges)(SELECT name FROM gyms WHERE city = 'Azure City')Hint : An aggregate without GROUP BY always gives one value.
Exercise 2 — At the top
Complete the query so that it returns the Codémon(s) at the maximum level.
Hint : Equal to the highest level.
Exercise 3 — Above the average
Trainers hold 3, 5, 2 and 0 badges. What does this query return?
SELECT name
FROM trainers
WHERE badges > (SELECT AVG(badges) FROM trainers);Hint : The average is 2.5.
Exercise 4 — The wild card
codemon.trainer_id is NULL for the wild Plantachu. What does this query return?
SELECT name
FROM trainers
WHERE trainer_id NOT IN (SELECT trainer_id FROM codemon);Hint : 4 <> NULL is unknown, and one unknown breaks the whole NOT IN.
Exercise 5 — Fixing NOT IN
Complete the query so that it returns the trainers who own no Codémon — Jordan — despite the wild Codémon's NULL.
Hint : Exclude the NULLs from the list before negating.
Exercise 6 — What is the 1 for?
In WHERE EXISTS (SELECT 1 FROM badges bd WHERE bd.trainer_id = t.trainer_id), what does SELECT 1 mean?
Hint : You could write SELECT * or SELECT 'hello': same result.
Exercise 7 — NOT EXISTS and the wild one
Same question as before, written with NOT EXISTS. What does it return?
SELECT name
FROM trainers t
WHERE NOT EXISTS (SELECT 1 FROM codemon c WHERE c.trainer_id = t.trainer_id);Hint : For Jordan, no Codémon row has trainer_id = 4: the subquery is empty.
Exercise 8 — Correlated or not?
Match each subquery with its nature.
(SELECT AVG(level) FROM codemon)(SELECT COUNT(*) FROM badges bd WHERE bd.trainer_id = t.trainer_id)(SELECT MAX(level) FROM codemon c2 WHERE c2.type = c.type)(SELECT gym_id FROM gyms WHERE city = 'Azure City')Exercise 9 — Name the table
Complete the query so that it returns the record number of badges held by one trainer.
Hint : The derived table gets an alias; the outer query takes the maximum.
Exercise 10 — Seen from outside
When a subquery is placed in FROM, what does the outer query see?
Hint : Derived table: rows and columns, like any table.
Exercise 11 — Distinct winners
Six battles were won by Morgan (3), Taylor (2) and Alex (1). What does this query return?
SELECT COUNT(*)
FROM (SELECT winner_id FROM battles GROUP BY winner_id) AS w;3641Hint : The inner query has one row per distinct winner.
Exercise 12 — Badges from Stone Ridge
Complete the query so that it lists the trainers who earned a badge in a gym located in Stone Ridge.
Hint : Each list must contain the kind of id it is compared with.
Exercise 13 — Compared with one row
Shadowchu is level 11. What does this query return?
SELECT name, level
FROM codemon
WHERE level > (SELECT level FROM codemon WHERE name = 'Shadowchu');Hint : Filtering on a unique name gives a single value: 11.
Exercise 14 — Distance to the average
The average level is 11.5 and Voltachu is level 10. What is diff for Voltachu?
SELECT name, level - (SELECT AVG(level) FROM codemon) AS diff
FROM codemon
WHERE name = 'Voltachu';-1.51.510NULLHint : 10 minus 11.5.
Exercise 15 — How many rows?
4 trainers (3 with badges), 5 gyms (all handed out badges), 8 Codémons (4 above average), 3 different winners. Match each query with its number of rows.
SELECT name FROM trainers WHERE trainer_id IN (SELECT trainer_id FROM badges)SELECT name FROM gyms WHERE gym_id NOT IN (SELECT gym_id FROM badges)SELECT name FROM codemon WHERE level > (SELECT AVG(level) FROM codemon)SELECT name FROM trainers t WHERE NOT EXISTS (SELECT 1 FROM battles b WHERE b.winner_id = t.trainer_id)3041Exercise 16 — NULL-proof
Which condition finds the trainers who never earned a badge and stays correct even if badges.trainer_id contained NULLs? (The outer query is SELECT name FROM trainers t.)
WHERE NOT EXISTS (SELECT 1 FROM badges bd WHERE bd.trainer_id = t.trainer_id)WHERE t.trainer_id NOT IN (SELECT trainer_id FROM badges)WHERE t.trainer_id <> (SELECT trainer_id FROM badges)WHERE t.trainer_id NOT IN (SELECT COUNT(*) FROM badges)Hint : NOT EXISTS never builds a list, so no NULL can poison it.
Exercise 17 — Team size
Complete the query so that it shows each trainer's number of Codémons, largest team first.
Hint : The correlation points to the outer alias; largest first means descending.
Exercise 18 — When does it run?
A subquery that does not reference the outer query, like (SELECT AVG(level) FROM codemon): how is it evaluated?
Hint : No correlation, no reason to recompute.
Exercise 19 — Where did Jordan go?
What does this query return?
SELECT t.name, w.wins
FROM trainers t
INNER JOIN (SELECT winner_id, COUNT(*) AS wins
FROM battles GROUP BY winner_id) AS w ON w.winner_id = t.trainer_id;Hint : The derived table has no row for winner_id 4.
Exercise 20 — Taylor's gyms
Complete the query so that it lists the gyms where Taylor (trainer_id 3) earned a badge.
Hint : Yes/no per gym, and the correlation links the badge to the current gym.
Exercise 21 — Two ways, one answer
WHERE trainer_id IN (SELECT trainer_id FROM badges) and WHERE EXISTS (SELECT 1 FROM badges bd WHERE bd.trainer_id = t.trainer_id): what is true?
Hint : Same rows, different mechanics.
Exercise 22 — What happens?
Match each fragment with what happens when you run it.
WHERE trainer_id IN (SELECT trainer_id, gym_id FROM badges)SELECT MAX(COUNT(*)) FROM badgesWHERE trainer_id NOT IN (SELECT trainer_id FROM codemon)FROM (SELECT trainer_id, COUNT(*) AS n FROM badges GROUP BY trainer_id) AS bExercise 23 — Morgan's share
Morgan holds 5 of the 10 badges. What does this query return?
SELECT ROUND(100.0 * (SELECT COUNT(*) FROM badges WHERE trainer_id = 2)
/ (SELECT COUNT(*) FROM badges), 0) AS pct;50.05100.5Hint : Two scalar subqueries: 5 and 10.
Exercise 24 — Everyone, with zeros
Complete the query so that it shows every trainer with their number of badges, 0 for Jordan.
Hint : Keep every trainer, then replace the missing count.
Exercise 25 — Rewrite with EXISTS
Which query is a correct EXISTS rewrite of SELECT name FROM trainers WHERE trainer_id IN (SELECT winner_id FROM battles)?
Hint : The correlation must link the battle's winner to the outer trainer.