Students Result Calculation program using PL/SQL

Objective

An examination has been conducted to a class of 5 students and four scores of each student have been provided in the data along with  register number and name. Write a PL/SQL block to do the following

Assign a  letter grade to each student based on the average score;

Average Score     Grade

90-100                   a

75-89                      b

60-74                     c

50-59                     d

0-49                        e

Program

SQL> create table studres(regno number(4) primary key, name varchar(20), paper1 number(2), paper2 number(2), paper3 number(2), paper4 number(2));

Table created

SQL> insert into studres values(1001,’mini’, 23, 49, 44, 46);

1 row created

SQL> insert into studres values(1002,’safeer’, 40, 30, 20, 10);

1 row created

SQL> insert into studres values(1003,’baby’, 49, 39, 46, 45);

1 row created

 SQL> insert into studres values(1004,’danish’, 40, 10, 20, 22);

1 row created

Read More

News Paper Vendor program using PL/SQL

Objective

 Every morning a newspaper vendor newspapers in wholesale from a distributor for 60 paise. He sells them in retail for 75 paise. At the end of the day the unsold papers are returned to the distributor for 30 paise rebate per paper. Write a PL/SQL code block to prepare a report for the newspaper vendor in the following format with 5 weeks data.

Week     Bought      Sold        Return        Profit        Loss

       1                   60                  30         

      2                   75                  40

        .                     .                      .

program:

SQL>Create table vendor (week number(2) primary key, bought number(2), sold number(2));

Table created.

SQL>inser into vendor values(1,100,75);

1 row created.

SQL>insert into vendor values(2,70,60);

1 row created.

SQL>insert into vendor values(3,45,20);

1 row created.

SQL>insert into vendor values(4,60,55);

1 row created.

SQL>insert into vendor values(5,50,50);

1 row created.

SQL>ALTER TABLE vendor add(return number(10), profit number(6,2), loss number(6,2));

Table altered.

SQL> ed news;

        declare

Read More

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

Read More

Sales report using SQL

Objective

A sales report contains the  record with the following fields: itemid, itemname, salesprice, quantity and total price. Write a PL/SQL procedure to generate sales report.

Program

SQL>create table sales(itemid varchar(10), itemname varchar(20), salesprice number(6,2), quantity number(4));

Table created

SQL>insert into sales values(‘s001’,’pen’,10,10);

1 row created

SQL>insert into sales values(‘s002’,’pencil’,4,20);

1 row created

SQL>insert into sales values(‘s003’,’scale’,5,10);

1 row created

SQL>insert into sales values(‘s004’,’eraser’,6,20);

1 row created

SQL>insert into sales values(‘s005’,’notebook’,15,10);

1 row created

SQL> alter table sales add (tot_price number(6,2));

Table altered

SQL>declare

Read More

Library information System using SQL

Objective

Create a library information system

Program

SQL> create table library(book_id varchar(10) primary key, b_name varchar(20) notnull, author varchar(20) notnull);

Table created

SQL>insert into library values(‘b001’,’vb’,’abbas’);

1 row created

SQL>insert into library values(‘b002’,’cpp’,’kanethkar’);

1 row created

SQL>insert into library values(‘b003’,’SAD’,’Alias’);

1 row created

SQL>insert into library values(‘b004’,’java’,’kanethkar’);

1 row created

SQL> ed lib

Declare

Read More

Hotel bill system using SQL

Objective

Create a hostel bill calculating system

Program

SQL> create table hotel(cust_id varchar(6) primary key, cust_name varchar(20), item varchar(20), quantity number(6,2), price number(10,2));

Table created

SQL> insert into hotel values(‘H001’,’midhun’,’tea’,3,5);

1 row created

SQL> insert into hotel values(‘H002’,’praveen’,’broast’,2,100);

1 row created

SQL> insert into hotel values(‘H003’,’dileep’,’rice’,3,20);

1 row created

SQL> insert into hotel values(‘H004’,’shiju’,’coffee’,4,10);

1 row created

SQL> alter table hotel add (total number(10,2));

Table altered

SQL> declare

2 cursor hot is select * from hotel;

3 v_t hotel.total%type;

4 begin

Read More

Hostel Accounting system

Objective

Create a hostel accounting system

Program

SQL>create table hostel(stud_id varchar(10) primary key, stud_name varchar(20), rent number(6,2), mess number(6,2),ectr number(6,2), days number(2) check(days<31)and (days>0));

Table created

SQL>insert into hostel values(‘100’,’vishnu’,12,5,3,24);

1 row created

SQL>insert into hostel values(‘101’,’rabeeh’,12,5,3,20);

1 row created

SQL>insert into hostel values(‘102’,’tinu’,12,5,3,28);

1 row created

SQL>insert into hostel values(‘103’,’sarath’,12,5,3,14);

1 row created

SQL> alter table hostel add(tot_fee number(6,2));

Table altered

SQL>ed hostel

Declare

Read More

Hospital Management System using PL/SQL

Hospital Information System having the following Information

Patient Number, Patient Name, Age, Doctor, Patient, type(in/out), Consultation charge, Blood test charge, Xray charge, other test

Write a PL/SQL procedure to retrieve the following

     1.  Patient undergo blood test

     2.  The patient taken x-ray

      3.  The patient who belong to patient’s category.

Program

SQL> create table patient(p_num number(10) primary key, p_name varchar(20),age number(3),dr varchar(20),p_type varchar(3) check(p_type=’in’ or p_type=’out’), cons_charge number(10,2), x_ray_charge number(10,2), b_t charge number(10,2), o_t_charge number(10,2));

Table Created

SQL> ed inspat

Insert into patient values(1,’anoop’,35,’anees’,’in’,250,125,360,130);

Insert into patient values(2,’najeeb’,35,’rajiya’,’out’,364,162,355,825);

Read More

Electricity billing system using PL/SQL

Objective

Create an electricity billing system, rent rs 20/-

Slab 1 : 1-40 units-0

Slab2: 40-80 units -40

Slab3: >80 -1.40+excess of 80

Program

SQL> create table electricity(cons_id varchar(4) primary key, c_name varchar(20), rent number(2) check (rent=20), unit number(6));

Table created

SQL> insert into electricity values (‘E001’,’deepika’,20,35);

1 row created

SQL> insert into electricity values (‘E002’,’varna’,20,61);

1 row created

SQL> insert into electricity values (‘E003’,’arun’,20,80);

1 row created

SQL> insert into electricity values (‘E004’,’rahul’,20,90);

1 row created

Read More