1. Retrieve customer names and number of orders placed; display the results in o
ID: 3787931 • Letter: 1
Question
1. Retrieve customer names and number of orders placed; display the results in order of “importance” as calculated by total orders placed.
2. Modify the query in #1 to also include the total dollar value of all orders placed by each customer; return the results in order of total dollar value of all orders.
3. Retrieve the total dollar amount of all orders placed on 10/24/2004.
4. Retrieve the number and last name of all sales reps who represent at least one customer who is from Florida or Hawaii. No rep should be listed multiple times in the results.
5. Retrieve the number and last name of all sales reps who do not represent any customers who are from Florida or Hawaii.
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 SalespersonExplanation / Answer
Please find below the answer of first five queries:
1. SELECT C.CUSTNAME, COUNT(SO.SOID) AS NumberOfOrders
FROM CUSTOMER C
INNER JOIN SALESORDER SO
ON C.CUSTOMERID=SO.CUSTOMERID
GROUP BY C.CUSTNAME ORDER BY 2
2. SELECT C.CUSTNAME, COUNT(SO.SOID) AS NumberOfOrders,SUM(P.PRICE) AS TotalValueOfOrders
FROM CUSTOMER C
INNER JOIN SALESORDER SO
ON C.CUSTOMERID=SO.CUSTOMERID
INNER JOIN ORDERLINE OL
ON SO.SOID=OL.SOID
INNER JOIN PRODUCT P
ON SO.PRODUCTID=P.PRODUCTID
GROUP BY C.CUSTNAME ORDER BY 2
3. SELECT SUM(P.PRICE) AS TotalValueOfOrders
FROM CUSTOMER C
INNER JOIN SALESORDER SO
ON C.CUSTOMERID=SO.CUSTOMERID
INNER JOIN ORDERLINE OL
ON SO.SOID=OL.SOID
INNER JOIN PRODUCT P
ON SO.PRODUCTID=P.PRODUCTID
WHERE SO.ORDERDATE='10-24-2004'
4. SELET DISTINCT SP.LNAME AS LastName,COUNT(SP.SALESPERSONID) AS CountofSalesPersons
FROM SALESPERSON SP
INNER JOIN CUSTOMER C
ON SP.SALESPERSONID=C.SALESPERSON ID
WHERE C.STATE IN ('FL','HI')
5. SELET DISTINCT SP.LNAME,COUNT(SP.SALESPERSONID) AS CountofSalesPersons
FROM SALESPERSON SP
INNER JOIN CUSTOMER C
ON SP.SALESPERSONID=C.SALESPERSON ID
WHERE C.STATE NOT IN ('FL','HI')
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.