Correlated subqueries execution model in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select employees whose salary is greater than the average salary in their department.

PostgreSQL

SELECT emp_name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.[1]);

Drag options to blanks, or click blank then click option'

Adept_id

Bsalary

Cemp_id

Demp_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee ID instead of department ID for correlation.

Referencing salary or name instead of department ID.

The correlated subquery compares each employee's salary to the average salary of their own department. The correlation happens via dept_id.

2fill in blank

medium

Complete the code to find products with price higher than the average price in their category.

PostgreSQL

SELECT product_name FROM products p WHERE price > (SELECT AVG(price) FROM products WHERE category_id = p.[1]);

Drag options to blanks, or click blank then click option'

Acategory_id

Bproduct_name

Cprice

Dproduct_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using product ID instead of category ID for correlation.

Comparing price to product name.

The subquery is correlated by matching the category ID of the outer product to compute the average price per category.

3fill in blank

hard

Fix the error in the correlated subquery to find customers who placed orders with total amount greater than average order amount for that customer.

PostgreSQL

SELECT customer_id FROM orders o WHERE total_amount > (SELECT AVG(total_amount) FROM orders WHERE customer_id = o.[1]);

Drag options to blanks, or click blank then click option'

Aorder_id

Bcustomer_id

Ctotal_amount

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using order ID instead of customer ID for correlation.

Referencing total_amount or order_date instead of customer_id.

The correlation must be on customer_id to compare each order's total amount to the average for that customer.

4fill in blank

hard

Fill both blanks to select employees whose salary is above the average salary in their department and who joined after 2010.

PostgreSQL

SELECT emp_name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.[1]) AND join_year [2] 2010;

Drag options to blanks, or click blank then click option'

Adept_id

B>

C<=

Demp_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee ID instead of department ID for correlation.

Using '<=' instead of '>' for join year condition.

The first blank correlates the subquery by department ID. The second blank uses '>' to filter employees who joined after 2010.

5fill in blank

hard

Fill all three blanks to select products with price above average in their category and stock quantity greater than 50.

PostgreSQL

SELECT product_name FROM products p WHERE price > (SELECT AVG(price) FROM products WHERE category_id = p.[1]) AND stock_quantity [2] [3];

Drag options to blanks, or click blank then click option'

Acategory_id

B>

C50

Dproduct_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using product ID instead of category ID for correlation.

Using '<' or '=' instead of '>' for stock quantity.

Using wrong number instead of 50.

The first blank correlates by category ID. The second blank uses '>' to filter stock quantity, and the third blank is the number 50.

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