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.

A section of the entire hierarchy

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 IDof 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.

Cet exercice fait partie du cours

Hierarchical and Recursive Queries in SQL Server

Afficher le cours

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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.___;

Modifier et exécuter le code