Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — CTE stands for
What does CTE stand for?
Common Table ExpressionComputed Table EntryConditional Table ExtensionColumn Type ExpressionHint : A table-like expression you can share within a statement.
Exercise 2 — Open and use
Complete the query so that it names the fire Codémons fire and counts them.
Hint : Open the CTE, then count its rows.
Exercise 3 — Same plan
Compared with a derived table, what does a CTE change for the database engine?
Hint : Different reading order, same work.
Exercise 4 — Twice
What can a CTE do that a derived table cannot, without copying the query?
Hint : One name, many uses.
Exercise 5 — Vocabulary
Match each term with its definition.
WITHAS (…)Exercise 6 — Two in a row
Complete the query so that the second CTE keeps only the trainers with more than 2 badges, then lists their names.
Hint : CTEs are separated by a comma; the second reads from the first.
Exercise 7 — After the statement
You run WITH strong AS (SELECT * FROM codemon WHERE level > 12) SELECT COUNT(*) FROM strong; and then, in a second run, SELECT * FROM strong;. What happens?
Hint : A CTE is not a table.
Exercise 8 — Backwards
WITH b AS (SELECT * FROM a), a AS (SELECT 1 AS x) SELECT * FROM b; — what happens?
Hint : SQLite is lenient here, but the habit to keep is: define before you use.
Exercise 9 — Rename the columns
Complete the query so that the CTE exposes the columns who and badge_count.
Hint : The names in parentheses are the names the main query must use.
Exercise 10 — Why a pipeline?
What is the main benefit of splitting a query into several CTEs?
Hint : Readable, testable steps.
Exercise 11 — Inspect a step
Your 3-CTE pipeline gives a wrong total. What is the quickest check?
Hint : Look at the intermediate rows.
Exercise 12 — How many rows?
Match each CTE with the number of rows it produces (3 trainers have badges, 4 trainers, 5 gyms, 6 battles).
WITH p AS (SELECT trainer_id FROM badges GROUP BY trainer_id) SELECT * FROM pWITH t AS (SELECT * FROM trainers) SELECT * FROM tWITH g AS (SELECT name FROM gyms) SELECT * FROM gWITH b AS (SELECT * FROM battles) SELECT * FROM b3456Exercise 13 — Which tool?
Match each need with the right construct.
CREATE TABLE … AS SELECTExercise 14 — Score in two steps
Complete the pipeline so that scored reads from counted and the final query sorts by score.
Hint : The second CTE reads the first; the final query sorts by the column it selects.
Exercise 15 — The record holder
Complete the query so that it returns the trainer with the most badges, using the CTE twice.
Hint : The highest count, looked up in the same CTE.
Exercise 16 — Inside the parentheses
What goes inside the parentheses after AS in WITH per_trainer AS (…)?
Hint : The CTE is filled by a query of its own.
Exercise 17 — Spot the invalid one
Which statement is not valid?
SELECT x FROM (SELECT 1 AS x) AS a;Hint : WITH always opens the statement.
Exercise 18 — Filter the CTE
Complete the query so that it returns the trainers with at least 3 badges.
Hint : A CTE is filtered like any table, and 3 badges count.
Exercise 19 — Recent badges
WITH recent AS (SELECT * FROM badges WHERE earned_on >= '2023-03-01') SELECT COUNT(*) FROM recent; returns:
Hint : Only the badges of March 1 and March 20 survive the filter.
Exercise 20 — Where things live
Match each piece of punctuation with its place in a statement that has two CTEs.
WITH,SELECT … FROM second_cte;Exercise 21 — Join two CTEs
Complete the join between the two CTEs: a trainer's badges next to their wins.
Hint : Match the trainer ids: the winner column on one side, the trainer column on the other.
Exercise 22 — Same count?
WITH p AS (SELECT * FROM trainers) SELECT COUNT(*) FROM p; compared with SELECT COUNT(*) FROM trainers;
Hint : A CTE is used exactly like a table.
Exercise 23 — Strong and owned
Complete the pipeline so that owned keeps, among the strong Codémons, only those that have a trainer.
Hint : The second step reads the first one, and a trainer means a non-NULL trainer_id.
Exercise 24 — Where is the bug?
WITH a AS (SELECT 1 AS x)
b AS (SELECT 2 AS y)
SELECT * FROM a, b;fails. Why?
Hint : One WITH, commas between the CTEs.
Exercise 25 — Spot the role
In WITH per_trainer AS (…), shares AS (… FROM per_trainer) SELECT … FROM shares, match each piece with its role.
per_trainersharesSELECT … FROM shares(…)