Your database contains sales information for millions of orders.
You need to identify the orders with the highest average unit price and an order total greater than
10,000.
The list should contain no more than 20 orders.
Which query should you use?
A.
SELECT TOP (20)
o.SalesOrderId,
o.OrderDate,
o.Total,
SUM(od.QTY * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice]
FROM Sales.SalesOrderHeader o
JOIN SALES.SalesOrderDetail od
ON o.SalesOrderId = od.SalesOrderId
WHERE o.Total> 10000
GROUP BY o.SalesOrderId, o.OrderDate, o.Total
ORDER BY AvgUnitPrice;
B.
SELECT TOP (20)
o.SalesOrderId,
o.OrderDate,
o.Total,
(SELECT SUM(od.Qty * od.UnitPrice) / SUM(od.QTY)
FROM Sales.SalesOrderDetail od
WHERE o.SalesOrderId = od.SalesOrderId) AS [AvgUnitPrice]
FROM Sales.SalesOrderHeader o
WHERE o.Total> 10000
ORDER BY AvgUnitPrice DESC;
C.
SELECT TOP (20)
o.SalesOrderId,
o.OrderDate,
o.Total,
SUM(od.Qty * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice]
FROM Sales.SalesOrderHeader o
JOIN Sales.SalesOrderDetail od
ON o.SalesOrderId = od.SalesOrderId
WHERE o.Total> 10000
GROUP BY o.SalesOrderId, o.OrderDate, o.Total
ORDER BY Total DESC;
D.
SELECT TOP (20)
o.SalesOrderId,
o.OrderDate,
o.Total,
(SELECT SUM(od.Qty * od.UnitPrice) / SUM(od.Qty)
FROM Sales.SalesOrderDetail od
WHERE o.SalesOrderId = od.SalesOrderId) AS [AvgUnitPrice]
FROM Sales.SalesOrderHeader o
WHERE o.Total > 10000
ORDER BY o.Total DESC,
AvgUnitPrice;