Derived tables
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — A subquery in FROM
A subquery can also sit in the FROM clause. Its result is used as if it were a table — a derived table.
The average number of badges per trainer, among trainers who have at least one:
SELECT ROUND(AVG(badge_count), 2) AS avg_badges
FROM (SELECT trainer_id, COUNT(*) AS badge_count
FROM badges
GROUP BY trainer_id) AS per_trainer;The inner query produces 3 rows (3, 5, 2); the outer query averages them (3.33). You cannot write AVG(COUNT(*)) directly — aggregates do not nest — so the two steps need two levels.
Always name it
A derived table must always get a name: here AS per_trainer. Without it the query is not valid, and the name is what lets you refer to its columns, like per_trainer.badge_count.
Exercise 2 — Joining a derived table
A derived table can be joined like any other. Wins per trainer, with names:
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
ORDER BY w.wins DESC;The aggregation happens before the join, inside w. With a LEFT JOIN and COALESCE, Jordan comes back with 0:
SELECT t.name, COALESCE(w.wins, 0) AS wins
FROM trainers t
LEFT JOIN (SELECT winner_id, COUNT(*) AS wins
FROM battles
GROUP BY winner_id) AS w ON w.winner_id = t.trainer_id
ORDER BY wins DESC, t.name;Exercise 3 — Why two levels?
Why can't you write SELECT MAX(COUNT(*)) FROM badges GROUP BY trainer_id to get the record number of badges?
Hint : One aggregate per level.
Exercise 4 — The record
Complete the query so that it returns the highest number of badges held by a single trainer.
Hint : The derived table is a full query; the outer query picks its maximum.
Exercise 5 — Two summaries side by side
Remember the fan-out problem? Joining badges and battles to trainers multiplied the rows. Derived tables avoid it: aggregate each side first, then join the summaries — each has one row per trainer, so nothing can multiply:
SELECT t.name,
COALESCE(b.badge_count, 0) AS badges,
COALESCE(w.wins, 0) AS wins
FROM trainers t
LEFT JOIN (SELECT trainer_id, COUNT(*) AS badge_count
FROM badges GROUP BY trainer_id) AS b ON b.trainer_id = t.trainer_id
LEFT JOIN (SELECT winner_id, COUNT(*) AS wins
FROM battles GROUP BY winner_id) AS w ON w.winner_id = t.trainer_id
ORDER BY wins DESC, badges DESC;No COUNT(DISTINCT …) needed: the counts were done before the joins.
Readability
Long derived tables make a query hard to read. A later lesson introduces WITH, which lets you name them above the query instead of inside the FROM.
Exercise 6 — Where does the subquery go?
Match each placement with the kind of subquery it calls for.
Exercise 7 — Read the levels
What does this query return? (Reminder: fire and plant are the only types with two Codémons.)
SELECT COUNT(*) AS big_types
FROM (SELECT type
FROM codemon
GROUP BY type
HAVING COUNT(*) >= 2) AS t;2681Hint : Inner query: the types with at least two Codémons. Outer query: how many such types.
Exercise 8 — Team strength
Professor Pi wants each trainer's strongest Codémon level — every trainer, Jordan included.
Build the maximum level per trainer_id in a derived table, LEFT JOIN it to trainers, and return trainer (the name) and best_level (NULL for Jordan). Sort by best_level descending.
-- Write your query below