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

SQL SERVER - Quiz and Video - Introduction to Hierarchical Query using a Recursive CTE organization-structure-500x232

Quiz – Hierarchical Query

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

EmpIDFirstNameLastNameMgrID
1DavidKennson11
2EricBender11
3LisaKendall4
4DavidLonning11
5JohnMarshbank4
6JamesNewton3
7SallySmithNULL

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

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read the earlier article over here. If you want to change the answer you still have a chance.

Solution

1) 1

2) 2

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?

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

SQL in Sixty Seconds, SQL Scripts, SQL Server
Previous Post
SQL SERVER – A Tricky Question and Even Trickier Answer – Index Intersection – Partition Function
Next Post
SQL SERVER – Saturday Fun Puzzle with SQL Server DATETIME2 and CAST

Related Posts

5 Comments. Leave new

  • love it… very informative.

    Thanks

    Reply
  • Hi pinal,

    I have tried the above example but its not working for me. i tried the first question. but its showing only one record. Here is my defination.

    create table #Employee
    (
    EmpID int identity(1,1),
    FirstName varchar(500),
    MgrID int
    )
    insert into #Employee
    (
    FirstName,
    MgrID
    )
    values
    (
    ‘David’,
    11
    ),(‘Eric’,11),(‘Lisa’,4),(‘David’,11),(‘John’,4),(‘James’,3),(‘Sally’,null)

    select * from #Employee

    ———–Emp with recurssion
    WITH EmpList AS
    (
    SELECT
    Boss.EmpID,
    Boss.FirstName,
    Boss.MgrID,
    1 AS Lvl
    FROM
    #Employee AS Boss
    WHERE
    MgrID IS NULL
    UNION ALL
    SELECT
    E.EmpID,
    E.FirstName,
    E.MgrID,
    EmpList.Lvl + 1
    FROM
    #Employee AS E
    INNER JOIN EmpList ON
    E.MgrID = EmpList.EmpID
    )
    SELECT distinct * FROM EmpList order by MgrID asc

    Thanks,

    Reply
  • Question…why would you join Manager ID to employee ID and not employee id to employee id?

    Reply
  • alll broken. None of its seems to work.

    Reply

Leave a Reply