Quizz

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

▶ Do the exercises

Exercise 1 — Which one is scalar?

Which subquery is guaranteed to return exactly one row and one column?

Options
• (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);
Options
• Alex and Morgan
• Morgan only
• Alex, Morgan and Taylor
• No rows

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);
Options
• No rows, because the list contains a NULL
• Jordan
• Jordan and Plantachu
• An error

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?

Options
• Nothing special: EXISTS only checks whether rows come back, the selected value is ignored
• Keep only the trainers with exactly 1 badge
• Select the badge whose id is 1
• Count the rows, one at a time

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);
Options
• Jordan
• No rows, because of the NULL
• Plantachu
• All four trainers

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.

Items to match
• (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')
Definitions
• Not correlated: a single fixed value
• Correlated: it uses t from the outer query
• Correlated: it uses c from the outer query
• Not correlated: a fixed list of ids

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?

Options
• A table with the subquery's columns, which should be given an alias
• A single value
• A list of values for IN
• A yes or no answer

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

Hint : 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');
Options
• 4 rows: the Codémons above level 11
• An error: the subquery returns a level, not a number
• 1 row: Shadowchu
• 8 rows

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';
Options
• -1.5
• 1.5
• 10
• NULL

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

Items to match
• 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)
Definitions
• 3
• 0
• 4
• 1

Exercise 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.)

Options
• 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?

Options
• Once, and its value is reused for every row of the outer query
• Once per row of the outer query
• After the outer query, to check its result
• Only if the outer query returns no rows

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;
Options
• 3 rows: Jordan never won, so the INNER JOIN drops him
• 4 rows, Jordan with 0
• 4 rows, Jordan with NULL
• 6 rows, one per battle

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?

Options
• Both return the trainers with at least one badge; the EXISTS version is correlated, the IN version is not
• Only the IN version works: EXISTS needs a GROUP BY
• The EXISTS version returns one row per badge
• The IN version returns duplicates

Hint : Same rows, different mechanics.

Exercise 22 — What happens?

Match each fragment with what happens when you run it.

Items to match
• WHERE trainer_id IN (SELECT trainer_id, gym_id FROM badges)
• SELECT MAX(COUNT(*)) FROM badges
• WHERE trainer_id NOT IN (SELECT trainer_id FROM codemon)
• FROM (SELECT trainer_id, COUNT(*) AS n FROM badges GROUP BY trainer_id) AS b
Definitions
• Error: the sub-select returns 2 columns
• Error: aggregates cannot be nested
• No rows: a NULL is in the list
• Works: a derived table named b

Exercise 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;
Options
• 50.0
• 5
• 10
• 0.5

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

Options
• SELECT name FROM trainers t WHERE EXISTS (SELECT 1 FROM battles b WHERE b.winner_id = t.trainer_id);
• SELECT name FROM trainers t WHERE EXISTS (SELECT winner_id FROM battles);
• SELECT name FROM trainers t WHERE EXISTS (SELECT 1 FROM battles b WHERE b.winner_id = b.trainer1_id);
• SELECT name FROM trainers t WHERE t.trainer_id EXISTS (SELECT winner_id FROM battles);

Hint : The correlation must link the battle's winner to the outer trainer.

Back to the course