Correlated subqueries in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select employees whose salary is greater than the average salary in their department.

MySQL

SELECT employee_id, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.[1]);

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

Adepartment_id

Bemployee_id

Csalary

Dmanager_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee_id instead of department_id causes no correlation.

Using salary in the WHERE clause of subquery is incorrect here.

The subquery compares each employee's salary to the average salary of their own department. The correlation is done using department_id.

2fill in blank

medium

Complete the code to find customers who have placed more orders than the average number of orders per customer.

MySQL

SELECT customer_id FROM orders o GROUP BY customer_id HAVING COUNT(*) > (SELECT AVG([1]) FROM (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) AS subquery);

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

Aorder_date

Border_id

Corder_count

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using customer_id would average customer IDs instead of order counts.

Using order_date or order_id does not represent the number of orders.

The subquery calculates the average number of orders per customer using the order_count column from the derived table.

3fill in blank

hard

Fix the error in the correlated subquery to find products priced higher than the average price in their category.

MySQL

SELECT product_id, price FROM products p WHERE price > (SELECT AVG(price) FROM products WHERE category_id = [1].category_id);

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

Acategory

Bproducts

Cc

Dp

Attempts:

3 left

💡 Hint

Common Mistakes

Using the table name 'products' inside the subquery instead of the alias causes an error.

Using an undefined alias like 'c' or 'category' causes syntax errors.

The subquery must refer to the alias of the outer query table, which is p, to correlate by category_id.

4fill in blank

hard

Fill both blanks to select employees who earn more than the average salary of their department and have a job title starting with 'Senior'.

MySQL

SELECT employee_id, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.[1]) AND job_title [2] 'Senior%';

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

Adepartment_id

Bemployee_id

CLIKE

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of LIKE for pattern matching causes no results.

Using employee_id instead of department_id breaks the correlation.

The first blank needs the department_id to correlate the subquery. The second blank uses LIKE to match job titles starting with 'Senior'.

5fill in blank

hard

Fill both blanks to select orders where the order amount is greater than the average order amount for the same customer and the order status is 'Completed'.

MySQL

SELECT order_id, order_amount FROM orders o WHERE order_amount > (SELECT AVG(order_amount) FROM orders WHERE customer_id = o.[1]) AND order_status = [2];

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

Acustomer_id

B'Completed'

D"Completed"

Attempts:

3 left

💡 Hint

Common Mistakes

Using double quotes for string literals causes syntax errors in MySQL.

Not correlating on customer_id causes wrong average calculation.

The subquery correlates on customer_id. The order status comparison requires the string 'Completed' enclosed in single quotes.