Quizz

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — What LEFT JOIN keeps

Which rows does FROM trainers t LEFT JOIN codemon c ON ... keep?

Options
• Every trainer, with NULL Codémon columns when there is no match
• Every Codémon, with NULL trainer columns when there is no match
• Only the matching rows
• Only the rows with a NULL

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.

Items to match
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
Definitions
• 7
• 8 (7 + Jordan)
• 8 (7 + Plantachu)
• 9

Exercise 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;
Options
• A row with Plantachu and NULL as trainer
• Nothing: Plantachu is dropped
• A row with Plantachu and Jordan as trainer
• An error because of the NULL

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?

Options
• None: OUTER is an optional word, both mean the same thing
• LEFT OUTER JOIN also keeps the unmatched rows of the right table
• LEFT JOIN only works with two tables
• LEFT OUTER JOIN is the SQLite name, LEFT JOIN the MySQL name

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.

Items to match
• Only trainers who own Codémons, with their Codémons
• Every trainer, even without Codémon (and no wild Codémon)
• Every Codémon, even wild (and no trainer without Codémon)
• Every trainer and every Codémon
Definitions
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

Exercise 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?

Options
• Plantachu has no trainer
• A trainer has no name
• Plantachu belongs to every trainer
• The join condition is wrong

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?

Options
• Because those trainers have no match, and INNER JOIN only returns matched rows
• Because INNER JOIN cannot be combined with WHERE
• Because INNER JOIN does not support the IS NULL operator
• It can: INNER JOIN and LEFT JOIN give the same result

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';
Options
• 2
• 4
• 7
• 8

Hint : 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.

Items to match
• Keep every trainer, attach only their fire Codémons
• Keep only the trainers who own a fire Codémon
• Find the trainers with no Codémon at all
• Match a Codémon with its trainer
Definitions
• In the ON clause, with AND
• In the WHERE clause: WHERE c.type = 'fire'
• WHERE c.codemon_id IS NULL
• ON c.trainer_id = t.trainer_id

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?

Options
• SELECT c.name, t.name FROM codemon c LEFT JOIN trainers t ON c.trainer_id = t.trainer_id;
• SELECT c.name, t.name FROM codemon c RIGHT JOIN trainers t ON c.trainer_id = t.trainer_id;
• SELECT c.name, t.name FROM trainers t LEFT JOIN codemon c ON c.trainer_id = t.trainer_id;
• SELECT c.name, t.name FROM trainers t INNER 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?

Options
• NULL
• 0
• 1
• An error

Hint : 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?

Options
• A real Codémon can have a NULL capture_date, but never a NULL codemon_id
• Dates cannot be compared with IS NULL
• codemon_id is faster to read
• There is no difference

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.

Items to match
• COUNT(*)
• COUNT(c.codemon_id)
• MAX(c.level)
• COALESCE(MAX(c.level), -1)
Definitions
• 1
• 0
• NULL
• -1

Exercise 20 — Full join, full count

Which query lists every trainer and every Codémon in a single result, including Jordan and Plantachu?

Options
• SELECT t.name, c.name FROM trainers t FULL OUTER JOIN codemon c ON c.trainer_id = t.trainer_id;
• SELECT t.name, c.name FROM trainers t LEFT JOIN codemon c ON c.trainer_id = t.trainer_id;
• SELECT t.name, c.name FROM trainers t RIGHT JOIN codemon c ON c.trainer_id = t.trainer_id;
• SELECT t.name, c.name FROM trainers t INNER JOIN codemon c ON c.trainer_id = t.trainer_id;

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;
Options
• = NULL is never true; it must be IS NULL
• LEFT JOIN should be RIGHT JOIN
• The condition should be in ON
• Jordan is not in the trainers table

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.

Items to match
• Outer join
• Anti-join
• Left table
• Unmatched row
Definitions
• A join that keeps unmatched rows (LEFT, RIGHT or FULL)
• LEFT JOIN + WHERE key IS NULL, to find rows with no match
• The table written after FROM
• A row whose columns from the other table are all NULL

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';
Options
• 3
• 2
• 4
• 1

Hint : The WHERE is on a trainer column, so Jordan's NULL row passes the test.

Back to the course