Recursive CTE Employee Hierarchy Query – Sql Null 1

Task: Write a complete, production‑ready SQL script that uses a recursive Common Table Expression (CTE) to retrieve an employee hierarchy from a single “Employees” table. The script must be ready to run on a standard ANSI‑SQL compliant RDBMS (e.g., PostgreSQL, SQL Server, MySQL 8.0+, Oracle 11g+).

Requirements:

1. Table definition – Assume the following table exists (include the CREATE TABLE statement in the script):

   CREATE TABLE Employees (
       EmployeeID   INT PRIMARY KEY,
       EmployeeName VARCHAR(100) NOT NULL,
       ManagerID    INT NULL,               -- references Employees.EmployeeID; NULL for top‑level executives
       Title        VARCHAR(50) NOT NULL,
       Salary       DECIMAL(12,2) NOT NULL,
       HireDate     DATE NOT NULL,
       CONSTRAINT fk_manager FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
   );
   

2. Recursive CTE – The CTE must:

   • Accept a starting employee (parameter @RootEmployeeID for SQL Server, :root_id for PostgreSQL/MySQL) and return the entire subtree under that employee, including the root.
   • Produce the following columns for each row:
     • EmployeeID
     • EmployeeName
     • Title
     • ManagerID
     • Level – 0 for the root, 1 for direct reports, 2 for their reports, etc.
     • Path – a string showing the hierarchy path from the root to the employee, e.g., CEO > VP Sales > Regional Manager.
     • TotalSubordinates – the number of direct + indirect reports under the employee (computed in a separate aggregation step).
   • Include cycle detection: if a circular reference exists, the query should stop recursion for that branch and add a column IsCycle (1 if a cycle was detected, otherwise 0).

3. Optional parameters – Add support for:

   • @MaxDepth (or :max_depth) to limit recursion depth; if NULL, recurse to unlimited depth.
   • @IncludeSalary (boolean) – when true, also return Salary and HireDate columns; when false, omit them.

4. Result ordering – Order the final result set by Path (lexicographically) so the hierarchy appears in a readable top‑down order.

5. Testing data – Include an INSERT block that populates the table with at least 10 rows covering:

   • A top‑level CEO (no manager)
   • Two VPs reporting to the CEO
   • Several managers and individual contributors under each VP
   • One intentional cycle (e.g., Employee 9’s ManagerID points to Employee 10, and Employee 10’s ManagerID points back to Employee 9) to demonstrate the cycle detection.

6. Demonstration queries – Provide two example executions: a) Retrieve the full hierarchy under the CEO with unlimited depth, including salaries. b) Retrieve only the first two levels under VP Sales, excluding salaries, and limiting depth to 2.

7. Output format – The assistant’s response must contain:

   • A single markdown code block with the full SQL script (CREATE TABLE, INSERT data, CTE definition, and demo queries).
   • A brief plain‑text explanation (≤ 150 words) describing how the recursive CTE works, how cycle detection is implemented, and how the optional parameters are used.

Expected Output Example (structure only, not actual data):

/* 1️⃣ Create table */
CREATE TABLE Employees ( ... );

/* 2️⃣ Insert sample data */
INSERT INTO Employees (...) VALUES
    (1, 'Alice Johnson', NULL, 'CEO', 250000, '2005-03-15'),
    (2, 'Bob Smith', 1, 'VP Sales', 180000, '2008-07-01'),
    ... ;

/* 3️⃣ Recursive CTE with parameters */
WITH RECURSIVE Hierarchy AS (
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.Title,
        e.ManagerID,
        0 AS Level,
        CAST(e.EmployeeName AS VARCHAR(500)) AS Path,
        CAST(0 AS BIT) AS IsCycle,
        CAST(e.Salary AS DECIMAL(12,2)) AS Salary,
        e.HireDate
    FROM Employees e
    WHERE e.EmployeeID = @RootEmployeeID   -- or :root_id

    UNION ALL

    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.Title,
        e.ManagerID,
        h.Level + 1,
        CONCAT(h.Path, ' > ', e.EmployeeName),
        CASE WHEN e.EmployeeID = ANY (SELECT EmployeeID FROM Hierarchy WHERE Level = h.Level) THEN 1 ELSE 0 END,
        CASE WHEN @IncludeSalary = 1 THEN e.Salary END,
        CASE WHEN @IncludeSalary = 1 THEN e.HireDate END
    FROM Employees e
    JOIN Hierarchy h ON e.ManagerID = h.EmployeeID
    WHERE (@MaxDepth IS NULL OR h.Level + 1 <= @MaxDepth)
      AND h.IsCycle = 0
)
SELECT
    h.EmployeeID,
    h.EmployeeName,
    h.Title,
    h.ManagerID,
    h.Level,
    h.Path,
    (SELECT COUNT(*) FROM Hierarchy h2 WHERE h2.Path LIKE h.Path || '%') - 1 AS TotalSubordinates,
    h.IsCycle
    /* optional salary columns */
FROM Hierarchy h
ORDER BY h.Path;

Followed by two demo queries using the parameters described.

Explanation (plain text): [Provide ≤ 150 words description].