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

Using Subqueries in FROM Clause (Derived Tables) in PostgreSQL

📖 Scenario: You work for a small bookstore that keeps track of book sales. You want to analyze sales data to find the total sales per author and then find authors with total sales above a certain amount.

🎯 Goal: Build a SQL query using a subquery in the FROM clause (a derived table) to calculate total sales per author, then filter authors with total sales greater than 100.

📋 What You'll Learn

Create a table called sales with columns book_id, author, and copies_sold.

Insert the exact sales data provided into the sales table.

Write a subquery in the FROM clause that sums copies_sold grouped by author.

Use the derived table to select authors with total sales greater than 100.

💡 Why This Matters

🌍 Real World

Analyzing sales data by grouping and filtering is common in business reporting and decision making.

💼 Career

Knowing how to use subqueries in the FROM clause helps you write clear and efficient SQL queries for data analysis roles.

1

Create the sales table and insert data

Create a table called sales with columns book_id (integer), author (text), and copies_sold (integer). Then insert these exact rows: (1, 'Alice Walker', 50), (2, 'Alice Walker', 60), (3, 'James Baldwin', 40), (4, 'James Baldwin', 70), (5, 'Toni Morrison', 30).

PostgreSQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the data.

2

Define a threshold for filtering authors

Create a variable called sales_threshold and set it to 100. This will be used to filter authors with total sales above this number.

PostgreSQL

CREATE TABLE sales (
    book_id INTEGER,
    author TEXT,
    copies_sold INTEGER
);

INSERT INTO sales (book_id, author, copies_sold) VALUES
(1, 'Alice Walker', 50),
(2, 'Alice Walker', 60),
(3, 'James Baldwin', 40),
(4, 'James Baldwin', 70),
(5, 'Toni Morrison', 30);

-- Define sales_threshold variable
-- Your code here

Need a hint?

In PostgreSQL psql shell, use \set to define a variable.

3

Write a subquery in FROM to sum sales per author

Write a SQL query that uses a subquery in the FROM clause named author_sales. This subquery should select author and the sum of copies_sold as total_sales, grouping by author from the sales table.

PostgreSQL

CREATE TABLE sales (
    book_id INTEGER,
    author TEXT,
    copies_sold INTEGER
);

INSERT INTO sales (book_id, author, copies_sold) VALUES
(1, 'Alice Walker', 50),
(2, 'Alice Walker', 60),
(3, 'James Baldwin', 40),
(4, 'James Baldwin', 70),
(5, 'Toni Morrison', 30);

\set sales_threshold 100

-- Write a query with a subquery in FROM named author_sales
-- Your code here

Need a hint?

Use a subquery inside the FROM clause with an alias. The subquery groups sales by author and sums copies sold.

4

Filter authors with total sales above the threshold

Extend the previous query to add a WHERE clause that filters authors where total_sales is greater than the variable :sales_threshold.

PostgreSQL

CREATE TABLE sales (
    book_id INTEGER,
    author TEXT,
    copies_sold INTEGER
);

INSERT INTO sales (book_id, author, copies_sold) VALUES
(1, 'Alice Walker', 50),
(2, 'Alice Walker', 60),
(3, 'James Baldwin', 40),
(4, 'James Baldwin', 70),
(5, 'Toni Morrison', 30);

\set sales_threshold 100

SELECT author, total_sales
FROM (
    SELECT author, SUM(copies_sold) AS total_sales
    FROM sales
    GROUP BY author
) AS author_sales

-- Add WHERE clause to filter total_sales > :sales_threshold
-- Your code here

Need a hint?

Use the WHERE clause after the derived table to filter results using the variable :sales_threshold.