PrepAway - Latest Free Exam Questions & Answers

Which four Transact-SQL segments should you use to deve…

DRAG DROP
You create three tables by running the following Transact-SQL statements:

For reporting purposes, you need to find the active user count for each role, and the total active user count. The
result must be ordered by active user count of each role. You must use common table expressions (CTEs).
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.
Select and Place:

PrepAway - Latest Free Exam Questions & Answers

Answer:

6 Comments on “Which four Transact-SQL segments should you use to deve…”

  2. Peter says:

    Given answer is wrong! The “WITH” has always to be at the beginning of the first statement.

    In my opinion the answer should be like the code example given below.

    IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblUsersRoles’)
    BEGIN
    DROP TABLE dbo.tblUsersRoles;
    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;
    IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblUsers’)
    BEGIN
    DROP TABLE dbo.tblUsers;
    END;

    CREATE TABLE tblRoles
    (
    RoleId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,RoleName VARCHAR(20) NOT NULL
    );

    INSERT INTO tblRoles (RoleName) VALUES
    (‘Role1’), (‘Role2’), (‘Role3’);

    CREATE TABLE tblUsers
    (
    UserId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,UserName VARCHAR(20) UNIQUE NOT NULL
    ,IsActive BIT NOT NULL DEFAULT (1)
    );

    INSERT INTO tblUsers (UserName, IsActive) VALUES
    (‘User1’, 1), (‘User2’,1), (‘User3’,0), (‘User4’,1), (‘User5’,0);

    CREATE TABLE tblUsersRoles
    (
    UserId INT NOT NULL FOREIGN KEY REFERENCES tblUsers(UserId)
    ,RoleId INT NOT NULL FOREIGN KEY REFERENCES tblRoles(RoleId)
    );

    INSERT INTO tblUsersRoles (UserId, RoleId) VALUES
    (3,2), (5,2), (1,3), (2,3), (4,3), (1,1), (2,1), (3,1), (5,1);

    ;
    ——– 1. Section
    WITH ActiveUsers AS (
    SELECT UserId
    FROM tblUsers
    WHERE IsActive = 1
    ),
    ——– 2. Section
    RoleNCount AS (
    SELECT RoleId, COUNT(*) AS ActiveUserCount
    FROM tblUsersRoles BRG
    INNER JOIN ActiveUsers U ON BRG.UserId = U.UserId
    GROUP BY BRG.RoleId
    ),
    ——– 3. Section
    RoleSummary AS (
    SELECT R.RoleName, ISNULL(S.ActiveUserCount,0) AS ActiveUserCount
    FROM tblRoles R
    LEFT JOIN RoleNCount S ON R.RoleId = S.RoleId
    ),
    ——– 4. Section
    Total AS (
    SELECT COUNT(*) AS TotalCountInAllRoles FROM ActiveUsers
    )
    SELECT S.*, Total.TotalCountInAllRoles
    FROM RoleSummary S, Total
    ORDER BY S.ActiveUserCount
    ;




    33



    0
    1. Peter says:

      Adding the ORDER BY also to the thrid section will result in a syntax error:

      The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.




      19



      0
      1. overkill says:

        I would like to add to Peter that Order by Clause is forbidden in those objects because tables in Databases are like SETs in Set theory. And By definition Sets are not ordered.




        2



        1

Leave a Reply