Subqueries in FROM clause (derived tables) in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

MySQL

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

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

A*

Bid

Cname

Dsub

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the alias name instead of columns.

Forgetting to use the alias in the FROM clause.

Using * selects all columns from the derived table sub.

2fill in blank

medium

Complete the code to count the number of users in the derived table.

MySQL

SELECT COUNT([1]) FROM (SELECT id FROM users) AS user_count;

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

Auser_count

B*

Cname

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Counting a column not selected in the derived table.

Using the alias name inside COUNT().

Counting id counts the number of rows in the derived table.

3fill in blank

hard

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

MySQL

SELECT sub.id FROM (SELECT id FROM users) [1];

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

AAS sub

BBY

CON

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect keywords like BY, ON, or IN for aliasing.

Omitting the alias entirely.

The AS keyword is used to alias the derived table.

4fill in blank

hard

Fill both blanks to select user names and their total orders from a derived table.

MySQL

SELECT [1], orders FROM (SELECT user_id, COUNT(*) [2] orders FROM orders GROUP BY user_id) AS order_summary JOIN users ON users.id = order_summary.user_id;

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

Ausers.name

BAS

Cuser_name

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect aliasing keywords.

Selecting columns without table prefix causing ambiguity.

users.name selects the user name, and AS aliases the count as orders.

5fill in blank

hard

Fill all three blanks to create a derived table that calculates average scores and select students with average above 80.

MySQL

SELECT [1], avg_score FROM (SELECT student_id, AVG(score) [2] avg_score FROM scores GROUP BY student_id) [3] WHERE avg_score > 80;

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

Astudent_id

BAS

Chigh_achievers

Dscore

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to alias the derived table.

Not aliasing the average score column.

Selecting wrong columns in the outer query.

student_id is selected, AS aliases the average score, and high_achievers is the alias for the derived table.