Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

MySQL Help #8-10 Please 8. Write a SELECT statement that answers this question: What is the total quantity purchased for each product within each category?

MySQL Help

#8-10 Please

image text in transcribed

8. Write a SELECT statement that answers this question: What is the total quantity purchased for each product within each category? Return these columns: The category_name column from the category table The product_name column from the products table The total quantity purchased for each product with orders in the Order_items table Use the WITH ROLLUP operator to include rows that give a summary for each category name as well as a row that gives the grand total. Use the IF and GROUPING functions to replace null values in the category_name and product_name columns with literal values if they're for summary rows. 9. Write a SELECT statement that uses an aggregate window function to get the total amount of each order. Return these columns: The order_id column from the Order_items table The total amount for each order item in the Order_items table (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) The total amount for each order Sort the result set in ascending sequence by the order_id column. 10. Modify the solution to exercise 9 so the column that contains the total amount for each order contains a cumulative total by item amount. Add another column to the SELECT statement that uses an aggregate window function to get the average item amount for each order. Modify the SELECT statement so it uses a named window for the two aggregate functions. 8. Write a SELECT statement that answers this question: What is the total quantity purchased for each product within each category? Return these columns: The category_name column from the category table The product_name column from the products table The total quantity purchased for each product with orders in the Order_items table Use the WITH ROLLUP operator to include rows that give a summary for each category name as well as a row that gives the grand total. Use the IF and GROUPING functions to replace null values in the category_name and product_name columns with literal values if they're for summary rows. 9. Write a SELECT statement that uses an aggregate window function to get the total amount of each order. Return these columns: The order_id column from the Order_items table The total amount for each order item in the Order_items table (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) The total amount for each order Sort the result set in ascending sequence by the order_id column. 10. Modify the solution to exercise 9 so the column that contains the total amount for each order contains a cumulative total by item amount. Add another column to the SELECT statement that uses an aggregate window function to get the average item amount for each order. Modify the SELECT statement so it uses a named window for the two aggregate functions

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Oracle Database Administration The Essential Reference

Authors: Brian Laskey, David Kreines

1st Edition

1565925165, 978-1565925168

More Books

Students also viewed these Databases questions