Why CTEs are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use Common Table Expressions (CTEs)?

Which of the following best explains why CTEs are useful in SQL queries?

A CTEs store data permanently in the database for faster access.

B CTEs allow breaking complex queries into simpler parts for better readability and reuse.

C CTEs replace the need for any JOIN operations in SQL queries.

D CTEs automatically speed up query execution by creating indexes on the fly.

💡 Hint

Think about how CTEs help organize and structure queries.

CTEs let you write a temporary named result set that you can reference multiple times in a query. This makes complex queries easier to read and maintain. They do not create indexes or store data permanently.

❓ query_result

intermediate

Output of a query using a CTE

Given the following tables:

Employees: (id, name, department_id)
Departments: (id, name)

What is the output of this query?

WITH DeptCount AS (
  SELECT department_id, COUNT(*) AS emp_count
  FROM Employees
  GROUP BY department_id
)
SELECT d.name, dc.emp_count
FROM Departments d
JOIN DeptCount dc ON d.id = dc.department_id
ORDER BY dc.emp_count DESC;

SQL

Employees:
1, 'Alice', 10
2, 'Bob', 20
3, 'Charlie', 10
4, 'Diana', 30
5, 'Eve', 20

Departments:
10, 'HR'
20, 'Sales'
30, 'IT'

A [('IT', 1), ('HR', 2), ('Sales', 2)]

B [('HR', 2), ('Sales', 2), ('IT', 1)]

C [('Sales', 2), ('HR', 2), ('IT', 1)]

D [('HR', 3), ('Sales', 2), ('IT', 1)]

💡 Hint

Count employees per department, then join with department names and order by count descending.

The CTE counts employees per department. Joining with Departments gives names. Ordering by count descending shows Sales and HR both have 2 employees, Sales listed first alphabetically.

📝 Syntax

advanced

Identify the syntax error in this CTE query

Which option contains the syntax error in the CTE usage?

WITH RecentOrders AS (
  SELECT order_id, customer_id, order_date
  FROM Orders
  WHERE order_date > '2024-01-01'
)
SELECT * FROM RecentOrders
WHERE customer_id = 123;

A No syntax error; the query is valid.

B Missing comma after CTE definition before SELECT statement.

C CTE must be followed by a semicolon before the main SELECT.

D Incorrect use of WHERE clause inside the CTE.

💡 Hint

Check if the CTE and main query are properly connected.

The query correctly defines a CTE and then selects from it. The WHERE clause inside the CTE is valid. No comma or semicolon is required between CTE and main query.

❓ optimization

advanced

Why might using a CTE hurt performance?

Which reason explains why CTEs can sometimes reduce query performance?

A CTEs are always materialized, causing repeated computation if referenced multiple times.

B CTEs automatically create indexes that slow down queries.

C CTEs force the database to scan all tables even if not needed.

D CTEs prevent the use of any WHERE clauses in the main query.

💡 Hint

Think about how databases handle CTEs internally.

Some databases materialize CTEs, meaning they compute and store the result temporarily. This forces full computation of the CTE upfront, which can prevent optimizations like predicate pushdown and lead to slower performance.

🔧 Debug

expert

Why does this recursive CTE cause an infinite loop?

Consider this recursive CTE to generate numbers:

WITH RECURSIVE Numbers AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM Numbers
  WHERE num < 5
)
SELECT * FROM Numbers;

Why might this query run indefinitely in some databases?

A The UNION ALL should be UNION to avoid duplicates.

B Recursive CTEs are not supported in standard SQL.

C The initial SELECT does not specify a column alias.

D The recursive part lacks a proper termination condition causing infinite recursion.

💡 Hint

Check the condition that stops recursion.

The recursive CTE appears to have a termination condition (num < 5), but because it is applied to the growing Numbers table which always retains the initial rows (< 5), the recursive SELECT keeps producing rows (num + 1 from those early rows), causing infinite recursion unless limited by database max depth.