IN LIVESQL.ORACLE

Let us continue with the OE Database. Answer the following questions During the ETL process, you may need to convert source data values from one unit of measure to another; this occurs mainly when you load your DW from heterogeneous data sources. In this lab, you will develop PL/SQL parameterized conversion functions. Temperature Conversion To convert a temperature value measured in degree Fahrenheit (F) to Celsius (C) use the formula: (F 32) *5/9 = C (1) Examples 32 F corresponds to 0 C 100 F converts into C as follows: (100 F 32) *5/9 = 37.78 C, and using the same formula (1) 4 F = -15.56 C 1. Develop a PL/SQL function called Fahrenheit_To_Celsius 1 Page CCDS-221 Data Warehouse Fahrenheit_To_Celsius (P_Temperature NUMBER /* p_Temperature is in Fahrenheit */ that accepts as input a Fahrenheit value (p_Temperature) and returns its correspnding Celsius temperture. Important Tip. In order to force the function to succeed even when an exception is raised, include a generic exception handler that must return a special value (that could never be a temperature like - 9999). Note that a stored function that does not use a DB table is callable in a SELECT statement as if it were a built-in function. In the next questions, you will call your developed functions as expression in a SELECT for testing. 2. Transform the function into a stored function (keep the same name of function as previous). 3. Test the function within a SELECT statement. 2. Transform the function into a stored function (keep the same name of function as previous). 3. Test the function within a SELECT statement. Celsius_To_Fahrenheit (P_Temperature NUMBER /* p_Temperature is in Celsius */ 4. Develop a PL/SQL function called Celsius_To_Fahrenheit that accepts as input a Celsius value ( p_Temperature ) and returns its corresponding Fahrenheit temperature. 5. Transform the function into a stored function (keep the same name of function). 6. Test the function by calling it (as an expression) in a SELECT statement. Standardizing Volume by Conversion (Gallon-Liter) To convert Liters to Gallons, multiply the liter value by 0.26417205236 or divide by 3.785411784. 2 Page Example 20 Liters = 20 * 0.26417205236 = 5.28344105 Gallons. Liter_To_Gallon (p_Volume NUMBER ( p Volume is a volume in Liter to convert to Gallon ) 7. Develop the following PL/SQL function called Liter_To_Gallon > Keep the comment text line in the function declaration for a better usability & maintenance. Gallon_To_Liter (p_Volume NUMBER, P_From_TO NUMBER DEFAULT 1 p Volume is a volume in Gallon to convert to Liter ) 8. Develop the following PL/SQL function 9. Transform the function into a stored function (keep the same function name). 10. Test the function by calling it (as an expression) in a SELECT statement. Standardizing an Amount of Money by Conversion to Standard Currency Suppose the column ORDERS.ORDER_TOTAL is the amount of the order expressed in the currency of 10. Test the function by calling it (as an expression) in a SELECT statement. Standardizing an Amount of money by Conversion to Standard Currency Suppose the column ORDERS.ORDER_TOTAL is the amount of the order expressed in the currency of the country (NLS_TERRITORY) of the Customer; i.e., when the CUSTOMER.NLS_TERRITORY is: CHINA the amount is in CHY, INDIA the amount is in INR, GERMANY and ITALY the amount is in EUR (Euro), ... In fact, there are eight (8) different countries in the CUSTOMERS.NLS_TERRITORY column (you can display). Before summing the amounts (of the column ORDERS.ORDER_TOTAL) we need to convert them to the same currency. Let us convert to Euro for example. For this purpose, we prepare the following table of change rates of currencies to Euro: CURRENCY RATES TABLE CUR_SIGN CNY COUNTRY CHINA CUR_QTY 1 EURO_RATE 0.13 0.011 0.92 INR 1 INDA SWITZERLAN CHE 1 3 Page CCDS-221 Data Warehouse THB THAILAND 1 0.027 JPY 1 0.0079 JAPAN ITALY EUR 1 1 USD AMERICA 1 0.81 EUR GERMANY 1 1 11. Execute the script BuildCurrency_Rates.sql to create the CURRENCY_RATES table and insert the 8 lines above. 12. Use the CREATE and SELECT statements to create, from the tables ORDERS and CUSTOMERS, a new table called ORDERS_IN_EURO (ORDER_ID, CUSTOMER_ID, COUNTRY, ORDER_TOTAL, ORDER_TOTAL_EURO). Note the COUNTRY is the value of NLS_TERRITORY column in the CUSTOMERS table. You should get 105 rows. 13. Write a PL/SQL function to populate the column ORDER_TOTAL_EURO that will contain the total of the order converted to EURO. 14. Check the content of some rows in the ORDER_TOTAL_EURO is correct. 15. Save your session as Lab5.sql, download and clean the script (i.e., keep valid statements only) f prospective use