Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 124 discussion

Actual exam question from Microsoft's 70-761

Question #: 124
Topic #: 1

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 deposit accounts.
Which Transact-SQL statement should you run?

A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
G. Option G
H. Option H

Show Suggested Answer

Suggested Answer: F 🗳️
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017

by flashed at Feb. 8, 2020, 7:07 p.m.

Comments

Submit Cancel

Jiacheng

Highly Voted 5 years, 4 months ago

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

upvoted 18 times

...

flashed

Highly Voted 5 years, 4 months ago

Can't see the all answer, but if there is any with except, is the one i choose

upvoted 10 times

jpdvm

5 years, 4 months ago

None of the answers are correct. The except does not filter out duplicates in tblDepositAcct (needs distinct), so count(*) would be wrong.

upvoted 1 times

anonimdom

5 years, 4 months ago

The except DOES filter out duplicates.

upvoted 11 times

...

Andy7622

Most Recent 4 years, 6 months ago

it's hardly seen but something like EXCEPT in F, it's correct though.

upvoted 2 times

...

chaoxes

4 years, 11 months ago

F. is correct. All 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

upvoted 8 times

...

Backy

5 years ago

F is correct, EXCEPT and INTERSECT remove duplicates

upvoted 1 times

...

Andi64

5 years, 4 months ago

E is wrong, because the right table is loanAcc and we are looking for DepositAcct

upvoted 5 times

...

gtc108

5 years, 4 months ago

Answer: E 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.

upvoted 2 times

...