Subquery in FROM clause (derived table) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a derived table with aggregation

Given the table sales with columns region, product, and amount, what is the output of this query?

SELECT region, total_sales FROM (SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region) AS region_totals ORDER BY total_sales DESC;

SQL

CREATE TABLE sales (region VARCHAR(10), product VARCHAR(10), amount INT);
INSERT INTO sales VALUES
('North', 'A', 100),
('South', 'B', 200),
('North', 'B', 150),
('East', 'A', 50),
('South', 'A', 100);

A [{"region": "East", "total_sales": 50}, {"region": "North", "total_sales": 250}, {"region": "South", "total_sales": 300}]

B [{"region": "South", "total_sales": 300}, {"region": "North", "total_sales": 250}, {"region": "East", "total_sales": 50}]

C [{"region": "North", "total_sales": 250}, {"region": "South", "total_sales": 300}, {"region": "East", "total_sales": 50}]

D [{"region": "South", "total_sales": 200}, {"region": "North", "total_sales": 150}, {"region": "East", "total_sales": 50}]

💡 Hint

Look at how the SUM aggregates amounts per region and then the outer query orders by total_sales descending.

The inner query sums amounts grouped by region. South has 200+100=300, North has 100+150=250, East has 50. The outer query orders by total_sales descending, so South first, then North, then East.

📝 Syntax

intermediate

Identify the syntax error in derived table usage

Which option contains a syntax error in using a subquery in the FROM clause?

A SELECT region, total FROM (SELECT region, COUNT(*) AS total FROM sales GROUP BY region) AS t;

B SELECT region, total FROM (SELECT region, COUNT(*) AS total FROM sales GROUP BY region) t

C SELECT region, total FROM (SELECT region, COUNT(*) AS total FROM sales GROUP BY region) AS;

D SELECT t.region, t.total FROM (SELECT region, COUNT(*) AS total FROM sales GROUP BY region) t;

💡 Hint

Check the alias syntax after the subquery in the FROM clause.

Option C has 'AS;' without an alias name, which is a syntax error. The alias must have a name after AS.

❓ optimization

advanced

Optimizing a query with a derived table

Consider this query:

SELECT d.region, d.avg_amount FROM (SELECT region, AVG(amount) AS avg_amount FROM sales GROUP BY region) d WHERE d.avg_amount > 100;

Which option is the most efficient way to write this query without changing the output?

A SELECT region, AVG(amount) AS avg_amount FROM sales GROUP BY region HAVING AVG(amount) > 100;

B SELECT region, avg_amount FROM sales WHERE avg_amount > 100 GROUP BY region;

C SELECT region, AVG(amount) AS avg_amount FROM sales WHERE amount > 100 GROUP BY region;

D SELECT region, AVG(amount) AS avg_amount FROM sales GROUP BY region WHERE AVG(amount) > 100;

💡 Hint

Try to use aggregation filtering directly without a derived table.

Option A uses HAVING to filter groups after aggregation, which is more efficient than filtering in an outer query on a derived table.

🔧 Debug

advanced

Debugging incorrect column reference in derived table

Given the query:

SELECT dt.region, dt.total_sales FROM (SELECT region, SUM(amount) AS total FROM sales GROUP BY region) dt WHERE total_sales > 100;

What error will this query produce?

A Column 'total_sales' does not exist

B No error, query runs successfully

C Syntax error near 'WHERE'

D Ambiguous column name 'region'

💡 Hint

Check the alias names used inside and outside the derived table.

Inside the derived table, the sum is aliased as 'total', but the outer query references 'total_sales', which does not exist.

🧠 Conceptual

expert

Understanding scope and naming in derived tables

Why is it necessary to provide an alias name for a subquery used in the FROM clause?

A Because alias names automatically create indexes on the derived table

B Because alias names improve query performance by caching results

C Because alias names allow the subquery to run independently of the outer query

D Because SQL requires every derived table to have a name to reference its columns in the outer query

💡 Hint

Think about how the outer query accesses the results of the subquery.

The alias acts like a temporary table name so the outer query can refer to the columns of the derived table.

Practice

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

easy

A. To update values in a table

B. To permanently store data in the database

C. To delete rows from a table

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

Solution

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

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

2. Step 2: Differentiate from other SQL operations

   Unlike DELETE or UPDATE, subqueries in FROM do not modify data but help organize data for selection.

3. Final Answer:

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

4. Quick Check:

   Subquery in FROM = temporary table [OK]

Hint: Subquery in FROM creates a temp table for main query use [OK]

Common Mistakes:

• Thinking subquery stores data permanently
• Confusing subquery with DELETE or UPDATE commands
• Forgetting subquery is temporary, not permanent

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

easy

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

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

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

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

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 misuse WHERE, alias placement, or parentheses.

3. Final Answer:

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

4. Quick Check:

   Subquery in FROM needs parentheses + alias [OK]

Hint: Subquery in FROM needs parentheses and alias [OK]

Common Mistakes:

• Omitting alias for subquery
• Placing subquery in WHERE instead of FROM
• Incorrect alias placement

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

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

medium

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

B. All users with their total order amount

C. Syntax error due to missing alias

D. Empty result because no users have orders

Solution

1. Step 1: Understand the subquery

   The subquery calculates total order amount per user by joining users and orders and grouping by user name.

2. Step 2: Apply the outer WHERE filter

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

3. Final Answer:

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

4. Quick Check:

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

Hint: Subquery calculates totals; outer query filters results [OK]

Common Mistakes:

• Ignoring the WHERE filter on total
• Assuming all users are returned
• Confusing alias usage

4. Identify the error in this query:

SELECT sub.name, sub.total FROM (SELECT name, SUM(amount) AS total FROM users JOIN orders ON users.id = orders.user_id) sub;

medium

A. Missing alias for subquery

B. Incorrect JOIN syntax

C. Missing GROUP BY clause in subquery

D. Using subquery in WHERE clause instead of FROM

Solution

1. Step 1: Analyze the subquery aggregation

   The subquery uses SUM(amount) but does not group by name, which is required when selecting non-aggregated columns.

2. Step 2: Confirm alias and JOIN syntax

   The subquery has an alias 'sub' and JOIN syntax is correct, so these are not errors.

3. Final Answer:

   Missing GROUP BY clause in subquery -> Option C

4. Quick Check:

   Aggregation needs GROUP BY for non-aggregated columns [OK]

Hint: Aggregation needs GROUP BY for other selected columns [OK]

Common Mistakes:

• Forgetting GROUP BY with aggregation
• Confusing alias requirement
• Misreading JOIN syntax

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 user_id, AVG(amount) FROM (SELECT * FROM orders WHERE COUNT(*) > 3) AS sub GROUP BY user_id;

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

C. SELECT user_id, AVG(amount) FROM orders WHERE COUNT(*) > 3 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 FROM orders GROUP BY user_id HAVING COUNT(*) > 3) AS sub; correctly uses a subquery to group orders by user_id, filters users with more than 3 orders using HAVING, then calculates average amount.

3. Final Answer:

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

4. Quick Check:

   Subquery filters users by order count, outer selects average [OK]

Hint: Use HAVING in subquery to filter groups before outer select [OK]

Common Mistakes:

• Using WHERE with aggregation functions
• Placing HAVING outside GROUP BY context
• Not using subquery to filter groups first