Quiz for Common Table Expressions (CTE)

Q1: What keyword is used to define a Common Table Expression (CTE)?

A. WITH B. AS C. TEMP D. DECLARE

Q2: Which SQL keyword is used to define a CTE?

A. LET B. WITH C. AS D. DEFINE

Q3: Can a CTE be recursive?

A. Yes B. No C. Only in MySQL D. Only in PostgreSQL

Q4: Where is a CTE declared in a query?

A. After SELECT B. Before WHERE C. At the start of the query D. Inside FROM clause

Q5: Which keyword is used within a recursive CTE to refer to itself?

A. SELF B. JOIN C. RECURSIVE D. UNION ALL

Q6: Which of the following databases supports CTEs?

A. MySQL B. PostgreSQL C. SQL Server D. All of the above

Q7: What is a key advantage of using CTEs?

A. Permanent storage B. Improved performance C. Readability and modular code D. Faster indexing

Q8: How long does a CTE exist during query execution?

A. Till logout B. Till session ends C. Till transaction ends D. Only during query execution

Q9: Which clause must follow the CTE?

A. FROM B. WHERE C. SELECT or INSERT or UPDATE D. ORDER BY

Q10: How do you give a name to a CTE?

A. Using TABLE keyword B. Using ALIAS keyword C. Directly with an identifier after WITH D. Using LABEL

Q11: Can a CTE be used in multiple queries?

A. Yes B. No C. Only in stored procedures D. Only with global temp tables

Q12: How are CTEs different from subqueries?

A. CTEs are slower B. CTEs can’t be nested C. CTEs improve readability and can be recursive D. Subqueries are always better

Q13: Can a CTE be used in an INSERT statement?

A. Yes B. No C. Only in MySQL D. Only in PostgreSQL

Q14: Which keyword is required in some databases before using recursive CTEs?

A. JOIN B. AS C. RECURSIVE D. INHERIT

Q15: Which is the correct syntax for a basic CTE?

A. WITH cte_name AS (SELECT ...) B. CREATE cte_name AS SELECT ... C. SELECT INTO cte_name ... D. DECLARE cte_name TABLE ...

Q16: Can a CTE reference another CTE declared above it?

A. Yes B. No C. Only in MySQL D. Only if nested

Q17: Can CTEs be used for updating data?

A. Yes B. No C. Only with triggers D. Only on temporary tables

Q18: How is a recursive CTE terminated?

A. After timeout B. When MAXRECURSION limit is reached or no rows returned C. After COMMIT D. When rollback is issued

Q19: What is the default MAXRECURSION value in SQL Server?

A. 10 B. 100 C. 0 D. 32767