Quiz on SQL Common Table Expressions

What is a CTE in SQL?

Which keyword starts a CTE?

What is the main benefit of using a CTE?

Which of the following is a valid syntax for a non-recursive CTE?

WITH top_customers AS (
SELECT * FROM customers WHERE purchases > 100
)
SELECT * FROM top_customers;

Can a CTE be used inside INSERT, UPDATE, or DELETE statements?

Which of the following best describes a recursive CTE?

What keyword is required in a recursive CTE to combine the base and recursive query?

What will happen if the recursive CTE doesn’t have a termination condition?

Which of the following use cases is ideal for recursive CTEs?

Can you define multiple CTEs in a single WITH clause?

What’s the scope of a CTE defined in a query?

What will this query output?

WITH avg_salary AS (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
)
SELECT e.name
FROM employees e
JOIN avg_salary a ON e.department_id = a.department_id
WHERE e.salary > a.avg_sal;

Can you reference one CTE inside another CTE?

Which database systems support CTEs?

What is the difference between a CTE and a subquery?