Quizz

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

▶ Do the exercises

Exercise 1 — ALL or not

What is the difference between UNION and UNION ALL?

Options
• UNION removes duplicate rows, UNION ALL keeps them
• UNION ALL also stacks the columns, not just the rows
• UNION ALL requires the same column names in both SELECTs
• There is none: ALL is optional

Hint : ALL = keep all rows.

Exercise 2 — Count the cities

Trainers live in Sunset Valley, Azure City, Stone Ridge and Azure City; gyms stand in Sunset Valley, Azure City, Stone Ridge, Azure City and Moonlight Hollow. How many rows does this return?

SELECT hometown FROM trainers UNION SELECT city FROM gyms;
Options
• 4
• 9
• 3
• 5

Hint : Distinct cities across both lists.

Exercise 3 — Count again, with ALL

Same lists as before (4 hometowns, 5 gym cities). How many rows now?

SELECT hometown FROM trainers UNION ALL SELECT city FROM gyms;
Options
• 9
• 4
• 5
• 20

Hint : Nothing is removed: 4 + 5.

Exercise 4 — Trainer and gym

Complete the query so that it lists the cities that have both a trainer and a gym.

Hint : In both lists; gyms have a city, not a hometown.

Exercise 5 — Gym but no trainer

What does this query return?

SELECT city FROM gyms EXCEPT SELECT hometown FROM trainers;
Options
• Moonlight Hollow
• No rows
• Azure City
• All five gym cities

Hint : The only gym city where nobody lives.

Exercise 6 — Swapped

And with the two SELECTs swapped?

SELECT hometown FROM trainers EXCEPT SELECT city FROM gyms;
Options
• No rows: every hometown has a gym
• Moonlight Hollow
• The same result as before: order does not matter
• 4 rows

Hint : Trainers' cities minus gyms' cities.

Exercise 7 — Types, four ways

8 Codémons of 6 types; 5 gyms of 5 types, all of them Codémon types too. Match each query with its number of rows.

Items to match
• SELECT type FROM codemon INTERSECT SELECT type FROM gyms
• SELECT type FROM codemon EXCEPT SELECT type FROM gyms
• SELECT type FROM gyms EXCEPT SELECT type FROM codemon
• SELECT type FROM codemon UNION ALL SELECT type FROM gyms
Definitions
• 5
• 1
• 0
• 13

Exercise 8 — Two columns, one column

What happens with SELECT name, type FROM codemon UNION SELECT name FROM gyms?

Options
• An error: different number of result columns
• 13 rows, gyms with an empty type
• Only the common column, name, is kept
• 13 rows with three columns

Hint : Same number of columns on both sides, always.

Exercise 9 — Where does ORDER BY go?

In a UNION query, where is ORDER BY written, and what does it sort?

Options
• Once, after the last SELECT: it sorts the whole combined result
• After each SELECT, to sort each part separately
• Before the first SELECT
• ORDER BY is not allowed with UNION

Hint : One result, one sort.

Exercise 10 — Label the rows

Complete the query so that it lists trainers and gyms of Azure City together, each row tagged 'trainer' or 'gym'.

Hint : A text constant for the label; the gyms table calls its city column city.

Exercise 11 — Whose names?

SELECT hometown AS place FROM trainers UNION SELECT city AS town FROM gyms — what is the result column called?

Options
• place
• town
• hometown
• An error: the aliases must match

Hint : The first SELECT decides.

Exercise 12 — The NULL that does not bite

codemon.trainer_id contains a NULL (the wild Plantachu). What does this query return?

SELECT trainer_id FROM trainers EXCEPT SELECT trainer_id FROM codemon;
Options
• 4, Jordan's id: the NULL in the second list changes nothing
• No rows: a NULL in the second list cancels the whole result
• An error
• NULL

Hint : Set operations compare values; a NULL on the right only matters for a NULL on the left.

Exercise 13 — Never won

Complete the query so that it returns the ids of the trainers who never won a battle.

Hint : All trainers minus the winners.

Exercise 14 — Always distinct

What is true about the result of INTERSECT and EXCEPT?

Options
• It contains no duplicates, even if the inputs had some
• It keeps duplicates, like UNION ALL
• It only contains rows of the second SELECT
• It must have exactly one column

Hint : Like UNION, they work on sets of distinct rows.

Exercise 15 — Two circles

Picture two overlapping circles, A on the left and B on the right. Match each area with the operator.

Items to match
• Both circles, each value once
• Only the overlap
• Circle A minus the overlap
• Both circles, the overlap counted twice
Definitions
• UNION
• INTERSECT
• EXCEPT
• UNION ALL

Exercise 16 — LIMIT on a stack

SELECT name FROM trainers UNION SELECT name FROM gyms LIMIT 3 — what does LIMIT 3 apply to?

Options
• The whole combined result: 3 rows in total
• Each SELECT: 3 trainers plus 3 gyms
• Only the second SELECT
• Nothing: LIMIT is ignored with UNION

Hint : Like ORDER BY, LIMIT is shared and applies at the end.

Exercise 17 — Morgan's exclusive

Complete the query so that it returns the Codémon types that Morgan (trainer_id 2) owns and that no other trainer owns.

Hint : Morgan's types minus the types of everyone who is not Morgan.

Exercise 18 — Not in SQLite

Which of these is not a set operator in SQLite?

Options
• MINUS
• UNION
• INTERSECT
• EXCEPT

Hint : SQLite spells the difference EXCEPT.

Exercise 19 — Fighters, once each

Six battles, fought by Alex, Morgan and Taylor in various pairs. How many rows does this return?

SELECT trainer1_id FROM battles UNION SELECT trainer2_id FROM battles;
Options
• 3
• 12
• 6
• 4

Hint : Distinct ids across both columns.

Exercise 20 — Sorted cities

Complete the query so that it lists every distinct city of gyms and trainers, sorted alphabetically.

Hint : Distinct means no ALL, and ORDER BY uses the first SELECT's column name.

Exercise 21 — No overlap

No trainer has the same name as a gym. What does SELECT name FROM trainers INTERSECT SELECT name FROM gyms return?

Options
• No rows
• 9 rows
• 4 rows
• An error

Hint : The overlap of two lists with nothing in common.

Exercise 22 — Who can counter what

Gym types: water, fire, rock, electric, ghost. Alex (1) owns water and electric; Morgan (2) fire and ghost; Taylor (3) plant, rock and fire. Match each query with its result.

Items to match
• SELECT type FROM gyms EXCEPT SELECT type FROM codemon WHERE trainer_id = 1
• SELECT type FROM codemon WHERE trainer_id = 1 INTERSECT SELECT type FROM gyms
• SELECT type FROM codemon WHERE trainer_id = 3 EXCEPT SELECT type FROM gyms
• SELECT type FROM codemon WHERE trainer_id = 2 INTERSECT SELECT type FROM codemon WHERE trainer_id = 3
Definitions
• fire, rock, ghost
• water, electric
• plant
• fire

Exercise 23 — A WHERE on each side

Can each SELECT of a UNION have its own WHERE clause?

Options
• Yes: each SELECT is complete and independent; only ORDER BY and LIMIT are shared, at the end
• No: a single WHERE at the end filters the whole result
• Only the first SELECT may have a WHERE
• Only with UNION ALL

Hint : Think of each SELECT as a full query.

Exercise 24 — Gyms Taylor cannot counter

Complete the query so that it returns the gym types for which Taylor (trainer_id 3) owns no Codémon of that type.

Hint : Gym types minus Taylor's types; Taylor is identified by trainer_id.

Exercise 25 — Same list twice

Six battles were won by three different trainers. What does this query return?

SELECT winner_id FROM battles UNION SELECT winner_id FROM battles;
Options
• 3 rows: UNION removes duplicates, even inside a single list
• 12 rows
• 6 rows
• An error: the same table cannot appear twice

Hint : UNION deduplicates the whole stack.

Back to the course