Self join concept in SQL - Mini Project: Build & Apply

Understanding Self Join in SQL

📖 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: Create a SQL query using a self join to list each employee's name alongside 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 provided.

Write a self join query that joins employees to itself to get employee and manager names.

Select the employee's name as employee_name and the manager's name as manager_name.

💡 Why This Matters

🌍 Real World

Companies often store employee and manager data in the same table. Self joins help find relationships within the same data set.

💼 Career

Understanding self joins is important for database analysts and developers to query hierarchical data efficiently.

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

Define aliases for the self join

Write a SQL query that selects from employees twice using aliases e for employee and m for manager. Start the query with SELECT and FROM employees e JOIN employees m.

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
-- Your code here

Need a hint?

Use table aliases e and m to represent employee and manager tables.

3

Add the join condition to match employees with their managers

Add the join condition ON e.manager_id = m.id to the query to link 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 * FROM employees e JOIN employees m
-- Add join condition here

Need a hint?

The join condition connects the employee's manager_id to the manager's id.

4

Select employee and manager names with aliases

Modify the query to select e.name AS employee_name and m.name AS manager_name to show employee and manager names clearly.

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
-- Your code here
FROM employees e JOIN employees m ON e.manager_id = m.id

Need a hint?

Use AS to rename columns for clear output.