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
UESTION 26
You have a database that contains the following tables.
You need to create a query that lists the lowest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements:
– Return a column named Fullname that includes the salesperson FirstName, a space, and then LastName.
– Include the current year-to-date sales for each salesperson.
– Display only data for the three salespersons with the lowest year-to-year sales values.
– Exclude salespersons that have no value for TerritoryID.
Construct the query using the following guidelines:
– Use the first letter of a table name as the table alias.
– Use two-part column names.
– Do not surround object names with square brackets.
– Do not use implicit joins.
– Use only single quotes for literal text.
– Use aliases only if required.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
Answer: Pending
QUESTION 27
You have a database that contains the following tables.
You need to create a query that lists all complaints from the Complaints table, and the name of the person handling the complaints if a person is assigned.
The ComplaintID must be displayed first, followed by the person name.
Construct the query using the following guidelines:
– Use two-part column names.
– Use one-part table names.
– Do not use aliases for column names or table names.
– Do not use Transact-SQL functions.
– Do not use implicit joins.
– Do not surround object names with square brackets.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements.
You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
Answer: Pending
QUESTION 28
You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)
You need to create a list of all customers, the order ID for the last order that the customer placed, and the date that the order was placed. For customers who have not placed orders, you must substitute a zero for the order ID and O 1/01/1990 for the date.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
More: https://www.braindump2go.com/70-761.html
G is correct.
G