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)

Tb_Date(DateKey, DateYear, DateMonth, DateMonthName, DateWeek, DateDayOfMonth, DateDayOfWeek, FiscalWeek, IsWeekEnd, IsHoliday, HolidayName)

Tb_Time(TimeKey, TheHour, IsLunchHour, IsBusinessHour, ShiftNumber)

Use the query below to create the Tb_Transactions_Cube table and populate with the rows of the cube:

SELECT DISTINCT S.Name "Supplier Name",

S.City "Supplier City", S.State "Supplier State", C.Name "Consumer Name", C.City "Consumer City", C.State "Consumer State", P.Name "Product Name",

SUM(Quantity) "Total Transactions Quantity",

COUNT(Tran_ID) "Number of Transactions"

INTO Tb_Transactions_Cube

FROM Tb_Supplier S, Tb_Consumer C, Tb_Product P, Tb_Transactions T

WHERE S.Supp_ID=T.Supp_ID AND

C.Con_ID=T.Con_ID AND P.Prod_ID=T.Prod_ID

GROUP BY CUBE((S.State, S.City, S.Name),

(C.State, C.City, C.Name), P.Name),

ROLLUP(S.State, S.City, S.Name), ROLLUP(C.State, C.City, C.Name)

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 and quantity Wisconsin suppliers sold of that 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 each auto consumer in Illinois?

8) Quantity of each product sold by each supplier in Madison to each consumer in Illinois?

9) Quantity of each product sold by supplier Bernstein to consumers in Chicago?

10) Quantity of milk sold by supplier Bernstein to each of his milk consumers in Chicago?

11) (Extra Credit 2%) 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)?