SQL CTE Quiz

What does a CTE (Common Table Expression) do?

• Stores data permanently

• Creates indexes

• Defines a temporary result set used by a query

• Updates a view

Answer explanation

A CTE (Common Table Expression) defines a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It helps organize complex queries and improve readability.

What keyword is used to start a CTE?

• BEGIN

• CTE

• WITH

• DECLARE

Answer explanation

The keyword used to start a Common Table Expression (CTE) in SQL is 'WITH'. This keyword allows you to define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Which of the following is true about CTEs?

• They must always be recursive

• Can be referenced multiple times in the same query

• Stored in the database schema

• Always require a JOIN

Answer explanation

CTEs (Common Table Expressions) can be referenced multiple times within the same query, allowing for cleaner and more organized SQL code. They do not have to be recursive, are not stored in the database schema, and do not always require a JOIN.

What distinguishes a Recursive CTE from a Non-Recursive CTE?

• It uses indexes

• It references itself

• It uses views

• It returns NULLs

Answer explanation

A Recursive CTE (Common Table Expression) distinguishes itself by referencing itself, allowing it to perform operations like traversing hierarchical data. In contrast, a Non-Recursive CTE does not reference itself.

What must a Recursive CTE include?

• Aggregates

• A LIMIT clause

• An anchor (base) query and a recursive query

• A materialized view

Answer explanation

A Recursive CTE must include an anchor (base) query to establish the initial result set and a recursive query to build upon that result set iteratively. This structure is essential for recursion.

What keyword combines the anchor and recursive parts of a CTE?

• UNION

• JOIN

• UNION ALL

• INTERSECT

Answer explanation

The keyword 'UNION ALL' combines the anchor and recursive parts of a Common Table Expression (CTE) in SQL, allowing all results to be included without eliminating duplicates, which is essential for recursive queries.

Which of the following is a valid use case for recursive CTEs?

• Flattening JSON

• Building hierarchical data

• String matching

• Materialized joins

Answer explanation

Recursive CTEs are ideal for building hierarchical data, such as organizational charts or category trees, as they can reference themselves to navigate through levels of data. Other options do not utilize recursion effectively.

What happens if a Recursive CTE does not have a stopping condition?

• Query is faster

• It runs infinitely or throws an error

• It self-corrects

• It returns zero rows

Answer explanation

If a Recursive CTE lacks a stopping condition, it can lead to infinite recursion, causing the query to run indefinitely or eventually throw an error due to resource limits. Thus, the correct answer is that it runs infinitely or throws an error.

What is a limitation of CTEs?

• Can’t reference tables

• Can’t include aggregates

• Can’t be indexed

• Must be permanent

Answer explanation

A limitation of Common Table Expressions (CTEs) is that they can’t be indexed. This means that while they are useful for organizing complex queries, they do not benefit from indexing, which can affect performance.

Which of the following can be used in a non-recursive CTE?

• Joins

• Aggregations

• Filters

• All of the above

Answer explanation

In a non-recursive CTE, you can use joins to combine data, aggregations to summarize data, and filters to limit results. Therefore, all of the above options are valid, making 'All of the above' the correct answer.