Estimated time to complete: 2 hours #1 Based on the tables in the database given by the description below: Tb_Supplier(Supp_ID, Name, City, State) Tb_Consumer(Con_ID, Name, City, State) Tb_Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging) Tb_Offers(Supp_ID, Prod_ID, Quantity, Price) Tb_Requests(Con_ID, Prod_ID, Quantity, Price) Tb_Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price) use SQL with GROUP BY, CUBE and ROLLUP to create a cube with the following characteristics: The dimensions of the cube are: Tb_Supplier and Tb_Product. Measure groups table is: Tb_Offers. Measure aggregates: SUM(Quantity), SUM(Quantity*Price), MAX(Price) , MIN(Price). Dimension hierarchies: Tb_Supplier: State > City > Name Tb_Product: Product_Packaging> Name Product_Category>Product_Line> Name b) Given the cube created at point a) solve the following queries using SQL: 1. Value of products offered by supplier and by product packaging? (2 points) 2. Volume of milk offered by each supplier in Wisconsin? (2 points) 3. Find the maximum price for each product offered in Madison? (5 points) 4. For each supplier city find the product offered in largest quantity? (8 points) 5. For each product find the city where it is offered at the lowest price? aBased on the tables in the database given by the description below: Tb_Supplier(Supp_ID, Name, City, State) Tb_Consumer(Con_ID, Name, City, State) Tb_Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging) Tb_Offers(Supp_ID, Prod_ID, Quantity, Price) Tb_Requests(Con_ID, Prod_ID, Quantity, Price) Tb_Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price) use SQL with GROUP BY, CUBE and ROLLUP to create a cube with the following characteristics: The dimensions of the cube are: Tb_Consumer and Tb_Product. Measure groups table is: Tb_Transactions. Measure aggregates: SUM(Quantity), SUM(Quantity*Price), MAX(Price) , MIN(Price). Dimension hierarchies: Tb_Consumer: State > City > Name Tb_Product: Product_Packaging> Name Product_Category>Product_Line> Name b) Given the cube created at point a) solve the following queries using SQL: 1. Value of products purchased by consumer and by product? (2 points) 2. Volume of gas purchased by each consumer in Wausau? (2 points) 3. Find the minimum purchase price for each product sold in Wausau? (5 points) 4. For each consumer find the cheapest product he/she purchased? (8 points) 5. Name of all consumers and volume of oil and milk each purchased (columns: consumer name, total quantity of oil – 0 if none, total quantity of milk – 0 if none)? (8 points)