NEW
Font size
WorksheetsSQL CTE Quiz
Total questions: 15
Worksheet time: 3mins
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
What keyword is used to start a CTE?
BEGIN
CTE
WITH
DECLARE
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
What distinguishes a Recursive CTE from a Non-Recursive CTE?
It uses indexes
It references itself
It uses views
It returns NULLs
What must a Recursive CTE include?
Aggregates
A LIMIT clause
An anchor (base) query and a recursive query
A materialized view
What keyword combines the anchor and recursive parts of a CTE?
UNION
JOIN
UNION ALL
INTERSECT
Which of the following is a valid use case for recursive CTEs?
Flattening JSON
Building hierarchical data
String matching
Materialized joins
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
What is a limitation of CTEs?
Can’t reference tables
Can’t include aggregates
Can’t be indexed
Must be permanent
Which of the following can be used in a non-recursive CTE?
Joins
Aggregations
Filters
All of the above
Can you use multiple CTEs in a single query?
No
Yes, but only one level
Yes, comma-separated
Only in views
What does the following syntax define? WITH RegionalSales AS (SELECT Region, SUM(Sales) AS TotalSales FROM Orders GROUP BY Region) SELECT * FROM RegionalSales;
Recursive CTE
Non-recursive CTE
Temporary table
Materialized view
What is a benefit of using CTEs over subqueries?
Easier to debug and read
Can replace views
Persist across sessions
CTEs are always faster
What is the purpose of the anchor part in a recursive CTE?
Defines the recursion limit
Defines the starting rows
Deletes base data
Returns NULL
When would you not use a recursive CTE?
To find hierarchy depth
To flatten a tree
To calculate aggregates per region
To expand manager-subordinate chains
