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

Need a 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

Need a 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

Need a 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

Need a hint?

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