You have a table named Employee. You document your company’s organizational hierarchy by inserting the EmployeeID of each employee’s manager in the ReportsTo 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. (Line numbers are included for reference only.)
01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
02 AS (
3
04 )
05 SELECT EmployeeID, FullName, ManagerName, Level
06 FROM EmployeeList;
Which code segment should you insert at line 3?
A. SELECT EmployeeID,FullName,” AS [Reports To],1 AS [Level]
FROM Employee
UNION ALL
SELECT emp.EmployeeID,emp.FullName,mgr.FullName,1 + 1 AS [Level]
FROM Employee emp
LEFT JOIN Employee mgrON emp.ReportsTo = mgr.EmployeeID
B. SELECT EmployeeID,FullName,” AS [ReportsTo],1 AS [Level]
FROM Employee
WHERE ReportsTo IS NULL
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName,mgr.Level + 1
FROM EmployeeList mgr
JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeId
C. SELECT EmployeeID,FullName,” AS [ReportsTo],1 AS [Level]
FROM Employee
UNION ALL
SELECT emp.EmployeeID,emp.FullName,mgr.FullName,mgr.Level + 1
FROM EmployeeList mgr
JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeID
D. SELECT EmployeeID,FullName,” AS [ReportsTo],1 AS [Level]
FROM Employee
WHERE ReportsTo IS NULL
UNION ALL
SELECT emp.EmployeeID,emp.FullNName,mgr.FullName,1 + 1 AS [Level]
FROM Employee emp
JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID
