Which Transact-SQL statement should you run?

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?

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



Leave a Reply 6

Your email address will not be published. Required fields are marked *


LuckyChap

LuckyChap

Hey, Looks like Option B doesn’t show the Role having no active user?

scotrideff

scotrideff

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

KK

KK

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

davgmane

davgmane

Answer is D

Peter

Peter

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;

EY Auditor

EY Auditor

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