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 (
03
04 )
05 SELECT EmployeeID, FullName, ManagerName, Level
06 FROM EmployeeList;
Which code segment should you insert at line 3?
A.
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
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 [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 mgr
ON emp.ReportsTo = mgr.EmployeeID
D.
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