Correlated subqueries execution model in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of correlated subquery filtering employees by department average salary

Consider the tables employees(emp_id, name, dept_id, salary) and departments(dept_id, dept_name). What is the output of the following query?

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

PostgreSQL

CREATE TABLE employees(emp_id INT, name TEXT, dept_id INT, salary INT);
INSERT INTO employees VALUES
(1, 'Alice', 10, 5000),
(2, 'Bob', 10, 6000),
(3, 'Charlie', 20, 4000),
(4, 'Diana', 20, 4500),
(5, 'Eve', 10, 5500);

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

A [{"name": "Alice", "salary": 5000}, {"name": "Bob", "salary": 6000}, {"name": "Eve", "salary": 5500}]

B []

C [{"name": "Charlie", "salary": 4000}, {"name": "Diana", "salary": 4500}]

D [{"name": "Bob", "salary": 6000}, {"name": "Diana", "salary": 4500}]

💡 Hint

Think about how the subquery calculates the average salary per department and compares each employee's salary to it.

The subquery calculates the average salary for each employee's department. For dept_id 10, average is (5000+6000+5500)/3 = 5500; Bob (6000 > 5500) yes, Alice (5000 < 5500) no, Eve (5500 = 5500) no. For dept_id 20, average is (4000+4500)/2 = 4250; Diana (4500 > 4250) yes, Charlie (4000 < 4250) no. Output is Bob and Diana, matching option D.

🧠 Conceptual

intermediate

Understanding correlated subquery execution frequency

In a correlated subquery, how often is the inner subquery executed during the execution of the outer query?

A Once for each row processed by the outer query

B Only if the outer query returns no rows

C Once for the entire outer query

D Twice for each row processed by the outer query

💡 Hint

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

A correlated subquery references columns from the outer query, so it must be re-evaluated for each row of the outer query to get the correct result.

📝 Syntax

advanced

Identify the syntax error in correlated subquery

Which option contains a syntax error in the correlated subquery usage?

PostgreSQL

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

A SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id == e1.dept_id);

B SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id);

C ;)di_tped.1e = di_tped.2e EREHW 2e seeyolpme MORF )yralas(GVA TCELES( > yralas EREHW 1e seeyolpme MORF eman.1e TCELES

D ELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id);

💡 Hint

Check the comparison operator used inside the subquery.

In SQL, the equality operator is a single equals sign '='. Using '==' causes a syntax error.

❓ optimization

advanced

Optimizing correlated subqueries with EXISTS

Which query is more efficient for checking if an employee's department has any employees with salary greater than 7000?

A SELECT name FROM employees e WHERE salary > 7000;

B SELECT name FROM employees e WHERE (SELECT COUNT(*) FROM employees WHERE dept_id = e.dept_id AND salary > 7000) > 0;

C SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM employees WHERE dept_id = e.dept_id AND salary > 7000);

D SELECT name FROM employees e WHERE dept_id IN (SELECT dept_id FROM employees WHERE salary > 7000);

💡 Hint

EXISTS stops scanning as soon as it finds a matching row, COUNT(*) counts all matching rows.

EXISTS is more efficient because it returns true as soon as it finds one matching row, avoiding counting all rows. COUNT(*) counts all rows, which is slower. Option C filters employees with salary > 7000, not checking department. Option C uses IN which can be less efficient and may return duplicates.

🔧 Debug

expert

Debugging unexpected results in correlated subquery with NULLs

Given the query:

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

Some employees with NULL salaries are missing from the results unexpectedly. What is the cause?

A The outer query filters out NULL salaries automatically, so they never appear.

B The subquery returns NULL if all salaries in the department are NULL, causing the comparison to fail.

C The AVG aggregate ignores NULL salaries, so employees with NULL salaries are excluded from comparison.

D The correlated subquery syntax is invalid when NULL values exist.

💡 Hint

Consider how AVG behaves with all NULL values and how NULL comparisons work in SQL.

If all salaries in a department are NULL, AVG returns NULL. Comparing salary > NULL yields UNKNOWN, so the row is excluded. This causes employees with NULL salaries to be missing unexpectedly.

Practice

1. What is a key characteristic of a correlated subquery in PostgreSQL?

easy

A. It runs the inner query once for each row of the outer query.

B. It runs the inner query only once regardless of outer rows.

C. It always returns a fixed number of rows.

D. It cannot reference columns from the outer query.

Solution

1. Step 1: Understand the definition of correlated subquery

   A correlated subquery references columns from the outer query and runs once per outer row.

2. Step 2: Compare options with this behavior

   Only It runs the inner query once for each row of the outer query. correctly states that the inner query runs once per outer row.

3. Final Answer:

   It runs the inner query once for each row of the outer query. -> Option A

4. Quick Check:

   Correlated subquery = runs per outer row [OK]

Hint: Correlated means inner query depends on outer row [OK]

Common Mistakes:

• Thinking inner query runs once for all rows
• Assuming no reference to outer query columns
• Confusing correlated with uncorrelated subqueries

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

easy

A. SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department);

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

C. SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.salary > 50000);

D. SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.name = 'John');

Solution

1. Step 1: Identify the correlated subquery condition

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department); references the outer query alias e.department inside the inner query, making it correlated.

2. Step 2: Check other options for correlation

   Options B, C, and D do not reference outer query columns inside the subquery, so they are not correlated.

3. Final Answer:

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department); -> Option A

4. Quick Check:

   Correlation requires outer reference inside subquery [OK]

Hint: Look for outer query column inside subquery WHERE clause [OK]

Common Mistakes:

• Missing outer query reference in subquery
• Using same alias for outer and inner queries
• Confusing filtering with correlation

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

SELECT d.name, (SELECT AVG(e.salary) FROM employees e WHERE e.department_id = d.id) AS avg_salary FROM departments d;

medium

A. Each department name with the overall average salary of all employees.

B. Each department name with the average salary of its employees.

C. An error because subquery is not correlated.

D. Each employee name with their salary.

Solution

1. Step 1: Identify the correlation in the subquery

   The subquery uses e.department_id = d.id, linking inner query to outer query row.

2. Step 2: Understand the output

   For each department, the subquery calculates average salary of employees in that department.

3. Final Answer:

   Each department name with the average salary of its employees. -> Option B

4. Quick Check:

   Correlated subquery returns per outer row result [OK]

Hint: Check if subquery filters by outer query column [OK]

Common Mistakes:

• Assuming subquery returns overall average salary
• Thinking subquery is uncorrelated and runs once
• Confusing department and employee columns

4. Identify the error in this correlated subquery:

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

medium

A. The query is missing a GROUP BY clause.

B. The subquery is missing a WHERE clause.

C. The subquery references a non-existent column.

D. The inner query alias shadows the outer query alias causing incorrect correlation.

Solution

1. Step 1: Analyze alias usage in inner and outer queries

   Both inner and outer queries use alias e1, causing the inner query to reference itself only.

2. Step 2: Understand impact on correlation

   Because of alias shadowing, the subquery condition e1.department_id = e1.department_id is always true and not correlated to outer row.

3. Final Answer:

   The inner query alias shadows the outer query alias causing incorrect correlation. -> Option D

4. Quick Check:

   Use different aliases to avoid shadowing [OK]

Hint: Use distinct aliases for outer and inner queries [OK]

Common Mistakes:

• Using same alias for inner and outer queries
• Assuming WHERE clause is missing
• Confusing alias shadowing with syntax error

5. You want to find departments where the highest employee salary is greater than the average salary of all employees in that department. Which query correctly uses a correlated subquery to achieve this?

hard

A. 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));

B. SELECT d.name FROM departments d WHERE MAX(e.salary) > AVG(e.salary) FROM employees e WHERE e.department_id = d.id;

C. SELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id);

D. SELECT d.name FROM departments d JOIN employees e ON e.department_id = d.id WHERE e.salary > (SELECT AVG(salary) FROM employees);

Solution

1. Step 1: Understand the requirement

   We need to compare the max salary per department with the average salary per department.

2. Step 2: Analyze each option's logic

   SELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id); uses two correlated subqueries referencing d.id to get max and average salary per department, correctly implementing the condition.

3. Step 3: Identify errors in other options

   SELECT d.name FROM departments d WHERE MAX(e.salary) > AVG(e.salary) FROM employees e WHERE e.department_id = d.id; has invalid syntax mixing aggregation and WHERE clause. SELECT d.name FROM departments d JOIN employees e ON e.department_id = d.id WHERE e.salary > (SELECT AVG(salary) FROM employees); compares employee salary to overall average, not per department. 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)); compares employee salary to overall average, not per department.

4. Final Answer:

   SELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id); -> Option C

5. Quick Check:

   Use correlated subqueries for per-department aggregates [OK]

Hint: Use correlated subqueries for per-group comparisons [OK]

Common Mistakes:

• Mixing aggregation without GROUP BY
• Comparing to overall average instead of per department
• Incorrect syntax for aggregation in WHERE clause