ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam
Go to Exam

Exam 70-761 topic 1 question 124 discussion

Actual exam question from Microsoft's 70-761
Question #: 124
Topic #: 1
[All 70-761 Questions]

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

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Jiacheng
Highly Voted 5 years, 2 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, 3 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, 2 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, 2 months ago
The except DOES filter out duplicates.
upvoted 11 times
...
...
...
Andy7622
Most Recent 4 years, 4 months ago
it's hardly seen but something like EXCEPT in F, it's correct though.
upvoted 2 times
...
chaoxes
4 years, 9 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
4 years, 10 months ago
F is correct, EXCEPT and INTERSECT remove duplicates
upvoted 1 times
...
Andi64
5 years, 2 months ago
E is wrong, because the right table is loanAcc and we are looking for DepositAcct
upvoted 5 times
...
gtc108
5 years, 2 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
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago