WITH clause syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple WITH clause query

What is the output of this SQL query using a WITH clause?

SQL

WITH numbers AS (
  SELECT 1 AS num UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
)
SELECT num * 2 AS doubled FROM numbers ORDER BY num;

A

1
2
3

B

2
4
6

C

3
6
9

D Error: WITH clause syntax incorrect

💡 Hint

The WITH clause defines a temporary table named 'numbers'. The SELECT multiplies each number by 2.

The WITH clause creates a temporary table 'numbers' with values 1, 2, and 3. The main query selects each number multiplied by 2, resulting in 2, 4, and 6.

📝 Syntax

intermediate

Identify the syntax error in this WITH clause

Which option contains a syntax error in the WITH clause usage?

SQL

WITH cte AS (
  SELECT id, name FROM users
)
SELECT * FROM cte WHERE id > 10;

A WITH cte AS SELECT id, name FROM users; SELECT * FROM cte WHERE id > 10;

B WITH cte AS (SELECT id, name FROM users) SELECT * FROM cte WHERE id > 10;

C WITH cte (SELECT id, name FROM users) SELECT * FROM cte WHERE id > 10;

D ;01 > di EREHW etc MORF * TCELES )sresu MORF eman ,di TCELES( SA etc HTIW

💡 Hint

The WITH clause requires the keyword AS before the subquery in parentheses.

Option C is missing the AS keyword before the parentheses, causing a syntax error. Options B and D are correct syntax, and A is invalid because it separates the WITH clause incorrectly.

❓ optimization

advanced

Optimizing repeated subqueries with WITH clause

Given a query that uses the same subquery multiple times, how does using a WITH clause improve performance?

A WITH clause has no effect on performance; it only improves readability.

B WITH clause duplicates the subquery each time, increasing computation time.

C WITH clause converts the subquery into a permanent table on disk.

D WITH clause stores the subquery result once and reuses it, reducing repeated computation.

💡 Hint

Think about how temporary tables can avoid repeating the same work.

The WITH clause defines a Common Table Expression (CTE) that is computed once and reused, which can improve performance by avoiding repeated execution of the same subquery.

🧠 Conceptual

advanced

Understanding recursive WITH clause usage

What is the purpose of a recursive WITH clause in SQL?

A To repeatedly execute a query until a condition is met, useful for hierarchical data.

B To create multiple temporary tables at once.

C To permanently store query results in the database.

D To optimize queries by caching results automatically.

💡 Hint

Recursive WITH clauses help process data that refers to itself.

Recursive WITH clauses allow a query to call itself repeatedly, which is useful for traversing hierarchical or tree-structured data like organizational charts or folder structures.

🔧 Debug

expert

Debugging a WITH clause with multiple CTEs

Given this SQL code, what error will it produce?

SQL

WITH first_cte AS (
  SELECT id FROM users WHERE active = 1
),
second_cte AS (
  SELECT id FROM first_cte WHERE id > 10
)
SELECT * FROM second_cte;

A No error; returns ids greater than 10 from active users

B Error: 'first_cte' does not exist in 'second_cte' query

C Syntax error: missing comma between CTEs

D Error: Cannot reference CTE in another CTE

💡 Hint

CTEs can reference previous CTEs defined in the same WITH clause.

The code correctly defines two CTEs where 'second_cte' references 'first_cte'. This is valid syntax and will return ids greater than 10 from active users.