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 CustomerID, CustomerName, ShippingCountry
FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
RANK() OVER (PARTITION BY c.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
B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) AS
OrderAmount FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC
C. SELECT CustomerID, CustomerName, ShippingCountry
FROM (SELECT c.CustomerID, c.CustomerName,
o. ShippingCountry,
RANK() OVER (PARTITION BY c. CustomerID
ORDER BY o. OrderAmount DESC) 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
D. 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.Rn
k = 1
Explanation:
Incorrect:
Not A: Should not use GROUP BY on CustomerName.
Not B: We must not display all order. Need a
Where o.Rnk = 1
Not C: Should order by the number of orders, not the amount of the orders. ORDER BY COUN T(OrderAmount),
not ORDER BY OrderAmount, must be used.