Self join patterns in PostgreSQL - Mini Project: Build & Apply

Self Join Patterns in PostgreSQL

📖 Scenario: You work in a company database where employees have managers who are also employees. You want to find pairs of employees and their managers using a self join.

🎯 Goal: Build a PostgreSQL query using a self join to list each employee with their manager's name.

📋 What You'll Learn

Create a table called employees with columns id, name, and manager_id

Insert exact employee data with specified IDs and manager relationships

Write a self join query joining employees to itself using manager_id

Select employee names and their manager names in the result

💡 Why This Matters

🌍 Real World

Companies often store employee-manager relationships in the same table. Self joins help retrieve hierarchical data like reporting lines.

💼 Career

Understanding self joins is essential for database roles involving organizational data, reporting structures, and recursive relationships.

1

Create the employees table

Write a SQL statement to create a table called employees with columns id as integer primary key, name as text, and manager_id as integer that can be null.

PostgreSQL

-- Create the employees table with id, name, and manager_id columns
-- Your code here

Need a hint?

Use CREATE TABLE with three columns: id, name, and manager_id. The id should be the primary key.

2

Insert employee data

Insert these exact rows into employees: (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'Diana', 2).

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER
);
-- Insert the employee data below
-- Your code here

Need a hint?

Use a single INSERT INTO employees statement with multiple rows.

3

Write the self join query

Write a SQL query that uses a self join on employees aliased as e and m where e.manager_id = m.id. Select e.name AS employee and m.name AS manager.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'Diana', 2);

-- Write the self join query below
-- Your code here

Need a hint?

Use JOIN employees m ON e.manager_id = m.id to join employees to their managers.

4

Complete the query with ordering

Add an ORDER BY e.name clause at the end of the query to sort results by employee name.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'Diana', 2);

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY e.name to sort the output by employee name alphabetically.