Subquery in FROM clause (derived table) in SQL - Mini Project: Build & Apply

Using Subquery in FROM Clause (Derived Table)

📖 Scenario: You work for a small bookstore that keeps track of book sales. You have a table called sales that records each sale with the book's book_id and the quantity sold.Your manager wants to see the total quantity sold for each book, but only for books that sold more than 10 copies in total.

🎯 Goal: Create a SQL query that uses a subquery in the FROM clause (a derived table) to calculate total sales per book, then filters to show only books with total sales greater than 10.

📋 What You'll Learn

Create a table called sales with columns book_id and quantity.

Insert the exact sales data provided.

Write a subquery in the FROM clause that sums quantity per book_id.

Filter the results to show only books with total quantity sold greater than 10.

💡 Why This Matters

🌍 Real World

Derived tables help organize complex queries by breaking them into smaller parts, making it easier to analyze grouped data like sales totals.

💼 Career

Knowing how to use subqueries in the FROM clause is useful for data analysts and developers who write reports or dashboards that summarize data.

1

Create the sales table and insert data

Create a table called sales with columns book_id (integer) and quantity (integer). Then insert these exact rows: (1, 5), (2, 8), (1, 7), (3, 3), (2, 5), (3, 10).

SQL

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

Need a hint?

Use CREATE TABLE to make the table, then INSERT INTO to add the rows exactly as given.

2

Write a subquery to sum quantity per book

Write a subquery named total_sales in the FROM clause that selects book_id and the sum of quantity as total_quantity from sales, grouped by book_id.

SQL

CREATE TABLE sales (book_id INT, quantity INT);
INSERT INTO sales (book_id, quantity) VALUES
(1, 5),
(2, 8),
(1, 7),
(3, 3),
(2, 5),
(3, 10);

-- Write a subquery named total_sales that sums quantity per book
-- Your code here

Need a hint?

Use SELECT book_id, SUM(quantity) AS total_quantity FROM sales GROUP BY book_id inside the subquery.

3

Use the subquery as a derived table in the FROM clause

Write a query that selects book_id and total_quantity from a derived table named total_sales which is the subquery summing quantity per book from sales.

SQL

CREATE TABLE sales (book_id INT, quantity INT);
INSERT INTO sales (book_id, quantity) VALUES
(1, 5),
(2, 8),
(1, 7),
(3, 3),
(2, 5),
(3, 10);

-- Select book_id and total_quantity from a derived table total_sales
-- Your code here

Need a hint?

Wrap the subquery in parentheses and give it the alias total_sales. Then select from it.

4

Filter to show only books with total sales greater than 10

Add a WHERE clause to the outer query to show only rows where total_quantity is greater than 10.

SQL

CREATE TABLE sales (book_id INT, quantity INT);
INSERT INTO sales (book_id, quantity) VALUES
(1, 5),
(2, 8),
(1, 7),
(3, 3),
(2, 5),
(3, 10);

-- Add WHERE clause to filter total_quantity > 10
-- Your code here

Need a hint?

Use WHERE total_quantity > 10 after the derived table to filter results.