USE OLAP
--Fact Table
SELECT top 20 *
FROM sales

--Geographic Dimension
SELECT top 20 *
FROM countries

--Product Dimension
SELECT top 20 *
FROM products

--Chronological Dimension
SELECT top 20 *
FROM times

--average cost of product from each country.
SELECT product_type, avg(product_cost) as average_cost_price
FROM products
GROUP BY product_type

--Try joining product to year
SELECT t.year,avg(product_cost)
FROM times as t JOIN product as p ON --What can we join on.

--Even if we join through year we will get a strange result
SELECT t.year,avg(product_cost)
FROM times as t JOIN sales as s ON s.date=t.date
                JOIN products as p ON s.product_code=p.code
GROUP BY t.year

SELECT avg(product_cost) FROM products

--Product lines and types
SELECT distinct product_line, product_type
FROM products

--Create product_types table
SELECT distinct product_line, product_type
INTO  product_types
FROM products

--Create a new_products_table
SELECT code,product_type,product,product_cost
INTO new_products_table
FROM products

SELECT code,product_type,product,product_cost
FROM new_products_table


--quantity_sold by product_line without product_types
SELECT product_line
       ,sum(s.quantity_sold) as quantity_sold
FROM sales as s JOIN products as p ON s.product_code=p.code
GROUP BY product_line

--quantity_sold by product_line with product_types
SELECT pt.product_line
       ,sum(s.quantity_sold) as quantity_sold
FROM sales as s JOIN products as p ON s.product_code=p.code
                JOIN product_types as pt ON pt.product_type=p.product_type
GROUP BY pt.product_line