Objectives:
a. Demonstrate use of SQL to perform OLAP operations; Estimated time to complete: 40 min reading +45-50 min.
solve queries Points:
Tasks:
In this assignment we are going to use the SQL ROLLUP and CUBE operators to generate and store a three dimensional cube with hierarchies 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)
Given the Tb_Transactions_Cube stored above solve the following queries:
1) Aggregates by combinations of supplier name and product name?
2) Aggregates by supplier states?
3) Number of transactions between supplier-city-consumer-city pairs?
4) Name of each product sold in Wisconsin and quantity of sales for the product?
5) Quantity of sales aggregated by product and supplier state?
6) Quantity of computer sales aggregated by suppliers in Wisconsin?
7) Quantity of auto sales by each supplier from Wisconsin to consumers in Illinois?
8) Quantity of auto sales by each supplier in Madison to consumers in Illinois?
9) Quantity of each product sold by supplier Bernstein to consumers in Chicago?
10) Quantity of milk sold by supplier Bernstein to consumers in Chicago?
11) For each product list quantity sold by suppliers in Madison to consumers in Chicago versus quantity sold by suppliers in Chicago to consumers in Madison (result columns will be: product name, quantity Madison_Chicago, quantity Chicago_Madison?
Return:
Please post your solutions to the queries above in properly formatted SQL syntax (each SQL clause on its own line and with capitalized keyword and operators) in the D2L dropbox created for this assignment.
Attachment:- DemarestTables.xlsx
Attachment:- Instructions.rar