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

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

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

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

Hint

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

Practice

1. What is the main purpose of using a subquery in the FROM clause in PostgreSQL?

easy

A. To create a permanent table in the database

B. To update data in multiple tables at once

C. To delete rows from a table based on a condition

D. To create a temporary table that can be used in the main query

Solution

1. Step 1: Understand the role of subqueries in FROM

   Subqueries in the FROM clause act like temporary tables that exist only during the query execution.

2. Step 2: Compare with other options

   Updating or deleting data and creating permanent tables are not done by subqueries in FROM.

3. Final Answer:

   To create a temporary table that can be used in the main query -> Option D

4. Quick Check:

   Subquery in FROM = temporary table [OK]

Hint: Subquery in FROM = think temporary table [OK]

Common Mistakes:

• Thinking subqueries in FROM create permanent tables
• Confusing subqueries with UPDATE or DELETE statements
• Forgetting subqueries need an alias

2. Which of the following is the correct syntax for using a subquery in the FROM clause in PostgreSQL?

easy

A. SELECT * FROM (SELECT id FROM users) AS sub;

B. SELECT * FROM users WHERE (SELECT id FROM users);

C. SELECT * FROM users JOIN (SELECT id FROM users);

D. SELECT * FROM users (SELECT id FROM users) AS sub;

Solution

1. Step 1: Identify correct subquery syntax in FROM

   The subquery must be enclosed in parentheses and given an alias using AS.

2. Step 2: Check each option

   SELECT * FROM (SELECT id FROM users) AS sub; correctly uses parentheses and alias. Others either misuse WHERE, JOIN without ON, or lack alias.

3. Final Answer:

   SELECT * FROM (SELECT id FROM users) AS sub; -> Option A

4. Quick Check:

   Subquery in FROM needs parentheses + alias [OK]

Hint: Always alias subqueries in FROM with AS [OK]

Common Mistakes:

• Omitting alias after subquery
• Using subquery in WHERE without proper syntax
• Trying to JOIN without ON condition

3. Given the tables:
users(id, name)
orders(id, user_id, amount)
What will the following query return?

SELECT sub.name, sub.total FROM (SELECT u.name, SUM(o.amount) AS total FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name) AS sub WHERE sub.total > 100;

medium

A. Syntax error due to missing GROUP BY

B. All users with their total order amount including those with zero

C. Names of users with total order amount greater than 100

D. Empty result because no user has orders

Solution

1. Step 1: Analyze the subquery

   The subquery calculates total order amount per user using LEFT JOIN and GROUP BY user name.

2. Step 2: Apply the outer WHERE filter

   The outer query filters users whose total order amount is greater than 100.

3. Final Answer:

   Names of users with total order amount greater than 100 -> Option C

4. Quick Check:

   Subquery sums orders, outer filters total > 100 [OK]

Hint: Subquery computes totals; outer filters results [OK]

Common Mistakes:

• Ignoring GROUP BY causing syntax error
• Confusing LEFT JOIN with INNER JOIN results
• Assuming all users appear without filtering

4. Identify the error in this query:

SELECT sub.name, sub.order_count FROM (SELECT name, COUNT(*) FROM users GROUP BY name) sub;

medium

A. No error, query is correct

B. Missing alias for COUNT(*) column

C. Incorrect table name 'users'

D. Subquery missing parentheses

Solution

1. Step 1: Check subquery SELECT list

   COUNT(*) needs an alias to be referenced outside the subquery.

2. Step 2: Understand outer query references

   Outer query uses sub.order_count, but COUNT(*) column has no alias, causing error.

3. Final Answer:

   Missing alias for COUNT(*) column -> Option B

4. Quick Check:

   Aggregate columns need aliases in subqueries [OK]

Hint: Always alias aggregate columns in subqueries [OK]

Common Mistakes:

• Forgetting to alias aggregate functions
• Assuming subquery columns auto-named
• Ignoring parentheses around subquery

5. You want to find the average order amount per user but only for users who have placed more than 3 orders. Which query correctly uses a subquery in the FROM clause to achieve this?

hard

A. SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3;

B. SELECT user_id, AVG(amount) FROM orders WHERE COUNT(*) > 3 GROUP BY user_id;

C. SELECT user_id, AVG(amount) FROM (SELECT * FROM orders WHERE COUNT(*) > 3) AS sub GROUP BY user_id;

D. SELECT user_id, AVG(amount) FROM orders GROUP BY user_id HAVING AVG(amount) > 3;

Solution

1. Step 1: Understand the requirement

   We need average order amount per user but only for users with more than 3 orders.

2. Step 2: Analyze each option

   SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3; uses a subquery to calculate average and count per user, then filters users with order_count > 3 outside. Others misuse COUNT in WHERE or HAVING incorrectly.

3. Final Answer:

   SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3; -> Option A

4. Quick Check:

   Subquery calculates aggregates; outer filters by count [OK]

Hint: Filter aggregates in outer query after subquery [OK]

Common Mistakes:

• Using COUNT(*) in WHERE clause directly
• Misplacing HAVING clause conditions
• Not aliasing subquery columns properly