1. Learn
    2. /
  2. Courses
    3. /
  3. Hierarchical and Recursive Queries in SQL Server

Exercise

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.

Instructions

100 XP
  • 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.