Derived tables

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

▶ Do the exercises

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

Options
• Aggregates cannot be nested: count per trainer in a derived table, then take MAX outside
• Because MAX only works on text columns
• Because GROUP BY must be written before FROM
• You can: it returns 5

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.

Items to match
• In WHERE, compared with = or >
• In WHERE, after IN
• In FROM, with an alias
• In WHERE, after EXISTS
Definitions
• Scalar subquery: one value
• List subquery: one column
• Derived table: rows and columns
• Correlated subquery: yes or no

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;
Options
• 2
• 6
• 8
• 1

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

Starter code
-- Write your query below
Back to the course