Hierarchical data is common in real-world scenarios, such as organizational charts, category trees, or file systems. In SQL, you can represent these hierarchies using self-referencing tables—tables that include a foreign key pointing back to the same table. This approach allows you to model complex relationships, like an employee reporting to another employee, directly within your database structure.

-- Create an 'employees' table with a self-referencing 'manager_id' column
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    department_id INTEGER REFERENCES departments(department_id),
    manager_id INTEGER REFERENCES employees(employee_id),
    employee_type VARCHAR(20) NOT NULL CHECK (employee_type IN ('FullTime', 'PartTime'))
);

The manager_id column in the employees table is a foreign key that references the employee_id of the same table. This setup enables you to build tree-like structures, where each employee can have a manager (who is also an employee), and managers can themselves have managers, forming a hierarchy. If an employee has no manager (such as a department head), the manager_id is set to NULL, indicating the top of the hierarchy.

-- Retrieve all direct subordinates of a given manager (e.g., Alice, employee_id = 1)
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.manager_id,
    m.first_name AS manager_first_name,
    m.last_name AS manager_last_name
FROM
    employees e
    LEFT JOIN employees m ON e.manager_id = m.employee_id
WHERE
    e.manager_id = 1;

By joining the table to itself, you can easily query hierarchical relationships, such as finding all employees who report directly to a specific manager. This pattern is powerful for representing and querying organizational structures within a relational database.