Write a query to return sales totals and quantity sold totals grouped by promotions id and product id: Business Intelligence Report, NCI, Ireland

Tasks

• Write a query to return sales totals and quantity sold totals grouped by promotions id and product id (no SQL extensions to group by required here). Exclude promotion id 999 as on examining the data this is the identification number for no promotion. Example output:

• Create a Materialised View called PROD_PROMO_SALES_MV that is based on the query you wrote above in question 1.
  Show the materialized view being used by query rewrite by noting the explained plan cost and table access.
• Write a query to return sales totals and quantity sold totals grouped by promotions name and product name.
• Does query 3 use the materialized view.? If it doesn’t then explain why query 3 does not use the materialized view PROD_PROMO_SALES_MV.
• Create the promotion dimension to allow query 3 and similar queries to use
  PROD_PROMO_SALES_MV.
• Rerun Query 3 again. Does it use the materialized view PROD_PROMO_SALES_MV?
• Management requires you to write a report that shows total sales by quarter for customers that have spent more than 60,000 for the specified year 2001. The report should show a ROLLUP of sales by quarter. It should resemble the following report values for current data.

Experiment with the Ranking Function to rank sales based on each quarter. Document your observations.