SQL Common Table Expressions (CTEs) 2

Question 1

Which keyword introduces a Common Table Expression in standard SQL?

Accepted Answer

WITH

Answer

A CTE is defined using the WITH keyword followed by the CTE name and its query.

Question 2

How long does a non-recursive CTE persist?

Accepted Answer

Only for the single statement it is attached to

Answer

A CTE exists only for the duration of the single query in which it is defined.

Question 3

What separates multiple CTEs defined in the same WITH clause?

Accepted Answer

A comma

Answer

Multiple CTEs are chained in one WITH clause, separated by commas.

Question 4

Can a later CTE in the same WITH clause reference an earlier CTE?

Accepted Answer

Yes, earlier CTEs are visible to later ones

Answer

CTEs are evaluated in order, so a later CTE can reference any CTE defined before it.

Question 5

Which statement type can a CTE precede in many databases like PostgreSQL?

Accepted Answer

SELECT, INSERT, UPDATE, and DELETE

Answer

In PostgreSQL, a WITH clause can prefix SELECT, INSERT, UPDATE, or DELETE statements.

SQL Practice Test