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.