Consider the following Data Warehouse tables Specify in SQL

Consider the following Data Warehouse tables: Specify in SQL and show the results of the following OLAP queries: Find total sales per city. Find total sales per state. Specify an SQL expression to perform the following roll up query: Given sales by cities get sales by states. Show the result of this query. Specify a linear algebra expression to perform the following drill down query: Given sales by states get sales by cities. Show the result of the drilling down using Least Squares Method Show a linear algebra expression and the result of the drilling down using an assumption that total sale in Los Angeles is twice as big as in San Francisco. Compute RMSE to for cases (b) and (c). Show the result of the drilling down using Least Squares Method Show a linear algebra expression and the result of the drilling down using an assumption that total sale in Los Angeles is twice as big as in San Francisco. Compute RMSE to for cases (b) and (c). Show the result of the dril

Solution

Given 3 tables LocDim, Fact, ProdDim

LocDim(locid, city, state)   - Primary Key (Locid)

Fact(locid, prodid, sale)   - LookupTable

ProdDim(prodid, pname, pcategory) - Primary Key (prodid)

1. select locid,sale from Fact group by locid

This Query will select Locid, sale after grouping the results by locid. So it will show the count of products sold in the particular location id.

2. select state, sale from Fact inner join LocDim on Fact.locid = LocDim.locid group by LocDim.state

This Query will select state, Sale after joining the two tables on Locid, if we group the results by state, then it will count the products sold in the particular state.