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 determine the total number of customers who have only loan accounts.
Which Transact-SQL statement should you run?
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 (SELECTCustNo
FROM 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 OND.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 OND.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 RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the
left table (table1). The result is NULL from the left side, when there is no match.
https://www.w3schools.com/sql/sql_join_right.asp
2017 New 70-761 Exam Dumps and 70-761 VCE Dumps: https://www.braindump2go.com/70-761.html
2
0
E
5
0
E is the correct answer.
23
0
E
6
0
CREATE SCHEMA Schema70761
CREATE TABLE Schema70761.tblLoanAcct (
CustNo INT NOT NULL,
AcctNo INT CONSTRAINT PK_Schema70761tblLoanAcct_AcctNo PRIMARY KEY CLUSTERED,
ProdCode VARCHAR(3)NOT NULL
)
CREATE TABLE Schema70761.tblDepositAcct (
CustNo INT NOT NULL,
AcctNo INT CONSTRAINT PK_Schema70761tblDepositAcct_AcctNo PRIMARY KEY CLUSTERED,
ProdCode VARCHAR(3)NOT NULL
)
/*
You need to determine the total number of customers who have only loan accounts.
Which Transact-SQL statement should you run?
*/
SELECT COUNT(DISTINCT l.CustNo)AS CountCust
FROM Schema70761.tblDepositAcct AS d
RIGHT JOIN Schema70761.tblLoanAcct AS l
ON d.CustNo=l.CUSTNO
WHERE D.CustNo IS NULL
THE LETTER E IS CORRECT
2
0