SQL Common Table Expressions (CTEs) 2

Question 1

Which keyword introduces a Common Table Expression in standard SQL?

Accepted Answer

WITH

Answer

USING

Answer

DECLARE

Answer

DEFINE

Question 2

How long does a non-recursive CTE persist?

Accepted Answer

Only for the single statement it is attached to

Answer

Until the session ends

Answer

Until the transaction commits

Answer

Permanently in the database

Question 3

What separates multiple CTEs defined in the same WITH clause?

Accepted Answer

A comma

Answer

A semicolon

Answer

The AND keyword

Answer

A new WITH keyword

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

No, CTEs are fully isolated

Answer

Only if both are recursive

Answer

Only in subqueries

Question 5

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

Accepted Answer

SELECT, INSERT, UPDATE, and DELETE

Answer

Only SELECT

Answer

Only INSERT

Answer

Only DDL statements

Question 6

What is a primary readability benefit of using a CTE over a nested subquery?

Accepted Answer

It names a query block so it can be referenced clearly

Answer

It always runs faster

Answer

It permanently stores results

Answer

It bypasses indexes

Question 7

In the syntax WITH cte_name AS (...), what does cte_name represent?

Accepted Answer

The temporary result set name used later in the query

Answer

A permanent table name

Answer

A column alias

Answer

A schema name