6 For each salesperson who made sales retrieve the total val

6. For each salesperson who made sales, retrieve the total value of sales. Include any additional fields that would make it easier for someone to use the output.

7. Modify the query in #6 to also include salespeople who DID NOT make sales.

8. What is the total dollar amount of all orders from customers who live in California?

9. Retrieve the last name, first name, and number of customers for the salesperson(s) with the highest number of customers.

10. Retrieve the last name and first name of any salesperson(s) who have not sold an Entertainment Center (note: make sure your spelling matches the spelling in the database). Order result(s) alphabetically by last name.

11. Retrieve product names, prices, and category name (renaming the product name field “Product_Name”). For each product, also include the average price for the category to which it belongs.

MIS/ACC 3353 Homework 2-Multi-Table SQL Write SELECT statements and provide the output for the following queries based on the ERD below (the database is available through D2L). Turn in a single PDF or Word document that shows for each problem the SQL query (b) output generated by that query. In creating your queries, please use only the information provided to you in the problem. Make sure that all columns are properly named. Customer SalesOrder Salesperson

Solution

6.

on customer.customerID=seectorder.customerID)

on SalesOrder.soID=OrderLine.soID)

on OrderLine.ProductID=product.ProductID)

GROUP BY customer.customerID, SalesOrders.soID, OrderLineolID, product.productID, OrderLine.qty, Product.price