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.
SELECT R.RoleName, COUNT(*) AS ActiveUserCount FROM tblRoles RCROSS JOIN (SELECT UserId,
RoleId FROM tblUsers WHERE IsActive = 1) UWHERE U.RoleId = R.RoleIdGROUP BY R.RoleId,
R.RoleName
B.
SELECTR.RoleName, COUNT(*) AS ActiveUserCount FROM tblRoles RLEFT JOIN (SELECT UserId,
RoleId FROM tblUsers WHERE IsActive = 1) UON U.RoleId = R.RoleIdGROUP BY R.RoleId, R.RoleName
C.
SELECT R.RoleName, U.ActiveUserCount FROM tblRoles R CROSS JOIN(SELECT RoleId, COUNT(*) AS
ActiveUserCountFROM tblUsers WHERE IsActive = 1 GROUP BY R.RoleId) U
D.
SELECT R.RoleName, ISNULL (U.ActiveUserCount,0) AS ActiveUserCountFROM tblRoles R LEFT JOIN
(SELECT RoleId, COUNT(*) AS ActiveUserCountFROM tblUsers WHERE IsActive = 1 GROUP BY
R.RoleId) U
Hey, Looks like Option B doesn’t show the Role having no active user?
3
3
Show, but ActiveUserCount = 1. COUNT(U.UserId) instead of COUNT(*) fix this.
7
1
D if added “on R.RoleId = U.RoleId”
4
1
My bad. forget this.
0
1
answer is not there it should be
Select
R.Rolename,
count(userID) as activeUsercount
from tblRoles R
Left join (select userID, RoleId from tblUsers Where Isactive = 1) u
On u.roleID = R.roleID
Group by R.roleID,R.rolename
12
2
More new questions:
QUESTION 41
You have a database that stored information about servers and application errors.
The database contains the following tables.
Servers
Errors
You need to return all error log messages and the server where the error occurs most often.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
QUESTION 42
Drag and Drop Question
You have a database that stored information about servers and application errors.
The database contains the following tables.
Servers
Errors
You are building a webpage that shows the three most common errors for each server.
You need to return the data for the webpage.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Answer
Full Version:https://drive.google.com/drive/folders/0B75b5xYLjSSNMDN6VjRLbFVKaWM?usp=sharing
0
0
Answer is D
18
0
Need add “ON R.RoleId = U.RoleId” and remove “R.” from inner select for this. But D is more like the right answer.
4
0
As stated by scotrideff non of the answers fulfill the requirement (Statement D will even throw a syntax error). The proposed statement is working properly.
See sample code below:
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblUsers’)
BEGIN
DROP TABLE dbo.tblUsers;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblRoles’)
BEGIN
DROP TABLE dbo.tblRoles;
END;
CREATE TABLE tblRoles (RoleId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, RoleName VARCHAR(20) NOT NULL);
CREATE TABLE tblUsers (UserId int NOT NULL IDENTITY(10000,1) PRIMARY KEY CLUSTERED, UserName VARCHAR(20) UNIQUE NOT NULL, RoleId INT NULL FOREIGN KEY REFERENCES tblRoles(RoleID),IsActive BIT NOT NULL DEFAULT(1));
INSERT INTO tblRoles VALUES
(‘Role1’),(‘Role2’),(‘Role3’),(‘Role4’);
INSERT INTO tblUsers VALUES
(‘User1’,1,1),(‘User2’,1,1),(‘User3’,2,1),(‘User4’,3,0);
SELECT R.RoleName, COUNT(*) AS ActiveUserCount FROM tblRoles R
CROSS JOIN (SELECT UserId, RoleId FROM tblUsers WHERE IsActive = 1) U
WHERE U.RoleId = R.RoleId
GROUP BY R.RoleId, R.RoleName;
SELECT R.RoleName, COUNT(*) AS ActiveUserCount FROM tblRoles R
LEFT JOIN (SELECT UserId, RoleId FROM tblUsers WHERE IsActive = 1) U
ON U.RoleId = R.RoleId
GROUP BY R.RoleId, R.RoleName;
SELECT R.RoleName, U.ActiveUserCount FROM tblRoles R
CROSS JOIN (SELECT RoleId, COUNT(*) AS ActiveUserCount FROM tblUsers WHERE IsActive = 1 GROUP BY RoleId) U;
–SELECT R.RoleName, ISNULL(U.ActiveUserCount,0) AS ActiveUserCount FROM tblRoles R
–LEFT JOIN (SELECT RoleId, COUNT(*) AS ActiveUserCount FROM tblUsers WHERE IsActive = 1 GROUP BY RoleId) U;
SELECT R.Rolename, COUNT(U.UserId) as ActiveUserCount FROM tblRoles R
LEFT JOIN (SELECT UserId, RoleId FROM tblUsers WHERE IsActive = 1) U ON U.RoleID = R.RoleID
GROUP BY R.RoleId, R.RoleName;
6
0
No correct answer.
Select R.Rolename, count(userID) as activeUsercount from tblRoles R
Left join (select userID, RoleId from tblUsers Where Isactive = 1) u
On u.roleID = R.roleID
Group by R.roleID,R.rolename
3
1
D is Correct Answer.
B returns count(*) = 1 if a role has no active users
0
0
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.
for me is like that:
SELECT R.RoleName, COUNT(
CASE U.RoleID
WHEN 0 THEN 0
–WHEN 1 THEN 1
else
u.RoleID
END )as ActiveUserCount
FROM [test].[tblRoles] R
LEFT JOIN (SELECT UserId, RoleId FROM [test].[tblUsers] WHERE IsActive = 1) U
ON U.RoleId = R.RoleId
GROUP BY R.RoleName
–or
SELECT R.RoleName, COUNT(UserId)AS ActiveUserCount
FROM [test].[tblRoles] R
LEFT JOIN (SELECT UserId, RoleId FROM [test].[tblUsers] WHERE IsActive = 1) U
ON U.RoleId = R.RoleId
GROUP BY R.RoleName
pero la respuesta que mas cubre y esta dentro de las alternativas es
SELECT R.RoleName, COUNT(*)AS ActiveUserCount
FROM [test].[tblRoles] R
LEFT JOIN (SELECT UserId, RoleId FROM [test].[tblUsers] WHERE IsActive = 1) U
ON U.RoleId = R.RoleId
GROUP BY R.RoleName
0
0
D is the answer if they finished the query :
ELECT R.RoleName, ISNULL(U.ActiveUserCount,0) AS ActiveUserCount FROM tblRoles R LEFT JOIN
(SELECT RoleId, COUNT(*) AS ActiveUserCount FROM tblUsers WHERE IsActive = 1 GROUP BY RoleId) U ON U.RoleId = R.RoleId
6
0