T-SQL and Common Table Expression

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