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.