Tricky Interview questions: Employee and manager hierarchy within an emp table

 To analyze the employee (emp) and manager (mgr) hierarchy within an emp table, you typically want to understand the reporting structure and how employees are organized under their managers. Here are some SQL queries and techniques to explore the hierarchical relationships:

Frequently asked Tricky interview questions employee and manager column in emp table, along with examples of SQL queries for each:

Click the link to find the Emp and dept table records

Display the hierarchy of employees and their managers.

This query shows each employee and their direct manager:

SELECT e1.ename AS employee_name, e2.ename AS manager_name
FROM emp e1
LEFT JOIN emp e2 ON e1.mgr = e2.empno;

List employees with their level in the hierarchy.

To calculate hierarchy levels, use a recursive common table expression (CTE):

WITH Hierarchy AS (
    SELECT empno, ename, mgr, 1 AS level
    FROM emp
    WHERE mgr IS NULL
    
    UNION ALL
    
    SELECT e.empno, e.ename, e.mgr, h.level + 1
    FROM emp e
    INNER JOIN Hierarchy h ON e.mgr = h.empno
)
SELECT empno, ename, mgr, level
FROM Hierarchy;

Find the top-level managers (those without managers).

SELECT ename
FROM emp
WHERE mgr IS NULL;

Find the chain of command for a specific employee.

To see the full chain of command for an employee (e.g., employee with empno 7566), use a recursive CTE:

WITH ChainOfCommand AS (
    SELECT empno, ename, mgr
    FROM emp
    WHERE empno = 7566
    
    UNION ALL
    
    SELECT e.empno, e.ename, e.mgr
    FROM emp e
    INNER JOIN ChainOfCommand c ON e.empno = c.mgr
)
SELECT empno, ename, mgr
FROM ChainOfCommand;

Find employees who have no subordinates.

SELECT e1.ename
FROM emp e1
LEFT JOIN emp e2 ON e1.empno = e2.mgr
WHERE e2.empno IS NULL;

Determine the number of direct and indirect reports for each manager.

This query counts all direct and indirect reports for each manager:

WITH EmployeeHierarchy AS (
    SELECT empno, mgr
    FROM emp
    
    UNION ALL
    
    SELECT e.empno, eh.mgr
    FROM emp e
    JOIN EmployeeHierarchy eh ON e.mgr = eh.empno
)
SELECT mgr, COUNT(DISTINCT empno) AS total_reports
FROM EmployeeHierarchy
GROUP BY mgr;

Find the longest chain of command in the organization.

To determine the maximum depth of the reporting structure:

WITH Hierarchy AS (
    SELECT empno, mgr, 1 AS depth
    FROM emp
    WHERE mgr IS NULL
    
    UNION ALL
    
    SELECT e.empno, e.mgr, h.depth + 1
    FROM emp e
    INNER JOIN Hierarchy h ON e.mgr = h.empno
)
SELECT MAX(depth) AS longest_chain
FROM Hierarchy;

Identify managers who have more than one level of subordinates.

WITH Hierarchy AS (
    SELECT empno, mgr, 1 AS level
    FROM emp
    WHERE mgr IS NULL
    
    UNION ALL
    
    SELECT e.empno, e.mgr, h.level + 1
    FROM emp e
    INNER JOIN Hierarchy h ON e.mgr = h.empno
)
SELECT mgr
FROM Hierarchy
GROUP BY mgr
HAVING MAX(level) > 1;

List all employees with their total number of direct reports (excluding indirect reports).

SELECT e1.ename, COUNT(e2.empno) AS direct_reports
FROM emp e1
LEFT JOIN emp e2 ON e1.empno = e2.mgr
GROUP BY e1.ename;

Recursive CTEs: These are essential for traversing hierarchical data where each record points to a parent or manager.
Handling Cyclic Hierarchies: Ensure your data does not have cyclic relationships, or use specific database features to handle them.

Click below links for more examples on Emp and dept table.
 Interview questions on emp and dept table (part 1)
Interview questions on emp and dept table (part 2)
Interview questions on emp and dept table (part 3)
Interview questions on emp and dept table (part 4)
Tricky interview questions employee and manager column in emp table
Tricky interview questions employee and manager column in emp table