Part 01

Lets try a query:

For ALL the orders report:

a. the date an order took place

b. the id of the products included in the order

c. the quantity ordered

d. PRODUCTSTANDARDPRICE

e. The calculated column: order_sales_price as PRODUCTSTANDARDPRICE* ORDEREDQUANTITY

For those orders that do not have an associated orderline assign a productid = -99 and quantity ordered = -99, PRODUCTSTANDARDPRICE = 0

Part 02

Lets create some tables:

1. Create a table named Product_Dim that holds the products and their product lines, including both ids and names. It should also hold a new column called Product_Key that will be the Primary key.

2. Insert data into product_Dim. Do not allow NULL values: substitute possible nulls with Undefined

Hint:

a. Create a sequence that you will use to populate the Product_Key:

CREATE SEQUENCE product_seq START WITH 1;

b. Write a query that selects the product and productline information from the operational database tables. Within this query you can also use the sequence as:

Select product_seq.nextval as product_Key, Product_ID, ..

From Product_T . ;

c. Use this query to insert the data as:

Insert INTO product_Dim

Select . ;

3. Insert into table Product_Dim a new record with:

productid = -99, productlineid = -99 and Undefined descriptions

4. Create a table named Sales_Fact with columns: Sales_date_Key, Product_Key, order_sales_price

5. Insert data into Sales_Fact

Hint: Modify the query of Part 01, so that you also select the corresponding DateKey from Date_Dim instead of order_date and the Product_Key from Product_Dim instead of Product_Id.

Be careful, when you are joining the tables you should take into account that sometimes the Product Id is NULL and in these cases you will be using the new Undefined record of Product_Dim.