Correlated subqueries in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of correlated subquery with aggregation

Given the tables Employees(emp_id, dept_id, salary) and Departments(dept_id, dept_name), what is the output of this query?

SELECT e.emp_id, e.salary FROM Employees e WHERE e.salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id);

Assume the following data:

Employees
emp_id | dept_id | salary
1 | 10 | 5000
2 | 10 | 6000
3 | 20 | 7000
4 | 20 | 8000
5 | 10 | 5500

What rows will be returned?

MySQL

SELECT e.emp_id, e.salary FROM Employees e WHERE e.salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id);

A

emp_id | salary
2      | 6000
4      | 8000

B

emp_id | salary
1      | 5000
2      | 6000
5      | 5500

C

emp_id | salary
2      | 6000
3      | 7000
4      | 8000

D

emp_id | salary
1      | 5000
3      | 7000

💡 Hint

Think about the average salary per department and which employees earn more than their department's average.

The subquery calculates the average salary for the employee's department. For dept 10, average is (5000+6000+5500)/3 = 5500. Employees with salary > 5500 are emp_id 2 (6000). For dept 20, average is (7000+8000)/2 = 7500. Employee with salary > 7500 is emp_id 4 (8000).

🧠 Conceptual

intermediate

Understanding correlated subquery execution

Which statement best describes how a correlated subquery is executed in SQL?

A The subquery is executed once before the outer query and its result is reused.

B The subquery is executed once for each row processed by the outer query.

C The subquery is executed only if the outer query returns no rows.

D The subquery is executed after the outer query completes.

💡 Hint

Think about how the subquery depends on values from the outer query.

A correlated subquery references columns from the outer query, so it must be executed once per outer row to evaluate the condition.

📝 Syntax

advanced

Identify the syntax error in correlated subquery

Which option contains a syntax error in the correlated subquery?

MySQL

SELECT e1.emp_id FROM Employees e1 WHERE e1.salary > (SELECT AVG(salary) FROM Employees e2 WHERE e2.dept_id = e1.dept_id

A SELECT e1.emp_id FROM Employees e1 WHERE e1.salary > (SELECT AVG(salary) FROM Employees e2 WHERE e2.dept_id = e1.dept_id) ORDER BY e1.emp_id;

B SELECT e1.emp_id FROM Employees e1 WHERE e1.salary > (SELECT AVG(salary) FROM Employees e2 WHERE e2.dept_id = e1.dept_id));

C SELECT e1.emp_id FROM Employees e1 WHERE e1.salary > (SELECT AVG(salary) FROM Employees e2 WHERE e2.dept_id = e1.dept_id);

D SELECT e1.emp_id FROM Employees e1 WHERE e1.salary > (SELECT AVG(salary) FROM Employees e2 WHERE e2.dept_id = e1.dept_id

💡 Hint

Check for missing parentheses or semicolons.

Option D is missing the closing parenthesis for the subquery, causing a syntax error.

❓ optimization

advanced

Optimizing correlated subquery for performance

You have a query with a correlated subquery that runs slowly:

SELECT e.emp_id FROM Employees e WHERE e.salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id);

Which approach is likely to improve performance?

A Use a UNION ALL instead of the correlated subquery.

B Add an index on Employees.emp_id to speed up the subquery.

C Rewrite the query using a JOIN with a derived table that precomputes average salaries per department.

D Remove the WHERE clause to avoid the subquery.

💡 Hint

Think about how to avoid running the subquery for each row.

Precomputing averages per department in a derived table and joining avoids repeated subquery execution, improving performance.

🔧 Debug

expert

Debugging unexpected results from correlated subquery

Consider this query:

SELECT e1.emp_id, e1.salary FROM Employees e1 WHERE e1.salary > (SELECT MAX(salary) FROM Employees e2 WHERE e2.dept_id != e1.dept_id);

It returns no rows, but you expect some. What is the most likely reason?

A The subquery compares salaries from all departments except the current one, so the condition is never true.

B The subquery is missing a GROUP BY clause causing aggregation errors.

C The outer query should use < instead of > to compare salaries.

D The inequality operator != is invalid in correlated subqueries.

💡 Hint

Think about what the subquery returns and how it compares to the outer salary.

The subquery returns the maximum salary from all other departments. Since this is a single maximum value, it's likely higher than any salary in the current department, so no rows satisfy salary > that max.