Who is your manager?
In this exercise, we are going to use the dataset of an IT-organization which is provided in the table employee
. The table has the fields ID
(ID number of the employee), Name
(the employee's name), and Supervisor
(ID number of the supervisor).
The IT-organization consists of different roles and levels.
The organization has one IT director (ID=1
, Heinz Griesser, Supervisor=0
) with many subordinate employees. Under the IT director you can find the IT architecture manager (ID=10
, Andreas Sternig, Supervisor=1
) with three subordinate employees. For Andreas Sternig Supervisor=1
which is the ID
of the IT-Director.
First, we want to answer the question: Who are the supervisors for each employee?
We are going to solve this problem by recursively querying the dataset.
This exercise is part of the course
Hierarchical and Recursive Queries in SQL Server
Exercise instructions
- Create a CTE with the name
employee_hierarchy
. - Select the information of the IT director as the initial step of the CTE by filtering on his
Supervisor
ID. - Perform a join with
employee
to get the name of the manager.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create the CTE employee_hierarchy
WITH ___ AS (
SELECT
ID,
NAME,
Supervisor
FROM employee
-- Start with the IT Director
WHERE ___ = ___
UNION ALL
SELECT
emp.ID,
emp.NAME,
emp.Supervisor
FROM employee emp
JOIN employee_hierarchy
ON emp.Supervisor = employee_hierarchy.ID)
SELECT
cte.Name as EmployeeName,
emp.Name as ManagerName
FROM employee_hierarchy as cte
JOIN employee as emp
-- Perform the JOIN on Supervisor and ID
ON cte.___ = emp.___;