Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — ALL or not
What is the difference between UNION and UNION ALL?
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;4935Hint : 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;94520Hint : 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;Moonlight HollowAzure CityHint : 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;Moonlight HollowHint : 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.
SELECT type FROM codemon INTERSECT SELECT type FROM gymsSELECT type FROM codemon EXCEPT SELECT type FROM gymsSELECT type FROM gyms EXCEPT SELECT type FROM codemonSELECT type FROM codemon UNION ALL SELECT type FROM gyms51013Exercise 8 — Two columns, one column
What happens with SELECT name, type FROM codemon UNION SELECT name FROM gyms?
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?
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?
placetownhometownHint : 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;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?
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.
UNIONINTERSECTEXCEPTUNION ALLExercise 16 — LIMIT on a stack
SELECT name FROM trainers UNION SELECT name FROM gyms LIMIT 3 — what does LIMIT 3 apply to?
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?
MINUSUNIONINTERSECTEXCEPTHint : 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;31264Hint : 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?
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.
SELECT type FROM gyms EXCEPT SELECT type FROM codemon WHERE trainer_id = 1SELECT type FROM codemon WHERE trainer_id = 1 INTERSECT SELECT type FROM gymsSELECT type FROM codemon WHERE trainer_id = 3 EXCEPT SELECT type FROM gymsSELECT type FROM codemon WHERE trainer_id = 2 INTERSECT SELECT type FROM codemon WHERE trainer_id = 3Exercise 23 — A WHERE on each side
Can each SELECT of a UNION have its own WHERE clause?
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;Hint : UNION deduplicates the whole stack.