1. Write a SQL statement that selects these columns: The count of the number of products in the Products table The average of the list_price columns in the Products table (4 points) 2. Write a SQL statement that returns one row for every category that has products with these columns: The category_name column from the Categories table The count of the products in the Products table per Category The list price of the cheapest product in the Products table per Category Sort the result set so the category with the most products appears first. (6 points) 3. Write a SQL statement that returns one row for each category and calculate total sum and total discount of the products sold in that category: The category_name column from the Category table Distinct product count that was sold in each category The Sum of the (Item Price multiplied by the quantity) in the Order_Items table per Category The Sum of the (Discount amount multiplied by the quantity) in the Order_Items table per Category

Sort the result set in descending sequence by the item price total for each category. (8 points) Hint: you will need to join tables: categories, products and order_items tables 4. Write a SQL statement that returns one row for every customer that has orders with these columns: The first_name from the Customers table A count of the number of orders Show the most expensive item_price that was purchased per customer Return only those rows where the customer has more than 1 order. Sort the result set in descending sequence by the highest priced item column (8 points) HINT You will need to join 3 tables: orders, order_items and customers 5. Modify the solution to exercise 4 so it only counts and finds max priced line items that have an item_price value thats greater than 300. (8 points) 6. Write a SQL statement that answers this question: What is the total amount ordered by each customer? Return these columns: Customer email address from the Customer table The total ship amount for each customer in the Orders table Show only data when total ship amount is greater than 5 Use the WITH ROLLUP operator to include a row that gives the grand total or the ship amount. (8 points) 7. Write a SQL statement that answers this question: Which product was purchased by more than ones per customer Product_name product name