SQL SERVER – Quiz and Video – Introduction to Hierarchical Query using a Recursive CTE

Above six are the most important concepts related to CTE and SQL Server.  There are many more things one has to learn, but without beginners fundamentals one can’t learn the advanced concepts. Let us have small quiz and check how many of you get the fundamentals right. Let us explore that how to build Hierarchical Query using a Recursive CTE

Quiz – Hierarchical Query

1) You have an employee table with the following data.

You need to write a recursive CTE that shows the EmpID, FirstName, LastName, MgrID, and employee level. The CEO should be listed at Level 1. All people who work for the CEO will be listed at Level 2. All of the people who work for those people will be listed at Level 3. Which CTE code will achieve this result?

1. WITH EmpList AS
   (SELECT Boss.EmpID, Boss.FName, Boss.LName, Boss.MgrID,
   1 AS Lvl
   FROM Employee AS Boss WHERE Boss.MgrID IS NULL
   UNION ALL
   SELECT E.EmpID, E.FirstName, E.LastName, E.MgrID, EmpList.Lvl + 1
   FROM Employee AS E INNER JOIN EmpList
   ON E.MgrID = EmpList.EmpID)
   SELECT * FROM EmpList
2. WITH EmpListAS
   (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
   FROM Employee WHERE MgrID IS NULL
   UNION ALL
   SELECT EmpID, FirstName, LastName, MgrID, 2 as Lvl )
   SELECT * FROM BossList
3. WITH EmpList AS
   (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
   FROM Employee WHERE MgrID is NOT NULL
   UNION
   SELECT EmpID, FirstName, LastName, MgrID, BossList.Lvl + 1
   FROM Employee INNER JOIN EmpList BossList
   ON Employee.MgrID = BossList.EmpID)
   SELECT * FROM EmpList

2) You have a table named Employee. The EmployeeID of each employee’s manager is in the ManagerID column. You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee’s level in the hierarchy. You write the following code segment:

WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
AS (
–PICK ANSWER CODE HERE
)

1. SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level] FROM Employee
   WHERE ManagerID IS NULL
   UNION ALL
   SELECT emp.EmployeeID, emp.FullName mgr.FullName, 1 + 1 AS [Level] FROM Employee emp JOIN Employee mgr
   ON emp.ManagerID = mgr.EmployeeId
2. SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level] FROM Employee
   WHERE ManagerID IS NULL
   UNION ALL
   SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
   FROM EmployeeList mgr JOIN Employee emp
   ON emp.ManagerID = mgr.EmployeeId

Reference: Pinal Dave (https://blog.sqlauthority.com)