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

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

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

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

Hint

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

Practice

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

easy

A. To create a permanent table in the database

B. To update data in multiple tables at once

C. To delete rows based on a condition

D. To create a temporary table that the outer query can use

Solution

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

   Subqueries in the FROM clause act like temporary tables that the outer query can use to simplify complex queries.

2. Step 2: Differentiate from other SQL operations

   Updating or deleting data is not the purpose of subqueries in FROM; they are for organizing data selection.

3. Final Answer:

   To create a temporary table that the outer query can use -> Option D

4. Quick Check:

   Subquery in FROM = temporary table [OK]

Hint: Think of subquery in FROM as a temporary table [OK]

Common Mistakes:

• Confusing subqueries in FROM with UPDATE or DELETE statements
• Thinking subqueries create permanent tables
• Ignoring the need for an alias for the subquery

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

easy

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

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

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

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

Solution

1. Step 1: Identify correct subquery syntax in FROM clause

   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 misuse syntax or place subquery incorrectly.

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 your subquery in FROM clause [OK]

Common Mistakes:

• Forgetting to alias the subquery
• Placing subquery outside parentheses
• Using subquery in WHERE or FROM incorrectly

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

SELECT sub.user_id, sub.total_amount FROM (SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id) AS sub WHERE sub.total_amount > 100;

medium

A. List of all orders with amount greater than 100

B. List of user IDs with total order amount greater than 100

C. Syntax error due to missing alias

D. Empty result because no user has orders

Solution

1. Step 1: Understand the subquery

   The subquery calculates total order amount per user by grouping orders by user_id and summing amounts.

2. Step 2: Apply the outer query filter

   The outer query selects users whose total order amount is greater than 100 from the subquery result.

3. Final Answer:

   List of user IDs with total order amount greater than 100 -> Option B

4. Quick Check:

   Subquery sums orders; outer filters total_amount > 100 [OK]

Hint: Subquery groups and sums; outer filters results [OK]

Common Mistakes:

• Confusing total order amount with individual order amounts
• Forgetting to alias the subquery
• Assuming syntax error without checking alias

4. Identify the error in the following query:

SELECT sub.user_id, sub.total FROM (SELECT user_id, SUM(amount) FROM orders GROUP BY user_id);

medium

A. GROUP BY cannot be used in subqueries

B. SUM(amount) must be renamed with AS

C. Missing alias for the subquery

D. SELECT cannot use subqueries in FROM clause

Solution

1. Step 1: Check subquery alias requirement

   Subqueries in FROM clause must have an alias; this query lacks it.

2. Step 2: Verify other parts

   SUM(amount) without alias is allowed but referencing it as sub.total will fail; aliasing subquery is mandatory.

3. Final Answer:

   Missing alias for the subquery -> Option C

4. Quick Check:

   Subquery in FROM needs alias [OK]

Hint: Always give an alias to subqueries in FROM [OK]

Common Mistakes:

• Assuming SUM() must be aliased
• Thinking GROUP BY is disallowed in subqueries
• Ignoring alias requirement for subqueries

5. You want to find users who have placed orders totaling more than 500, but only include users who have more than 3 orders. Which query correctly uses a subquery in the FROM clause to achieve this?

Tables:
users(id, name)
orders(id, user_id, amount)

hard

A. SELECT u.name, stats.total_amount FROM users u JOIN (SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY user_id) AS stats ON u.id = stats.user_id WHERE stats.total_amount > 500 AND stats.order_count > 3;

B. SELECT u.name FROM users u WHERE (SELECT SUM(amount) FROM orders WHERE user_id = u.id) > 500 AND (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 3;

C. SELECT u.name, SUM(o.amount) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id HAVING SUM(o.amount) > 500 AND COUNT(o.id) > 3;

D. SELECT name FROM users WHERE id IN (SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 500 AND COUNT(*) > 3);

Solution

1. Step 1: Understand the requirement

   We need users with total order amount > 500 and more than 3 orders.

2. Step 2: Analyze each option's approach

   SELECT u.name, stats.total_amount FROM users u JOIN (SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY user_id) AS stats ON u.id = stats.user_id WHERE stats.total_amount > 500 AND stats.order_count > 3; uses a subquery in FROM to calculate order count and total per user, then joins users to filter correctly. SELECT u.name FROM users u WHERE (SELECT SUM(amount) FROM orders WHERE user_id = u.id) > 500 AND (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 3; uses correlated subqueries but not FROM clause subquery. SELECT u.name, SUM(o.amount) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id HAVING SUM(o.amount) > 500 AND COUNT(o.id) > 3; uses HAVING but no subquery in FROM. SELECT name FROM users WHERE id IN (SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 500 AND COUNT(*) > 3); uses IN with subquery but no join.

3. Step 3: Confirm correct use of subquery in FROM

   Only SELECT u.name, stats.total_amount FROM users u JOIN (SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY user_id) AS stats ON u.id = stats.user_id WHERE stats.total_amount > 500 AND stats.order_count > 3; uses a derived table (subquery in FROM) with alias and joins properly.

4. Final Answer:

   SELECT u.name, stats.total_amount FROM users u JOIN (SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY user_id) AS stats ON u.id = stats.user_id WHERE stats.total_amount > 500 AND stats.order_count > 3; -> Option A

5. Quick Check:

   Subquery in FROM calculates stats; outer query filters users [OK]

Hint: Use subquery in FROM to pre-aggregate data, then join [OK]

Common Mistakes:

• Not aliasing the subquery in FROM
• Using correlated subqueries instead of derived tables
• Missing join between users and subquery results