1. (10 pts). What is the best-selling product, based on the number of orders that contain one or more orders of that product?
(a) Gorgonzola Telino.
(b)
Select p.productname, od.productID, sum(quantity) as OrderCount from OrderDetails as od left join products as p on od.productID = p.productID group by od.productID order by sum(quantity) desc;

2. (10 pts). List the number of customers by country, from least to most.
(a)
Country NumCustomers
Ireland 1
Norway 1
Poland 1
Austria 2
Belgium 2
Denmark 2
Finland 2
Portugal 2
Sweden 2
Switzerland 2
Argentina 3
Canada 3
Italy 3
Venezuela 4
Mexico 5
Spain 5
UK 7
Brazil 9
France 11
Germany 11
USA 13

(b)
SELECT
Country,
COUNT(*) AS NumCustomers
FROM Customers
GROUP BY Country
ORDER BY NumCustomers ASC;

3. (10 pts). How many shippers are there, and how many orders has each shipper shipped?
1) How many Shippers are there?
(a) There are 3 shippers.
(b)
SELECT
COUNT(*) AS Shippers
FROM Shippers

2) How many orders has each shipper shipped?
(a) ShipperID 1 (Speedy Express) shipped 54 orders, ShipperID 2 (United Package) shipped 74 orders, and ShipperID 3 (Federal Shipping) shipped 68 orders.
(b)
SELECT
ShipperID,
COUNT(*) AS NumOrders
FROM Orders
GROUP BY ShipperID

4. (20 pts). How many customers have spent, in their lifetime, more than $500 but less than $1,000 and have at least 3 distinct orders?
(a) 9 customers spent more than $500 but less than $1,000 and have at least 3 distinct orders
(b)
i) Create a table showing each customer’s orders:
CREATE TABLE CustomerOrder AS
Select CustomerID,
Count(orderID) AS CountOrderID,
Sum(subtotal) AS CustomerSubtotal
FROM ByCustomerOrders
GROUP by CustomerID;

ii) Show customers that spent more than $500 but less than $1,000 and have at least 3 distinct orders:
SELECT *
FROM CustomerOrder
WHERE