1)Write a PL/SQL to Reverse of a Number 2)Write a PL/SQL to Factorial of a number 3) Write a PL/SQL to update the rate field by 20% more than the current rate in inventory table which has the following fields: Prono, ProName and Rate. After updating the table a new field (Alter) called for Number of item and place for values for the new field without using PL/SQL block.

Hello Everyone This is Gautam, in this post i m going to solve some  SQL query Practice Set 5. Please Refer set 1 ,2 ,3 and 4 Link is Given Below

Practice List 1
Practice List 2
Practice List 3
Practice List 4

Write a PL/SQL to Factorial of a number

Solution:-

set serveroutput on;

declare 

fact number :=1;

n number;

begin

n :=&n;

while n>0 loop

fact := n*fact;

n :=n-1;

end loop;

dbms_output.put_line('Factorial Number is:'||fact);

end;

/

2. Write a PL/SQL to Reverse Number 

Solution:-

declare

   n number;

   r number;

    rev number :=0;

    begin

   n := &n;

   while n>0 loop

    r :=mod(n,10);

    rev :=(rev*10)+r;

   n := trunc(n/10);

   end loop;

  dbms_output.put_line('Reverse number is '||rev);

  end;

3.Write a PL/SQL to update the rate field by 20% more than the current rate in inventory table

which has the following fields: Prono, ProName and Rate. After updating the table a new field

(Alter) called for Number of item and place for values for the new field without using PL/SQL

block. 

Solution:

 

create table inventory (Pro_no varchar(6) check(Pro_no like 'P%'),Pro_name varchar(20),rate number);

set serveroutput on;

begin 

insert into inventory(Pro_no,Pro_name,rate) values(&Pro_no,&Pro_name,&rate);

end;

/

 --Insert two value  like (P01, Apple ,10),(P02,Asus,20) 

declere 

v_rate inventory.rate%type;

begin

update inventory 

set rate=rate+(rate*0.2);

commit;

end;

/

select * from inventory;

alter table inventory add (no_of_item number);

update inventory set no_of_item=10 where Pro_no='P01';

update inventory set no_of_item=20 where Pro_no='P02';

select * from inventory;