Write store procedure called factorial that gets an integer number n and calculates and displays its factorial. You must use RECURSION to calculate the results.
Example:
0! = 1
1! = fact(1) = 1 2! = fact(2) = 2 * 1 = 2 3! = fact(3) = 3 * 2 * 1 = 6 . . . n! = fact(n) = n * (n-1) * (n-2) * . . . * 2 * 1
n! = fact(n) = n * (n-1)!

 

2. Write stored procedure called fibonacci that gets an integer number n and calculates the sum of the Fibonacci sequence up to that point. Example: f(0) = 0
f(1) = 1 f(2) = f(1) + f(0) = 1 + 0 = 1
f(3) = f(2) + f(1) = 1 + 1 = 2
f(4) = f(3) + f(2) = 2 + 1 = 3
...
f(n) = f(n-1) + f(n-2)

 

3. Every year, the company increases the price of all products in one category. For example, the company wants to increase the price (list_price) of products in category 1 by $5. Write procedure named update_price_by_cat to update the price of all products in a given category and the given amount to be added to the current price if the price is greater than 0. The procedure shows the number of updated rows if the update is successful.
The procedure gets two parameters:
• category_id IN NUMBER
• amount NUMBER(9,2)
To define the type of variables that store values of a table' column, u can also write:
vriable_name table_name.column_name%type;
The above statement defines a variable of the same type as the type of the table' column.
category_id products.category_id%type;
Or you need to see the table definition to find the type of the category_id column. Make sure the type of your variable is compatible with the value that is stored in your variable.
To show the number of affected rows the update query, declare a variable named rows_updated of type NUMBER and use the SQL variable sql%rowcount to set your variable. Then, print its value in your stored procedure.
Rows_updated := sql%rowcount;
SQL%ROWCOUNT stores the number of rows affected by an INSERT, UPDATE, or DELETE.

 

4. Every year, the company increase the price of products whose price is less than the average price of all products by 1%. (list_price * 1.01). Write stored procedure named update_price_under_avg. This procedure do not have any parameters. You need to find the average price of all products and store it into a variable of the same type. If the average price is less than or equal to $1000,
update products' price by 2% if the price of the product is less than the calculated average. If the average price is greater than $1000, update products' price by 1% if the price of the product is less than the calculated average. The query displays an error message if any error occurs. Otherwise, it displays the number of updated rows.

 

5. The company needs a report that shows three category of products based their prices. The company needs to know if the product price is cheap, fair, or expensive. Let's assume that
▪ If the list price is less than
o (avg_price - min_price) / 2
The product's price is cheap.
▪ If the list price is greater than
o (max_price - avg_price) / 2
The product' price is expensive.
▪ If the list price is between
o (avg_price - min_price) / 2
o and
o (max_price - avg_price) / 2
o the end values included
The product's price is fair.
Write procedure named product_price_report to show the number of products in each price category:
The following is a sample output of the procedure if no error occurs:
Cheap: 10
Fair: 50
Expensive: 18
The values in the above examples are just random values and may not match the real numbers in your result.
The procedure has no parameter. First, you need to find the average, minimum, and maximum prices (list_price) in your database and store them into varibles avg_price, min_price, and max_price.
You need more three varaibles to store the number of products in each price category:
cheap_count fair_count exp_count
Make sure you choose a proper type for each variable. You may need to define more variables based on your solution.