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.

Practice

1. What is a correlated subquery in MySQL?

easy

A. A subquery that uses values from the outer query for each row.

B. A subquery that runs only once before the outer query.

C. A query that joins two tables without any subqueries.

D. A subquery that returns a fixed constant value.

Solution

1. Step 1: Understand subquery behavior

   A correlated subquery depends on the outer query's current row values to execute.

2. Step 2: Compare options

   The option describing a subquery that uses values from the outer query for each row correctly defines a correlated subquery.

3. Final Answer:

   A subquery that uses values from the outer query for each row. -> Option A

4. Quick Check:

   Correlated subquery = uses outer row values [OK]

Hint: Correlated subqueries depend on outer query row values [OK]

Common Mistakes:

• Thinking subqueries always run once
• Confusing joins with subqueries
• Assuming subqueries return constants only

2. Which of the following is the correct syntax for a correlated subquery in MySQL?

easy

A. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id);

B. SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees);

C. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

D. SELECT * FROM employees WHERE salary = 50000;

Solution

1. Step 1: Identify correlated subquery syntax

   A correlated subquery references a column from the outer query inside the subquery, like e2.department_id = employees.department_id.

2. Step 2: Check each option

   SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id); uses the outer table's column inside the subquery condition, making it a correlated subquery.

3. Final Answer:

   SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id); -> Option A

4. Quick Check:

   Correlated subquery syntax = SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id); [OK]

Hint: Look for subquery referencing outer query table alias [OK]

Common Mistakes:

• Missing outer table reference in subquery
• Using subquery without WHERE clause for correlation
• Confusing correlated with simple subqueries

3. Given the tables:
employees(id, name, department_id, salary)
and
departments(id, name)
What will this query return?

SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

medium

A. Employees whose salary is above the average salary of all departments combined.

B. Employees whose salary is above the average salary of all employees.

C. Employees whose salary is above the average salary of their department.

D. Syntax error due to subquery referencing outer query.

Solution

1. Step 1: Understand the subquery condition

   The subquery calculates the average salary for the department of the current employee (using e.department_id).

2. Step 2: Compare employee salary with department average

   The outer query selects employees whose salary is greater than their department's average salary.

3. Final Answer:

   Employees whose salary is above the average salary of their department. -> Option C

4. Quick Check:

   Correlated subquery filters by department average salary [OK]

Hint: Check if subquery filters by outer row's department_id [OK]

Common Mistakes:

• Thinking subquery calculates overall average salary
• Assuming syntax error due to correlation
• Confusing department and employee averages

4. Identify the error in this correlated subquery:

SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department_id);

medium

A. Subquery cannot reference outer query columns.

B. Alias 'e1' is not allowed in subquery.

C. Missing GROUP BY clause in subquery.

D. Column 'department' does not exist in employees table.

Solution

1. Step 1: Check column names in subquery condition

   The subquery compares e2.department to e1.department_id, but 'department' column does not exist in employees table.

2. Step 2: Confirm correct column usage

   The correct column should be e2.department_id to match e1.department_id for correlation.

3. Final Answer:

   Column 'department' does not exist in employees table. -> Option D

4. Quick Check:

   Invalid column name causes error [OK]

Hint: Verify column names match exactly in both queries [OK]

Common Mistakes:

• Using wrong column names for correlation
• Assuming alias usage causes error
• Thinking GROUP BY is required in subquery

5. You want to find all departments where every employee earns more than the average salary of all employees in the company. Which query correctly uses a correlated subquery to achieve this?

hard

A. SELECT d.name FROM departments d WHERE ALL (SELECT salary FROM employees WHERE department_id = d.id) > (SELECT AVG(salary) FROM employees);

B. SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary <= (SELECT AVG(salary) FROM employees));

C. SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > (SELECT AVG(salary) FROM employees));

D. SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = d.id));

Solution

1. Step 1: Understand the requirement

   We want departments where every employee earns more than the company-wide average salary.

2. Step 2: Analyze the NOT EXISTS query

   SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary <= (SELECT AVG(salary) FROM employees)); uses NOT EXISTS to ensure no employee in the department earns less or equal to the company average, meaning all earn more.

3. Step 3: Check other options

   The options using EXISTS check for the existence of at least one qualifying employee, not all. The option attempting to use ALL with a subquery on the left side uses invalid syntax in MySQL.

4. Final Answer:

   SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary <= (SELECT AVG(salary) FROM employees)); -> Option B

5. Quick Check:

   NOT EXISTS ensures all employees earn above average [OK]

Hint: Use NOT EXISTS to check all employees meet condition [OK]

Common Mistakes:

• Using EXISTS instead of NOT EXISTS for 'all' condition
• Misusing ALL keyword in MySQL
• Comparing with department average instead of company average