Quizz

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

▶ Do the exercises

Exercise 1 — CTE stands for

What does CTE stand for?

Options
• Common Table Expression
• Computed Table Entry
• Conditional Table Extension
• Column Type Expression

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

Options
• Nothing: the gain is readability for humans
• The result is cached on disk
• The query runs twice as fast
• The CTE becomes a permanent table

Hint : Different reading order, same work.

Exercise 4 — Twice

What can a CTE do that a derived table cannot, without copying the query?

Options
• Be referenced several times in the same statement
• Use GROUP BY
• Return more than one column
• Be joined to a table

Hint : One name, many uses.

Exercise 5 — Vocabulary

Match each term with its definition.

Items to match
• WITH
• AS (…)
• Chaining
• Derived table
Definitions
• Introduces one or more CTEs
• The query that fills a CTE
• A CTE that reads from a previous CTE
• A nameless query inside FROM

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?

Options
• An error: no such table strong — the CTE died with its statement
• The three strong Codémons are listed
• An empty result
• The count is shown again

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?

Options
• It works in SQLite, which tolerates this order — most other databases would refuse it
• An error in every database: b cannot use a, which is defined after it
• It works but b is empty
• Only a is created

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?

Options
• Each step is small, named, and can be checked alone
• The database uses less memory
• It avoids writing JOINs
• CTEs can replace WHERE

Hint : Readable, testable steps.

Exercise 11 — Inspect a step

Your 3-CTE pipeline gives a wrong total. What is the quickest check?

Options
• Keep the CTEs and replace the final query with SELECT * FROM the step you doubt
• Rewrite everything as nested subqueries
• Remove the WITH and run the final query alone
• Add DISTINCT to the final query

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).

Items to match
• WITH p AS (SELECT trainer_id FROM badges GROUP BY trainer_id) SELECT * FROM p
• WITH t AS (SELECT * FROM trainers) SELECT * FROM t
• WITH g AS (SELECT name FROM gyms) SELECT * FROM g
• WITH b AS (SELECT * FROM battles) SELECT * FROM b
Definitions
• 3
• 4
• 5
• 6

Exercise 13 — Which tool?

Match each need with the right construct.

Items to match
• Name an intermediate result used twice
• Split a calculation into readable steps
• Keep a result for later statements
• A one-off subquery that needs no name
Definitions
• A CTE
• Several chained CTEs
• CREATE TABLE … AS SELECT
• A derived table in FROM

Exercise 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 (…)?

Options
• A complete SELECT query
• A list of column names
• The name of an existing table
• A filter condition

Hint : The CTE is filled by a query of its own.

Exercise 17 — Spot the invalid one

Which statement is not valid?

Options
• SELECT x FROM a WITH a AS (SELECT 1 AS x);
• WITH a AS (SELECT 1 AS x) SELECT x FROM a;
• SELECT x FROM (SELECT 1 AS x) AS a;
• WITH a AS (SELECT 1 AS x) SELECT x FROM a WHERE x = 1;

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:

Options
• 2
• 10
• 3
• 0

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.

Items to match
• WITH
• ,
• SELECT … FROM second_cte
• ;
Definitions
• The very beginning of the statement
• Between the two CTEs
• Right after the closing parenthesis of the last CTE
• Once, after the main query

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;

Options
• Same result, 4: the CTE adds a name, not rows
• The CTE version returns 1
• The CTE version returns 0
• An error: COUNT needs a real table

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?

Options
• A comma is missing between the two CTEs
• b must be defined before a
• A CTE cannot be named b
• Two CTEs need two WITH

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.

Items to match
• per_trainer
• shares
• SELECT … FROM shares
• (…)
Definitions
• First step, computed from a base table
• Second step, which reads the first CTE
• The main query
• The body of a CTE
Back to the course