ASSIGNMENT-1

Q1:-Write a program to calculate the area of a triangle by accepting the 3 sides

(s=(a+b+c)/2 area=sqrt(s*(s-a)*(s-b)*(s-c)))

Sol:-

DECLARE
S NUMBER;
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER:=&C;
AREA NUMBER(7,2);
BEGIN
S:=(A+B+C)/2;
AREA:=SQRT(S*(S-A)*(S-B)*(S-C));
DBMS_OUTPUT.PUT_LINE('THE AREA OF TRIANGLE IS '||AREA);
END;
/

Q2:-:-WAP to accept the length and breadth of a rectangle and find out the perimeter
Sol:-

DECLARE
L NUMBER(4,2):=&L;
B NUMBER(4,2):=&B;
A NUMBER(4,2);
BEGIN
A:=2*(L+B);
DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF RECTANGLE IS '||A);
END;
/

Q3:-.Write a program to accept principle amount ,rate,time calculate the simple interest

Sol:-

DECLARE
P NUMBER(6,2):=&P;
R NUMBER(6,2):=&R;
T NUMBER(6,2):=&T;
SI NUMBER(6,2);
BEGIN
SI:=(P*R*T)/100;
DBMS_OUTPUT.PUT_LINE('SIMPLE INTEREST IS '||''||SI);
END;
/
Q4:- Write PL/SQL Program To Find Area and Circumference Of Circle.
SQL> DECLARE
2 pi constant number := 3.141592654;
3 radius number(5,2);
4 circumference number(7, 2);
5 area number (10, 2);
6 BEGIN
7 radius :=&radius;
8 circumference := 2.0 * pi * radius;
9 area := pi * radius * radius;
10 dbms_output.put_line('Circumference: ' || circumference);
11 dbms_output.put_line('Area: ' || area);
12 end;
13 /

Q5:- PL/SQL Program to Swap two Numbers

declare
    a number;
    b number;
    temp number;
 
begin
    a:=5;
    b:=10;
    
    dbms_output.put_line('before swapping:');
    dbms_output.put_line('a='||a||' b='||b);
    
    temp:=a;
    a:=b;
    b:=temp;
    
    dbms_output.put_line('after swapping:');
    dbms_output.put_line('a='||a||' b='||b);
    
end;
/

Q6:-Write a program to aceept the principle amount,rate,time and find the compound interest
Sol:-

DECLARE
P NUMBER(6,2):=&P;
R NUMBER(6,2):=&R;
T NUMBER(6,2):=&T;
CI NUMBER(6,2);
BEGIN
CI:=P*POWER(1+(R/100),T);
DBMS_OUTPUT.PUT_LINE('COMPOUND INTEREST IS '||CI);
END;
/
 ASSIGNMENT-2
Q1:- PL/SQL Program to Check Number is Odd or Even
declare
n number:=&n;
 
begin
if mod(n,2)=0
then
dbms_output.put_line('number is even');
else
dbms_output.put_line('number is odd');
end if;
end;
/

Q2:- Write a program to accept a year and check whether it is leap year or not

SQL> DECLARE
2 Y NUMBER:=&Y;
3 R NUMBER;
4 BEGIN
5 IF MOD(Y,4)=0 AND MOD(Y,100)!=0 OR MOD(Y,400)=0
6 THEN
7 DBMS_OUTPUT.PUT_LINE(Y ||' IS A LEAP YEAR');
8 ELSE
9 DBMS_OUTPUT.PUT_LINE(Y ||' IS NOT A LEAP YEAR');
10 END IF;
11 END;
12 /

Q3:- Write a program accept the value of A,B&C display which is greater
DECLARE
A NUMBER(4,2):=&A;
B NUMBER(4,2):=&B;
C NUMBER(4,2):=&C;
BEGIN
IF (A>B AND A>C) THEN
DBMS_OUTPUT.PUT_LINE('A IS GREATER '||''||A);
ELSIF B>C THEN
DBMS_OUTPUT.PUT_LINE('B IS GREATE '||''||B);
ELSE
DBMS_OUTPUT.PUT_LINE('C IS GREATER '||''||C);
END IF;
 END;
/

Q4:- Write a program to accept a char and check it is vowel or consonant

SQL> DECLARE
2 C CHAR:='&C';
3 BEGIN
4 IF C='A' OR C='E' OR C='I' OR C='O' OR C='U' THEN
5 DBMS_OUTPUT.PUT_LINE('VOWEL');
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('CONSONANT');
8 END IF;
9 END;
10 /

Q5:-WAP or accept marks of 3 subject as i/p and calculate the total marks and division of a
student
i) If totmark>=60 then division is First
ii) If totmark <60 and totmark>=50 then division is second
iii) If totmark< 50 and >=35 then division is third
iv) If totmark< 35 then fail

SQL> DECLARE

2 M1 NUMBER(2):=&M1;

3 M2 NUMBER(2):=&M2;

4 M3 NUMBER(2):=&M3;

5 TOTMARK NUMBER(5,2);

6 AVE NUMBER(5,2):=0;

7 BEGIN

8 TOTMARK:=M1+M2+M3;

9 AVE:=TOTMARK/3;

10 IF AVE>=60 THEN

11 DBMS_OUTPUT.PUT_LINE('THE DIVISION IS FIRST '||AVE);

12 ELSIF AVE<60 AND AVE>=50 THEN

 13 DBMS_OUTPUT.PUT_LINE('THE DIVISION IS SECOND '||AVE);

14 ELSIF AVE<50 AND AVE>=35 THEN

15 DBMS_OUTPUT.PUT_LINE('THE DIVISION IS THIRD '||AVE);

16 ELSE

17 DBMS_OUTPUT.PUT_LINE('FAIL '||AVE);

18 END IF;

19 END;

20 /

ASSIGNMENT-3

Q1:- Find the factorial of a number in pl/sql

declare
fac number :=1;
n number := &1;
begin
while n > 0 loop
fac:=n*fac;
n:=n-1;
end loop;
dbms_output.put_line(fac);
end;
Second Method
declare
n number;
fac number:=1;
i number;
 
begin
n:=&n;
 
for i in 1..n
 loop
fac:=fac*i;
end loop;
 
dbms_output.put_line('factorial='||fac);
end;
/

Q2:- Reverse a number in PL/SQL

SET SERVEROUTPUT ON;
declare

n number;
i number;
rev number:=0;
r number;
 
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 is '||rev);
 
end;
/

Q3:- PL/SQL Program for Prime Number

declare
n number;
i number;
flag number;
 
begin
i:=2;
flag:=1;
n:=&n;
 
for i in 2..n/2
loop
if mod(n,i)=0
then
flag:=0;
exit;
end if;
end loop;
 
if flag=1
then
dbms_output.put_line('prime');
else
dbms_output.put_line('not prime');
end if;
end;
/

Q4:- PL SQL Program to check whether a number

is perfect or not
declare
n number;
i number;
tot number;
begin
n:=&n;
tot:=0;
for i in 1..n/2
loop
if(n mod i=0) then
tot:= tot+i;
end if;
end loop;
if(n=tot)then
 dbms_output.put_line('Perfect no');
else
dbms_output.put_line('Not a Perfect no');
end if;
end;

Q5:- Write a program to accept a number and find how many digits it contain
DECLARE
N NUMBER(5):=&N;
CNT NUMBER:=0;
R NUMBER(2):=0;
BEGIN
WHILE N !=0
LOOP
R:=MOD(N,10);
CNT:=CNT+1;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF DIGITS OF GIVEN NUMBER IS '||CNT);
END
 ASSIGNMENT-4

Q1:- PL/SQL Program for Armstrong Number

declare
    n number:=407;
    s number:=0;
    r number;
    len number;
    m number;

begin
    m:=n;
 
    len:=length(to_char(n));
    
    while n>0
    loop
        r:=mod(n,10);
        s:=s+power(r,len);
        n:=trunc(n/10);
    end loop;
    
    if m=s
    then
        dbms_output.put_line('armstrong number');
    else
        dbms_output.put_line('not armstrong number');
    end if;
    
end;
/
 Q2:- Pl/SQL Program for Palindrome Number

declare
    n number;
    m number;
    rev number:=0;
    r number;

begin
    n:=12321;
    m:=n;

    while n>0
    loop
        r:=mod(n,10);
        rev:=(rev*10)+r;
        n:=trunc(n/10);
    end loop;
    
    if m=rev
    then
        dbms_output.put_line('number is palindrome');
    else
        dbms_output.put_line('number is not palindrome');
    end if;
end;
/

Q3:- Write PL/SQL Program to generate a Fibonacci series.

 declare

 first number := 0;
second number := 1;
temp number;
  
n number :=&n;
i number;
  
begin
  
    dbms_output.put_line('Series:');
  
--print first two term first and second
    dbms_output.put_line(first);
    dbms_output.put_line(second);
  
    for i in 3..n
    loop
        temp:=first+second;
  
first := second;
second := temp;
  
    dbms_output.put_line(temp);
end loop;
  
end;

Q4:- PL/SQL Program to Print Table of a

Number
declare
 n number;
i number;
 
begin
n:=&n;

for i in 1..10
loop
dbms_output.put_line(n||' x '||i||' = '||n*i);
end loop;
end;
/

Q5:-Write a program to accept a number and find the sum of the digits

DECLARE
N NUMBER(5):=&N;
S NUMBER:=0;
R NUMBER(2):=0;
BEGIN
WHILE N !=0
LOOP
R:=MOD(N,10);
S:=S+R;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF DIGITS OF GIVEN NUMBER IS '||S);
END;
/

ASSIGNMENT-5
Write a PL/SQL block to enter Rollno and check attendance for 75% for form fill
up of examination
set serveroutput on;

SQL> select * from student_attend1;

ROLLNO PAPER1 PAPER2 PAPER3 TOTALCLASS

---------- ---------- ---------- ---------- ----------
100 10 10 20 60
200 5 10 10 60
300 10 20 20 60
400 5 5 5 60

SQL> declare
2 per number(5);
3 total number(5);
4 sumall number(5);
5 rno number(5);
6 begin
7 rno:=&rno;
8 select paper1+paper2+paper3 into sumall from student_attend1 where
rno=rollno;
9 select totalclass into total from student_attend1 where rno=rollno;
10 per:=(sumall* 100)/total;
11 if per>=75 then
12 dbms_output.put_line ('Student can fill examination form');
13 else
14 dbms_output.put_line ('cannot fill exam form');
15 end if;
16 end;
17 /
Enter value for rno: 100
old 7: rno:=&rno;
new 7: rno:=100;
Student can fill examination form

PL/SQL procedure successfully completed.

ASSIGNMENT-6
 WAP a PL/SQL block to accept an account number from user, check if the user
balance is less then minimum balance, then deduct RS 100/- from the bank
account.

Sol:-
SQL>create table customers (
2 ID INT NOT NULL,
3 NAME VARCHAR (20) NOT NULL,
4 AGE INT NOT NULL,
5 ADDRESS CHAR (25),
6 SALARY DECIMAL (18, 2),
7 PRIMARY KEY (ID)
8 );

Table created.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

2 VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

1 row created.

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

2 VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

1 row created.

SQL> declare
2 vacc number(10);
3 cbal number(10);
4 mbal number(10):=4000;
5 begin
6 vacc:=&vacc;
7 select bal into cbal from abcbank where accno=vacc;
8 if(cbal<mbal) then
9 update abcbank set bal=bal-100 where accno=vacc;
10 cbal:=cbal-100;
11 dbms_output.put_line('Rs is deducted and current balance
is'||cbal);
12 else
13 dbms_output.put_line('Current balanceis'||cbal);
14 end if;
15 end;
16 /
 ASSIGNMENT-7
Write a PL/SQL block to accept an account number and amount from user,
check if the user account exist then update the balance, otherwise show
account does not exist

SQL> desc abcbank;

Name Null?
Type
----------------------------------------- --------
----------------------------
ACCNO NUMBER(10)
NAME VARCHAR2(15)
BAL NUMBER(10)
ADDRESS VARCHAR2(15)
GENDER VARCHAR2(1)

SQL> insert into abcbank

values(&ACCNO,'&NAME',&BAL,'&ADDRESS','&GENDER');
Enter value for accno: 100
Enter value for name: CHAND
Enter value for bal: 6789
Enter value for address: MANGO
Enter value for gender: M
old 1: insert into abcbank
values(&ACCNO,'&NAME',&BAL,'&ADDRESS','&GENDER')
new 1: insert into abcbank
values(100,'CHAND',6789,'MANGO','M')

1 row created.

SQL> /
Enter value for accno: 200
Enter value for name: SITA
Enter value for bal: 7000
Enter value for address: SAKCHI
Enter value for gender: F
old 1: insert into abcbank
values(&ACCNO,'&NAME',&BAL,'&ADDRESS','&GENDER')
new 1: insert into abcbank
values(200,'SITA',7000,'SAKCHI','F')
1 row created.

SQL> /
Enter value for accno: 300
Enter value for name: RITU
Enter value for bal: 4567
Enter value for address: TELCO
Enter value for gender: F
old 1: insert into abcbank
values(&ACCNO,'&NAME',&BAL,'&ADDRESS','&GENDER')
new 1: insert into abcbank
values(300,'RITU',4567,'TELCO','F')

1 row created.

SQL> declare
2 vacc number(10);
3 depo number(10);
4 acc number(10);
5 cbal number(10);
6 begin
7 vacc:=&vacc;
8 depo:=&depo;
9 select ACCNO into acc from abcbank where ACCNO=vacc;
10 select bal into cbal from abcbank where ACCNO=vacc;
11 if acc=vacc then
12 dbms_output.put_line('account exist ');
13 update abcbank set bal =bal + depo where ACCNO= vacc;
14 cbal:=cbal+depo;
15 dbms_output.put_line('Current balance='|| cbal);
16 else
17 dbms_output.put_line('account does not exist');
18 end if;
19 end;
20 /

(To run
Enter value for vacc: 100
old 7: vacc:=&vacc;
new 7: vacc:=100;
Enter value for depo: 678
old 8: depo:=&depo;
new 8: depo:=678;
account exist
Current balance=7667)

PL/SQL procedure successfully completed.