PrepAway - Latest Free Exam Questions & Answers

Which Transact-SQL statement should you run?

Note: This question is part of a series of questions that use the same or similar answer choices. An
answer choice may be correct for more than one question in the series. Each question is independent
of the other questions in this series. Information and details provided in a question apply to that
question.
You have a database for a banking system. The database has two tables named tblDepositAcct and
tblLoanAcct that store deposit and loan accounts, respectively. Both tables contain the following columns:

You need to run a query to find the total number of customers who have both deposit and loan accounts.
Which Transact-SQL statement should you run?

PrepAway - Latest Free Exam Questions & Answers

A.
SELECT COUNT(*)
FROM (SELECT AcctNo
FROM tblDepositAcct
INTERSECT
SELECT AcctNo
FROM tblLoanAcct) R

B.
SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
UNION
SELECT CustNo
FROM tblLoanAcct) R

C.
SELECT COUNT(*)
FROM (SELECT CustNoFROM tblDepositAcct
UNION ALL
SELECT CustNo
FROM tblLoanAcct) R

D.
SELECT COUNT (DISTINCT D.CustNo)
FROM tblDepositAcctD, tblLoanAcct L
WHERE D.CustNo = L.CustNo

E.
SELECT COUNT(DISTINCT L.CustNo)
FROM tblDepositAcct D
RIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL

F.
SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
EXCEPT
SELECT CustNo
FROM tblLoanAcct) R

G.
SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL OR L.CustNo IS NULL

H.
SELECT COUNT(*)
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo

Explanation:
The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only
returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will
be omitted from the INTERSECT results.
https://www.techonthenet.com/sql/intersect.php

27 Comments on “Which Transact-SQL statement should you run?”

  1. scotrideff says:

    WRONG answer is SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))
    FROM tblDepositAcct D
    FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
    WHERE D.CustNo IS NULL OR L.CustNo IS NULL




    4



    23
  4. BigDataGuy says:

    Why would I expect the loan account number to equal the deposit account number? If A is the correct answer then the column should not be AccNo, but CustNo. It is the customer number CustNo that must match between the tables tblDepositAcct and tblLoanAccnt, if the customer has both a loan account and a deposit account. AcctNo need not match. The correct answer is actually D, although it is using the non recommended NON ANSI join.




    18



    0
  7. E Rod says:

    D is the answer correct.

    A – is not correct because the column that must be part of the operator INTERSECT must be CustNo and not AcctNo. A customer can have many accounts in both tables, the query ask for the numbers of customers who have both accounts.
    B – is not correct, the operator UNION used the column CustNo but this operator specifies that multiple result sets are to be combined and returned as a single result set. Then, you can have a result set with customers that have accounts of the type Deposit and not of the Loan, or vice versa.
    C – is not correct, the operator UNION ALL used the column CustNo, the result set can include duplicates.
    D – is correct because the clause WHERE modifies the cross join defined between the two tables in the clause FROM. COUNT (DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, non null values.
    E – is not correct because the RIGHT JOIN and the clause WHERE exclude customers with Deposit accounts.
    F – is not correct because EXCEPT operator excludes customers with Loan accounts.
    G – is not correct because in addition to the FULL JOIN, the clause WHERE identifies customers with account of one type and not the other type, for example, customer with deposit account and no loan account.
    H – is not correct due FULL JOIN




    31



    2
  9. yasseryy says:

    Your network contains an Active Directory domain named contoso.com.
    You have a Group Policy object (GPO) named GPO1. GPO1 is linked to an organizational unit (OU) named
    OU1.
    GPO1 contains several corporate desktop restrictions that apply to all computers.
    You plan to deploy a printer to the computers in OU1.
    You need to ensure that any user who signs in to a computer that runs Windows 10 in OU1 receives the new
    printer. All of the computers in OU1 must continue to apply the corporate desktop restrictions from GPO1.
    What should you configure?

    A.
    a user preference and a WMI filter on GPO1.

    B.
    a computer preference that uses item-level targeting

    C.
    a computer preference and WMI filter on GPO1

    D.
    a user preference that uses item-level targeting

    Why (D) ? Why not computer 0tem-level targeting ?




    0



    2
  11. berend says:

    Passed yesterday. 48 questions. Used Microsoft Press exam ref and, to train query writing skills, I used sql-ex.ru site (unfortunately there is only russian version of this site).
    About five questions require you to write your own script.




    0



    0
  12. EY Auditor says:

    D is CORRECT!

    IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblLoanAcct’)
    BEGIN
    DROP TABLE dbo.tblLoanAcct;
    END;
    IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘tblDepositAcct’)
    BEGIN
    DROP TABLE dbo.tblDepositAcct;
    END;

    CREATE TABLE tblLoanAcct (
    AcctNo int PRIMARY KEY CLUSTERED,
    CustNo int NOT NULL,
    ProdCode varchar(3) Not NULL
    )

    CREATE TABLE tblDepositAcct (
    AcctNo int PRIMARY KEY CLUSTERED,
    CustNo int NOT NULL,
    ProdCode varchar(3) Not NULL
    )

    INSERT INTO tblLoanAcct VALUES
    (10001, 125, ‘Loa’ ),
    (10002, 101, ‘Loa’ ),
    (10003, 185, ‘Loa’ ),
    (10004, 165, ‘Loa’ ),
    (10005, 142, ‘Loa’ ),
    (10006, 144, ‘Loa’ ),
    (10007, 152, ‘Loa’ )

    INSERT INTO tblDepositAcct VALUES
    (20001, 165, ‘Dep’ ),
    (20002, 652, ‘Dep’ ),
    (20003, 142, ‘Dep’ ),
    (20004, 111, ‘Dep’ ),
    (20005, 101, ‘Dep’ )

    select * from tblLoanAcct

    select * from tblDepositAcct

    — 3 customers have both loan account and deposit account

    A.
    SELECT COUNT(*)
    FROM (SELECT AcctNo
    FROM tblDepositAcct
    INTERSECT
    SELECT AcctNo
    FROM tblLoanAcct) R

    B.
    SELECT COUNT(*)
    FROM (SELECT CustNo
    FROM tblDepositAcct
    UNION
    SELECT CustNo
    FROM tblLoanAcct) R

    C.
    SELECT COUNT(*)
    FROM (SELECT CustNoFROM tblDepositAcct
    UNION ALL
    SELECT CustNo
    FROM tblLoanAcct) R

    D.
    SELECT COUNT (DISTINCT D.CustNo)
    FROM tblDepositAcct D, tblLoanAcct L
    WHERE D.CustNo = L.CustNo

    E.
    SELECT COUNT(DISTINCT L.CustNo)
    FROM tblDepositAcct D
    RIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNo
    WHERE D.CustNo IS NULL

    F.
    SELECT COUNT(*)
    FROM (SELECT CustNo
    FROM tblDepositAcct
    EXCEPT
    SELECT CustNo
    FROM tblLoanAcct) R

    G.
    SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))
    FROM tblDepositAcct D
    FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
    WHERE D.CustNo IS NULL OR L.CustNo IS NULL

    H.
    SELECT COUNT(*)
    FROM tblDepositAcct D
    FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo




    11



    1
  14. eder says:

    SELECT COUNT(DISTINCT d.CustNo)AS CountCust FROM Schema70761.tblLoanAcct AS l
    INNER JOIN Schema70761.tblDepositAcct AS d
    ON L.CustNo=D.CUSTNO

    –OR

    SELECT COUNT(*)AS CountCust FROM
    (
    select CustNo FROM Schema70761.tblLoanAcct
    INTERSECT
    select CustNo FROM Schema70761.tblDepositAcct
    )AS R

    THE LETTER A IS CORRECT.

    SALUDOS DESDE LIMA,PERU




    0



    0
  15. jeremy73 says:

    CustNo and AcctNo has the same description: “This column uniquely identifies a customer in the bank”
    The query in answer D might be correct, but is not the obvious answer. Besides, answer A uses a primary key which also has a clustered index, which is must faster.




    0



    0

Leave a Reply