Recursive CTE Example With Sample Data For Employee Hierarchy
Recursive CTE used to retrieve employee hierarchy, starting from top-level manager Alice. Sample table created with employees data, then recursive query written to display hierarchy levels.
Let’s create a complete example using a recursive CTE with sample data, including table creation, data insertion, and the recursive query itself.
Step 1: Create the Sample Table
First, we'll create a simple employees table to hold our employee data.
CREATE TABLE employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES employees(EmployeeID)
);
Step 2: Insert Sample Data
Now, we will insert some sample data into the employees table.
INSERT INTO employees (EmployeeID, Name, ManagerID) VALUES
(1, 'Alice', NULL), -- Alice is the t...