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

Actual exam question from Microsoft's 70-761
Question #: 80
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 either deposit accounts or loan accounts, but not both types of accounts.
Which Transact-SQL statement should you run?

A.

B.

C.

D.

E.

F.

G.

H.

Show Suggested Answer Hide Answer
Suggested Answer: G
SQL Server provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.
Consider a join of the Product table and the SalesOrderDetail table on their ProductID columns. The results show only the Products that have sales orders on them. The ISO FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.
You can include a WHERE clause with a full outer join to return only the rows where there is no matching data between the tables. The following query returns only those products that have no matching sales orders, as well as those sales orders that are not matched to a product.
USE AdventureWorks2008R2;

GO -
-- The OUTER keyword following the FULL keyword is optional.

SELECT p.Name, sod.SalesOrderID -

FROM Production.Product p -
FULL OUTER JOIN Sales.SalesOrderDetail sod

ON p.ProductID = sod.ProductID -

WHERE p.ProductID IS NULL -

OR sod.ProductID IS NULL -

ORDER BY p.Name -
References:
https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx
by HimanshuKshatriya at July 5, 2020, 4:04 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
chaoxes
Highly Voted 4 years, 9 months ago
correct answer is G, tested it. D returns only those clients that have both deposit and loans so its invalid.
upvoted 10 times
...
kimalto452
Most Recent 4 years, 4 months ago
G its ok
upvoted 2 times
...
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 1 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
...
HimanshuKshatriya
4 years, 10 months ago
Correct ans is D
upvoted 1 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