Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2008 database that contains tables named Sales.Customer and Sales.SalesOrder. A diagram of the tables is shown in the exhibit. (Click the Exhibit button.)
You need to execute a query to update the value of the CustomerValue field to HV when a customer has more than 5 orders for a total sales amount of more than 5,000 U.S. dollars.
Which Transact-SQL statement should you use?
Exhibit:


A. UPDATE Sales.Customer
SET CustomerValue = ‘HV’
FROM Sales.Customer
WHERE SalesAmount > 5000
AND CustomerID IN (SELECT c.CustomerID
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(*) > 5)
B. UPDATE Sales.Customer
SET CustomerValue = ‘HV’
FROM Sales.Customer
WHERE CustomerID IN (SELECT c.CustomerID
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000)
C. UPDATE c
SET CustomerValue = ‘HV’
FROM Sales.SalesOrder o
INNER JOIN Sales.Customer c ON c.CustomerID=o.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000
D. UPDATE c
SET CustomerValue = ‘HV’
FROM Sales.SalesOrder o
INNER JOIN Sales.Customer c ON c.CustomerID=o.CustomerID
WHERE SalesAmount > 5000
GROUP BY c.CustomerID
HAVING COUNT(*) > 5
E. UPDATE u
SET CustomerValue = ‘HV’
FROM Sales.Customer u
WHERE EXISTS (SELECT c.CustomerID
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID
WHERE c.CustomerID=u.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000)
F. UPDATE u
SET CustomerValue = ‘HV’
FROM Sales.Customer u
WHERE SalesAmount > 5000
AND EXISTS(SELECT c.CustomerID
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID
WHERE c.CustomerID=u.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(*) > 5)
G. UPDATE Sales.Customer
SET CustomerValue = ‘HV’
FROM Sales.SalesOrder o
INNER JOIN Sales.Customer c ON c.CustomerID=o.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000
H. UPDATE Sales.Customer
SET CustomerValue = ‘HV’
FROM Sales.SalesOrder o
INNER JOIN Sales.Customer c ON c.CustomerID=o.CustomerID
WHERE SalesAmount > 5000
GROUP BY c.CustomerID
HAVING COUNT(*) > 5

microsoft-exams

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.