Subquery in FROM clause (derived table) in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from a derived table named 'sub'.

SQL

SELECT [1] FROM (SELECT id, name FROM employees) AS sub;

Drag options to blanks, or click blank then click option'

Aid

Bname

C*

Dsub

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the alias name instead of columns.

Selecting only one column when all are needed.

Using * selects all columns from the derived table sub.

2fill in blank

medium

Complete the code to alias the derived table as 'dept_avg'.

SQL

SELECT department, avg_salary FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS [1];

Drag options to blanks, or click blank then click option'

Adept_avg

Bsalary_avg

Caverage

Demp_avg

Attempts:

3 left

💡 Hint

Common Mistakes

Using a generic alias that doesn't match the data.

Forgetting to alias the derived table.

The alias dept_avg names the derived table for use in the outer query.

3fill in blank

hard

Fix the error in the code by completing the alias for the derived table.

SQL

SELECT name, total_sales FROM (SELECT name, SUM(sales) AS total_sales FROM sales_data GROUP BY name) [1];

Drag options to blanks, or click blank then click option'

Asales_summary

BBY

CAS

DAS sales_summary

Attempts:

3 left

💡 Hint

Common Mistakes

Including the keyword AS incorrectly after the derived table.

Omitting the alias entirely.

The derived table must have an alias without the keyword AS in this position; just the alias name sales_summary is correct.

4fill in blank

hard

Fill both blanks to select the average salary and alias the derived table correctly.

SQL

SELECT department, [1] FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) [2];

Drag options to blanks, or click blank then click option'

Aavg_salary

BAVG(salary)

Cdept_avg

Daverage_salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using the aggregate function directly in the outer query.

Incorrectly aliasing the derived table.

The outer query selects the alias avg_salary from the derived table, which is aliased as dept_avg.

5fill in blank

hard

Fill all three blanks to create a derived table that calculates total sales per region and select from it.

SQL

SELECT [1], [2] FROM (SELECT region, SUM(sales) AS [3] FROM sales GROUP BY region) sales_totals;

Drag options to blanks, or click blank then click option'

Aregion

Btotal_sales

Dsales_sum

Attempts:

3 left

💡 Hint

Common Mistakes

Mismatching the alias names between inner and outer queries.

Selecting columns not present in the derived table.

The outer query selects region and total_sales from the derived table. The derived table aliases the sum as total_sales.