Correlated subquery execution model in SQL - Mini Project: Build & Apply

Understanding Correlated Subquery Execution Model

📖 Scenario: You are working with a small company database that stores information about employees and their departments. You want to find employees who earn more than the average salary of their own department.

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

📋 What You'll Learn

Create a table called employees with columns id, name, department_id, and salary.

Insert the exact employee data provided.

Write a correlated subquery that calculates the average salary per department.

Select employees earning more than their department's average salary.

💡 Why This Matters

🌍 Real World

Correlated subqueries are useful when you need to compare each row to a related set of rows, such as comparing an employee's salary to their department's average salary.

💼 Career

Understanding correlated subqueries helps in writing efficient and meaningful SQL queries for data analysis, reporting, and decision-making in many business roles.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), department_id (integer), and salary (integer). Then insert these exact rows: (1, 'Alice', 10, 70000), (2, 'Bob', 20, 48000), (3, 'Charlie', 10, 60000), (4, 'Diana', 20, 52000), (5, 'Eve', 10, 75000).

SQL

-- Create the employees table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a variable for department average salary

Write a correlated subquery inside the SELECT statement that calculates the average salary for the department of each employee. Use the alias avg_dept_salary for this value.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department_id INTEGER,
  salary INTEGER
);

INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Alice', 10, 70000),
(2, 'Bob', 20, 48000),
(3, 'Charlie', 10, 60000),
(4, 'Diana', 20, 52000),
(5, 'Eve', 10, 75000);

-- Add a correlated subquery to calculate avg_dept_salary
-- Your code here

Hint

Use a correlated subquery with a WHERE clause comparing e2.department_id to e1.department_id.

3

Filter employees earning more than their department average

Add a WHERE clause to the query to select only employees whose salary is greater than the correlated subquery average salary of their department.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department_id INTEGER,
  salary INTEGER
);

INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Alice', 10, 70000),
(2, 'Bob', 20, 48000),
(3, 'Charlie', 10, 60000),
(4, 'Diana', 20, 52000),
(5, 'Eve', 10, 75000);

SELECT
  id,
  name,
  department_id,
  salary,
  (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS avg_dept_salary
FROM employees e1
-- Add WHERE clause to filter employees earning more than avg_dept_salary
-- Your code here

Hint

Use the same correlated subquery in the WHERE clause to compare salary.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by department_id ascending and then by salary descending.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department_id INTEGER,
  salary INTEGER
);

INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Alice', 10, 70000),
(2, 'Bob', 20, 48000),
(3, 'Charlie', 10, 60000),
(4, 'Diana', 20, 52000),
(5, 'Eve', 10, 75000);

SELECT
  id,
  name,
  department_id,
  salary,
  (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS avg_dept_salary
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id)
-- Add ORDER BY department_id ASC, salary DESC
-- Your code here

Hint

Use ORDER BY department_id ASC, salary DESC at the end of the query.

Practice

1. What is a correlated subquery in SQL?

easy

A. A subquery that runs independently of the outer query

B. A subquery that uses values from the outer query to filter results

C. A query that joins two tables without conditions

D. A query that only returns aggregate values

Solution

1. Step 1: Understand subquery types

   A correlated subquery depends on the outer query's current row to run.

2. Step 2: Identify correlation

   It uses columns from the outer query inside the subquery's WHERE clause.

3. Final Answer:

   A subquery that uses values from the outer query to filter results -> Option B

4. Quick Check:

   Correlated subquery = uses outer query values [OK]

Hint: Correlated subqueries reference outer query columns [OK]

Common Mistakes:

• Thinking subquery runs once independently
• Confusing with JOIN operations
• Assuming it returns only aggregates

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

easy

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

B. SELECT e.name FROM employees e WHERE salary > 50000

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

D. SELECT e.name FROM employees e JOIN departments d ON e.department = d.id

Solution

1. Step 1: Identify correlation in subquery

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) uses 'e.department' inside the subquery, linking it to the outer query.

2. Step 2: Check other options

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees) has no reference to outer query in subquery; the option with 'salary > 50000' lacks a subquery and the JOIN option is not a subquery.

3. Final Answer:

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

4. Quick Check:

   Correlation needs outer query column inside subquery [OK]

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

Common Mistakes:

• Missing outer query reference inside subquery
• Confusing JOIN with subquery
• Using subquery without correlation

3. Given the tables employees(id, name, department, salary) and the query:

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

What does this query return?

medium

A. Employees whose salary is above the average salary of their own department

B. Employees whose salary is above the average salary of all employees

C. Employees with salary above 50000

D. All employees regardless of salary

Solution

1. Step 1: Understand the subquery correlation

   The subquery calculates average salary for the department of the current employee (e1.department).

2. Step 2: Compare salaries

   The outer query selects employees whose salary is greater than that department average.

3. Final Answer:

   Employees whose salary is above the average salary of their own department -> Option A

4. Quick Check:

   Salary > department average = Employees whose salary is above the average salary of their own department [OK]

Hint: Check which outer column is used inside subquery condition [OK]

Common Mistakes:

• Assuming average is for all employees
• Ignoring the correlation condition
• Confusing with simple WHERE salary > value

4. Identify the error in the following correlated subquery:

SELECT c.customer_id FROM customers c WHERE c.orders_count > (SELECT AVG(o.orders_count) FROM orders o WHERE o.customer_id = c.customer_id);

medium

A. The subquery will return multiple rows causing an error

B. The subquery uses a wrong table alias 'o' which is not defined

C. The subquery compares orders_count incorrectly; should use SUM instead of AVG

D. The subquery references the outer query correctly; no error

Solution

1. Step 1: Analyze correlation

   The subquery correctly uses 'c.customer_id' from the outer query in its WHERE clause.

2. Step 2: Check aggregation and output

   AVG(o.orders_count) is an aggregate that returns a single scalar value, even for customers with multiple orders.

3. Final Answer:

   The subquery references the outer query correctly; no error -> Option D

4. Quick Check:

   Subquery must return single value for comparison [OK]

Hint: Ensure subquery returns one value for comparison [OK]

Common Mistakes:

• Thinking AVG returns multiple rows without GROUP BY
• Assuming alias 'o' is undefined
• Believing SUM is needed instead of AVG

5. You want to find all products whose price is higher than the average price of products in the same category. Which query correctly uses a correlated subquery to achieve this?

hard

A. SELECT p.product_name FROM products p JOIN categories c ON p.category = c.id WHERE p.price > c.avg_price

B. SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products)

C. SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category)

D. SELECT product_name FROM products WHERE price > ALL (SELECT price FROM products)

Solution

1. Step 1: Identify correlation condition

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category) uses 'p.category' inside the subquery to calculate average price per category, correlating outer and inner queries.

2. Step 2: Verify other options

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products) compares to overall average, not per category; the option using JOIN with categories uses a JOIN but no subquery; the option using > ALL compares to all individual prices, not average.

3. Final Answer:

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category) -> Option C

4. Quick Check:

   Correlated subquery filters by category [OK]

Hint: Use outer query column inside subquery WHERE for correlation [OK]

Common Mistakes:

• Using overall average instead of per category
• Confusing JOIN with subquery
• Using ALL instead of AVG in subquery