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.
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 CustNo
FROM tblDepositAcct
UNION ALL
SELECT CustNo
FROM tblLoanAcct) R
D.
SELECT COUNT (DISTINCT D.CustNo)
FROM tblDepositAcct D, 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
Explanation:
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
https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx