1. List the customers from California who purchased race bicycles in 2006 larger than 60 cm (frame size).
SELECT Customer.CustomerID, Customer.LastName, Bicycle.ModelType, Bicycle.FrameSize, Bicycle.OrderDate, City.State
FROM City RIGHT JOIN (Customer INNER JOIN Bicycle ON Customer.CustomerID = Bicycle.CustomerID) ON City.CityID = Customer.CityID
WHERE (((Bicycle.FrameSize)>60) AND ((Bicycle.OrderDate) Between #1/1/2006# And #12/31/2006#) AND ((City.State)="CA"));

2. Compute the total sales by model type for 2006.
SELECT Bicycle.ModelType, Sum(Bicycle.SalePrice) AS SumOfSalePrice
FROM Bicycle
WHERE (((Bicycle.OrderDate) Between #1/1/2006# And #12/31/2006#))
GROUP BY Bicycle.ModelType;

3. Which employee sold the most mountain or full suspension mountain bikes in 2005 (by count)?
SELECT Employee.EmployeeID, Employee.LastName, Count(Bicycle.SerialNumber) AS CountOfSerialNumber
FROM Employee INNER JOIN Bicycle ON Employee.EmployeeID = Bicycle.EmployeeID
WHERE (((Bicycle.ModelType) Like "Mount*") AND ((Bicycle.OrderDate) Between #1/1/2005# And #12/31/2005#))
GROUP BY Employee.EmployeeID, Employee.LastName
ORDER BY Count(Bicycle.SerialNumber) DESC;

4. Which manufacturer gave us the greatest average percent discount in 2005?
SELECT Manufacturer.ManufacturerID, Manufacturer.ManufacturerName, Avg([Discount]/[TotalList]) AS PctDsct
FROM Manufacturer INNER JOIN PurchaseOrder ON Manufacturer.ManufacturerID = PurchaseOrder.ManufacturerID
WHERE (((PurchaseOrder.OrderDate) Between #1/1/2005# And #12/31/2005#))
GROUP BY Manufacturer.ManufacturerID, Manufacturer.ManufacturerName
ORDER BY Avg([Discount]/[TotalList]) DESC;

5. On average, do cranks for mountain bike weigh more or less than cranks for road bikes?
SELECT Component.Road, Component.Category, Avg(Component.Weight) AS AvgOfWeight
FROM Component
GROUP BY Component.Road, Component.Category
HAVING (((Component.Category)="crank"));