Subqueries in FROM clause (derived tables) in MySQL - Mini Project: Build & Apply

Using Subqueries in the FROM Clause (Derived Tables) in MySQL

📖 Scenario: You work for a small online bookstore. You have a table called sales that records each book sale with the book's title, author, and the copies_sold for that sale.Your manager wants to see the total copies sold per author, but only for authors who sold more than 100 copies in total.

🎯 Goal: Build a MySQL query that uses a subquery in the FROM clause (a derived table) to calculate total copies sold per author, then filters authors with total sales greater than 100.

📋 What You'll Learn

Use a subquery in the FROM clause to calculate total copies sold per author.

Name the subquery result as author_sales.

Select author and total_copies from the derived table.

Filter results to show only authors with total_copies greater than 100.

💡 Why This Matters

🌍 Real World

Derived tables help break complex queries into manageable parts, making it easier to analyze grouped or aggregated data before applying filters or sorting.

💼 Career

Understanding subqueries in the FROM clause is essential for data analysts and developers to write efficient and readable SQL queries for reporting and data processing.

1

Create the sales table with sample data

Create a table called sales with columns title (VARCHAR), author (VARCHAR), and copies_sold (INT). Insert these exact rows: ('Book A', 'Author X', 50), ('Book B', 'Author Y', 120), ('Book C', 'Author X', 60), ('Book D', 'Author Z', 30).

MySQL

-- Create the sales table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as shown.

2

Write a subquery to calculate total copies sold per author

Write a subquery that selects author and the sum of copies_sold as total_copies from the sales table, grouping by author. Name this subquery author_sales in the FROM clause.

MySQL

CREATE TABLE sales (
  title VARCHAR(100),
  author VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (title, author, copies_sold) VALUES
('Book A', 'Author X', 50),
('Book B', 'Author Y', 120),
('Book C', 'Author X', 60),
('Book D', 'Author Z', 30);

-- Write a subquery in the FROM clause named author_sales
-- Your code here

Need a hint?

Use SUM(copies_sold) and GROUP BY author inside the subquery. Give the subquery an alias author_sales.

3

Filter authors with total copies sold greater than 100

Add a WHERE clause to the outer query to select only rows where total_copies is greater than 100.

MySQL

CREATE TABLE sales (
  title VARCHAR(100),
  author VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (title, author, copies_sold) VALUES
('Book A', 'Author X', 50),
('Book B', 'Author Y', 120),
('Book C', 'Author X', 60),
('Book D', 'Author Z', 30);

SELECT author, total_copies
FROM (
  SELECT author, SUM(copies_sold) AS total_copies
  FROM sales
  GROUP BY author
) AS author_sales
-- Add WHERE clause to filter total_copies > 100
-- Your code here

Need a hint?

Use WHERE total_copies > 100 after the derived table to filter results.

4

Complete the query with ordering by total copies sold descending

Add an ORDER BY clause to the outer query to sort the results by total_copies in descending order.

MySQL

CREATE TABLE sales (
  title VARCHAR(100),
  author VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (title, author, copies_sold) VALUES
('Book A', 'Author X', 50),
('Book B', 'Author Y', 120),
('Book C', 'Author X', 60),
('Book D', 'Author Z', 30);

SELECT author, total_copies
FROM (
  SELECT author, SUM(copies_sold) AS total_copies
  FROM sales
  GROUP BY author
) AS author_sales
WHERE total_copies > 100
-- Add ORDER BY clause to sort by total_copies descending
-- Your code here

Need a hint?

Use ORDER BY total_copies DESC to sort authors from highest to lowest sales.