Common Table Expressions (WITH) in MySQL - Mini Project: Build & Apply

Using Common Table Expressions (WITH) in MySQL

📖 Scenario: You work at a bookstore that keeps track of books and their sales. You want to find the top-selling books and their total sales.

🎯 Goal: Create a query using a Common Table Expression (CTE) with WITH to calculate total sales per book, then select books with total sales above a certain threshold.

📋 What You'll Learn

Create a CTE named BookSales that sums sales per book

Use a threshold variable min_sales to filter books

Select book titles and their total sales from the CTE

Order the results by total sales descending

💡 Why This Matters

🌍 Real World

CTEs help organize complex queries by breaking them into simpler parts, making it easier to analyze sales data in business.

💼 Career

Knowing how to use CTEs is important for data analysts and database developers to write clear and efficient SQL queries.

1

Create the books and sales tables

Write SQL statements to create two tables: books with columns book_id (integer, primary key) and title (varchar 100), and sales with columns sale_id (integer, primary key), book_id (integer), and quantity (integer).

MySQL

-- Create the books and sales tables
-- Your code here

Hint

Use CREATE TABLE statements with the specified columns and types.

2

Insert sample data into books and sales

Insert these exact rows into books: (1, 'Learn SQL'), (2, 'Python Basics'), (3, 'Data Science'). Insert these exact rows into sales: (1, 1, 10), (2, 2, 5), (3, 1, 7), (4, 3, 3).

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100)
);

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  book_id INT,
  quantity INT
);
-- Insert sample data into books and sales
-- Your code here

Hint

Use INSERT INTO with the exact values given.

3

Create a CTE named BookSales to sum sales per book

Write a SQL query using WITH BookSales AS that selects book_id and the sum of quantity as total_sales from sales, grouped by book_id.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100)
);

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  book_id INT,
  quantity INT
);

INSERT INTO books (book_id, title) VALUES
(1, 'Learn SQL'),
(2, 'Python Basics'),
(3, 'Data Science');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 10),
(2, 2, 5),
(3, 1, 7),
(4, 3, 3);

-- Create the CTE BookSales to sum quantity per book_id
-- Your code here

Hint

Use WITH BookSales AS (SELECT ... GROUP BY book_id) to create the CTE.

4

Select books with total sales above min_sales using the CTE

Add a variable min_sales set to 10. Then write a query that selects title and total_sales from BookSales joined with books on book_id. Filter results where total_sales is greater than or equal to min_sales. Order the results by total_sales descending.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100)
);

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  book_id INT,
  quantity INT
);

INSERT INTO books (book_id, title) VALUES
(1, 'Learn SQL'),
(2, 'Python Basics'),
(3, 'Data Science');

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 10),
(2, 2, 5),
(3, 1, 7),
(4, 3, 3);

WITH BookSales AS (
  SELECT book_id, SUM(quantity) AS total_sales
  FROM sales
  GROUP BY book_id
)
-- Set min_sales and select titles with total_sales >= min_sales
-- Your code here

Hint

Use the CTE BookSales joined with books, filter by total_sales >= 10, and order descending.

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