Exam 70-761 topic 1 question 165 discussion

Actual exam question from Microsoft's 70-761

Question #: 165
Topic #: 1

DRAG DROP -
You have a database named DB1 that contains a table named HR.Employees. HR.Employees contains two columns named ManagerID and EmployeeID.
ManagerID refers to EmployeeID.
You need to create a query that returns a list of all employees, the manager of each employee, and the numerical level of each employee in your organization's hierarchy.
Which five statements should you add to the query in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer

Suggested Answer:

References:
https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

by Dieter at Aug. 19, 2019, 5:03 p.m.

Comments

Submit Cancel

Dieter

Highly Voted 5 years, 10 months ago

wrong order. should start with WITH clause, then Select ...Manager IS NULL (anchor query), UNION ALL then the other select for the with clause, finally the select * from managers

upvoted 23 times

...

getstoopid

Highly Voted 4 years, 10 months ago

tested and working code IF OBJECT_ID (N'tempdb..#Employees', N'U') IS NOT NULL DROP TABLE #Employees; CREATE TABLE #Employees ( [EmployeeID] int, [Name] nvarchar(50), [ManagerID] int null ); insert into #Employees values (1, 'TheBoss', null) ,(2, 'Manager1', 1) ,(3, 'Manager2', 1) ,(4, 'Dev1', 2) ,(5, 'TeamLead', 2) ,(6, 'Dev2', 5); ;WITH Managers AS ( SELECT [EmployeeID], [Name], 0 AS [EmployeeLevel], [ManagerId] FROM #Employees WHERE [ManagerID] is null UNION ALL SELECT e.[EmployeeID], e.[Name], m.[EmployeeLevel] +1, e.[ManagerId] From #Employees e inner join Managers m on m.[EmployeeID] = e.[ManagerId] ) SELECT * FROM Managers ORDER BY ManagerID;

upvoted 7 times

...

Postarion

Most Recent 5 years, 4 months ago

WITH Managers AS ( SELECT ManagerId, EmployeeId, 0 AS EmployeeLevel FROM Employees WHERE MAnagerId IS NULL UNION SELECT Employees.ManagerId, Employees.EmployeeId, EmployeeLevel +1 From Employees JOIN Managers ON Employees.EmployeeId = Managers.ManagerId ) SELECT * FROM Managers ORDER BY ManagerID

upvoted 6 times