Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — What LEFT JOIN keeps
Which rows does FROM trainers t LEFT JOIN codemon c ON ... keep?
Hint : Left = the table after FROM.
Exercise 2 — Trainers first
Complete the query so that every trainer is listed, even without Codémon.
Hint : Protect the left side, then write the matching condition.
Exercise 3 — The four joins
Our database: 4 trainers (Jordan owns nothing), 8 Codémons (Plantachu is wild). Match each join of trainers t with codemon c to its row count.
INNER JOINLEFT JOINRIGHT JOINFULL OUTER JOIN78 (7 + Jordan)8 (7 + Plantachu)9Exercise 4 — Where does Plantachu go?
Plantachu has trainer_id = NULL. What does this query show for Plantachu?
SELECT c.name, t.name AS trainer
FROM codemon c
LEFT JOIN trainers t ON c.trainer_id = t.trainer_id;Hint : codemon is the left table here.
Exercise 5 — Count properly
Complete the query so that trainers without Codémon get a count of 0.
Hint : COUNT(*) would give 1 to Jordan; count a Codémon column instead.
Exercise 6 — OUTER is optional
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
Hint : Think of the info card in the very first exercise of this lesson.
Exercise 7 — Pick the right tool
Match each need with the join that answers it, starting from FROM trainers t ... codemon c.
INNER JOINLEFT JOINRIGHT JOINFULL OUTER JOINExercise 8 — Reading NULLs
In the result of FROM trainers t FULL OUTER JOIN codemon c ON ..., a row has NULL in t.name and Plantachu in c.name. What does it mean?
Hint : The NULL side is the side where nothing matched.
Exercise 9 — Find the loners
Complete the anti-join that returns the trainers who own no Codémon.
Hint : Filter the final result on the right-side key that stayed empty.
Exercise 10 — Why not INNER?
Why can't an INNER JOIN be used to find the trainers who own no Codémon?
Hint : Where would Jordan be in an INNER JOIN?
Exercise 11 — The silent INNER JOIN
How many rows does this query return? (Only Morgan and Taylor own fire Codémons.)
SELECT t.name, c.name
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
WHERE c.type = 'fire';2478Hint : The WHERE runs after the join: rows with a NULL Codémon fail the test and disappear.
Exercise 12 — Keep them all, attach only fire
Complete the query so that all four trainers appear, with a fire Codémon attached when they have one and NULL otherwise.
Hint : Both conditions must be part of the join, not of a filter applied afterwards.
Exercise 13 — Where to put the condition
Match each goal with where the condition belongs in a LEFT JOIN query.
WHERE c.type = 'fire'Exercise 14 — Swap instead of RIGHT
Which query returns the same rows as SELECT c.name, t.name FROM trainers t RIGHT JOIN codemon c ON c.trainer_id = t.trainer_id?
Hint : A RIGHT JOIN is a LEFT JOIN with the tables swapped.
Exercise 15 — Name the wild ones
Complete the query so that every Codémon is shown with its trainer's name, or wild when it has none.
Hint : Replace the trainer's missing name, and keep every Codémon.
Exercise 16 — SUM over nothing
With a LEFT JOIN from trainers to codemon and GROUP BY t.name, what does SUM(c.level) return for Jordan?
NULL01Hint : Jordan's only row has NULL in every Codémon column, and aggregates ignore NULL — a sum of nothing is NULL.
Exercise 17 — Zero levels
Complete the query so that the total level of each trainer's team is shown, with 0 instead of NULL for trainers who own nothing.
Hint : A sum of nothing is NULL; give it a fallback.
Exercise 18 — Which key to test?
In an anti-join looking for trainers without Codémon, why test c.codemon_id IS NULL rather than c.capture_date IS NULL?
Hint : Which column is guaranteed to be filled on every real row?
Exercise 19 — Results for Jordan
Each query uses FROM trainers t LEFT JOIN codemon c ON c.trainer_id = t.trainer_id GROUP BY t.name. Match each expression with its value for Jordan.
COUNT(*)COUNT(c.codemon_id)MAX(c.level)COALESCE(MAX(c.level), -1)10NULL-1Exercise 20 — Full join, full count
Which query lists every trainer and every Codémon in a single result, including Jordan and Plantachu?
Hint : Only one join protects both sides.
Exercise 21 — Both sides
Complete the query so that unmatched trainers and unmatched Codémons both appear.
Hint : Both circles of the diagram.
Exercise 22 — Spot the bug
This query is supposed to return the trainers without Codémon but returns nothing. Why?
SELECT t.name
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
WHERE c.codemon_id = NULL;Hint : Remember the NULL lesson: how do you test for NULL?
Exercise 23 — Trainers without a plant Codémon
Complete the query so that it returns the trainers who own no plant-type Codémon (including trainers who own nothing).
Hint : The type condition must be part of the join; the NULL test filters the result.
Exercise 24 — Vocabulary
Match each term with its meaning.
Exercise 25 — Azure City roll call
Morgan (2 Codémons) and Jordan (none) both live in Azure City. How many rows does this query return?
SELECT t.name, c.name
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
WHERE t.hometown = 'Azure City';3241Hint : The WHERE is on a trainer column, so Jordan's NULL row passes the test.