Common Table Expressions (WITH) in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a Common Table Expression (CTE) named 'recent_orders'.

MySQL

WITH [1] AS (SELECT * FROM orders WHERE order_date > '2024-01-01') SELECT * FROM recent_orders;

Drag options to blanks, or click blank then click option'

Arecent

Borders_recent

Corders

Drecent_orders

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different name for the CTE than in the main query.

Forgetting to name the CTE after WITH.

The CTE name must match the name used after WITH and in the main query. Here, 'recent_orders' is the correct CTE name.

2fill in blank

medium

Complete the code to select customer_id and total_orders from the CTE named 'order_counts'.

MySQL

WITH order_counts AS (SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id) SELECT [1] FROM order_counts;

Drag options to blanks, or click blank then click option'

A*

Bcustomer_id

Ccustomer_id, total_orders

Dtotal_orders

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting only one column instead of both.

Using '*' which selects all columns but the task asks to specify columns.

To get both customer_id and total_orders columns, list them separated by a comma in the SELECT statement.

3fill in blank

hard

Fix the error in the CTE by completing the missing keyword.

MySQL

WITH recent_orders [1] (SELECT * FROM orders WHERE order_date > '2024-01-01') SELECT * FROM recent_orders;

Drag options to blanks, or click blank then click option'

AAS

BIS

CTO

DBY

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the 'AS' keyword causes syntax errors.

Using incorrect keywords like 'IS' or 'TO'.

The keyword 'AS' is required after the CTE name to define the query that forms the CTE.

4fill in blank

hard

Fill both blanks to create a CTE named 'top_customers' that selects customer_id and total_spent.

MySQL

WITH [1] [2] (SELECT customer_id, SUM(amount) AS total_spent FROM sales GROUP BY customer_id) SELECT * FROM top_customers;

Drag options to blanks, or click blank then click option'

Atop_customers

BAS

Crecent_orders

DIS

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different name than 'top_customers'.

Using 'IS' instead of 'AS'.

The CTE name is 'top_customers' and the keyword 'AS' is needed after the name to define the CTE query.

5fill in blank

hard

Fill all three blanks to create a CTE named 'high_value_orders' that selects order_id and total from orders where total > 1000.

MySQL

WITH [1] [2] (SELECT order_id, total FROM orders WHERE total [3] 1000) SELECT * FROM high_value_orders;

Drag options to blanks, or click blank then click option'

Ahigh_value_orders

BAS

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' for the condition.

Omitting the 'AS' keyword.

Using a different CTE name than 'high_value_orders'.

The CTE name is 'high_value_orders', the keyword 'AS' is required, and the condition uses '>' to select totals greater than 1000.

Practice

1. What is the main purpose of using a WITH clause (Common Table Expression) in a MySQL query?

easy

A. To permanently store data in a new table

B. To create a temporary named result set that can be referenced within the main query

C. To delete rows from a table

D. To create an index on a table column

Solution

1. Step 1: Understand the role of WITH clause

   The WITH clause defines a temporary named result set, called a Common Table Expression (CTE), which exists only during the execution of the query.

2. Step 2: Differentiate from permanent operations

   Unlike creating tables or indexes, CTEs do not store data permanently but help organize complex queries by breaking them into parts.

3. Final Answer:

   To create a temporary named result set that can be referenced within the main query -> Option B

4. Quick Check:

   WITH clause = temporary named result set [OK]

Hint: WITH creates temporary named results for easier queries [OK]

Common Mistakes:

• Thinking WITH creates permanent tables
• Confusing WITH with DELETE or UPDATE commands
• Assuming WITH creates indexes

2. Which of the following is the correct syntax to start a Common Table Expression in MySQL?

easy

A. DEFINE cte_name AS SELECT * FROM table_name

B. CREATE CTE cte_name SELECT * FROM table_name

C. START CTE cte_name (SELECT * FROM table_name)

D. WITH cte_name AS (SELECT * FROM table_name)

Solution

1. Step 1: Recall the standard syntax for CTE

   The correct syntax to define a CTE starts with the keyword WITH, followed by the CTE name, AS, and then the query in parentheses.

2. Step 2: Compare options with syntax

   Only WITH cte_name AS (SELECT * FROM table_name) matches the correct syntax: WITH cte_name AS (SELECT * FROM table_name). Other options use invalid keywords or structure.

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table_name) -> Option D

4. Quick Check:

   WITH + name + AS + (query) = correct syntax [OK]

Hint: WITH cte_name AS (query) is the standard start [OK]

Common Mistakes:

• Using CREATE or DEFINE instead of WITH
• Omitting AS keyword
• Not enclosing query in parentheses

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

WITH SalesCTE AS (
  SELECT product_id, SUM(quantity) AS total_qty
  FROM sales
  GROUP BY product_id
)
SELECT * FROM SalesCTE WHERE total_qty > 100;

medium

A. All products with total quantity sold greater than 100

B. All products with total quantity sold less than or equal to 100

C. Syntax error due to missing GROUP BY

D. Empty result because CTE is not used properly

Solution

1. Step 1: Analyze the CTE definition

   The CTE named SalesCTE calculates total quantity sold per product by grouping sales by product_id and summing quantity.

2. Step 2: Understand the main query filter

   The main query selects rows from SalesCTE where total_qty is greater than 100, so only products with sales above 100 units appear.

3. Final Answer:

   All products with total quantity sold greater than 100 -> Option A

4. Quick Check:

   CTE sums and filters total_qty > 100 [OK]

Hint: CTE calculates sums; main query filters by condition [OK]

Common Mistakes:

• Confusing > 100 with <= 100
• Thinking GROUP BY is missing (it is present)
• Assuming CTE is unused or causes error

4. Identify the error in the following MySQL query using a CTE:

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

medium

A. CTE query missing a semicolon after closing parenthesis

B. WITH clause must be followed by INSERT statement

C. Column 'age' does not exist in the CTE result

D. CTE name cannot be 'cte'

Solution

1. Step 1: Check columns selected in CTE

   The CTE selects only 'id' and 'name' columns from users table; 'age' is not included.

2. Step 2: Analyze main query filter

   The main query tries to filter on 'age', which is not part of the CTE result, causing an error.

3. Final Answer:

   Column 'age' does not exist in the CTE result -> Option C

4. Quick Check:

   Filter column must be in CTE output [OK]

Hint: Filter columns must be selected in CTE first [OK]

Common Mistakes:

• Assuming semicolon needed inside CTE
• Thinking CTE name 'cte' is invalid
• Believing WITH must precede INSERT

5. You want to find the top 3 customers by total purchase amount using a CTE. Which query correctly uses a CTE to achieve this?

hard

A. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM CustomerTotals
ORDER BY total_amount DESC
LIMIT 3;

B. WITH CustomerTotals AS (
SELECT customer_id, amount
FROM orders
)
SELECT customer_id, SUM(amount) AS total_amount
FROM CustomerTotals
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 3;

C. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
)
SELECT * FROM CustomerTotals
LIMIT 3;

D. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 3
)
SELECT * FROM CustomerTotals;

Solution

1. Step 1: Check CTE calculation and grouping

   WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ) SELECT customer_id, total_amount FROM CustomerTotals ORDER BY total_amount DESC LIMIT 3; correctly groups orders by customer_id and sums amount inside the CTE.

2. Step 2: Verify ordering and limiting in main query

   WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ) SELECT customer_id, total_amount FROM CustomerTotals ORDER BY total_amount DESC LIMIT 3; orders by total_amount descending and limits to top 3 customers outside the CTE, which is valid in MySQL.

3. Step 3: Analyze other options for errors

   WITH CustomerTotals AS ( SELECT customer_id, amount FROM orders ) SELECT customer_id, SUM(amount) AS total_amount FROM CustomerTotals GROUP BY customer_id ORDER BY total_amount DESC LIMIT 3; does not sum inside CTE, causing incorrect grouping. WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders ) SELECT * FROM CustomerTotals LIMIT 3; misses GROUP BY and ordering. WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ORDER BY total_amount DESC LIMIT 3 ) SELECT * FROM CustomerTotals; tries to use ORDER BY and LIMIT inside CTE, which is invalid in MySQL.

4. Final Answer:

   Option A query correctly finds top 3 customers by total purchase amount -> Option A

5. Quick Check:

   Group and sum in CTE, order and limit outside [OK]

Hint: Group and sum in CTE; order and limit outside [OK]

Common Mistakes:

• Placing ORDER BY and LIMIT inside CTE
• Not grouping before summing
• Trying to sum outside CTE without grouping