Which Transact-SQL query do you use?

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.Rnk = 1

microsoft-exams

One thought on “Which Transact-SQL query do you use?

  1. Correct answer is D.
    C contains a line “ORDER BY o. OrderAmount DESC) AS Rnk” where the COUNT is missing for OrderAmount. This causes a GROUP BY to throw an error (OrderAmount is not included in Group By).

Leave a Reply

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


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