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.

Practice

1. What is the main purpose of the WITH clause in SQL?

easy

A. To create temporary named result sets called Common Table Expressions (CTEs)

B. To permanently store data in a new table

C. To delete rows from a table

D. To update values in a table

Solution

1. Step 1: Understand the role of WITH clause

   The WITH clause defines temporary named result sets called CTEs that exist only during query execution.

2. Step 2: Compare options with WITH clause purpose

   Options B, C, and D describe permanent or data modification actions, which are not the purpose of WITH.

3. Final Answer:

   To create temporary named result sets called Common Table Expressions (CTEs) -> Option A

4. Quick Check:

   WITH clause = Temporary named results [OK]

Hint: WITH clause defines temporary named queries [OK]

Common Mistakes:

• Thinking WITH creates permanent tables
• Confusing WITH with data modification commands
• Assuming WITH is used for deleting or updating data

2. Which of the following is the correct syntax to start a query using a single CTE named cte_example?

easy

A. CREATE cte_example AS (SELECT * FROM table_name); SELECT * FROM cte_example;

B. WITH (SELECT * FROM table_name) AS cte_example SELECT * FROM cte_example;

C. WITH cte_example AS (SELECT * FROM table_name) SELECT * FROM cte_example;

D. SELECT * FROM cte_example WITH (SELECT * FROM table_name);

Solution

1. Step 1: Recall correct WITH clause syntax

   The correct syntax is: WITH cte_name AS (subquery) followed by the main SELECT using cte_name.

2. Step 2: Check each option's syntax

   WITH cte_example AS (SELECT * FROM table_name) SELECT * FROM cte_example; matches the correct syntax. The other options misuse keywords or order.

3. Final Answer:

   WITH cte_example AS (SELECT * FROM table_name) SELECT * FROM cte_example; -> Option C

4. Quick Check:

   WITH cte_name AS (query) SELECT ... [OK]

Hint: WITH cte_name AS (query) SELECT ... is correct syntax [OK]

Common Mistakes:

• Using CREATE instead of WITH for CTE
• Placing AS after the subquery instead of after CTE name
• Misordering clauses in the query

3. Given the following SQL query, what will be the output?

WITH numbers AS (SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3) SELECT SUM(num) FROM numbers;

medium

A. 1

B. 3

C. Syntax error

D. 6

Solution

1. Step 1: Understand the CTE content

   The CTE named 'numbers' contains rows with values 1, 2, and 3.

2. Step 2: Calculate the SUM of num column

   Sum of 1 + 2 + 3 equals 6.

3. Final Answer:

   6 -> Option D

4. Quick Check:

   1 + 2 + 3 = 6 [OK]

Hint: Sum values inside CTE to get result [OK]

Common Mistakes:

• Assuming UNION ALL removes duplicates
• Confusing SUM with COUNT
• Expecting syntax error due to CTE

4. Identify the error in this SQL query using a CTE:

WITH cte1 AS SELECT * FROM employees SELECT * FROM cte1;

medium

A. CTE name cannot be cte1

B. Missing parentheses around the CTE's SELECT statement

C. SELECT * FROM cte1 should come before WITH clause

D. WITH clause cannot be used with SELECT

Solution

1. Step 1: Check CTE syntax

   The CTE definition must have parentheses around the SELECT query: AS (SELECT ...)

2. Step 2: Identify missing parentheses

   The query misses parentheses after AS, causing syntax error.

3. Final Answer:

   Missing parentheses around the CTE's SELECT statement -> Option B

4. Quick Check:

   CTE query must be in parentheses [OK]

Hint: Always put CTE query inside parentheses after AS [OK]

Common Mistakes:

• Omitting parentheses around CTE query
• Placing main SELECT before WITH clause
• Thinking CTE names have restrictions beyond naming rules

5. Consider this SQL query using multiple CTEs:

WITH cte1 AS (SELECT id, salary FROM employees WHERE department = 'Sales'), cte2 AS (SELECT id, bonus FROM bonuses) SELECT cte1.id, cte1.salary, cte2.bonus FROM cte1 LEFT JOIN cte2 ON cte1.id = cte2.id;

What is the purpose of using multiple CTEs here?

hard

A. To organize complex queries by separating filtering and joining steps

B. To permanently store intermediate results in the database

C. To speed up query execution by caching results automatically

D. To avoid using JOINs by combining data in CTEs

Solution

1. Step 1: Analyze the role of each CTE

   cte1 filters employees in Sales with id and salary; cte2 selects bonuses by id.

2. Step 2: Understand the main query usage

   The main query joins these CTEs to combine salary and bonus data for Sales employees.

3. Step 3: Identify why multiple CTEs are used

   Using multiple CTEs organizes the query into clear steps: filtering and joining, improving readability.

4. Final Answer:

   To organize complex queries by separating filtering and joining steps -> Option A

5. Quick Check:

   Multiple CTEs = Organize complex queries [OK]

Hint: Use multiple CTEs to break complex queries into parts [OK]

Common Mistakes:

• Thinking CTEs store data permanently
• Assuming CTEs automatically speed up queries
• Believing CTEs replace JOINs entirely