Salary Report program using PL/SQL

Objective

A salary statement contains Name, Basic pay , allowance total , deduction

(include , IT ),  gross pay, and net pay .

Allowance      =    20% of basic pay

gross pay       =   Basic pay + Allowance.

Deduction      =  10% of basic pay

income tax is calculated on the basis of annual income under the following condition.

annual salary                                                      Income tax

  < =300,000                                                           Nil

  >30,000 but <55,000                                         30% of excess over the amount Rs = 30,000/-

 >=55,000                                                                50% of excess over the amount Rs = 55,000/-

program:

SQL > Create table salary (empno number (5) primary key , name varchar(20), basis pay

number (10,2));

Table Created.

SQL> Insert into . salary values (1001 , 'Baby' ,15,000);

1 row Created

SQL > Insert into salary values (1002, 'Hanna', 20,000);

1 row Created

SQL>insert into salary values (1003, 'chinnu',6000);

1 row Created

SQL > insert into salary values (1004, 'megha', 400,000);

1 row Created.

SQL > Insert into salary values (1005, 'swetha', 5200);

1 row Created.

SQL > ALTER TABLE salary add (allowance number (10,2) , deduction number (10,2),gross pay number (10,2), net pay number (10,2), income tax number (10,2));

Table Altered.

SQL > ed Sal;

declare

        v-allw  salary allowance%type ;

        v-gp  salary gross pay%type ;

        v-ded salary deduction%type ;

         v-net salary net pay%type ;

         v-inc salary income tax%type

 cursor c selected  *  from  salary ;

an in number (10,2);

begin

     for i in c loop

     v- allw : = (20 * i . basic pay) /100 ;

     v- gp   : = i . basic pay + v-allw ;

     v- ded : = (10* i . basic pay)/100 ;

     v-net   : = v-gp - v-ded ;

     an-in   : = v-net  * 12 ;

     if (an -in < =  30,000) then

     v- inc  := 0

    elsif (an- in between 30,000 and 55,000) then

    v- inc  := (an in - 30,000) * 30/100 ;

    else  

    v-inc : = ((an - in -55,000) * 50/100+ (30 * 25,000)/100) ;

    end  if;

     update salary set allowance = v- allw,

     gross pay = v-gp , deduction = v-ded ,

     net pay = v-net , income = v-inc

     where empno = i . empno;

    end  loop;

    end  ;

    SQL > @ Sal ;

    25/

    PL/ SQL  Procedure successfully comleted.

    SQL > select * from salary ;

    empno    name    basic pay   allowance  grosspay   dedution    netpay        income

     1001       baby       15000         3000          18000        1500          16500          81500

     1002       Hanna    20,000        4000          24,000       2000          22,000         11,2000

     1003       chinnu    6000           1200          7200           600           6600            47100

     1004       megha    40,000        8000         48000         4000         44000          24,4000

     1005       swetha    5200           1040         6240           520           5720            14320