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

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

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

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

Need a hint?

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