Subqueries in FROM clause (derived tables) in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple derived table query

What is the output of this query?

SELECT dt.category, dt.total_sales FROM (SELECT category, SUM(sales) AS total_sales FROM products GROUP BY category) AS dt ORDER BY dt.total_sales DESC;

MySQL

CREATE TABLE products (id INT, category VARCHAR(20), sales INT);
INSERT INTO products VALUES (1, 'Books', 100), (2, 'Books', 150), (3, 'Electronics', 200), (4, 'Clothing', 50);

A [{"category": "Clothing", "total_sales": 50}, {"category": "Books", "total_sales": 250}, {"category": "Electronics", "total_sales": 200}]

B [{"category": "Books", "total_sales": 100}, {"category": "Books", "total_sales": 150}, {"category": "Electronics", "total_sales": 200}, {"category": "Clothing", "total_sales": 50}]

C [{"category": "Books", "total_sales": 250}, {"category": "Electronics", "total_sales": 200}, {"category": "Clothing", "total_sales": 50}]

D [{"category": "Electronics", "total_sales": 200}, {"category": "Books", "total_sales": 250}, {"category": "Clothing", "total_sales": 50}]

💡 Hint

Think about how the subquery groups sales by category and sums them, then the outer query orders by total_sales descending.

The subquery groups products by category and sums their sales. The outer query selects these results and orders them by total sales from highest to lowest. So Books with 250 comes first, then Electronics with 200, then Clothing with 50.

📝 Syntax

intermediate

Identify the syntax error in derived table usage

Which option contains a syntax error when using a derived table in the FROM clause?

MySQL

SELECT dt.category, dt.total FROM (SELECT category, SUM(amount) AS total FROM sales GROUP BY category) dt;

A SELECT dt.category, dt.total FROM (SELECT category, SUM(amount) AS total FROM sales GROUP BY category);

B SELECT dt.category, dt.total FROM (SELECT category, SUM(amount) AS total FROM sales GROUP BY category) AS dt;

C SELECT dt.category, dt.total FROM (SELECT category, SUM(amount) AS total FROM sales GROUP BY category) dt;

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

💡 Hint

Remember that every derived table must have an alias.

Option A is missing the alias for the derived table, which is required in SQL syntax. Options A and B correctly alias the derived table, and D adds a WHERE clause but still aliases correctly.

❓ optimization

advanced

Optimizing a query with multiple derived tables

Given two derived tables joined on a common key, which option is the most efficient way to write the query to avoid unnecessary computation?

MySQL

SELECT a.category, a.total_sales, b.total_returns FROM
(SELECT category, SUM(sales) AS total_sales FROM products GROUP BY category) AS a
JOIN
(SELECT category, SUM(returns) AS total_returns FROM products GROUP BY category) AS b
ON a.category = b.category;

A Keep the two derived tables and join them as is, since it is clearer and easier to read.

B Use a single derived table that calculates both sums in one pass: SELECT category, SUM(sales) AS total_sales, SUM(returns) AS total_returns FROM products GROUP BY category;

C Use two separate queries and combine results in application code to reduce database load.

D Replace derived tables with temporary tables to speed up the join.

💡 Hint

Think about how many times the table is scanned in each approach.

Option B scans the products table once and calculates both sums together, which is more efficient. Option B scans the table twice. Option B shifts load to application and may increase latency. Option B adds overhead of managing temp tables.

🔧 Debug

advanced

Debugging a query with nested derived tables

This query returns an error. What is the cause?

SELECT dt1.category, dt2.avg_price FROM
(SELECT category, COUNT(*) AS cnt FROM products GROUP BY category) dt1
JOIN
(SELECT category, AVG(price) AS avg_price FROM products GROUP BY category) dt2
ON dt1.category = dt2.category
WHERE dt1.cnt > 10;

A The query is valid and should run without errors.

B The WHERE clause cannot reference columns from derived tables; it should be in HAVING.

C The JOIN condition is invalid because it uses columns from different derived tables.

D The alias dt1 is missing in the WHERE clause.

💡 Hint

Check if referencing derived table columns in WHERE is allowed after the JOIN.

The query is valid. The WHERE clause filters after the JOIN and can reference columns from derived tables. The JOIN condition is valid. No alias is missing in WHERE.

🧠 Conceptual

expert

Understanding scope and visibility of derived tables

Consider this query:

SELECT category, total_sales FROM
(SELECT category, SUM(sales) AS total_sales FROM products GROUP BY category) AS dt
WHERE total_sales > (SELECT AVG(total_sales) FROM (SELECT category, SUM(sales) AS total_sales FROM products GROUP BY category) AS dt2);

What is the role of the derived table dt2 in the subquery?

A dt2 filters categories with total_sales above average before the outer query runs.

B dt2 is redundant and can be removed without affecting the query result.

C dt2 is the same as dt and causes a naming conflict leading to an error.

D dt2 provides the average total sales across all categories to compare each category's total_sales against.

💡 Hint

Think about how the subquery calculates the average and how it relates to the outer query.

dt2 is a derived table inside the subquery that calculates total sales per category. The subquery then computes the average of these totals. The outer query compares each category's total_sales to this average to filter results.