HOTSPOT
You have a database that contains the following tables: tblRoles, tblUsers, and tblUsersInRoles.
The table tblRoles is defined as follows.
You have a function named ufnGetRoleActiveUsers that was created by running the following Transact-SQL statement:
You need to list all roles and their corresponding active users. The query must return the RoleId, RoleName, and UserName columns. If a role has no active users, a NULL value should be returned as the UserName for that role.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
Hot Area:
corret answer is
SELECT *
FROM tblRoles
OUTER APPLY ufnGetRoleActiveUsers(RoleId)
CREATE TABLE [tblRoles]
(
[RoleID] INT PRIMARY KEY,
[RoleName] VARCHAR(20)
)
GO
CREATE TABLE [tblUsers]
(
[UserID] INT PRIMARY KEY,
[UserName] VARCHAR(20)
)
GO
CREATE TABLE [tblUsersInRoles]
(
[UserID] INT,
[RoleID] INT,
PRIMARY KEY ([UserID],[RoleID])
)
GO
INSERT INTO [tblRoles]([RoleID],[RoleName])
VALUES
(1,’Admin’),
(2,’Users’),
(3,’RemoteAccess’),
(4,’Guest’)
GO
INSERT INTO [tblUsers]([UserID],[UserName])
VALUES
(1,’AdminUser’),
(2,’User1′),
(3,’User2′),
(4,’User3′),
(5,’RemoteUser1′),
(6,’RemoteUser2′)
GO
INSERT INTO [tblUsersInRoles]([UserID],[RoleID])
VALUES
(1,1),
(2,2),
(3,2),
(4,2),
(5,3),
(6,3)
GO
CREATE FUNCTION ufnGetRoleActiveUsers(@RoleId AS INT)
RETURNS @roleSummary TABLE(
UserName VARCHAR(20)
)
AS
BEGIN
INSERT INTO @roleSummary
SELECT U.UserName
FROM tblUsersInRoles BRG
INNER JOIN tblUsers U
ON U.UserId = BRG.UserId
WHERE BRG.RoleId = @RoleId /*AND U.IsActive = 1*/
RETURN
END
what is difference between ‘outer apply’ and ‘cross apply’? As I understand, if we want to get the zero count record, ‘Outer Apply’ should be used. Because we want know which role name has zero user, the correct answer is ‘Outer apply’. If we don’t care the one has no user, ‘Cross Apply’ should be ok.