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.