USING SQL I ANSWERED THE FIRST 4 AND I NEED WITH THE REST FROM 5-9

1. CREATE TABLE T_ORDER_DATE AS (SELECT ORDER_DATE FROM OE.ORDERS);

We use CREATE TABLE ... AS statement to create a new table T_ORDER_DATE using the required structure and data from OE.ORDERS, specified by the SELECT query.

2. SELECT ROWID, ORDER_DATE FROM T_ORDER_DATE;

Along with ORDER_DATE column, we can also select pseudocolumn ROWID in SELECT to get ROWID values for all records.

3. SELECT COUNT(DISTINCT ORDER_DATE) FROM T_ORDER_DATE;

We can use aggregate function COUNT to get row count from T_ORDER_DATE. Using DISTINCT ORDER_DATE within COUNT, we will get the count of only distinct values of ORDER_DATE.

4. SELECT ORDER_DATE, COUNT(1) FROM T_ORDER_DATE GROUP BY ORDER_DATE;

Using GROUP BY, we group the recordset of T_ORDER_DATE on the basis of ORDER_DATE values. We then fetch ORDER_DATE as well as row count (specified by COUNT(1)) for each ORDER_DATE value in SELECT.

Answer the following questions 1. Use the CREATE and SELECT statements to create one-column temporary table T_ORDER_DATE (ORDER_DATE DATE) containing ORDER_DATE values (formatted as DD-MM-YYYY) from the OE.ORDERS table. 2. Display the whole content of T_ORDER_DATE including the ROWID pseudo-column. Note that ROWID values are all distinct (even for identical rows). Check the duplicates using one of the following 2 methods: 3. Write a SELECT to display the number of distinct values (You should get 70). 4. Display the number of occurrences of each ORDER_DATE value. Deduplicate. The Deduplication is a frequent ETL task to prepare Non-Redundant data for loading dimensions and/or facts. It keeps a single value of each repeated value. 5. Remove duplicates from T_ORDER_DATE: (you can use ROWID). If the operation is tedious, skip to the next question to do it otherwise. 6. The T_ORDER_DATE table could be created (from the beginning) without redundant rows in order to avoid deduplicating it. DROP T_ORDER_DATE. Rewrite your answer to question 1 to obtain directly distinct values of dates in T_ORDER_DATE. 7. Whatever the method you have used so far, you have deduplicated table T_ORDER_DATE. Create a temporary table (assumed in the staging area) called T_DATE. Structure of the table T_DATE to create: T_DATE ( ORDER_DATE DATE, Day_NO NUMBER (1), Day_Name CHAR(10), Month_NO NUMBER (2), Month_Name CHAR(10), Quarter NUMBER (1), YYear NUMBER (4) ) You will need T_DATE to populate the TIME Dimension of your DW. 8. Populate the T_DATE table from T_ORDER_DATE (use the built-in functions TO_CHAR for extracting the Date elements and TO_NUMBER to convert into NUMBER). Note. All extracted values for CHAR fields (Day_Name and Month_Name) must be directly obtained in CAPITAL letters to avoid converting them in a further step. The syntax of the CHAR function is: Syntax: TO_CHAR( Date_value, 'format_mask' ) where the format_mask uses the following: Symbol Explanation YYYY 4-digit year YYY YY Y Last 3, 2, or 1 digit(s) of year. g MM MON or Mon Quarter of year (1, 2, 3, 4; JAN-MAR = 1). Month (01-12; JAN = 01). Abbreviated name of month in UPPERCASE or First letter in upper. Name of month, padded with blanks to length of 9 characters. Day of week (1-7). MONTH D Name of day in upper (DAY) or First letter upper (Day). DAY or Day DD Day of month (1-31). Day of year (1-366). DDD 9. Display the content of the T_DATE table (Now, you can DROP T_ORDER_DATE). Answer the following questions 1. Use the CREATE and SELECT statements to create one-column temporary table T_ORDER_DATE (ORDER_DATE DATE) containing ORDER_DATE values (formatted as DD-MM-YYYY) from the OE.ORDERS table. 2. Display the whole content of T_ORDER_DATE including the ROWID pseudo-column. Note that ROWID values are all distinct (even for identical rows). Check the duplicates using one of the following 2 methods: 3. Write a SELECT to display the number of distinct values (You should get 70). 4. Display the number of occurrences of each ORDER_DATE value. Deduplicate. The Deduplication is a frequent ETL task to prepare Non-Redundant data for loading dimensions and/or facts. It keeps a single value of each repeated value. 5. Remove duplicates from T_ORDER_DATE: (you can use ROWID). If the operation is tedious, skip to the next question to do it otherwise. 6. The T_ORDER_DATE table could be created (from the beginning) without redundant rows in order to avoid deduplicating it. DROP T_ORDER_DATE. Rewrite your answer to question 1 to obtain directly distinct values of dates in T_ORDER_DATE. 7. Whatever the method you have used so far, you have deduplicated table T_ORDER_DATE. Create a temporary table (assumed in the staging area) called T_DATE. Structure of the table T_DATE to create: T_DATE ( ORDER_DATE DATE, Day_NO NUMBER (1), Day_Name CHAR(10), Month_NO NUMBER (2), Month_Name CHAR(10), Quarter NUMBER (1), YYear NUMBER (4) ) You will need T_DATE to populate the TIME Dimension of your DW. 8. Populate the T_DATE table from T_ORDER_DATE (use the built-in functions TO_CHAR for extracting the Date elements and TO_NUMBER to convert into NUMBER). Note. All extracted values for CHAR fields (Day_Name and Month_Name) must be directly obtained in CAPITAL letters to avoid converting them in a further step. The syntax of the CHAR function is: Syntax: TO_CHAR( Date_value, 'format_mask' ) where the format_mask uses the following: Symbol Explanation YYYY 4-digit year YYY YY Y Last 3, 2, or 1 digit(s) of year. g MM MON or Mon Quarter of year (1, 2, 3, 4; JAN-MAR = 1). Month (01-12; JAN = 01). Abbreviated name of month in UPPERCASE or First letter in upper. Name of month, padded with blanks to length of 9 characters. Day of week (1-7). MONTH D Name of day in upper (DAY) or First letter upper (Day). DAY or Day DD Day of month (1-31). Day of year (1-366). DDD 9. Display the content of the T_DATE table (Now, you can DROP T_ORDER_DATE)