Question

SQL PROGRAMMING

Exercises

1. Create a function named MyCube that will take a NUMBER as in input and RETURN the cubed value of the input as a NUMBER. To calculate the CUBE, multiply the input number by itself three times (eg. num * num * num). Once you have the function created, test it using the following SQL statement:

SELECT MyCube(3) FROM dual;

MYCUBE (3) Download CSV

*The dual table is a built in table that you can use to test your functions.

2. Create a function called SalesTax that will take a NUMBER as an input, multiply the number by 0.06, round the result to 2 decimal places, and return the result as a NUMBER. (e.g. ROUND(num,2) )

Test the function using the following SQL:

select ORDER_ID, ORDER_TOTAL, SalesTax(ORDER_TOTAL) AS SALES_TAX from oe.orders

3. Below is a function that will determine if an order has shipped or not. However, the developer is having problems making it work.  

BEGIN

CREATE OR REPLACE FUNCTION ShipStatus

(status IN NUMBER)

RETURN VARCHAR

AS

statusText VARCHAR(20);

IF (status < 5) THEN

     statusText = 'Not Shipped';

ELSE

     statusText = 'Shipped';

END IF;

  RETURN statusText;

END;

Fix the four (4) errors and then test the function using the SQL statement below

select order_id, order_date, order_total, ShipStatus(order_status) from oe.orders;

Answer 1

According to HomeworkLib policy we answer the first, but O answer 2 question for you.

Answer 1)
The function is done in PL/SQL, which is the SQL programming enviourment. Function name is not case sensitive. I had develop in SQL Command line, but it can be execute on any Oracle developer tools.I take c number as input and return c1 for the output.And c is multiplied 3 times and stored in c1.

create or replace function mycube(c number)
return integer
as
c1 number;
begin
c1:=c*c*c;
return c1;
end;

Screenshot

SQL> create or replace function mycube(c number) return integer 2 3 as c1 number; 5 begin 6 c1:-c*c*c; 7 return c1; 8 end; 4 9 Function created. SQL> select MyCube (3) from dual; MYCUBE (3) 27

Answer 2) Here we use the number as the return type for decimal values. And I use the round function to round upto 2 decimal places.I take tax as input number. In PL/SQL number can be treated as decimal.

create or replace function salestax(tax number)
return number
as
st decimal(10,2);
begin
st:=round(tax*0.06,2);
return st;
end;

Screenshot

SQL> create or replace function salestax (tax number) return number 2 3 as st decimal(10,2); 5 begin st:round(tax*0.06,2); return st; end; 4 б 7 8 9 10 Function created. SQL> select order_id, order_total,salestax (order_total) from orders; ORDER_ID ORDER_TOTAL SALESTAX(ORDER_TOTAL) 2458 78279.6 4696.78 2454 6653.4 399.2 SQL> select order_id, order_total, salestax (order_total) as SALESTAX from orders; ORDER_ID ORDER_TOTAL SALESTAX 2458 78279.6 4696.78 2454 6653.4 399.2