Exam 70-761 topic 1 question 43 discussion

the correct is A. "If a role has no active users. You must display a zero as the active users count" WITH COUNT(*) RETURN ALWAYS 1

explanation: COUNT(*) – Returns the total number of records in a table (Including NULL valued records). COUNT(Column Name) – Returns the total number of Non-NULL records

"If a role has no active users. You must display a zero as the active users count." You can only achieve this if you do a count on userid, so the answer has to be A.

Correct answer is A although you can't GROUP BY roleID since it isn't in the select clause

Correct Ans is A we have to count the no. of active users so, COUNT(UserID) will also work.

tested. the answer is "A"

B: inner join omits roles with no active users C: group by has r.roleid...that is wrong D: cross join has no "ON" clause thus A is best answer

the answer is A

CREATE TABLE tblRole ( RoleID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, RoleName varchar(20) NOT NULL ) CREATE TABLE tblUsers ( UserID int NOT NULL IDENTITY(1000,1) PRIMARY KEY CLUSTERED, UserName varchar(20) UNIQUE NOT NULL, RoleID int NULL FOREIGN KEY REFERENCES tblRole(RoleID), IsActive bit NOT NULL DEFAULT(1) ) INSERT INTO tblRole VALUES ('Supervisory'), ('Management'), ('HR'), ('Accounting') INSERT INTO tblUsers VALUES ('Grakie', 2, DEFAULT), ('Jen', 2, 0), ('Bar', 2, DEFAULT), ('Jude', 3, DEFAULT), ('Paoula', 4, 0 ), ('Gooses', 3, DEFAULT), ('Harri', 3, DEFAULT) SELECT R.RoleName, ISNULL (U.ActiveUserCount,0) AS ActiveUserCount FROM tblRole R LEFT JOIN (SELECT RoleId, COUNT(*) AS ActiveUserCount FROM tblUsers WHERE IsActive = 1 GROUP BY RoleId) U ON r.RoleID = U.RoleID

How does C return a 0 when a role has no active user?

because the record in table Roles is always present

Any ideas for an explanatio why you cannot use Count(UserID)?