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?
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