Exam 70-761 topic 1 question 45 discussion

Actual exam question from Microsoft's 70-761

Question #: 45
Topic #: 1

DRAG DROP -
You create three tables by running the following Transact-SQL statements:

For reporting purposes, you need to find the active user count for each role, and the total active user count. The result must be ordered by active user count of each role. You must use common table expressions (CTEs).
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer

Suggested Answer:

by safiullah at Aug. 13, 2019, 7:21 a.m.

Comments

Submit Cancel

safiullah

Highly Voted 5 years, 10 months ago

"With ActiveUsers" segment should be the first one followed by "RoleNCount" segment, because "WITH" keyword is used to start the CTE definition which is then followed by multiple CTE definitions with same "WITH" keyword separtaed by "Commas". Also the "RoleSummary" segment should be the one wihout "ORDER BY" clause because CTEs are not allowed to have "ORDER BY" clause without "TOP" or "FETCH" caluses inside.

upvoted 31 times

MML

5 years, 1 month ago

Plus the forth segment whish is the CTE that starts with Total and ends with order by ..

upvoted 3 times

...

BabyBee

Highly Voted 4 years, 7 months ago

Answer: ;WITH ActiveUsers AS ( SELECT userID FROM tblUsers WHERE IsActive = 1 ), RoleNCount AS ( SELECT RoleId, COUNT (*) AS ActiveUserCount FROM tblUsersInRoles BRG INNER JOIN ActiveUsers U ON BRG.UserID = U.UserID GROUP BY BRG.RoleID ), RoleSummary AS ( SELECT R.RoleName, ISNULL(S.ActiveUserCount,0) AS ActiveUserCount FROM tblRole R LEFT JOIN RoleNCount S ON R.RoleID = S.RoleID ), Total AS ( SELECT COUNT (*) AS TotalCountInAllRoles FROM ActiveUsers ) SELECT S.*, Total.TotalCountInAllRoles FROM RoleSummary AS S, Total ORDER BY S.ActiveUserCount;

upvoted 10 times

...