Multiple CTEs in one query in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does CTE stand for in SQL?

CTE stands for Common Table Expression. It is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

beginner

How do you define multiple CTEs in a single SQL query?

You define multiple CTEs by separating each CTE with a comma after the WITH keyword. Each CTE has its own name and query inside the same WITH clause.

intermediate

Why use multiple CTEs in one query?

Using multiple CTEs helps break down complex queries into smaller, easier-to-understand parts. Each CTE can build on the previous ones, making the query clearer and more organized.

beginner

Write a simple example of two CTEs used in one query.

WITH first_cte AS (SELECT 1 AS num), second_cte AS (SELECT num + 1 AS num_plus_one FROM first_cte) SELECT * FROM second_cte;

intermediate

Can CTEs reference each other in the same WITH clause?

Yes, CTEs can reference earlier CTEs defined before them in the same WITH clause, but not the ones defined after.

How do you separate multiple CTEs in a single SQL query?

A With a comma

B With a semicolon

C With the AND keyword

D With the OR keyword

Multiple CTEs are separated by commas within the WITH clause.

Which keyword starts the definition of CTEs in a SQL query?

A SELECT

B JOIN

C WITH

D FROM

The WITH keyword starts the CTE definitions.

Can a CTE reference another CTE defined after it in the same WITH clause?

A Only in PostgreSQL

B Yes, always

C Only if you use UNION

D No, only earlier CTEs can be referenced

A CTE can only reference CTEs defined before it in the same WITH clause.

What is the main benefit of using multiple CTEs in one query?

A To make queries run faster

B To break complex queries into simpler parts

C To avoid using indexes

D To store data permanently

Multiple CTEs help organize complex queries into simpler, readable parts.

Which of the following is a valid way to start multiple CTEs?

A WITH cte1 AS (...), cte2 AS (...)

B WITH cte1 AS (...); WITH cte2 AS (...)

C SELECT cte1 AS (...), cte2 AS (...)

D FROM cte1, cte2

Multiple CTEs are defined together after a single WITH keyword, separated by commas.

Explain how to write multiple CTEs in one SQL query and why it is useful.

Think about how you can organize a big task into smaller steps.

Describe the rules for referencing CTEs within multiple CTEs in the same query.

Consider the order you read a list from top to bottom.