In this exercise, you will practice working with aggregate functions. For all of these questions, it's okay if your result set's rows are in a different order.
Order_Detailstable.The report should only return rows for which
TotalUnitsis less than 200.
Productstable. The report should only return rows for which the average unit price of a product is greater than 70.
NumOrdersrepresents the number of orders placed by a certain customer. Only return rows where
NumOrdersis greater than 15.
Query number 2 above has something strange about it. It is, in fact, a ridiculous query. Why? Try to get the exact same results without using an aggregate function.
SELECT ProductID, SUM(Quantity) AS TotalUnits FROM Order_Details /* SQL Server users should use "Order Details" */ GROUP BY ProductID HAVING SUM(Quantity) < 200; SELECT ProductID, AVG(UnitPrice) AS AveragePrice FROM Products GROUP BY ProductID HAVING AVG(UnitPrice) > 70 ORDER BY AveragePrice; SELECT CustomerID, COUNT(OrderID) AS NumOrders FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 15 ORDER BY NumOrders DESC; /*CHALLENGE ANSWER:*/ SELECT ProductID, UnitPrice FROM Products WHERE UnitPrice > 70 ORDER BY UnitPrice;