Exam 70-761 topic 1 question 201 discussion

Actual exam question from Microsoft's 70-761

Question #: 201
Topic #: 1

You have a database named MyDb. You run the following Transact-SQL statements:

A value of 1 in the IsActive column indicates that a user is active.
You need to create a count for active users in each role. If a role has no active users, you must display a zero as the active users count.
Which Transact-SQL statement should you run?

A.

B.

C.

D.

Show Suggested Answer

Suggested Answer: A

by imran at Nov. 4, 2019, 2:28 a.m.

Comments

Submit Cancel

Patty_attack

5 years, 3 months ago

A should be using a RIGHT JOIN as we want a list of all roles

upvoted 2 times

supermario

5 years, 3 months ago

Nope. tbl roles is on the left which will list all roles.

upvoted 3 times

...

imran

5 years, 7 months ago

should include isNull function to put zero when role name gives null

upvoted 1 times

flashed

5 years, 3 months ago

don't need the count goes to zero when it is null. CREATE TABLE #ROLES( ROLEID INT IDENTITY(1,1) PRIMARY KEY, ROLENAME VARCHAR(20) ) CREATE TABLE #USERS( USERID INT IDENTITY(1,1) PRIMARY KEY, USERNAME VARCHAR(20), ROLEID INT NULL FOREIGN KEY REFERENCES #ROLES(ROLEID), ISACTIVE BIT NOT NULL DEFAULT(1) ) INSERT INTO #ROLES VALUES ('ADMIN'),('SERVER'),('MANAGER'),('USER') ; INSERT INTO #USERS VALUES ('HUGO',1,0),('NUNO',2,1),('PEDRO',3,1) ; SELECT * FROM #ROLES SELECT * FROM #USERS SELECT R.ROLENAME, COUNT(U.USERID) AS ACTIVEUSERCOUNT FROM #ROLES R LEFT JOIN (SELECT U.USERID,U.ROLEID FROM #USERS U WHERE U.ISACTIVE=1) U ON R.ROLEID = U.ROLEID GROUP BY R.ROLENAME ; OUTPUT: ROLENAME ACTIVEUSERCOUNT ADMIN 0 MANAGER 1 SERVER 1 USER 0

upvoted 12 times

...