Correlated subquery execution model in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of correlated subquery with aggregation

Consider the tables Employees(emp_id, dept_id, salary) and Departments(dept_id, dept_name). What is the output of the following query?

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

Assume the data:

Employees: (1, 10, 5000), (2, 10, 6000), (3, 20, 4000), (4, 20, 4500)

SQL

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

A

emp_id: 2, salary: 6000
emp_id: 4, salary: 4500

B

emp_id: 1, salary: 5000
emp_id: 2, salary: 6000

C emp_id: 4, salary: 4500

D emp_id: 2, salary: 6000

💡 Hint

Think about how the average salary is calculated per department and which employees have salaries above that average.

The subquery calculates the average salary for the employee's department. For dept 10, average is (5000+6000)/2=5500. Only emp_id 2 has salary 6000 > 5500. For dept 20, average is (4000+4500)/2=4250. emp_id 4 has salary 4500 > 4250 (emp_id 3: 4000 < 4250). Thus, the query returns emp_id 2, salary 6000 and emp_id 4, salary 4500, which is option A.

📝 Syntax

intermediate

Identify syntax error in correlated subquery

Which option contains a syntax error in the correlated subquery?

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

A SELECT e1.emp_id FROM Employees e1 WHERE e1.salary &gt; (SELECT AVG(e2.salary) FROM Employees e2 WHERE e2.dept_id = e1.dept_id);

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

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

D SELECT emp_id FROM Employees WHERE salary &gt; (SELECT AVG(salary) FROM Employees WHERE dept_id = dept_id);

💡 Hint

Look for missing table aliases or ambiguous column references.

Option D has WHERE dept_id = dept_id which compares the same column without alias, causing ambiguity and logical error. It is syntactically valid but logically incorrect. However, since no alias is used, it can cause confusion. Other options correctly use aliases.

❓ optimization

advanced

Optimize correlated subquery for performance

Given the query:

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

Which option is the best way to optimize this query for large datasets?

A Rewrite using JOIN with GROUP BY to precompute averages.

B Add an index on Employees.salary column.

C Use DISTINCT inside the subquery to reduce duplicates.

D Replace AVG with MAX to reduce computation.

💡 Hint

Think about avoiding repeated subquery execution for each row.

Correlated subqueries run the inner query for each outer row, which is expensive. Precomputing averages with a JOIN and GROUP BY avoids repeated calculations, improving performance. Indexing salary helps but does not eliminate repeated subqueries. DISTINCT and replacing AVG with MAX do not optimize correlated subqueries effectively.

🔧 Debug

advanced

Debug unexpected empty result from correlated subquery

Why does the following query return no rows?

SELECT emp_id FROM Employees e WHERE salary > (SELECT MAX(salary) FROM Employees WHERE dept_id = e.dept_id AND emp_id <> e.emp_id);

Assume each department has only one employee.

A Because the outer query filters out all employees by mistake.

B Because the subquery syntax is invalid and causes error.

C Because the subquery returns NULL, comparison with NULL yields false.

D Because the MAX function returns zero when no rows match.

💡 Hint

Consider what happens when the subquery has no matching rows.

If a department has only one employee, the subquery filters out that employee (emp_id <> e.emp_id), so no rows match. The subquery returns NULL. Comparing salary > NULL yields unknown (false), so no rows are returned.

🧠 Conceptual

expert

Understanding execution order of correlated subqueries

Which statement best describes how a correlated subquery executes?

A The inner query runs once and its result is reused for all outer rows.

B The inner query runs once for each row processed by the outer query, using values from that row.

C The inner query runs independently of the outer query and returns a fixed result.

D The inner query runs after the outer query completes and uses aggregated results.

💡 Hint

Think about how the inner query depends on the outer query's current row.

Correlated subqueries execute the inner query once per each row of the outer query, using values from that row to filter or compute results. This is why they can be slower than uncorrelated subqueries.