PrepAway - Latest Free Exam Questions & Answers

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database con

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:



Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.

Which Transact-SQL query do you use?

A.

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry

FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry,

RANK() OVER (PARTITION BY CustomerID

ORDER BY COUNT(OrderAmount) DESC) AS Rnk

FROM Orders GROUP BY CustomerID, ShippingCountry)

AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1

B.

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry

FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,

RANK()OVER (PARTITION BY CustomerID

ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk

FROM Customer c INNER JOIN Orders o ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs

WHERE Rnk = 1

C.

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry

FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry,

RANK() OVER (PARTITION BY CustomerID

ORDER BY OrderAmount DESC) AS Rnk

FROM Orders GROUP BY CustomerID, ShippingCountry) AS o

ON c.CustomerID = o.CustomerID

WHERE o.Rnk = 1

D.

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry

FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry,COUNT(OrderAmount) DESC)

AS OrderAmount FROM Orders GROUP BY CustomerID, ShippingCountry)

AS o ON c.CustomerID = o.CustomerID ORDER BY OrderAmount DESC

Explanation:

Use descending (DESC) ordering.

To order by the number of orders we use ORDER BY COUNT(OrderAmount).

Finally a WHERE close is needed: WHERE o.Rnk = 1

Incorrect Answers:

B: The ascending (ASC) sorting would produce the country from which each customer has placed the least orders.

C: We are interested in the number of the orders, not the amount of

the orders. We should use ORDER BY COUNT(OrderAmount), not ORDER BY OrderAmount.

D: We are only interested in one single post, only the country from which each customer has placed the most orders. Need to use a WHERE statement (here Where o.Rnk =1 ).


Leave a Reply