Correlated subqueries execution model in PostgreSQL - Mini Project: Build & Apply

Understanding Correlated Subqueries Execution Model in PostgreSQL

📖 Scenario: You are working with a small company database that has two tables: employees and departments. You want to find employees who earn more than the average salary in their own department.

🎯 Goal: Build a SQL query using a correlated subquery to list employees whose salary is higher than the average salary of their department.

📋 What You'll Learn

Create a table called departments with columns dept_id (integer) and dept_name (text).

Create a table called employees with columns emp_id (integer), emp_name (text), salary (integer), and dept_id (integer).

Insert exact data into departments and employees as specified.

Write a correlated subquery that compares each employee's salary to the average salary of their department.

Use the exact table and column names as given.

💡 Why This Matters

🌍 Real World

Correlated subqueries are useful when you need to compare each row to a group-level calculation, such as finding employees earning above their department average.

💼 Career

Understanding correlated subqueries helps in writing efficient and clear SQL queries for reporting and data analysis tasks common in many data-related jobs.

1

Create the departments and employees tables with data

Create a table called departments with columns dept_id (integer) and dept_name (text). Then create a table called employees with columns emp_id (integer), emp_name (text), salary (integer), and dept_id (integer). Insert these exact rows into departments: (1, 'Sales'), (2, 'Engineering'), (3, 'HR'). Insert these exact rows into employees: (101, 'Alice', 70000, 1), (102, 'Bob', 60000, 1), (103, 'Charlie', 80000, 2), (104, 'Diana', 90000, 2), (105, 'Eve', 50000, 3).

PostgreSQL

-- Create tables and insert data here

Hint

Use CREATE TABLE statements for both tables and INSERT INTO with exact values.

2

Define a helper variable for department average salary

Write a SQL snippet that will be used as a subquery to calculate the average salary for a given department. This will be used later in the correlated subquery. Use the exact phrase (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) where e is the alias for the outer employees table.

PostgreSQL

CREATE TABLE departments (dept_id INTEGER, dept_name TEXT);
CREATE TABLE employees (emp_id INTEGER, emp_name TEXT, salary INTEGER, dept_id INTEGER);

INSERT INTO departments VALUES (1, 'Sales'), (2, 'Engineering'), (3, 'HR');
INSERT INTO employees VALUES (101, 'Alice', 70000, 1), (102, 'Bob', 60000, 1), (103, 'Charlie', 80000, 2), (104, 'Diana', 90000, 2), (105, 'Eve', 50000, 3);

-- Define the correlated subquery part for average salary
-- Your code here

Hint

Use the exact subquery (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) referencing the outer alias e.

3

Write the correlated subquery to find employees earning above their department average

Write a SQL SELECT statement to get emp_name and salary from employees aliased as e. Use a WHERE clause with a correlated subquery that compares e.salary to the average salary of e.dept_id using the exact subquery (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id). Use the exact alias e for the outer employees table.

PostgreSQL

CREATE TABLE departments (dept_id INTEGER, dept_name TEXT);
CREATE TABLE employees (emp_id INTEGER, emp_name TEXT, salary INTEGER, dept_id INTEGER);

INSERT INTO departments VALUES (1, 'Sales'), (2, 'Engineering'), (3, 'HR');
INSERT INTO employees VALUES (101, 'Alice', 70000, 1), (102, 'Bob', 60000, 1), (103, 'Charlie', 80000, 2), (104, 'Diana', 90000, 2), (105, 'Eve', 50000, 3);

SELECT emp_name, salary FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id)
-- Your code here

Hint

Use the alias e for the outer employees table and compare e.salary with the correlated subquery.

4

Complete the query with ordering by salary descending

Add an ORDER BY clause to the existing query to sort the results by salary in descending order. Use the exact phrase ORDER BY salary DESC at the end of the query.

PostgreSQL

CREATE TABLE departments (dept_id INTEGER, dept_name TEXT);
CREATE TABLE employees (emp_id INTEGER, emp_name TEXT, salary INTEGER, dept_id INTEGER);

INSERT INTO departments VALUES (1, 'Sales'), (2, 'Engineering'), (3, 'HR');
INSERT INTO employees VALUES (101, 'Alice', 70000, 1), (102, 'Bob', 60000, 1), (103, 'Charlie', 80000, 2), (104, 'Diana', 90000, 2), (105, 'Eve', 50000, 3);

SELECT emp_name, salary FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id)
-- Add ORDER BY clause here

Hint

Add ORDER BY salary DESC at the end of the query to sort results from highest to lowest salary.

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