Practical No. # 4
Q1: Write a PL/SQL code block to find sum and average of three numbers.
Solution:
declare
a number:=&a;
b number:=&b;
c number:=&c;
sm number;
av number;
begin
sm:=a+b+c;
av:=sm/3;
dbms_output.put_line('Sum = '||sm);
dbms_output.put_line('Average = '||av);
end;
Output:
Enter value for a: 12
old 2: a number:=&a;
new 2: a number:=12;
Enter value for b: 4
old 3: b number:=&b;
new 3: b number:=4;
Enter value for c: 21
old 4: c number:=&c;
new 4: c number:=21;
Sum = 37
Average = 12.33333333333333333333333333333333333333
PL/SQL procedure successfully completed.
Q2: Write a PL/SQL code block to find Simple Interest.
Solution:
declare
p number(9,2);
r number(9,2);
t number(9,2);
si number(9,2);
begin
p:=&p;
r:=&r;
t:=&t;
si:=(p*r*t)/100;
dbms_output.put_line('Simple Interest = '||si);
end;
Output:
Enter value for p: 150
old 7: p:=&p;
new 7: p:=150;
Enter value for r: 4
old 8: r:=&r;
new 8: r:=4;
Enter value for t: 3
old 9: t:=&t;
new 9: t:=3;
Simple Interest = 18
PL/SQL procedure successfully completed.
Q3: Write a PL/SQL code block to find area of circles with radius greater than 3 and less than equal to 7 and store the result in a table with attributes radius and area.
Solution:
declare
area number(5,2);
radius number(1):=3;
pi constant number(3,2):=3.14;
begin
while radius<=7
loop
area:=pi*radius*radius;
insert into areas values (radius,area);
radius:=radius+1;
end loop;
end;
Output:
PL/SQL procedure successfully completed.
SQL> select * from areas;
RADIUS AREA
---------- ----------
3 28.26
4 50.24
5 78.5
6 113.04
7 153.86
Q4: Write a PL/SQL code block to find factorial of a number.
Solution:
declare
n number;
i number;
f number:=1;
begin
n:=&n;
for i in 1..n
loop
f:=f*i;
end loop;
dbms_output.put_line(n||'! = '||f);
end;
Output:
Enter value for n: 5
old 6: n:=&n;
new 6: n:=5;
5! = 120
PL/SQL procedure successfully completed.
Q5: Write a PL/SQL code block to find reverse of a number.
Solution:
declare
N number;
S NUMBER := 0;
R NUMBER;
K number;
begin
N := &N;
K := N;
loop
exit WHEN N = 0;
S := S * 10;
R := MOD(N,10);
S := S + R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line('THE REVERSED DIGITS OF '||K||' = '||S);
end;
Output:
Enter value for n: 4567
old 7: N := &N;
new 7: N := 4567;
THE REVERSED DIGITS OF 4567 = 7654
PL/SQL procedure successfully completed.
Q6: Write a PL/SQL code block to find greatest of three numbers.
Solution:
declare
a number := &a;
b number := &b;
c number := &c;
begin
if a>b and a>c then
dbms_output.put_line(a||' is greatest.');
elsif b>a and b>c then
dbms_output.put_line(b||' is greatest.');
else
dbms_output.put_line(c||' is greatest.');
end if;
end;
Output:
Enter value for a: 6
old 2: a number := &a;
new 2: a number := 6;
Enter value for b: 3
old 3: b number := &b;
new 3: b number := 3;
Enter value for c: 4
old 4: c number := &c;
new 4: c number := 4;
6 is greatest.
PL/SQL procedure successfully completed.
Q7: Write a PL/SQL code block to generate Fibonacci series.
Solution:
declare
a number:= 0 ;
b number:= 1;
c number;
begin
dbms_output.put(a||' '||b||' ');
for i in 3..10 loop
c := a + b;
dbms_output.put(c||' ');
a := b;
b := c;
end loop;
dbms_output.put_line(' ');
end;
Output:
0 1 1 2 3 5 8 13 21 34
PL/SQL procedure successfully completed.
Q8: Write a PL/SQL code block to find sum of digits of a number.
Solution:
declare
N number ;
S number:=0;
R number;
begin
N:=&N;
WHILE N<>0 LOOP
R := MOD(N,10);
S := S + R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line('THE SUM OF THE DIGITS = '||S);
end;
Output:
Enter value for n: 535
old 6: N:=&N;
new 6: N:=535;
THE SUM OF THE DIGITS = 13
PL/SQL procedure successfully completed.
