WorksheetsT-SQL and Common Table Expression
Total questions: 30
Worksheet time: 5mins
What is the primary purpose of a Common Table Expression (CTE) in T-SQL?
To permanently store temporary data
To simplify complex subqueries and make queries more readable
To define a new database schema
To create a clustered index on a table
A CTE is defined using which T-SQL keyword?
CREATE VIEW
WITH
DECLARE
CREATE FUNCTION
Which of the following statements about a non-recursive CTE is TRUE?
It can be referenced multiple times within the same query.
It is a permanent object stored in the database.
It can be used to perform DML operations (e.g., INSERT, UPDATE, DELETE).
It can only reference a single table.
CTE's scope is limited to the:
Entire database instance
Entire stored procedure or function
immediate SELECT, INSERT, UPDATE, or DELETE statement that follows it
Current user session
What is the main advantage of a CTE over a derived table (subquery in the FROM clause)?
Better performance
The ability to be referenced by itself (recursion)
The ability to be referenced by other CTEs in the same WITH clause
All of the above
Consider the following query:
SQL
WITH EmployeeCTE (EmployeeID, EmployeeName) AS (
SELECT EmployeeID, FirstName + ' ' + LastName
FROM Employees
)
SELECT * FROM EmployeeCTE;
What is EmployeeCTE?
A temporary table
A view
A CTE name
A table alias
Which type of CTE allows a query to refer to itself, making it suitable for traversing hierarchical data?
Simple CTE
Non-recursive CTE
Recursive CTE
Common CTE
In a recursive CTE, the first SELECT statement is known as the:
Anchor member
Recursive member
Union member
Base member
What operator is used to combine the anchor member and the recursive member in a recursive CTE?
UNION ALL
UNION
INTERSECT
EXCEPT
A recursive CTE must have a terminating condition to prevent an infinite loop. This condition is typically handled by a WHERE clause in the:
Anchor member
Recursive member
SELECT statement outside the CTE
Both the anchor and recursive members
Which of the following is a valid use case for a CTE?
Calculating running totals
Finding a company's organizational hierarchy
Simplifying a complex query with multiple joins
All of the above
True or False: A CTE is physically materialized and stored on disk.
True
False
True or False: You can define multiple CTEs within a single WITH clause, separated by a comma.
True
False
To reference a CTE defined earlier in the same WITH clause, you must:
se the JOIN keyword
Simply refer to it by its name
Use a subquery
It is not possible to reference a CTE from another CTE in the same WITH clause
What is the maximum number of recursive iterations for a CTE, by default?
100
500
1000
Unlimited
How can you override the default maximum recursion limit?
OPTION (MAXRECURSION n)
SET MAXRECURSION = n
WITH (MAXRECURSION n)
LIMIT RECURSION n
Which of these is a non-recursive use of a CTE?
Traversing a part-of-a-whole relationship
Finding the top 10 most expensive products
Finding a manager's direct reports and their reports
Building a bill of materials (BOM)
What is a key limitation of using a CTE?
It cannot be used with DML statements.
It cannot be nested.
It cannot contain an ORDER BY clause (except in the TOP or OFFSET context).
It can only be used with SELECT statements.
In a recursive CTE, the anchor and recursive members must have the same number of columns and compatible data types. What is this concept known as?
Column alignment
Data type consistency
Structure conformity
Union-compatible
What is the main difference between a CTE and a subquery in terms of readability?
CTEs are always more complex.
Subqueries are easier to read and debug.
CTEs allow for the breakdown of a complex query into logical, readable steps.
There is no significant difference.
A CTE can be referenced by the final SELECT statement multiple times.
True
False
Which of the following is NOT a good use case for a recursive CTE?
Finding the path from a child node to a root node in a tree structure.
Generating a series of dates.
Calculating a single aggregate value from a table.
Traversing a family tree hierarchy.
Which clause is required in a non-recursive CTE definition?
WHERE
GROUP BY
WITH
HAVING
A CTE's execution plan is typically the same as the equivalent query without a CTE.
True
False
What happens if a recursive CTE reaches the MAXRECURSION limit without terminating?
The query continues until all rows are returned.
The query terminates with a warning message.
The query fails and returns an error.
The query returns a partial result.
Which of these is a correct definition of a simple (non-recursive) CTE?
WITH MyCTE AS (SELECT * FROM Sales)
CREATE CTE MyCTE AS (SELECT * FROM Sales)
BEGIN MyCTE AS (SELECT * FROM Sales)
DEFINE MyCTE AS (SELECT * FROM Sales)
A CTE can be used in a CREATE VIEW statement.
True
False
The columns in a CTE can be explicitly named.
True
False
What is the name of the operation that combines the anchor and recursive parts in a recursive CTE?
UNION
JOIN
UNION ALL
CROSS JOIN
In a recursive CTE, what part of the query is responsible for iterating and adding new rows to the result set?
The anchor member
he base query
The recursive member
The outer SELECT statement
