Ravi Tutorials Provide Project Training

RDBMS

RDBMS



                              PL-SQL Programs…
·         Program 1:-

 Write a PL/SQL block to find the maximum number from given three numbers.

declare
         a number;
         b number;
         c number;
begin
            a:=&a;
            b:=&b;
            c:=&c;

            if  (a>b and a>c) then
                        dbms_output.put_line('a is maximum ' || a);
            elsif (b>a and b>c) then
                        dbms_output.put_line('b is maximum ' || b);
            else
                        dbms_output.put_line('c is maximum ' || c);
            end if;
end;

·         Program 2:-

         Write a PL/SQL block to find the sum of first 100 natural nos.

declare
            a number:=0;
       
begin

       for i in 1..100
         loop
            a:=a+i;
        end loop;
            dbms_output.put_line('The sum of 100 natural nos is = '||a);
        
end;

·         Program 3:-

Write a PL/SQL block to find the sum of first 100 odd nos. and even nos)
declare
       odd number:=0;
       even number:=0;
       i number;
begin
       for i in 1..100
        loop
              if(i mod 2 = 0) then
                  even:=even+i;
              else
                  odd:=odd+i;
              end if;
       end loop;
dbms_output.put_line('The Sum of 100 even nos is ' || even);
dbms_output.put_line('The Sum of 100 odd  nos is ' || odd);
end;

·         Program 4:-

Write a PL/SQL block to display the Information of given student on following table Stud (sno, sname, address, city).

è Table Creation…

create table stud(
  sno number primary key,
  sname char(15),
  addr varchar(30),
  city char(15));

 insert into stud values(1,'hiral','2,krishna society','Mehsana.');
 insert into stud values(2,'pinky','4,Kalyaneshwer society','Mehsana.');
 insert into stud values(3,'Dhruvi','24,Pushpavati society','Mehsana');

è Program…

declare
            no number;
              n number;
            name char(15);
            add varchar(50);
            c char(15);
             
begin
             n:=&n;             
             select sno,sname,addr,city into no,name,add,c from stud where sno=n;

             dbms_output.put_line('The Sno is ' || no);
             dbms_output.put_line('The Sname is ' || name);
             dbms_output.put_line('The address is ' || add);
             dbms_output.put_line('The city is ' || c);
end;






·         Program 5:-

Write a PL/SQL block for preparing a Net Salary, given employee on following table
Emp (eno, ename, address, city)
                        Salary (eno, basic, da, hra, it)
                        Net_Salary (eno, total_allowance, total_deduction, netpay)
                        Notes : D.A. = 59% of basic , H.R.A. = 500,  I.T. = 2% of basic
                           Total_Allowance = Basic + D.A. + H.R.A., Total_Deduction = I.T.
                           Netpay = Total_Allowance – Total_Deduction.


è Table Creation…

create table emp(
eno number primary key,
ename char(15),
addr varchar(30),
city char(15));

 insert into emp values(1,'hiral','2,krishna society','Mehsana.');
 insert into emp values(2,'pinky','4,Kalyaneshwer society','Mehsana.');
 insert into emp values(3,'Dhruvi','24,Pushpavati society','Mehsana');

create table salary(
eno number references emp,
basic number(10,2),
da number(10,2) default NULL,
hra number(10,2) default 500,
it number(10,2) default NULL);


 insert into salary(eno,basic) values(1,20000);
 insert into salary(eno,basic) values(2,30000);
insert into salary(eno,basic) values(3,40000);

update salary set da=basic*0.59,it=basic*0.02;


create table netsal(
eno number references emp,
totalallow number(10,2),
totalded number(10,2),
netpay number(10,2));

è Program…

declare

  no number;
  n number;
  d number(10,2);
  b number(10,2);
  h number(10,2);
  i number(10,2);
  ta number(10,2);
  td number(10,2);
  np number(10,2);

begin
       n:=&n; 
       select eno,basic,da,hra,it into no,b,d,h,i from salary where eno=n;
       ta:=b+d+h;
       td:=i; 
       np:=ta-td;
       insert into netsal values(no,ta,td,np);
end;




·         Program 6:-

Write a PL/SQL block to raise the salary by 20% of given employee on following table.

                                    Emp_Salary (eno, ename, city, salary)

è Table Creation…




eno number primary key,
ename char(15),
city char(15),
 sal number(10,2));


insert into empsal values(1,'Hiral','Mehsana',20000);
insert into empsal values(2,'Pinkey','Mehsana',15000);
insert into empsal values(3,'Dhruvi','Mehsana',10000);

è Program…

declare

      n number;
      s number(10,2);

begin
  
    n:=&n;
     
      --select sal into s from empsal where eno=n;
     
      update empsal set sal=sal+(sal*0.20) where eno=n;

end;




·         Program 7:-

Write an Implicit Cursor to accept the employee number from the user. You have to delete this record and display the appropriate message on the following table.
                                    Emp (eno, ename, address, city)


è Table Creation…

create table emp1(
eno number primary key,
ename char(15),
addr varchar(30),
city char(15));


 insert into emp1 values(1,'hiral','2,krishna society','Mehsana.');
insert into emp1 values(2,'pinky','4,Kalyaneshwer society','Mehsana.');
 insert into emp1 values(3,'Dhruvi','24,Pushpavati society','Mehsana');

è Program…

declare

       n number;

begin

        n:=&n;
    
       delete from emp1 where eno=n;
       
        if sql%found then
             dbms_output.put_line('The record ' ||n|| ' success fully deleted');
        else
              dbms_output.put_line('The record ' ||n|| ' not found');
         end if;
end;
              


·         Program 8:-

Write a Cursor to display the first five records on the following table.
                                               
Student(sno, sname, address, city)

è Table Creation…

  create table stu(
  sno number primary key,
  sname char(15),
  addr varchar(30),
  city char(15));

 insert into stu values(1,'hiral','2,krishna society','Mehsana.');
insert into stu values(2,'pinky','4,Kalyaneshwer society','Mehsana.');
 insert into stu values(3,'Dhruvi','24,Pushpavati society','Mehsana');
 insert into stu values(4,'ukti','2,krishna society','Mehsana.');
insert into stu values(5,'jaya','4,Kalyaneshwer society','Mehsana.');
 insert into stu values(6,'prisha','2,krishna society','Ahmedabad');
insert into stu values(7,'pray','4,Kalyaneshwer society','Mehsana.');



è Program…
      
declare

       cursor c_stu is select sno,sname,addr,city from stu;
       n number;
       no number;
       name char(15);
       a varchar(30);
       c char(15);

begin
       
       open c_stu;
       if c_stu%isopen then
       loop
       
             fetch c_stu into no,name,a,c;
            exit when c_stu%rowcount > 5;
              dbms_output.put_line('  '||no||'     ' ||name||'     '||a||'     '||c);
        
        end loop;
       end if;
 close c_stu;
end;
   

·         Program 9:-

Write a Cursor for preparing a Net Salary for employee’s of finance department and Net Pay is more than 10,000 on following table.
                                               
Emp (eno, ename, department, address, city)
                                                Salary (eno, basic, da, hra, it)
                                                Net_Salary (eno,total_allowance, total_deduction, netpay)


è Table Creation…

create table emp2(
eno number primary key,

ename char(15),
dept char(20),
addr varchar(30),
city char(15));

 insert into emp2 values(1,'hiral','finace','2,krishna society','Mehsana.');
insert into emp2 values(2,'pinky','account','4,Kalyaneshwer society','Mehsana.');
 insert into emp2 values(3,'Dhruvi','finace','24,Pushpavati society','Mehsana');
insert into emp2 values(4,'ukti','account','4,Kalyaneshwer society','Mehsana.');
 insert into emp2 values(5,'jaya','finace','24,Pushpavati society','Mehsana');


create table salary1(
eno number references emp2,
basic number(10,2),
da number(10,2) default NULL,
hra number(10,2) default 500,
it number(10,2) default NULL);


 insert into salary1(eno,basic) values(1,2000);
 insert into salary1(eno,basic) values(2,30000);
insert into salary1(eno,basic) values(3,40000);
 insert into salary1(eno,basic) values(4,15000);
insert into salary1(eno,basic) values(5,10000);

update salary1 set da=basic*0.59,it=basic*0.02;


create table netsalary(
eno number references emp2,
totalallow number(10,2),
totalded number(10,2),
netpay number(10,2));


è Program…

declare

           cursor c_salemp is select emp2.eno,basic,da,hra,it from emp2,salary1 where dept='finace' and emp2.eno=salary1.eno;
             no number;
             d number(10,2);
             b number(10,2);
     
 h number(10,2);
             i number(10,2);
             ta number(10,2);
             td number(10,2);
             np number(10,2);
begin

            open c_salemp;
            loop
            fetch c_salemp into no,b,d,h,i;
              exit when c_salemp%notfound;
                ta:=b+h+d;
                td:=d;
                np:=ta-td;
                if np > 10000 then
                      insert into netsalary values(no,ta,td,np);
              end if;
           end loop;
 close c_salemp;
end;




·         Program 10:-

Write a Cursor to display the employee number, name, department and salary of first employee getting the highest salary.
                                   
Emp (eno, ename, department, address, city)
                                                Salary (eno, salary)


è Table Creation…

create table emp2(
eno number primary key,
ename char(15),
dept char(20),
addr varchar(30),
city char(15));

 insert into emp2 values(1,'hiral','finace','2,krishna society','Mehsana.');
insert into emp2 values(2,'pinky','account','4,Kalyaneshwer society','Mehsana.');
 insert into emp2 values(3,'Dhruvi','finace','24,Pushpavati society','Mehsana');
insert into emp2 values(4,'ukti','account','4,Kalyaneshwer society','Mehsana.');
 insert into emp2 values(5,'jaya','finace','24,Pushpavati society','Mehsana');

create table salary2(
eno number references emp2,
sal number(10,2));


insert into salary2 values(1,22000);
insert into salary2 values(1,12000);
insert into salary2 values(2,25000);
insert into salary2 values(4,10000);


è Program…

declare
         cursor c_empsal is select salary2.eno,ename,dept,sal from salary2,emp2 where sal in(select max(sal) from salary2) and emp2.eno=salary2.eno;

                  n salary2.eno%type ;
                  name emp2.ename%type;
             s salary2.sal%type;
                  d emp2.dept%type;

begin

               open c_empsal;
               loop
                  fetch c_empsal into n,name,d,s;
                      exit when c_empsal%notfound;
                                                dbms_output.put_line('The employee no is '||n);
                                       dbms_output.put_line('The employee name is '||name);
                                       dbms_output.put_line('The employee department is '||d);
                                       dbms_output.put_line('The employee salary is '||s);
               end loop;
                     close c_empsal;
end;  


 


·         Program 11:-
Writes a Function to check whether the given number is prime or not.

è Program…

create or replace function prime(a in number) return number is
j number:=0;
b number:=0;
n number:=a;
begin
b:=n-1;
      for i  in 2..b
      loop
         if (mod(a,i)=0) then
                j:=1;
              exit;
         end if;
      end loop;
--dbms_output.put_line('The j is'||j);
return j;
end;

declare 
           
            a number;
            j number;
           
begin
       a:=&a;
      j:=prime(a);
      if(j=1) then
         dbms_output.put_line ('Not prime no');
      else
         dbms_output.put_line ('prime no');

       
      end if;
end;


·         Program 12:-
Write a Function to find the sum of digits of accepted no.




è Program…
create or replace function sumdig(a in number) return number is
b number;
            c number:=0;
            m number;
begin
  m:=a;
      for a in 0..m
      loop
         b:=m mod 10;
         c:=b+c;
         m:=trunc(m/10);
 
      end loop;
return c;

end;



declare 
           
            a number;
c number;       
begin
       a:=&a;
      c:=sumdig(a);
      dbms_output.put_line ('sum of all digits is  = ' ||c);
     
end;


·         Program 13:-
Write a Function to display first 25 Fibonacci nos.

è Program…


create or replace function fibo(a in number) return number is
n number:=a;
m number:=0;
s  number;
c number;
begin

      dbms_output.put_line('m= '||m);
      dbms_output.put_line('n= '||n);
      for c in 1..27
      loop
         s:=m+n;
         dbms_output.put_line (''||s);
         m:=n;
         n:=s;
    
      end loop;
      return 0;
end;





declare 
            n number:=1;
            s  number;
           
begin
      s:=fibo(n);

end;



·         Program 14:-
Write a Function to display the reverse string of a given string.

è Program…

create or replace function f_reverse(str in varchar) return varchar is
     s varchar(5);
      l number;

begin
         l:=length(str);
         for i in reverse 1..l
         loop
                     s:=s||substr(str,i,1);
         end loop;
       return s;
end; 

declare
         str varchar(50);
         s varchar(50);
begin

         str:='&str';
         s:=f_reverse(str);
         dbms_output.put_line('The reverse string is '||s);
end;




·         Program 15:-
                       
Write a Function that take Employee Number and return the salary on following table.
                                   
Emp (eno, ename, city, salary)

è Table Creation…

create table emps(
eno number primary key,
  ename char(15),
  city char(15),
  sal number(10,2));

insert into emps values(1,'Hiral','Mehsana',20000);
insert into emps values(2,'Pinky','Mehsana',21000);
insert into emps values(3,'Dhruvi','Mehsana',22000);

è Program…

create or replace function getno(no in number) return number is
s number(10,2);
begin
  select sal into s from emps where eno=no;
return s;
end;

declare
 no number;
  s number(10,2);
begin

   no:=&no;
   s:=getno(no);
   dbms_output.put_line('The salary of  ' ||no|| ' is '||s);
end;


·         Program 16:-
                       
Write a Function to count the total number of student having grade ‘PASS’ on following table.

  Student (sno, sname, grade)
                       
è Table Creation…    

create table stud1(
sno number primary key,
sname char(15),
sub1 number,
sub2 number,
sub3 number,
grade char(15));

insert into stud1 values(1,'Pray',98,94,90,'Distinction');
insert into stud1 values(2,'Jay',57,74,40,'First');
insert into stud1 values(3,'Prisha',58,54,50,'Second');
insert into stud1 values(4,'Masum',48,44,40,'Pass');
insert into stud1 values(5,'Shyam',40,40,40,'Pass');


è Program…

create or replace function totalpass(s in char) return number is
no number;
cursor c_total is select count(sno) from stud1 where grade=s;
begin
open c_total;   
loop
           fetch c_total into no;
           exit when c_total%notfound;
end loop;
     close c_total;
           return no;
end;    



declare
   s char(5):='Pass';
   n number;
begin
  n:=totalpass(s);
  dbms_output.put_line('The total no of student who are pass is '||n);
end;
  

·         Program 17:-
                       
Write a Function to assign the grade to the entire student using following table
     
Stud (sno, sname, sub1, sub2, sub3, and grade)

      Note:      If percentage >= 70 then ‘Distinction’ else
                      If percentage >= 60 then ‘First’ else
                      If percentage >= 50 then ‘Second’
                      Otherwise ‘Fail’

è Table Creation…    

create table stud1(
sno number primary key,
sname char(15),
sub1 number,
sub2 number,
sub3 number,
grade char(15));

insert into stud1 values(1,'Pray',98,94,90,'Distinction');
insert into stud1 values(2,'Jay',57,74,40,'First');
insert into stud1 values(3,'Prisha',58,54,50,'Second');
insert into stud1 values(4,'Masum',48,44,40,'Pass');
insert into stud1 values(5,'Shyam',40,40,40,'Pass');

è Program…

create or replace function givegrade(p in number) return char is
 g char(15);
begin
            if p >= 70 then
                        g:='Distinction';

                        return g;
            elsif p >= 60 then
                        g:='First';
                        return g;
            elsif p>= 50 then
                        g:='Pass';
                        return g;
            else
                        g:='Fail';
                        return g;
            end if;
end;



declare
     cursor c_grade is select sno,sub1,sub2,sub3 from stud1;
      no stud1.sno%type;
      s1 stud1.sub1%type;
      s2 stud1.sub2%type;
      s3 stud1.sub3%type;
      t number;
      g stud1.grade%type;
      p number(10,2);                
begin
            open c_grade;
            loop
                        fetch c_grade into no,s1,s2,s3;
                        exit when c_grade%notfound;
                            t:=s1+s2+s3;
                            p:=t/3;
                            g:=givegrade(p);
                        update stud1 set grade=g where sno=no;
            end loop;
close c_grade;
end;    
                                                                                   

·         Program 18:-
                       
Write a Procedure to check the given year is leap year or not.

è Program…



create or replace procedure leapyear(y in number) is
begin

        if y mod 4 =0 and y mod 100 <>0 or y mod 400=0 then
                    dbms_output.put_line('The '|| y|| ' is leap year');
        else
                    dbms_output.put_line('The '|| y|| ' is not leap year');
        end if;
end;


declare
  y number;
begin
 y:=&y;
 leapyear(y);
end;




·         Program 19:-
                       
Write a Procedure to display the following type of Multiplication Table as per given number.
     
5 * 1   = 5
   5 * 2   = 10
          =  
          =  
   5 * 10   = 50

è Program…

create or replace procedure mult(n in number) is
a number:=1;
begin
              for i in 1..10
        loop
                    a:=n*i;
                                 dbms_output.put_line ( n ||'  *  '||i|| ' = '||a);
        end loop;
end;



declare
        n number;
begin
        n:=&n;
        mult(n);
end;



·         Program 20:-
           
Write a Procedure to display this kind of output on screen.
            
 1
             2 3
             3 4 5
             4 5 6 7
             5 6 7 8 9
            .  .  .  .  . 90 91

è Program…

create or replace procedure disp(n in number) is
a number:=0;

begin

 for i in 1..n
 loop
     for j in 1..i
     loop
 a:=a+1;
  dbms_output.put(' '||a);
  
     end loop;
  dbms_output.put_line(' ');
a:=i;
 end loop;
end;








declare
           
            n number;
begin
             n:=&n;
            disp(n);
end;
                                   


·         Program 21:-
           
                                    Write a Procedure to convert given octal number to decimal number.

è Program…

create or replace procedure octdes(n in number) is
a number:=1;
no number;
ans number:=1;
r number;
s number:=0;
begin          
no:=n;    
 while no > 0
      loop      
        r:=no mod 10;
        ans:=r  * a;
        s:=s+ans;
        a:=a * 8;
        no:=trunc(no/10);
         
     end loop;
  dbms_output.put_line('The decimal no of octal no is ' ||s);
end;


declare
 no number;
begin
   no:=&no;
  octdes(no);
end;


·         Program 22:-
           
                                    Write a Procedure that take Employee Number and return all the information related to the employee. Display the following format using table - Emp (eno, ename, city, salary)

Employee Number
Employee Name
City
Salary

Records are displayed here



è Program…

create or replace procedure recdisp(n in number) is
 cursor c_emps is select eno,ename,city,sal from emps where eno=n;
 no emps.eno%type;
 name emps.ename%type;
 c emps.city%type;
 s emps.sal%type;
begin
open c_emps;
  loop
        fetch c_emps into no,name,c,s;
        exit when c_emps%notfound;
              dbms_output.put_line(no||'                 '||name||'  '||c||'  '||s);
 end loop;  

end;


declare
n number;
begin
  n:=&n;
 dbms_output.put_line('Employee no      Employee name     city             salary');
  recdisp(n);
end;


·         Program 23:-

            Writes a Package that has a Function that checks the given string is palindrome or not

è Program…

create or replace package strings as
        function palindrom(s varchar) return varchar;
end strings;

create or replace package body strings as
        function palindrom(s varchar) return varchar is
                    n number;
                    c varchar(50);
        begin
                    n:=length(s);
                    for i in reverse 1..n
                    loop
                    c:=c || substr(s,i,1);
                    end loop;
                    dbms_output.put_line('The Entered string is '||s);      
                      dbms_output.put_line('The Reverse string is '||c);
                    return(c);
        end;
end strings;

declare
        s varchar(50);
        c varchar(50);
begin
        s:='&s';
        c:=strings.palindrom(s);
        if s=c then
                      dbms_output.put_line('The given string is Palindrom ');
        else
                      dbms_output.put_line('The given string is NotPalindrom ');
        end if;
end;


·         Program 24:-

                      Write a Package that has a Procedure to find  1+1/2+1/3+ . . . . . +1/n.

è Program…





create or replace package con as
  function raci(n number) return number;
End con;
create or replace package body con as
        function raci(n number) return number is
                    a number(10,2):=0;
        begin
                    for i in 1..n
                    loop
                    a:=a+1/i;
                    end loop;
                                return(a);
        end;
end con;




declare
n number;
a number(10,2);
begin
        n:=&n;
        a:=con.raci(n);
                    dbms_output.put_line('The Answer is '||a);
end;

create or replace package con as
 procedure raci(n number);
End con;
create or replace package body con as
        procedure raci(n number) is
                    a number(10,2):=0;
        begin
                    for i in 1..n
                    loop
                    a:=a+1/i;
                    end loop;
                    dbms_output.put_line('The Answer is '||a);    
        end;
end con;




declare
n number;
begin
        n:=&n;
               con.raci(n);
end;

·         Program 25:-

                        Write a Package that has a Function to check given number is not negative and a Procedure to convert the given number into word. For Example 25 = Twenty Five.

è Program…

create or replace package pac1 as
  function check1(n number) return number;
  function basic(n number) return varchar;
End pac1;        

create or replace package body pac1 as
  function basic(n number) return varchar as
  begin
              if n=1 then
                          return (' One');
              elsif n=2 then
                          return (' Two');
              elsif n=3 then
                          return (' Three');
              elsif n=4 then
                          return (' Four');
              elsif n=5 then
                          return (' Five');
              elsif n=6 then
                          return (' Six');
              elsif n=7 then
                          return (' Seven');
              elsif n=8 then
                          return (' Eight');
              elsif n=9 then
                          return (' Nine');
              end if;                         
  end;
  function check1(n number) return number is
  m number;
  mo varchar(10);
  r number;
  l number;
  begin
              m:=n;
                mo:=m;
                            l:=length(mo);
              dbms_output.put_line('length ='||l);
              if n<0 span="" then="">
                          dbms_output.put_line('The Number is Negative');
                                                  return 0;
              elsif n>0 then
                          dbms_output.put_line('The Number is Positive');
                          if n<=20 then
                                      if n=1 then
                                                  dbms_output.put_line(n||' = One');
                                      elsif n=2 then
                                                  dbms_output.put_line(n||' = Two');
                                      elsif n=3 then
                                                  dbms_output.put_line(n||' = Three');
                                      elsif n=4 then
                                                  dbms_output.put_line(n||' = Four');
                                      elsif n=5 then
                                                  dbms_output.put_line(n||' = Five');
                                      elsif n=6 then
                                                  dbms_output.put_line(n||' = Six');
                                      elsif n=7 then
                                                  dbms_output.put_line(n||' = Seven');
                                      elsif n=8 then
                                                  dbms_output.put_line(n||' = Eight');
                                      elsif n=9 then
                                                  dbms_output.put_line(n||' = Nine');
                                      elsif n=10 then
                                                  dbms_output.put_line(n||' = Ten');
                                      elsif n=11 then
                                                  dbms_output.put_line(n||' = Elevan');
                                      elsif n=12 then
                                                  dbms_output.put_line(n||' = Twelve');
                                      elsif n=13 then
                                                  dbms_output.put_line(n||' = Thirteen');
                                      elsif n=14 then
                                                  dbms_output.put_line(n||' = Fourteen');
                                      elsif n=15 then
                                                  dbms_output.put_line(n||' = Fifteen');
                                      elsif n=16 then
                                                  dbms_output.put_line(n||' = Sixteen');
                                      elsif n=17 then
                                                  dbms_output.put_line(n||' = Seventeen');
                                      elsif n=18 then
                                                  dbms_output.put_line(n||' = Eighteen');
                                      elsif n=19 then
                                                  dbms_output.put_line(n||' = Nineteen');
                                      elsif n=20 then
                                                  dbms_output.put_line(n||' = Twenty');
                                      end if;
                          end if;
                          if n>=21 and n<=29 then
                                      mo:=substr(mo,1,1);
                                      if mo=2 then
                                                  mo:='Twenty';
                                                              while m>0
                                                              loop
                                                                          r:=m mod 10;
                                                                          exit;
                                                              end loop;
                                                              dbms_output.put_line(n||'= '||mo||pac1.basic(r));
                                                             
                                      end if;
                          elsif n=30 then
                                      dbms_output.put_line(n||'= Thirty');
                          elsif n>=31 and n<=39 then
                                      mo:=substr(mo,1,1);
                                      if mo=3 then
                                                  mo:='Thirty';
                                                              while m>0
                                                              loop
                                                                          r:=m mod 10;
                                                                          exit;
                                                              end loop;
                                                              dbms_output.put_line(n||'= '||mo||pac1.basic(r));
                                                             
                                      end if;
                          elsif n=40 then
                                      dbms_output.put_line(n||'= Fourty');
                          elsif n>=41 and n<=49 then
                                      mo:=substr(mo,1,1);
                                      if mo=4 then
                                                  mo:='Fourty';
                                                              while m>0
                                                              loop
                                                                          r:=m mod 10;
                                                                          exit;
                                                              end loop;
                                                              dbms_output.put_line(n||'= '||mo||pac1.basic(r));
                                                             
                                      end if;
                          elsif n=50 then
                                      dbms_output.put_line(n||'= Fifty');
                          elsif n>=51 and n<=59 then
                                      mo:=substr(mo,1,1);
                                      if mo=5 then
                                                  mo:='Fifty';
                                                              while m>0
                                                              loop
                                                                          r:=m mod 10;
                                                                          exit;
                                                              end loop;
                                                              dbms_output.put_line(n||'= '||mo||pac1.basic(r));
                                                             
                                      end if;
                          elsif n=60 then
                                      dbms_output.put_line(n||'= Sixty');
                          elsif n>=61 and n<=69 then
                                      mo:=substr(mo,1,1);
                                      if mo=6 then
                                                  mo:='Sixty';
                                                              while m>0
                                                              loop
                                                                          r:=m mod 10;
                                                                          exit;
                                                              end loop;
                                                              dbms_output.put_line(n||'= '||mo||pac1.basic(r));
                                                             
                                      end if;
                          elsif n=70 then
                                      dbms_output.put_line(n||'= Seventy');
                          elsif n>=71 and n<=79 then
                                      mo:=substr(mo,1,1);
                                      if mo=7 then
                                                  mo:='Seventy';
                                                              while m>0
                                                              loop
                                                                          r:=m mod 10;
                                                                          exit;
                                                              end loop;
                                                              dbms_output.put_line(n||'= '||mo||pac1.basic(r));
                                                             
                                      end if;
                          elsif n=80 then
                                      dbms_output.put_line(n||'= Eighty');
                          elsif n>=81 and n<=89 then
                                      mo:=substr(mo,1,1);
                                      if mo=8 then
                                                  mo:='Eighty';
                                                              while m>0
                                                              loop
                                                                          r:=m mod 10;
                                                                          exit;
                                                              end loop;
                                                              dbms_output.put_line(n||'= '||mo||pac1.basic(r));
                                                             
                                      end if;
                          elsif n=90 then
                                      dbms_output.put_line(n||'= Ninty');
                          elsif n>=91 and n<=99 then
                                      mo:=substr(mo,1,1);
                                      if mo=9 then
                                                  mo:='Ninty';
                                                              while m>0
                                                              loop
                                                                          r:=m mod 10;
                                                                          exit;
                                                              end loop;
                                                              dbms_output.put_line(n||'= '||mo||pac1.basic(r));
                                                             
                                      end if;
                          elsif n=100 then
                                      dbms_output.put_line(n||'= Hundred');
                          end if;
                          return 0;
              elsif n=0 then
                          dbms_output.put_line(n||' = Zero');
                           return 0;
              end if;
  end;
end pac1;


declare
n number;
a number;
begin
n:=&n;
if  n<=100 then
a:=pac1.check1(n);
else
dbms_output.put_line('Number must be Less than 100');
end if;
end;



·         Program 26:-

                             Write a Package that has two object on following table.
                                     (i) The Function is used to calculate the NetSalary.
                                     (ii) The Procedure is used to display the Pay Slip in following format.
                       
Emp (eno, ename, basic)
                                                Salary (eno, da, hra, it, gross_sal, net_sal)
                                                           
                                     Notes : D.A. = 59% of basic , H.R.A. = 500,  I.T. = 2% of basic
                         Gross_salary = Basic + D.A. + H.R.A., Net_salary = Gross_salary – I.T.

Employee Number
Employee Name
Gross Salary
Net Salary





Total
********
********


è Table Creation…    


create table empac(
eno number,
ename char(25),
basic number(10,2));
insert into empac values(1,'Hiral',100000);
insert into empac values(2,'Dhruvi',200000);
insert into empac values(3,'Pinky',10000);


create table salpac(
eno number,
da number(10,2),
hra number(10,2),
it number(10,2),
gross number(10,2),
net number(10,2) default NULL);

insert into salpac(eno,da,hra,it)  values(1,100000*0.59,500,100000*0.02);
insert into salpac(eno,da,hra,it)  values(2,200000*0.59,500,200000*0.02);
insert into salpac(eno,da,hra,it)  values(3,10000*0.59,500,10000*0.02);
update salpac set gross = 100000+da+hra where eno=1;
update salpac set gross = 200000+da+hra where eno=2;
update salpac set gross = 10000+da+hra where eno=3;
procedure display(n in number);




è Program…

create or replace package countnetsal as
           function netsalary(n number) return number;
           procedure display;
end countnetsal;


create or replace package body countnetsal as
           function netsalary(n number) return number is
                       a number(10,2):=0;
                       d number(10,2);
                       c number(10,2);
                       g number(10,2);
                       no number;
                       b number(10,2);
                       cursor c_emp is select eno,basic from empac where eno=n;
           begin
                       open c_emp;
                       if c_emp%isopen then
                       loop
                       fetch c_emp into no,b;
                       exit when c_emp%notfound;
                       d:=b*0.59;
                       c:=b*0.02;
                       g:=b+c+d;
                       insert into salpac values(n,d,500,c,g,g-500);
                       dbms_output.put_line('Successfully Inserted');
                       end loop;
                       else
                         dbms_output.put_line('Successfully Not Inserted');
                       end if;
                       close c_emp;
                       return(a);
           end;
           procedure display as
                      
cursor c_sal is select empac.eno,ename,gross,net from empac,salpac where empac.eno=salpac.eno;
                       no empac.eno%type;
                       name empac.ename%type;
                       g salpac.gross%type;
                       n salpac.net%type;
                       tg number(10,2):=0;
                       tn number(10,2):=0;
           begin
                       open c_sal;
                       loop
                       fetch c_sal into no,name,g,n;
                       exit when c_sal%notfound;
                       tg:=tg+g;
                       tn:=tn+n;
                       dbms_output.put_line('   '||no ||'            '||name||'  '||g||'   '||n);
                       end loop;
                       close c_sal;
dbms_output.put_line('__________________________________');
dbms_output.put_line('                Total                    '       tg ||'     '||tn);
           end;               
end countnetsal;

declare
           n number;
           a number(10,2);
begin
           n:=&n;
           a:=countnetsal.netsalary(n);
           dbms_output.put_line(' Employee No     Employee Name     GrossSalary       Netsalary');
           countnetsal.display();
end;



declare
           cursor c_sal is select empac.eno,ename,gross,net from empac,salpac where empac.eno=salpac.eno;
           no empac.eno%type;
           name empac.ename%type;
           g salpac.gross%type;
           n salpac.net%type;
           tg number(10,2):=0;
           tn number(10,2):=0;

begin
           open c_sal;
           loop
           fetch c_sal into no,name,g,n;
           exit when c_sal%notfound;
           tg:=tg+g;
           tn:=tn+n;
           dbms_output.put_line('  '||no||'     '||name||'       '||g||'        '||n);
           end loop;
           close c_sal;
           dbms_output.put_line('  Total Gross       '||tg);
           dbms_output.put_line('  Total Netsalary    '||tn);
end;
          
create or replace procedure check1 is
begin
  dbms_output.put_line('HELLO');
end;
begin
check1();
end;



·         Program 27:-

                                    Write a Trigger on Insert to convert the name into capital letters.


è Program…

create  or replace trigger t1
             before insert on stud
             for each row
declare
no number;
name varchar(10);
begin
             no:=:new.sno;
             name:=upper(:new.sname);
             dbms_output.put_line('the '||name||'No '||no);
             :new.sno:=no;
             :new.sname:=name;
end;




****************************************Extra*************************************

create or replace trigger Upperletter
             after insert on stud
             for each row
declare
sno number;
sname varchar(10);
begin
             sno:=:new.sno;
             sname:=:new.sname;
             insert into stud1 values(Sno,Sname);
end;


*****************************************************************************


declare
sname varchar(10);
begin
                        sname:=upper(:new.sname);
            
end;



·         Program 28:-

                            Write a Trigger to check the Pincode is exactly six digit or not.


è Program…

create  or replace trigger tpin
             before insert on pin
             for each row
declare
no varchar(10);
begin
             no:=length(:new.p);
             if no<>6 then

                        raise_application_error(-20001,'Pincode must be six digit');
              end if;
end;


·         Program 29:-

                        Write a Trigger to check the mark is not zero or negative.


è Program…

create  or replace trigger neg
               before insert on tran
               for each row
declare
no number;
begin
               no:=:new.obt;
               if no<=0 then
                     raise_application_error(-200002,'Number is Negative');
              end if;
end;



·         Program30:-

                          Write a Trigger that check the student_id must be start with ‘M’.

è Program…

create  or replace trigger cap
           before insert on str
           for each row
declare
name char(10);
begin
           name:=:new.sname;
           if name not like '%M%'  then
                       raise_application_error(-20003,'Name is not start with M');
              end if;
end;



·         Program31:-

                                      Develop a PL/SQL Project for Student Marksheet using all the database object (Cursor,Function, Procedure, Package and Trigger) on following table.
                                    Student_Master(sno, sname, total_marks, marks_obtain, result)
                                    Subject_Master(sub_code, sub_name, sub_max_mark)
                                    Student_Transaction(sno, sub_code, marks_obtain)

             Student Mark Sheet
Sr. No.
Roll. No.
Name
Total Marks
Mark Obtain
Grade







è Table Creation…

create table student(
sno number primary key,
sname char(10),
total number,
obt number,
grade char(10));

insert into student(sno,sname,total) values(1,'Ronak',250);
insert into student(sno,sname,total) values(2,'Reena',250);

create table subject(
sbno number primary key,
sbname char(10),
sbm number);

insert into subject values(301,'CPP',48);
insert into subject values(302,'OR',48);

create table tran(
sno number references student,
sbno number references subject,
obt number);

insert into tran values(1,301,25);
insert into tran values(1,302,48);
insert into tran values(2,301,55);
insert into tran values(2,302,48);


è Program…

*************************package****************************

create or replace package mixt as
            procedure stdins(no number);
            function stdgrd(no number) return number;
            procedure disp;
end mixt;

create or replace package body mixt as
            procedure stdins(no number) as
             cursor c_mark is select sum(obt) from tran where sno=no;
             tt number;
            begin
             open c_mark;
             loop
             fetch c_mark into tt;
             exit when c_mark%notfound;
                          update student set obt=tt where sno=no;
             end loop;
            end;
           
            function stdgrd(no number) return number as
             cursor c_mark is select obt from student where sno=no;
             tt number;
             g char(10);
            begin
             open c_mark;
             loop
             fetch c_mark into tt;
             exit when c_mark%notfound;
                        if tt>=35 and tt<=50 then
                                    g:='Pass';
                        elsif tt>=51 and tt<=100 then
                                    g:='Seond';
                        elsif tt>=101 and tt<=200 then
                                    g:='First';
                        elsif tt>=200 and tt<=250 then
                                    g:='Dist';
                        end if;
              update student set grade=g where sno=no;
             end loop;
            return 0;
            end;
           
            procedure disp as
                         i number:=1;
                         cursor c_stud is select sno,sname,total,obt,grade from student;
                         no student.sno%type;
                         name student.sname%type;
                         t student.total%type;
                         o student.obt%type;
                         g student.grade%type;
              begin
                        Dbms_output.put_line('Sr.No     Rollno     Name     Totalmarks     Marksobtain         Grade');
                        open c_stud;
                         loop
                         fetch c_stud into no,name,t,o,g;
                         exit when c_stud%notfound;
                          dbms_output.put_line( i ||'        '||no||'     '||name||'     '||t||'             '||o||'                       '||g);
                         i:=i+1;
                        end loop;                      
            end;
end mixt;



declare
n number;
a number;
begin
n:=&n;
mixt.stdins(n);
a:=mixt.stdgrd(n);
mixt.disp;
end;
           

*************************Trigger***************************

create or replace trigger tck
            before insert on tran
            for each row
declare
            t number;
            m number;
begin


            select sum(obt) into t from tran where sno=:new.sno;
            t:=t+:new.obt;
            select sbm into m from subject where sbno=:new.sbno;
            if t>250 or :new.obt>m then
                  raise_application_error(-200004,'Total of obtain marks must less than 250');
            end if;
end;


·         Program32:-

                               Develop a PL/SQL Project for Employee Payslip using all the database object (Cursor,Function, Procedure, Package and Trigger) on following table.

Emp_Master(eno,ename,desc_code, basic, gross_sal, net_sal)
                                    Pay_Master(desc_code, desc_name, da, hra, it, max_basic)
                                    Emp_Transaction(eno,desc_code, basic)

                                               Nisarg Softech Pvt. Ltd.                          Date :
Salary Slip for Month of :-
Employee Number
Employee Name
Gross Salary
Net Salary





Total
********
********


è Table Creation…


create table empmas(
eno number primary key,
ename char(10),
dcode number references paymas,
basic number,
grossal number,
netsal number);

insert into empmas(eno,ename,dcode,basic) values(1,'Ronak',1,10000);
insert into empmas(eno,ename,dcode,basic) values(2,'Reena',2,20000);






create table paymas(
dcode number primary key,
dname char(10),
da number,
hra number,
it number,
mbasic number);

insert into paymas values(1,'Computer',59,500,2,50000);
insert into paymas values(2,'Printer',49,400,2,20000);

create table emptran(
eno number,
dcode number,
basic number);

è Program…

****************************Package*****************************

create or replace package pacemp as
               function countsals(no number) return number;
               procedure disp;
end pacemp;

create or replace package body pacemp as
               function countsals(no number) return number as
               cursor c_empc is select da,hra,it,basic from paymas,emptran where emptran.dcode=no and emptran.dcode=paymas.dcode;
               b emptran.basic%type;
               d paymas.da%type;
               h paymas.hra%type;
               i paymas.it%type;
               n empmas.netsal%type;
               g empmas.grossal%type;
               begin
                   open c_empc;
                    loop
                     fetch c_empc into d,h,i,b;
                      exit when c_empc%notfound;
                       d:=b*(d/100);
                       i:=b*(i/100);
                       g:=b+d+h;
                       n:=g-i;
                    
                    
update empmas set grossal=g,netsal=n where dcode=no; 
                   end loop;
               return 0;
               end;

               procedure disp as
                     cursor c_empt is select eno,ename,grossal,netsal from empmas;
                     no empmas.eno%type;
                     name empmas.ename%type;
                     g empmas.grossal%type;
                     ns empmas.netsal%type;
               begin
                     dbms_output.put_line('  Emp No                Emp Name       Gross sal      Net sal');
                     open c_empt;
                     loop
                     fetch c_empt into no,name,g,ns;
                     exit when c_empt%notfound;
                                 dbms_output.put_line(no||'            '||name||'          '||g||'            '||ns);               
                     end loop;
               end;
end pacemp;

declare
               no number;
               a number;
begin
               no:=&no;
               a:=pacemp.countsals(no);
               pacemp.disp;
end;
              

*****************************Trigger***************************

create or replace trigger empt
               before insert on empmas
               for each row
declare
               b number;
begin
               select mbasic into b from paymas where dcode=:new.dcode;
              

if :new.basic>b then

               raise_application_error(-200005,'Basic less than maximum basic');
              
else
                             insert into emptran values(:new.eno,:new.dcode,:new.basic);
               end if;
end;