University of Jeddah Academic Year 2020/2021

College of Computer Science & Engineering IST Dept.

CCDS-221 Data Warehouse Lab # 4

ETL Process

Derive data using Correlated statements, Grouping and Aggregate Functions

Statement purpose

To understand how to prepare data (in the staging area) by adding new columns and populate them using simple or Correlated SQL statements along with built-in aggregate functions.

Activity Outcomes

Students will be able to derive new values or transform existing values and then store them in columns. They use SQL, PL/SQL, for grouping and using aggregate functions.

Instructor Note

Implement all the questions below and submit, for each one, the code and its execution result (as a screenshot) displayed by Oracle.

Use the OE Database schema available at the link https://livesql.oracle.com to answer.

Note. The SELECT privilege is GRANTed to PUBLIC on the tables so that you prefix each table of this DB by its creator (OE) in SQL statements (You can also create a synonym).

Before using the tables, Display the content of each one: CUSTOMERS, ORDERS and ORDER_ITEMS. A sample of the content of these tables is in the appendix 1.

Answer the following questions.

For analysis purposes, Decision-Makers want to classify the Customers according to the Turnover of each one according to the following:

	The Turnover of a customer is the total amount of all his Orders. This data is not in the DB tables; therefore, you need to calculate it in order to determine the Customer Type.
	The Type of a customer depends on his Turnover. We expect three types of customers noted A, B and C. The Type is: o A if the Turnover Avg_Turnover (where Avg_Turnover is the Average Turnover of all customers). o B if 0.5 * Avg_Turnover Turnover < Avg_Turnover, and o C otherwise

University of Jeddah Academic Year 2020/2021

College of Computer Science & Engineering IST Dept.

CCDS-221 Data Warehouse

To calculate the Turnover and the Type of customers, follow the data preparation steps below:

1. CREATE a table CUSTOMER_COPY by copying the CUSTOMER table.
2. Add a column TUROVER NUMBER (12, 2) to the CUSTOMER_COPY table.
3. Write SQL correlated statements to populate the TUROVER column.
4. Display the entire content of the CUSTOMER_COPY table.
5. Add the column CUST_TYPE CHAR (1) to the CUSTOMER_COPY table.

To populate the column CUST_TYPE with the appropriate TYPE, follow the steps below:

6. Implement a PL/SQL block using the IF and UPDATE statements to set the CUST_TYPE column value to A, B or C according to the rule explained above.
7. Display the whole content of the CUSTOMER_COPY table.
8. Display the number of customers, write it here.........
9. Count the number of Customers by CUST_TYPE, write it below:

A B ..

C

Check manually the number of customers is the total of customers of all types. Save your session as Lab4.sql, download and clean the script file.