(solution) 1. Write a SELECT statement that returns these columns: The count

 1 Write a SELECT statement that returns these columns:  The count of the number of orders in the Orders table The sum of the tax_amount columns in the Orders table 2 Write a SELECT statement that returns one row for each category that has products with these columns:  The category_name column from the Categories table The count of the products in the Products table The list price of the most expensive product in the Products table 3 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. 4 Write a SELECT statement that returns one row for each customer that has orders with these columns: The email_address 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 item ever ordered.  Another way to say this would be, show only those rows where the customer has an order history of ordering more than 1 item.   Sort the result set in descending sequence by the sum of the line item amounts. 5 Modify the solution to exercise 4 so it only counts and totals line items that have an item_price value that?s greater than 400. 6 Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns: The product name from the Products table The total amount for each product in the Order_Items (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) Use the WITH ROLLUP operator to include a row that gives the grand total.    Note: Once you add the WITH ROLLUP operator, you may need to use MySQL Workbench?s Execute SQL Script button instead of its Execute Current Statement button to execute this statement. 7 Write a SELECT statement that answers this question: Which customers have ordered more than one product? Return these columns: The email address from the Customers table The count of distinct products from the customer?s orders

Solution details:

STATUS

QUALITY

Approved

Sep 13, 2020

EXPERT

Tutor