Question
Using MYSQL Write a SELECT statement that returns one row for each customer that has orders with these columns: The email_address column from the Customers
Using MYSQL
- Write a SELECT statement that returns one row for each customer that has orders with these columns:
The email_address column from the Customers table
The sum of the item price in the Order_Items table multiplied by the quantity in the Order_Items table
The sum of the discount amount column in the Order_Items table multiplied by the quantity in the Order_Items table
Sort the result set in descending sequence by the item price total for each customer.
- Write a SELECT statement that returns one row for each customer that has orders with these columns:
The email_address column from the Customers table
A count of the number of orders
The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)
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.
- Modify the solution to exercise 2 so it only counts and totals line items that have an item_price value thats greater than 200.
customer_id | email_address | password | first_name | last_name | shipping_address_id | billing_address_id |
1 | allan.sherwood@ yahoo.com | 650215acec746f0e32bdfff387439eefc1358737 | Allan | Sherwood | 1 | 2 |
2 | barryz@ g | 3f563468d42a448cb1e56924529f6e7bbe529cc7 | Barry | Zimmer | 3 | 3 |
3 | christineb@ solarone.com | ed19f5c0833094026a2f1e9e6f08a35d26037066 | Christine | Brown | 4 | 4 |
4 | david.goldstein@ hotmail.com | b444ac06613fc8d63795be9ad0beaf55011936ac | David | Goldstein | 5 | 6 |
5 | erinv@ g | 109f4b3c50d7b0df729d299bc6f8e9ef9066971f | Erin | Valentino | 7 | 7 |
6 | frankwilson@ sbcglobal.net | 3ebfa301dc59196f18593c45e519287a23297589 | Frank Lee | Wilson | 8 | 8 |
7 | gary_hernandez@ yahoo.com | 1ff2b3704aede04eecb51e50ca698efd50a1379b | Gary | Hernandez | 9 | 10 |
8 | heatheresway@ mac.com | 911ddc3b8f9a13b5499b6bc4638a2b4f3f68bf23 | Heather | Esway | 11 | 12 |
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