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

Need a 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

Need a 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

Need a 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

Need a hint?

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