Navigating the Organizational Chart

Hierarchical data is common in many real-world systems, such as organizational structures, file systems, or product categories. Effectively querying this type of data often requires joining a table to itself to traverse the relationships. This challenge will test your ability to use a self-join to retrieve hierarchical information.

Problem Description

You are given a table representing an organizational hierarchy, where each employee reports to a manager. Your task is to write a SQL query that, for a given employee, returns their direct manager's name. You should assume that the employee table has a structure where each employee record contains their own employee_id, their name, and the manager_id of the person they report to. The manager_id column will be NULL for the top-level executive (e.g., the CEO).

Key Requirements:

• Identify the manager of a specific employee.
• Retrieve the manager's name.
• Handle the case where an employee has no manager (the top of the hierarchy).

Expected Behavior:

Your query should accept an employee_id as input and return a single row containing the name of that employee's direct manager. If the employee has no manager, the result should indicate this (e.g., return NULL or an empty result set, depending on how the query is structured).

Edge Cases:

• The top-level executive who has a NULL manager_id.
• An employee_id that does not exist in the table.

Examples

Example 1:

Input Table: Employees

Query Input: employee_id = 3

Output:

Explanation: Charlie (employee_id 3) reports to Bob (manager_id 2). The query joins the Employees table to itself, matching Charlie's manager_id (2) with another employee's employee_id (Bob's employee_id).

Example 2:

Input Table: Employees (same as Example 1)

Query Input: employee_id = 1

Output:

Explanation: Alice (employee_id 1) is the top executive and has a NULL manager_id. The self-join will not find a match for NULL, correctly indicating no manager.

Example 3:

Input Table: Employees (same as Example 1)

Query Input: employee_id = 10 (an employee not in the table)

Output:

Explanation: If the provided employee_id does not exist, no matching records will be found, and the query should return an empty result set or NULL for the manager name.

Constraints

• The employee_id column is a unique identifier for each employee.
• The manager_id column refers to an employee_id within the same table.
• The name column stores the employee's name as a string.
• The hierarchy depth is not explicitly limited, but queries should be reasonably efficient.

Notes

This challenge is designed to be solved using a SQL self-join. Consider how you will link the employee record to their manager's record. You'll need to alias the table to differentiate between the "employee" perspective and the "manager" perspective in your join condition. Think about how to handle the case of an employee at the top of the hierarchy.