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;