Preliminary work for this Project

$1.$ Use the CREATE and SELECT SQL statements to Create a Temporary table as a full copy $($all rows & columns$)$ of the table ORDERS. Name the new table ORDERS$\_$COPY.

$2.$ Display the content of the new table.

$3.$ Write a SQL statement to convert the column ORDER$\_$MODE of your ORDERS$\_$COPY table into Capital letters $($use the built$-$in function UPPER$).$

$4.$ Write a SQL statement to check, by COUNTing, if there are NULLs in the SALES$\_$REP$\_$ID column of the table ORDERS$\_$COPY.

$5.$ Display the rows having null values in the SALES$\_$REP$\_$ID column.

$6.$ Write a SQL statement to replace all NULLs in the column SALES$\_$REP$\_$ID of the table ORDERS$\_$COPY with $999($as an ID for a vitual Sales Representative$).$

$7.$ Display the content of the table ORDERS$\_$COPY.

$8.$ Run the statement SELECT COUNT$($PROMOTION$\_$ID$)$ FROM ORDERS$\_$COPY; Note that you get $0$ althought there are $105$ rows with NULL in the column PROMOTION$\_$ID$.$

$9.$ Explain why you get zero.

$10.$ Write a SQL statement to replace all nulls in the PROMOTION$\_$ID column of the table ORDERS$\_$COPY with $0($zero$).$

$11.$ Re$-$run the statement SELECT COUNT$($PROMOTION$\_$ID$)$ FROM ORDERS$\_$COPY; Explain why this result value.

You need table ORDERS$\_$COPY to complete your Project $3.$

Project Tasks.

You want to load a Star Schema as shown above. To do this you must use the tables that you have prepared in previous project $-$ T$\_$DATE, CUSTOMERS$\_$COPY, and ORDERS$\_$COPY.

$1.$ The OE database does not have a SALESMAN table, we need to CREATE one called T$\_$SALESMAN $($SALES$\_$REP$\_$ID$,$ SNAME$)$ from the OE$.$ORDERS.SALES$\_$REP$\_$ID; its columns are:

$$ SALES$\_$REP$\_$ID unique values. Replace Null IDs with $999$; it is better than ignore Nulls in analysis. Do not define a PK because the table has too few rows.

$$ SNAME is a new column. Unfortunately, we do not have the names of the sales representatives in the OE database. To overcome this problem and facilitate filling the new table, set the SNAME as the concatenation of the SALES$\_$REP$\_$ID and the constant $$NAME$($for example, Salesman $153$ will have name $$NAME$-153....)$; note this tip is just for the lab work but does not apply is real situations.

You should get the following content for T$\_$SALESMAN check attachment. CCDS$-221$ Data Warehouse

$\begin{array}{cc}SALE{S}_{R}E{P}_{\text{I}}D& \text{S}\text{N}\text{A}\text{M}\text{E}\\ 153& \text{N}\text{A}\text{M}\text{E}-153\\ 154& \text{N}\text{A}\text{M}\text{E}-154\\ 155& \text{N}\text{A}\text{M}\text{E}-155\\ 156& \text{N}\text{A}\text{M}\text{E}-156\\ 158& \text{N}\text{A}\text{M}\text{E}-158\\ 159& \text{N}\text{A}\text{M}\text{E}-159\\ 160& \text{N}\text{A}\text{M}\text{E}-160\\ 161& \text{N}\text{A}\text{M}\text{E}-161\\ 163& \text{N}\text{A}\text{M}\text{E}-163\\ 999& \text{N}\text{A}\text{M}\text{E}-999\end{array}$

$2.$ Create Dimensions

Use the CREATE with an embedded SELECT statement $($and tables T$\_$DATE, CUSTOMERS$\_$COPY, ORDERS$\_$COPY, and T$\_$SALESMAN$)$ to create and load the four dimensional tables D$\_$ORDERS, D$\_$CUSTOMERS, D$\_$TIME and D$\_$SALESMAN as they are described in the figure shows in the attachment.

For each Dimension, define the PK constraint and name it as follows:

$$PK$\_$D$\_$ORDERS Primary Kay for D$\_$ORDERS

$$PK$\_$D$\_$SALESMAN Primary Kay for D$\_$SALESMAN

$$PK$\_$D$\_$TIME Primary Kay for D$\_$TIME

$$PK$\_$D$\_$CUSTOMERS Primary Kay for D$\_$CUSTOMERS

$3.$ Create Fact Table

CREATE the fact table F$\_$ORDERS $($F$\_$ORDERS$\_$ID$,$ ORDER$\_$AMOUNT,...Complete with the other columns....$).$

$4.$ The PK of the fact table F$\_$ORDERS could be either the concatenation of all FKs referencing the dimensions linked to the fact, or the UNIQUE column F$\_$ORDERS$\_$ID$.$ Define a PK constraint called PK$\_$F$\_$ORDERS$\_$ID on the column F$\_$ORDERS$\_$ID$.$

$5.$ The Fact table F$\_$ORDERS references the four dimensions D$\_$TIME, D$\_$CUSTOMERS, D$\_$ORDERS and D$\_$SALESMAN. Define all its Foreign keys $($Follow the naming convention: FK$\_$ORDERS$\_$TO$\_$DATE, $.....)$

$6.$ Create an Oracle Sequence SEQ$\_$F$\_$ORDERS starting with $1.($You will use this sequence to populate F$\_$ORDERS$\_$ID primary key$).$

$7.$ Develop SQL statement$($s$)$ to load F$\_$ORDERS. Note that the measure ORDER$\_$AMOUNT is in EURO and you extract it from the table ORDERS$\_$COPY.

$8.$ Display the SUM of the column ORDER$\_$AMOUNT from the fact F$\_$ORDERS.

$9.$ Display the Turnover $($sum of ORDER$\_$AMOUNT$)$ by Customer $($ID$\_$C$,$ C$\_$FName$)$ and by Year.

$10.$ COUNT the number of ORDERS having an amount greater that a given value $($choose a value$).$

$11.$ Display the percentage of amount of DIRECT Orders and ONLINE Orders.