Question
you will use the my_guitar_shop database to answer the questions in this assignment. Please download the script file https://www.dropbox.com/s/o966abkh72nc3sn/create_my_guitar_shop.sql?dl=0 and build the database on your
you will use the my_guitar_shop databaseto answer the questions in this assignment. Please download the script file https://www.dropbox.com/s/o966abkh72nc3sn/create_my_guitar_shop.sql?dl=0 and build the database on your server before starting your work on the questions if you have not already done so. Please contact the instructors if you encounter problems downloading or running the database script.
You may lose points if you use more than one query for each of questions 1, 2, 3, 5, and 6, and more than two queries for question 4.
For this assignment, you are not allowed to use the LIMIT keyword to filter result rows in any of yourSELECT queries. You may lose points if you do so.
For this assignment, you are not allowed to use exact match conditions to filter result rows in any of your SELECT queries. For example, you cannot use ... WHERE last_name=Smith... in a SELECT query. You may lose points if you do so.
Q.5) (20 points) Write a SELECT statement that returns one row for each customer that has multiple orders. Display these columns: The email_address from the Customers table A count of the number of orders The total order amount for each customer (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.) The average order amount for each customer Return only those rows where the customer has more than 1 order. Sort the result set in descending sequence by the sum of the line item amounts. Round the values on the average order amount column, so that the values include only two decimal spaces. Result Grid Filter Rows: a search Export order_count order_total avg_order total email_address allan.sherwood@yahoo.com 3 frankwilson@sbcglobal.net 3 david.goldstein@hotmail.com2 2300.61 766.87 539.28 513.09 88.30 394.15
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started