Self join for hierarchical data in SQL - Mini Project: Build & Apply

Self Join for Hierarchical Data

📖 Scenario: You work in a company database where employees have managers. Each employee record has an ID and a manager ID that points to another employee. You want to find out who manages whom by linking employees to their managers.

🎯 Goal: Build a SQL 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 the exact employee data given.

Write a self join query to link employees to their managers.

Select employee names and their manager names in the output.

💡 Why This Matters

🌍 Real World

Many companies store employee-manager relationships in one table. Self joins help find reporting lines.

💼 Career

Understanding self joins is important for database analysts and developers working with hierarchical data.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), and manager_id (integer). Then insert these exact rows: (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2).

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 an alias for the employees table

Write a SQL query that selects from the employees table twice using aliases e for employees and m for managers. Start the query with SELECT e.name, m.name and FROM employees e.

SQL

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, 'David', 2);

-- Start the SELECT query with aliases e and m
SELECT e.name, m.name
FROM employees e
-- Your code here

Need a hint?

Use table aliases to refer to the same table twice.

3

Add the self join condition

Complete the SQL query by adding a LEFT JOIN on employees m where e.manager_id = m.id. This links each employee to their manager.

SQL

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, 'David', 2);

SELECT e.name, m.name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
-- Your code here

Need a hint?

Use LEFT JOIN to include employees without managers.

4

Rename columns for clarity

Modify the SELECT clause to rename e.name as employee_name and m.name as manager_name using AS.

SQL

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, 'David', 2);

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
-- Your code here

Need a hint?

Use AS to rename columns in the output.