Exam 70-761 topic 1 question 82 discussion

It is not INTERSECT because the "INTERSECT operator is used to return the records that are in common between two SELECT statements or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. It is the intersection of the two SELECT statements." So, if a customer has a deposit account, but not a loan account (null), the record will be omitted from the result set. The question asks for "all ("?) customers who have both deposit and loan accounts". It is how we interpret this phrase that will decide the correct answer ?

UNION ALL. Please read question and description. "all customers" who have both loan and deposit accounts.

A is ok , custNo and AccNo( PK ) its the same... read description of column...

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

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

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');

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,'abc') ,(2,200, 'def') ,(3, 300, 'ghi') ,(1, 400, 'jkl'); INSERT INTO #tblLoanAcct VALUES (1,500,'mno') ,(2,600, 'pqr'); SELECT * FROM #tblDepositAcct; SELECT * FROM #tblLoanAcct; --D SELECT COUNT (DISTINCT D.CustNo) FROM #tblDepositAcct D, #tblLoanAcct L WHERE D.CustNo = L.CustNo; --RETURNS 2 (Correct is D)

Sorry, only D is correct.

Both D & G are correct

Tested. D is correct. the syntax in A is correct except the join. its supposed to be CustNo no AcctNo.

A is incorrect, both tables use different values of AcctNo for the same customer, so INTERSECT will be empty D is correct

Both A and D seems to be right. A - counts the Account Number once in one table and D counts the customer number

A is wrong - question concern customers, not accounts. D seems ok