Do OLAP with SQL Use the SQL ROLLUP & CUBE to generate & store a 3D cube with hi

1. SELECT COUNT(*) FROM Product WHERE Product_Category; 2. SELECT COUNT(*) FROM Supplier WHERE State;

ID: 3762648 • Letter: D

Question

Do OLAP with SQL

Use the SQL ROLLUP & CUBE to generate & store a 3D cube with hierarchies based on the tables in the database given by the description:

Supplier(Supp_ID, Name, City, State)

Consumer(Con_ID, Name, City, State)

Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging)

Offers(Supp_ID, Prod_ID, Quantity, Price)

Requests(Con_ID, Prod_ID, Quantity, Price)

Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price)

Given the Transactions_Cube stored abov solve the queries:

1) Aggregate by product category?

2) Aggregate by supplier states?

3) # of trasactions btwn supplier-city-consumer-city pairs?

4) Volum of each product sold in WI?

5) Volum of sales by product & supplier state?

6) Volum of computer sales by suppliers in WI?

7) Volum of auto sales by suppliers in Wisconsin to consumers in Illinois?

8) Volum of truck sales by suppliers in Madison to consumers in Illinois?

9) Volum of each product sold by supplier Redfield to consumers in

Chicago?

10) Quantity of milk sold by supplier Redfield to consumers in Chicago?

11)4 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?

SELECT COUNT(*)

FROM Product

WHERE Product_Category;

SELECT COUNT(*)

FROM Supplier

WHERE State;

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.