Cycle 03

Lab Cycle 3 — Set Operations, Advanced/Recursive CTEs & TCL

4 Questions
Question 03 of 04

Hierarchical & Recursive Queries (WITH RECURSIVE)

Problem Statement

Write SQL recursive CTE queries on an Employee table (emp_id, emp_name, manager_id, salary) to: 1. Retrieve employee details using hierarchical recursion, displaying level depth, root manager, and hierarchy path string. 2. Indicate if an employee node is a leaf node (has no subordinates). 3. Display results in Breadth-First and Depth-First traversal orders. 4. Show equivalent of LEVEL, CONNECT_BY_ROOT, and CONNECT_BY_PATH.

C++ 
-- Recursive CTE for Hierarchical Traversal
WITH RECURSIVE EmpHierarchy AS (
    -- Anchor: Top-level executives (manager_id IS NULL)
    SELECT 
        emp_id,
        emp_name,
        manager_id,
        emp_id AS root_id,
        emp_name AS root_name,
        1 AS level,
        CAST(emp_name AS VARCHAR(1000)) AS path,
        ARRAY[emp_id] AS path_array
    FROM Employee
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Step: Subordinates
    SELECT 
        e.emp_id,
        e.emp_name,
        e.manager_id,
        h.root_id,
        h.root_name,
        h.level + 1 AS level,
        CAST(h.path || ' -> ' || e.emp_name AS VARCHAR(1000)) AS path,
        h.path_array || e.emp_id AS path_array
    FROM Employee e
    JOIN EmpHierarchy h ON e.manager_id = h.emp_id
)
SELECT 
    h.emp_id,
    h.emp_name,
    h.manager_id,
    h.level,
    h.root_id,
    h.root_name AS connect_by_root,
    h.path AS connect_by_path,
    CASE 
        WHEN EXISTS (SELECT 1 FROM Employee sub WHERE sub.manager_id = h.emp_id) THEN 'NO'
        ELSE 'YES'
    END AS is_leaf
FROM EmpHierarchy h
-- Depth-First Order (sorted by path string):
ORDER BY h.path;

-- Breadth-First Order:
-- ORDER BY h.level, h.emp_name;
Question 3 of 4