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 81 discussion

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

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.

B.

C.

D.

E.

F.

G.

H.

Show Suggested Answer Hide Answer
Suggested Answer: E
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.
References:
https://www.w3schools.com/sql/sql_join_right.asp
by Vanesa30 at March 21, 2020, 2:32 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
KosteK
4 years, 5 months ago
UNION ALL SELECT 'F', count(*),'Total number of customers who have only deposit accounts' FROM ( SELECT CustNo FROM #tblDepositAcct D EXCEPT SELECT CustNo FROM #tblLoanAcct L ) R UNION ALL SELECT 'G', count(DISTINCT COALESCE(d.CustNo, l.CustNo)),'#80: Total number of customers who have either deposit accounts or loan accounts, but not both types of accounts' FROM #tblDepositAcct D FULL JOIN #tblLoanAcct L ON d.CustNo=l.CustNo WHERE d.CustNo IS NULL OR l.CustNo IS NULL UNION ALL SELECT 'H', count(*),'' FROM #tblDepositAcct D FULL JOIN #tblLoanAcct L ON d.CustNo=l.CustNo
upvoted 1 times
...
KosteK
4 years, 5 months ago
SELECT 'A' 'Ans', count(*) 'Count', 'The same Accounts in two tables' 'Desc' FROM ( SELECT AcctNo FROM #tblDepositAcct D INTERSECT SELECT AcctNo FROM #tblLoanAcct L ) R UNION ALL SELECT 'B', count(*),'Distict customers' FROM ( SELECT CustNo FROM #tblDepositAcct D UNION SELECT CustNo FROM #tblLoanAcct L ) R UNION ALL SELECT 'C', count(*),'Count of all Acc' FROM ( SELECT CustNo FROM #tblDepositAcct D UNION ALL SELECT CustNo FROM #tblLoanAcct L ) R UNION ALL SELECT 'D', COUNT (DISTINCT D.CustNo),'#82: Total number of customers who have both deposit and loan accounts' FROM #tblDepositAcct D, #tblLoanAcct L WHERE D.CustNo = L.CustNo UNION ALL SELECT 'E', COUNT (DISTINCT L.CustNo),'#81: Total number of customers who have only loan accounts' FROM #tblDepositAcct D RIGHT JOIN #tblLoanAcct L ON D.CustNo=L.CustNo WHERE D.CustNo IS NULL
upvoted 2 times
...
KosteK
4 years, 5 months ago
DROP TABLE IF EXISTS #tblDepositAcct CREATE TABLE #tblDepositAcct ( CustNo int, AcctNo int PRIMARY KEY, ProdCode varchar(3) ); DROP TABLE IF EXISTS #tblLoanAcct CREATE TABLE #tblLoanAcct ( CustNo int, AcctNo int PRIMARY KEY, ProdCode varchar(3) ); INSERT INTO #tblDepositAcct VALUES (1,100,'a') ,(1,200, 'b') ,(2, 300, 'c') ,(3, 400, 'd') ,(3, 500, 'd') ,(3, 600, 'd'); INSERT INTO #tblLoanAcct VALUES (1,500,'g') ,(2,600, 'h') ,(4,700, 'c') ,(4,800, 'i') ,(5,900, 'j') ,(6,1000, 'j');
upvoted 1 times
...
KosteK
4 years, 5 months ago
Answer E is corret
upvoted 4 times
...
Vanesa30
5 years, 1 month ago
For me the option H is valid too, becasuse EXCEPT is to retrieve data that doesn't match with the others tables.
upvoted 2 times
xd1
5 years ago
is not valid as we only need loan accounts, and that one gives you only deposit accounts
upvoted 9 times
xd1
5 years ago
nevermind that's F
upvoted 2 times
Braindripper
4 years, 7 months ago
EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. so you will have only deposit accounts - you need only those with loan.
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