GENERATE A HOSPITAL INFORMATION SYSTEM USING THE HOSPITAL TABLE

1)Patients undergone bloodtest
2)Patients who have taken X-ray
3)Details of in-patients


create table hospital(pid varchar2(4),pname varchar2(7),page number(3),doctor
varchar2(7),p varchar2(10),consfee number(3),btest number(3),xfee number(3),other
number(3),total number(5));
insert into hospital values('p101','Sona',23,'Hasna','Outpatient',100,50,20,10,null);
insert into hospital values('p102','Sithu',18,'Thennu','Outpatient',150,30,45,15,null);
insert into hospital values('p103','Sruthi',32,'Banu','inpatient',200,0,15,20,null);
insert into hospital values('p104','Meher',26,'Rahi','Outpatient',150,23,0,25,null);
insert into hospital values('p105','Thafsi',21,'Renju','inpatient',100,0,40,30,null);
declare
cursor c1 is
select*from hospital where btest>0 order by pname;
btest c1%rowtype;
begin
dbms_output.put_line('ABC HOSPITAL CALICUT,KERALA');
dbms_output.put_line('DETAILS OF PATIENTS UNDERGONE BLOOD TEST');
dbms_output.put_line('-------------------------------------------------------');
dbms_output.put_line(‘PID PNAME P_AGE DOCTOR P BTEST’);
dbms_output.put_line(‘--------------------------------------------------------‘);
open c1;
loop
fetch c1 into btest;
exit when c1%notfound;
dbms_output.put_line('btest.pid,btest.pname,btest.page,btest.doctor,btest.p,btest.btest');
end loop;
close c1;
end;
/

OUTPUT

DETAILS OF PATIENTS UNDERGONE BLOODTEST

------------------------------------------------------------------------------------------------------------

PID PNAME P_AGE DOCTOR P BTEST

-----------------------------------------------------------------------------------------------------------

p101 Sona 23 Hasna Out-patient 50

p102 Sithu 18 Thennu Out-patient 30

p104 Meher 26 Rahi Out-patient 23

-----------------------------------------------------------------------------------------------------------

declare
cursor c1 is
select*from hospital where xfee>0 order by pname;
vxtest c1%rowtype;
begin
dbms_output.put_line('ABC HOSPITAL CALICUT - 3,KERALA');
dbms_output.put_line('--------------------------------------');
dbms_output.put_line(‘PID PNAME P_AGE P XFEE');
dbms_output.put_line('----------------------------------------------------');
open c1;
loop
fetch c1 into vxtest;
exit when c1%notfound;
dbms_output.put_line('vxtest.pid,vxtest.pname,vxtest.page,vxtest.doctor,vxtest.p,vxtest.xfee'
);
end loop;
close c1;
end;
/

OUTPUT

-------------------------------------------------------------------------------------------

PID PNAME P_AGE P XFEE

------------------------------------------------------------------------------------------

p101 Sona 23 Out-patient 20

p102 Sithu 18 Out-patient 45

p103 Sruthi 32 In-patient 15

p105 Thafsi 21 In-patient 40

-------------------------------------------------------------------------------------------

 declare
cursor c1 is
select*from hospital where p='inpatient' order by pname;
vtype c1%rowtype;
begin
dbms_output.put_line('ABC HOSPITAL CALICUT-3,KERALA');
dbms_output.put_line('DETAILS OF THE IN-PATIENTS');
dbms_output.put_line('---------------------------------------------------');
dbms_output.put_line('PID PNAME DOCTOR P CONSFEE BTEST XFEE OTHER
TOTAL');
dbms_output.put_line('---------------------------------------------------');
open c1;
loop
fetch c1 into vtype;
exit when c1%notfound;
vtype.total:=vtype.consfee+vtype.btest+vtype.xfee+vtype.other;
update hospital set total=vtype.total where vtype.pid=pid;
dbms_output.put_line('vtype.pid,vtype.pname,vtype.doctor,vtype.p,vtype.consfee,vtype.btest
,vtype.xfee,vtype.other,vtype.total');
end loop;
close c1;
end;
/

OUTPUT

ABC HOSPITAL CALICUT-3,KERALA
DETAILS OF THE IN-PATIENTS
--------------------------------------------------------------------------------------------------------------
PID PNAME DOCTOR P CONSFEE BTEST XFEE OTHER TOTAL
--------------------------------------------------------------------------------------------------------------
p103 Sruthi Banu In-patient 200 0 15 20 235
p105 Thafsi Renju In-patient 100 0 40 30 170
--------------------------------------------------------------------------------------------------------------

ABC HOSPITAL CALICUT-3,KERALAABC HOSPITAL CALICUT,KERALA

HOTEL BILLING STSTEM

create table hotel(cust_no number(5),cust_name varchar2(10),AC_NON_AC
varchar2(12),room_rent number(5),food_charge number(5),tip number(3),total
number(5));
insert into hotel values(100,'Hasna','AC',25000,1000,100,null);
insert into hotel values(101,'Sithara','NON_AC',5000,200,500,null);
insert into hotel values(102,'Thennal','AC',20000,1500,400,null);
insert into hotel values(103,'Sruthi','AC',23000,400,50,null);
insert into hotel values(104,'Banu','NON_AC',5000,600,30,null);
insert into hotel values(105,'Meher','NON_AC',6000,100,50,null);
declare
cursor c1 is
select*from hotel order by cust_no;
vhot c1%rowtype;
begin
dbms_output.put_line('THE TAJ HOTEL ,CALICUT,KERALA');
dbms_output.put_line('--------------------------------------------');
dbms_output.put_line('cust_no cust_name AC/NON_AC room_rent food_charge tip total');
dbms_output.put_line('----------------------------------------------------------------');
open c1;
loop
fetch c1 into vhot;
exit when c1%notfound;
vhot.total:=vhot.room_rent+vhot.food_charge-vhot.tip;
update hotel set total=vhot.total where vhot.cust_no=cust_no;
dbms_output.put_line('vhot.cust_no,vhot.cust_name,vhot.AC/NON_AC,vhot.room_rent,vho
t.food_charge,vhot.total');
end loop;
close c1;
dbms_output.put_line('----------------------------------------------------------------');
end;
/

OUTPUT

TAJ HOTEL ,CALICUT,KERALA
--------- ---------- ------------ --------- ----------- --------- ------------------- --------- ------------------
CUST_NO CUST_NAME AC_NON_AC ROOM_RENT FOOD_CHARGE TIP TOTAL
--------- ---------- ------------ --------- ----------- --------- -------------------- --------- ---------------
100 Hasna AC 25000 1000 100 25900
101 Sithara NON_AC 5000 200 500 4700
102 Thennal AC 20000 1500 400 21100
103 Sruthi AC 23000 400 50 23350
104 Banu NON_AC 5000 600 30 5570
105 Meher NON_AC 6000 100 50 6050
--------- ---------- ------------

TRAIN INFORMATION SYSTEM

create table train(tid number(5) primary key,tname varchar2(20));
create table passenger(pid varchar2(4) primary key,pname varchar2(10),sex
varchar2(6),mstatus varchar2(8),age number(3));
create table passtrain(trainno number(6) references train(tid),passid varchar2(5)
references passenger(pid),ticketno varchar2(9),seatno varchar2(5));
insert into passenger values('p101','Hasna','female','single',18);
insert into passenger values('p102','Deepu','male','married',27);
insert into passenger values('p103','Banu','female','single',19);
insert into passenger values('p104','Raman','male','single',30);
insert into passenger values('p105','Sithara','female','married',30);
insert into passenger values('p106','John','male','single',14);
insert into passenger values('p107','Meher','female','single',25);
insert into passenger values('p108','Raheela','female','married',50);
insert into passenger values('p109','Sruthi','female','single',33);
insert into train values(6347,'Manglore express');
insert into train values(6328,'Malabar express');
insert into train values(6123,'Madras mail');
insert into train values(6227,'Nizamudheen express');
insert into train values(6326,'Kerala express');
insert into passtrain values(6347,'p101','TNR-1001','s1-33');
insert into passtrain values(6328,'p109','TNR-1010','s2-62');
insert into passtrain values(6347,'p105','TNR-1021','s1-56');
insert into passtrain values(6123,'p104','TNR-1034','s4-23');
insert into passtrain values(6227,'p103','TNR-1054','s7-45');
insert into passtrain values(6227,'p106','TNR-1044','f1-67');
insert into passtrain values(6347,'p102','TNR-1089','s1-62');
insert into passtrain values(6326,'p107','TNR-1076','f2-11');
insert into passtrain values(6347,'p108','TNR-1034','s2-32');
set serveroutput on;
declare
cursor trains is select passid,pname,sex,age,ticketno,seatno,trainno,tname from
passenger,train,passtrain where pid=passid and trainno=tid order by trainno;
tpassengers number;
tmale number;
tmalemarried number;
tage30_60 number;
tabove60 number;
tfemale number;
tfemalemarried number;
begin
dbms_output.put_line('date:'||sysdate);
dbms_output.put_line('SOUTHERN RAILWAY,CALICUT,KERALA');
dbms_output.put_line('PASSENGER DATA FOR THE DAY:'||sysdate);
dbms_output.put_line('---------------------------------------------');
select count(passid) into tpassengers from passtrain;
select count(passid) into tmale from passtrain,passenger where
passtrain.passid=passenger.pid and passenger.sex='male';
select count(passid) into tmalemarried from passtrain,passenger where
passtrain.passid=passenger.pid and passenger.sex='male' and passenger.mstatus='married';
select count(passid) into tfemale from passtrain,passenger where
passtrain.passid=passenger.pid and passenger.sex='female';
select count(passid) into tfemalemarried from passtrain,passenger where
passtrain.passid=passenger.pid and passenger.sex='female' and passenger.mstatus='married';
select count(passid) into tage30_60 from passtrain,passenger where
passtrain.passid=passenger.pid and passenger.age>29 and passenger.age<61;
select count(passid) into tabove60 from passtrain,passenger where
passtrain.passid=passenger.pid and passenger.age>60;
dbms_output.put_line('total number of passengers :'||tpassengers);
dbms_output.put_line('total number of male passengers is :'||tmale);
dbms_output.put_line('total number of married male passengers is :'||tmalemarried);
dbms_output.put_line('total number of female passengers is :'||tfemale);
dbms_output.put_line('total number of married female passengers is :'||tfemalemarried);
dbms_output.put_line('total number of passengers in age group 30_60 :'||tage30_60);
dbms_output.put_line('total number of passengers in age group above 60 is :'||tabove60);
end;
/

OUTPUT

date:19-MAR-10
SOUTHERN RAILWAY,CALICUT,KERALA
PASSENGER DATA FOR THE DAY:19-MAR-10
---------------------------------------------
total number of passengers :9
total number of male passengers is :3
total number of married male passengers is :1
total number of female passengers is :6
total number of married female passengers is :2
total number of passengers in age group 30_60 :4
total number of passengers in age group above 60 is :0

HOSPITAL INFORMATION SYSTEM

create table hospital(pid varchar2(5),pname varchar2(10),p_age number(5),doctor
varchar2(10),p varchar2(11),consfee number(5),total number(5));
insert into hospital values('p101','Banu',23,'Shukoor','out-patient',100,null);
insert into hospital values('p102','Hasna',18,'Thennal','in-patient',80,null);
insert into hospital values('p103','Nasiya',34,'Reshma','out-patient',90,null);
insert into hospital values('p104','Raheela',45,'Sruthi','in-patient',50,null);
insert into hospital values('p105','Ranjini',56,'Sithara','out-patient',100,null);
insert into hospital values('p106','Sona',12,'Jumana','in-patient',50,null);
insert into hospital values('p107','Thafseena',25,'Arifa','out-patient',100,null);
insert into hospital values('p108','Meher',08,'Sabreena','in-patient',80,null);
insert into hospital values('p109','Deepu',29,'Neeraj','out-patient',100,null);
insert into hospital values('p110','Bimal',35,'Vijeesh','in-patient',100,null);
declare
cursor c1 is
select * from hospital order by pid;
vtype c1%rowtype;
begin
dbms_output.put_line('MIMS HOSPITAL CALICUT-3,KERALA');
dbms_output.put_line('DETAILS OF THE PATIENTS');
dbms_output.put_line('----------------------------------------');
dbms_output.put_line('PID PNAME P_AGE DOCTOR P CONSFEE TOTAL');
dbms_output.put_line('----------------------------------------------');
open c1;
loop
fetch c1 into vtype;
exit when c1%notfound;
vtype.total:=vtype.consfee;
update hospital set total=vtype.total where pid=vtype.pid;
dbms_output.put_line('vtype.pid,vtype.pname,vtype.page,vtype.doctor,vtype.p,vtype.consfee
,vtype.total');
end loop;
dbms_output.put_line('-----------------------------------');
close c1;
end;
/

OUTPUT

MIMS HOSPITAL CALICUT-3,KERALA
DETAILS OF THE PATIENTS
----- ---------- --------- ---------- ----------- --------- ------------------ ------- -----
PID PNAME P_AGE DOCTOR P CONSFEE TOTAL
----- ---------- --------- ---------- ----------- --------- ------------------ ------- -----
p101 Banu 23 Shukoor out-patient 100 100
p102 Hasna 18 Thennal in-patient 80 80
p103 Nasiya 34 Reshma out-patient 90 90
p104 Raheela 45 Sruthi in-patient 50 50
p105 Ranjini 56 Sithara out-patient 100 100
p106 Sona 12 Jumana in-patient 50 50
p107 Thafseena 25 Arifa out-patient 100 100
p108 Meher 8 Sabreena in-patient 80 80
p109 Deepu 29 Neeraj out-patient 100 100
p110 Bimal 35 Vijeesh in-patient 100 100
----- ---------- --------- ---------- ----------- --------- ------------------ ------- -----

SALARY REPORT OF A FIRM

create table employ(empno varchar2(5),name varchar2(10),basic number(10),da
number(7),gross number(8),pf number(8),net number(8),annual number(10),tax
number(13));
insert into employ values('E101','Hasna',899563,null,null,null,null,null, null);
insert into employ values('E102','Thennal',895476,null,null,null,null,null, null);
insert into employ values('E103','Sithara',756243,null,null,null,null,null, null);
insert into employ values('E104','Sruthi',985243,null,null,null,null,null, null);
insert into employ values('E105','Meher',951543,null,null,null,null,null, null);
declare
cursor c1 is
select * from employ order by empno;
vsal c1%rowtype;
basic number;
da number;
gross number;
pf number;
net number;
annual number;
tax number;
begin
dbms_output.put_line('date:'||sysdate);
dbms_output.put_line('THE ABC PRPIVATE LIMITED');
dbms_output.put_line('SALARIES REPORT FOR THE YEAR 2010');
dbms_output.put_line('EMPNO NAME BASIC DA GROSS PF NETSAL
ANNUALSAL TAX');
dbms_output.put_line('--------------------------');
OPEN C1;
loop
fetch c1 into vsal;
exit when c1%notfound;
vsal.da:=vsal.basic*0.4;
vsal.gross:=vsal.basic+vsal.da;
vsal.pf:=vsal.basic;
vsal.net:=vsal.gross-vsal.pf;
vsal.annual:=vsal.net*12;
if(vsal.annual<=100000) then
vsal.tax:=0;
else if(vsal.annual<=150000) then
vsal.tax:=(vsal.annual-100000)*0.1;
else if(vsal.annual<=250000) then
vsal.tax:=5000+(vsal.annual-150000)*0.2;
else
vsal.tax:=25000+(vsal.annual-100000)*0.3;
end if;
end if;
end if;
update employ set
da=vsal.da,gross=vsal.gross,pf=vsal.pf,net=vsal.net,annual=vsal.annual,tax=vsal.tax
where empno=vsal.empno;
dbms_output.put_line('vsal.empno,vsal.name,vsal.basic,vsal.da,vsal.gross,vsal.pf,vs
al.net ,vsal.annual,vsal.tax');
end loop;
close c1;
dbms_output.put_line('---------------');
dbms_output.put_line('tax');
end;
/

OUTPUT

----- ---------- --------- --------- --------- --------- --------- --------- ------------------ -----------------
EMPNO NAME BASIC DA GROSS PF NET ANNUAL TAX
----- ---------- --------- --------- --------- --------- --------- --------- ------------------ ------------
E101 Hasna 899563 359825 1259388 899563 359825 4317900 1290370
E102 Thennal 895476 358190 1253666 895476 358190 4298280 1284484
E103 Sithara 756243 302497 1058740 756243 302497 3629964 1083989
E104 Sruthi 985243 394097 1379340 985243 394097 4729164 1413749
E105 Meher 951543 380617 1332160 951543 380617 4567404 1365221
---- ---------- --------- --------- --------- --------- --------- --------- ------------------ -----------------

LIBRARY INFORMATION SYSTEM

create table library(cardno number(10),bookname varchar2(15),author
varchar2(10),subject varchar2(10),no_of_copies number(5),issue date,return
date,fine number(3));
insert into library values(101,'data structure','Abbas','computer',4,'1-jan-10','20-
jan-10',null);
insert into library values(102,'Visual Basic','Abbas','computer',1,'20-jan-10','25-
jan10',null);
insert into library values(103,'Mathilukal','Basheer','Novel',5,'25-jan-10','30-
jan-10',null);
insert into library values(104,'matrix','R.A Japan','Mathematics',2,'1-feb-10','10-
feb-10',null);
insert into library values(105,'Alkemist','Paulocoyle','Novel',3,'10-feb-10','20-
feb-10',null);
insert into library values(106,'James Bond','Fleming','Detective',1,'20-feb-10','28-
feb-10',null);
insert into library values(107,'Romeo Juliet','Shakespere','love story',1,'28-feb-10','20-
march-10',null);
insert into library values(108,'Warpeace','Tolstoy','Socialism',2,'20-march-10','25-
march-10',null);
insert into library values(109,'DBMS','Abbas','computer',2,'25-march-10','27-march-10',null);
insert into library values(110,'Microbiology','Ushakumari','Botany',1,'27-march-10','30-
march-10',null);
declare
cursor c1 is
select*from library order by cardno;
vlib c1%rowtype;
mdate int;
begin
dbms_output.put_line('date');
dbms_output.put_line('---------------------------');
dbms_output.put_line('farook college calicut');
dbms_output.put_line('cardno bookname author subject no_of_copies issue return
fine');
open c1;
loop
fetch c1 into vlib;
exit when c1%notfound;
mdate:=vlib.return-vlib.issue;
if(mdate<=8) then
vlib.fine:=2;
else if(mdate<=15) then
vlib.fine:=5;
else
vlib.fine:=10;
end if;
end if;
update library set fine=vlib.fine where cardno=vlib.cardno;
dbms_output.put_line('vlib.cardno,vlib.bookname,vlib.author,vlib.subject,vlib.no_of
_copies,vlib.issue,vlib.return,vlib.fine');
end loop;
dbms_output.put_line('--------------------------------------------------------------------------
---------------------------');
close c1;
end;
/

OUTPUT

------- --------------- ---------- ---------- ------------ --------- --------- ---------------- ----
CARDNO BOOKNAME AUTHOR SUBJECT NO_OF_COPIES
ISSUE RETURN FINE
------- --------------- ---------- ---------- ------------ --------- --------- --------- ------- ----
101 data structure Abbas computer 4
01-JAN-10 20-JAN-10 10
102 Visual Basic Abbas computer 1
20-JAN-10 25-JAN-10 2
103 Mathilukal Basheer Novel 5
25-JAN-10 30-JAN-10 2
105 Alkemist Paulocoyle Novel 3
10-FEB-10 20-FEB-10 5
106 James Bond Fleming Detective 1
20-FEB-10 28-FEB-10 2
107 Romeo Juliet Shakespere love story 1
28-FEB-10 20-MAR-10 10
108 Warpeace Tolstoy Socialism 2
20-MAR-10 25-MAR-10 2
109 DBMS Abbas computer 2
25-MAR-10 27-MAR-10 2
110 Microbiology Ushakumari Botany 1
27-MAR-10 30-MAR-10 2
------- --------------- ---------- ---------- ------------ --------- --------- --------- -------

ELECTRICITY BILLING SYSTEM

create table electricity(customer_id varchar2(11),ename varchar2(8),phase
number(1),prereading number(4)
,currentreading number(4),unit_consumed number(4),metercharge
number(2),charge_unit number(3),total_charge number(6));
insert into electricity values('e101','Hasna',1,456,564,null,null,null,null);
insert into electricity values('e102','Umaibanu',3,1235,1545,null,null, null,null);
insert into electricity values('e103','Sruthi',3,2785,3575,null,null,null,null);
insert into electricity values('e104',Raheele',2,2345,3458,null,null,null,null);
declare i
cursor c1 is
select * from electricity order by customer_id;
eb c1%rowtype;
begin
dbms_output.put_line('------------------KSEB-----------------------');
dbms_output.put_line('------------------CUSTOMER BILL REPPORT FOR THE
MONTH OF -------------');
dbms_output.put_line('DATE:'||sysdate);
dbms_output.put_line('----------------------------------------------------------------');
dbms_output.put_line('customer_id ename prereading currentreading phase
unit_consumed metercharge charge_unit total_charge');
open c1;
loop
fetch c1 into eb;
exit when c1%notfound;
if(eb.phase=1) then
eb.metercharge:=20;
else
eb.metercharge:=40;
end if;
eb.unit_consumed:=(eb.currentreading)-(eb.prereading);
if((eb.unit_consumed)>1 and (eb.unit_consumed)<40) then
eb.charge_unit:=0;
else if((eb.unit_consumed)>40 and (eb.unit_consumed)<80) then
eb.charge_unit:=0.4;
else
eb.charge_unit:=1.4+((eb.unit_consumed)-80)*0.4;
end if;
end if;
if (eb.unit_consumed<40 and eb.phase=3) then
eb.total_charge:=240;
else
eb.total_charge:=( eb.unit_consumed) * (eb.charge_unit);
end if;
update electricity set
unit_consumed=eb.unit_consumed,metercharge=eb.metercharge,charge_unit=eb.ch
arge_unit,total_charge=eb.total_charge where customer_id=eb.customer_id;
dbms_output.put_line('eb.customer_id,eb.name,eb.prereading,eb.currentreading,eb.
phase,eb.unit_consumed,eb.metercharge,eb.charge_unit,eb.total_charge');
dbms_output.put_line('-------------------------------------------------');
end loop;
dbms_output.put_line('-----------------billing agent------------');
dbms_output.put_line('-----------------chief engineer------------------ ');
close c1;
dbms_output.put_line('--------------------------------------');
end;
/

OUTPUT

……………………………………………KSEB………………………………………
........ ……………….CUSTOMER BILL REPORT FOR THE MONTH OF …………………
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- --------
CUSTOMER_ID CNAME PHASE PREREADING CURRENTREADING
UNIT_CONSUMED METERCHARGE CHARGE_UNIT TOTAL_CHARGE
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- --------- --
e101 Hasna 1 456 564
108 20 13 1404
e102 Umaibanu 3 1235 1545
310 40 93 28830
e103 Sruthi 3 2785 3575
790 40 285 225150
e104 Raheela 2 2345 3458
1113 40 415 461895
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- --------- --

HOSTEL ACCOUNTING SYSTEM

create table hostel(slno number(3),name varchar2(10),roomno number(8),rent
number(8),electricity number(25),messfee number(5),oc number(10),specialfee
number(30),total number(25));
insert into hostel values(101,'Sithara',301,150,100,250,50,25,null);
insert into hostel values(102,'Thennal',302,150,100,45,50,25,null);
insert into hostel values(103,'Sruthi',303,100,150,36,50,25,null);
insert into hostel values(104,'Raheela',304,100,150,24,50,25,null);
insert into hostel values(105,'Reshma',305,150,150,25,50,25,null);
declare
cursor c1 is
select * from hostel order by slno;
vhos c1 %rowtype;
begin
dbms_output.put_line('date');
dbms_output.put_line('FAROOK COLLEGE CALICUT,KERALA');
dbms_output.put_line('HOSTEL DUE REPORT FOR THE MONTH OF
JANUARY 2010');
dbms_output.put_line('------------------------------------------------------------------');
dbms_output.put_line('slno name roomno rent electricity messfee OC specialfee
total');
open c1;
loop
fetch c1 into vhos;
exit when c1%notfound;
vhos.total:=vhos.rent+vhos.electricity+vhos.messfee+vhos.OC+vhos.specialfee;
update hostel set total=vhos.total where slno=vhos.slno;
end loop;
close c1;
dbms_output.put_line('---------------------------------------------------------------------');
dbms_output.put_line('warden');
end;
/

OUTPUT

FAROOK COLLEGE CALICUT.KERALA
HOSTEL DUE REPORT FOR THE MONTH OF JANUARY 2010
---- ---------- --------- --------- ----------- --------- --------- ---------- - ------ ----------- - -- ---
SLNO NAME ROOMNO RENT ELECTRICITY MESS FEE OC SPECIALFEE TOTAL
---- ---------- --------- --------- ----------- --------- --------- ---------- - ------ ----------- - -- ---
101 Sithara 301 150 100 250 50 25 575
102 Thennal 302 150 100 45 50 25 370
103 Sruthi 303 100 150 36 50 25 361
104 Raheela 304 100 150 24 50 25 349
105 Reshma 305 150 150 25 50 25 400
---- ---------- --------- --------- ----------- --------- --------- ---------- - ------ ----------- - -- ---

VENDOR REPORT FOR A FIRM

create table vendor(day date,bought number(5),sold number(5),ret
number(5),profit_loss number(5),remark varchar2(10));
insert into vendor values(to_date('01_jan_09'),100,80,null,null,null);
insert into vendor values(to_date('02_jan_09'),100,99,null,null,null);
insert into vendor values(to_date('03_jan_09'),100,98,null,null,null);
insert into vendor values(to_date('04_jan_09'),100,85,null,null,null);
insert into vendor values(to_date('05_jan_09'),100,90,null,null,null);
insert into vendor values(to_date('06_jan_09'),100,99,null,null,null);
insert into vendor values(to_date('07_jan_09'),100,78,null,null,null);
insert into vendor values(to_date('08_jan_09'),100,88,null,null,null);
insert into vendor values(to_date('09_jan_09'),100,87,null,null,null);
insert into vendor values(to_date('10_jan_09'),100,80,null,null,null);
insert into vendor values(to_date('11_jan_09'),100,85,null,null,null);
insert into vendor values(to_date('12_jan_09'),100,82,null,null,null);
insert into vendor values(to_date('13_jan_09'),100,86,null,null,null);
insert into vendor values(to_date('14_jan_09'),100,90,null,null,null);
insert into vendor values(to_date('15_jan_09'),100,95,null,null,null);
declare
cursor c1 is
select * from vendor order by day;
vven c1%rowtype;
begin
dbms_output.put_line('Date No.bought No.sold No.returned profit/loss remark');
dbms_output.put_line('--------------------------------------------------------');
open c1;
loop
fetch c1 into vven;
exit when c1%notfound;
vven.ret:=vven.bought-vven.sold;
vven.profit_loss:=vven.sold*.75+vven.ret*.30-vven.bought*.70;
if(vven.profit_loss<0)>0) then
vven.remark:='Profit';
else
vven.remark:='Nill';
end if;
end if;
update vendor set ret=vven.ret,profit_loss=vven.profit_loss where
day=vven.day;
dbms_output.put_line('vven.day,vven.bought,vven.sold,vven.ret,vven.profit_loss,re
mark');
end loop;
close c1;
end;
/

select * from vendor;
--------- --------- --------- --------- ----------- ---------- --------- -------
DAY BOUGHT SOLD RET PROFIT_LOSS
--------- --------- --------- --------- ----------- ---------- --------- ------
01-JAN-09 100 80 20 4
02-JAN-09 100 99 1 5
03-JAN-09 100 98 2 4
04-JAN-09 100 85 15 -2
05-JAN-09 100 90 10 1
06-JAN-09 100 99 1 5
07-JAN-09 100 78 22 -5
08-JAN-09 100 88 12 0
09-JAN-09 100 87 13 -1
10-JAN-09 100 80 20 -4
11-JAN-09 100 85 15 -2
12-JAN-09 100 82 18 -3
13-JAN-09 100 86 14 -1
14-JAN-09 100 90 10 1
15-JAN-09 100 95 5 3
--------- --------- --------- --------- ----------- ---------- --------- ------

Set server output on;
declare
cursor c1 is
select*from vendor order by day;
vven c1%rowtype;
begin
dbms_output.put_line('DATE NO.BOUGHT NO.SOLD NO.RETURNED
PROFIT_LOSS REMARK');
dbms_output.put_line('--------------------------------------------------------');
open c1;
loop
fetch c1 into vven;
exit when c1%notfound;
vven.ret:=vven.bought-vven.sold;
vven.profit_loss:=vven.sold*.75+vven.ret*.30-vven.bought*.70;
if(vven.profit_loss<0) then
vven.remark:='LOSS';
else if(vven.profit_loss>0) then
vven.remark:='PROFIT';
else
vven.remark:='NULL';
end if;
end if;
update vendor set ret=vven.ret,profit_loss=vven.profit_loss,remark=vven.remark
where day=vven.day;
dbms_output.put_line('vven.day,vven.bought,vven.sold,vven.ret,vven.profit_loss,vv
en.remark');
end loop;
close c1;
end
/

OUTPUT

--------- --------- --------- --------- ----------- ---------- --------- --------- --------- ---------
DAY BOUGHT SOLD RET PROFIT_LOSS REMARK
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- --------- --
01-JAN-09 100 80 20 4 LOSS
02-JAN-09 100 99 1 5 PROFIT
03-JAN-09 100 98 2 4 PROFIT
04-JAN-09 100 85 15 -2 LOSS
05-JAN-09 100 90 10 1 PROFIT
06-JAN-09 100 99 1 5 PROFIT
07-JAN-09 100 78 22 -5 LOSS
08-JAN-09 100 88 12 0 NULL
09-JAN-09 100 87 13 -1 LOSS
10-JAN-09 100 80 20 -4 LOSS
11-JAN-09 100 85 15 -2 LOSS
12-JAN-09 100 82 18 -3 LOSS
13-JAN-09 100 86 14 -1 LOSS
14-JAN-09 100 90 10 1 PROFIT
15-JAN-09 100 95 5 3 PROFIT
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- ---------

SALES REPORT

create table report(item_id varchar2(4),item_name varchar2(15),sales_price number(5),
quantity number(3),unit_price number(7),salestax number(5),total number(7));
insert into report values('f10','T.V',null,1,3000,null,null);
insert into report values('f11','Grinder',null,2,3500,null,null);
insert into report values('f12','Hometheatre',null,3,2500,null,null);
insert into report values('f13','DVDplayer',null,1,1000,null,null);
insert into report values('f14','Ipod',null,2,1000,null,null);
insert into report values('f15','Computer',null,1,18000,null,null);
insert into report values('f16','Mobile',null,4,10000,null,null);
insert into report values('f17','Stabilizer',null,3,750,null,null);
insert into report values('f18','Washing machine',null,2,4000,null,null);
insert into report values('f19','Vaccumcleaner',null,3,5000,null,null);
insert into report values('f20','Pen drive',null,5,900,null,null);
declare
cursor c1 is
select*from report order by item_id;
vrep c1%rowtype;
begin
dbms_output.put_line('* THE ABC MARKETING COMPANY,CALICUT,KERALA
SALES REPORT FOR THE MONTH OF JANUARY 2010');
dbms_output.put_line('--------------------------------------------------------------------------------------');
dbms_output.put_line('Item_id Item_name Sales_price Quantity Unit Price Salestax Total');
dbms_output.put_line('-------------------------------------------------------------------------');
open c1;
loop
fetch c1 into vrep;
exit when c1%notfound;
vrep.sales_price:=vrep.unit_price*vrep.quantity;
vrep.salestax:=(vrep.sales_price*6)/100;
vrep.total:=vrep.sales_price-vrep.salestax;
update report set sales_price=vrep.sales_price,salestax=vrep.salestax,total=vrep.total where
item_id=vrep.item_id;
dbms_output.put_line('vrep.item_id,vrep.item_name,vrep.sales_price,vrep.quantity,
vrep.unit_price,vrep.salestax,vrep.total');
end loop;
close c1;
dbms_output.put_line('-----------------------------------------------------------------------------------');
end;
/

OUTPUT

THE ABC MARKETTING COMPANY,CALICUT,KERALA SALES REPORT FOR
THE MONTH OF JANUARY 2010
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- ---------
ITEM ITEM_NAME SALES_PRICE QUANTITY UNIT_PRICE SALESTAX TOTAL
---- --------------- ----------- --------- ---------- --------- --------- ---- --------------- ----------- --------
f10 T.V 3000 1 3000 180 2820
f11 Grinder 7000 2 3500 420 6580
f12 Hometheatre 7500 3 2500 450 7050
f13 DVDplayer 1000 1 1000 60 940
f14 Ipod 2000 2 1000 120 1880
f15 Computer 18000 1 18000 1080 16920
f16 Mobile 40000 4 10000 2400 37600
f17 Stabilizer 2250 3 750 135 2115
f18 Washing machine 8000 2 4000 480 7520
f19 Vaccumcleaner 15000 3 5000 900 14100
f20 Pen drive 4500 5 900 270 4230
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- ------------

STUDENT INFORMATION SYSTEM

create table student (regno varchar2(3),name varchar2(7),paper1
number(3),paper2 number(3),paper3 number(3),paper4 number(3),total
number(3),average number(3),grade varchar2(5));
insert into student values('c42','Hasna',98,85,95,90,null,null,null);
insert into student values('c43','Banu',78,63,45,58,null,null,null);
insert into student values('c44','Sona',65,50,45,68,null,null,null);
insert into student values('c45','Mehu',78,77,75,76,null,null,null);
insert into student values('c46','Renju',68,67,60,74,null,null,null);
insert into student values('c47','rahi',98,84,95,87,null,null,null);
insert into student values('c48','Jumi',55,70,65,67,null,null,null);
insert into student values('c49','Naz',95,94,89,88,null,null,null);
declare
cursor c1 is
select * from student order by regno;
vstud c1%rowtype;
begin
dbms_output.put_line('------------------------------------');
dbms_output.put_line('regno name paper1 paper2 paper3 paper4 total average
grade');
dbms_output.put_line('------------------------------------');
open c1;
loop
fetch c1 into vstud;
exit when c1%notfound;
vstud.total:=vstud.paper1+vstud.paper2+vstud.paper3+vstud.paper4;
vstud.average:=(vstud.total)/4;
if(vstud.average>=90) then
vstud.grade:='A';
else if(vstud.average>=60) then
vstud.grade:='B';
else if(vstud.average>=50) then
vstud.grade:='C';
else if(vstud.average>=40) then
vstud.grade:='D';
else
vstud.grade:='E';
end if;
end if;
end if;
end if;
update student set total=vstud.total,average=vstud.average,grade=vstud.grade where
regno=vstud.regno;
dbms_output.put_line('vstud.regno,vstud.name,vstud.paper1,vstud.paper2,vstud.pap
er3,vstud.paper4,vstud.total,vstud.average,vstud.grade');
end loop;
close c1;
end;
/

OUTPUT

------- --------- --------- --------- --------- --------- --------- ---- --------------- ----------- -
REG NAME PAPER1 PAPER2 PAPER3 PAPER4 TOTAL AVERAGE GRADE
--- ------- --------- --------- --------- --------- --------- --------- - --- ------- --------- -- ------
c42 Hasna 98 85 95 90 368 92 A
c43 Banu 78 63 45 58 244 61 B
c44 Sona 65 50 45 68 228 57 C
c45 Mehu 78 77 75 76 306 77 B
c46 Renju 68 67 60 74 269 67 B
c47 rahi 98 84 95 87 364 91 A
c48 Jumi 55 70 65 67 257 64 B
c49 Naz 95 94 89 88 366 92 A
--------- -- --------- -- --------- -- --------- -- --------- -- --------- -- --------- -- -

SQl With 10 steps(Basics)

//

Step1:

Create table in SQL &Display the table

SQL> create table Employee (Eno int,Ename char(20),job char(20),grade char(5),Salary decimal(10,2));

Table created.
SQL> insert into Employee values (10,'Tom','Manager','a',10000);
1 row created.
SQL> insert into Employee values (12,'Anoop','Manager','a',20000);
1 row created.
SQL> insert into Employee values (14,'Raju','Programer','b',80000);
1 row created.
SQL> insert into Employee values (15,'Sabu','Analyst','c',70000);
1 row created.
SQL> insert into Employee values (16,'Shalih','clerk','d',1000);
1 row created.
SQL> insert into Employee values (17,'Narju','Manager','a',15000);
1 row created.

SQL> Select*from Employee;

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
10 Tom Manager a 10000
12 Anoop Manager a 20000
14 Raju Programer b 80000
15 Sabu Analyst c 70000
16 Shalih clerk d 1000
17 Narju Manager a 15000
6 rows selected.

Step2:

Select with condition

SQL> Select eno,ename,salary from employee;
ENO ENAME SALARY
--------- -------------------- ---------
10 Tom 10000
12 Anoop 20000
14 Raju 80000
15 Sabu 70000
16 Shalih 1000
17 Narju 15000
6 rows selected.

SQL> select eno,ename
2 from employee
3 where salary>=10000;

ENO ENAME
--------- --------------------
10 Tom
12 Anoop
14 Raju
15 Sabu
17 Narju

SQL> select*from Employee
2 where salary>700;

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
10 Tom Manager a 10000
12 Anoop Manager a 20000
14 Raju Programer b 80000
15 Sabu Analyst c 70000
16 Shalih clerk d 1000
17 Narju Manager a 15000
6 rows selected.

Step3:
Select with relation & logical operator

SQL> select ename,job,salary
2 from employee
3 where job='Manager';

ENAME JOB SALARY
-------------------- -------------------- ---------
Tom Manager 10000
Anoop Manager 20000
Narju Manager 15000

SQL> Select job,salary
2 from Employee
3 where job<>'Analyst';

JOB SALARY
-------------------- ---------
Manager 10000
Manager 20000
Programer 80000
clerk 1000
Manager 15000

SQL> Select *from Employee
2 where (salary>10000 and job='Manager');

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
12 Anoop Manager a 20000
17 Narju Manager a 15000

SQL> Select *from Employee
2 Where (job='analyst' or job='Programer');

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
14 Raju Programer b 80000

Step4:
Select statement with order by And Distict clauses

SQL> Select eno,ename,salary
2 from employee
3 where salary>7500
4 order by eno desc;

ENO ENAME SALARY
--------- -------------------- ---------
17 Narju 15000
15 Sabu 70000
14 Raju 80000
12 Anoop 20000
10 Tom 10000

SQL> Select *from employee
2 order by ename;

ENO ENAME JOB GRADE SALARY
--------- -------------------- -------------------- ----- ---------
12 Anoop Manager a 20000
17 Narju Manager a 15000
14 Raju Programer b 80000
15 Sabu Analyst c 70000
16 Shalih clerk d 1000
10 Tom Manager a 10000

6 rows selected.

SQL> Select distinct job from employee;

JOB
--------------------
Analyst
Manager
Programer
Clerk

Step5:
Group function

SQL> select sum(salary) from employee;

SUM(SALARY)
-----------
196000

SQL> select sum(salary) from employee;

SUM(SALARY)
-----------
196000

SQL> select count(*) from Employee ;

COUNT(*)
---------
6

SQL> Select max(salary) from employee;

MAX(SALARY)
-----------
80000
SQL> Select min(salary) from employee;

MIN(SALARY)
-----------
1000

Step6:
Alter table command

SQL> Alter table Employee
2 add phoneNo int;
Table altered.

SQL> desc Employee;
Name Null? Type
------------------------------- -------- ----
ENO NUMBER(38)
ENAME CHAR(20)
JOB CHAR(20)
GRADE CHAR(5)
SALARY NUMBER(10,2)
PHONENO NUMBER(38)

SQL> Alter table employee
2 modify (job char(25));
Table altered.

SQL> desc Employee;
Name Null? Type
------------------------------- -------- ----
ENO NUMBER(38)
ENAME CHAR(20)
JOB CHAR(25)
GRADE CHAR(5)
SALARY NUMBER(10,2)
PHONENO NUMBER(38)

Step7:
Update command

SQL> Update Employee
2 set phoneNo=2546238
3 where Eno=10;
1 row updated.

SQL> Update Employee
2 set phoneNo=2708089

3 where Eno=12;

1 row updated.

SQL> Update Employee
2 set phoneNo=2705836
3 where Eno=14;
1 row updated.

SQL> Update Employee
2 set phoneNo=2705134
3 where Eno=15;
1 row updated.

SQL> Update Employee
2 set phoneNo=2441232
3 where Eno=16;
1 row updated.

SQL> Update Employee
2 set phoneNo=2787896
3 where Eno=17;
1 row updated.

SQL> select *from Employee;

ENO ENAME JOB GRADE SALARY PHONENO
--------- -------------------- ------------------------- ----- --------- ---------
10 Tom Manager a 10000 2546238
12 Anoop Manager a 20000 2708089
14 Raju Programer b 80000 2705836
15 Sabu Analyst c 70000 2705134
16 Shalih clerk d 1000 2441232
17 Narju Manager a 15000 2787896
6 rows selected.

SQL> Update Employee
2 set salary=5000
3 where job='clerk';
1 row updated.

SQL> update Employee
2 set Salary=Salary+500;
6 rows updated.

SQL> select *from Employee;

ENO ENAME JOB GRADE SALARY PHONENO
--------- -------------------- ------------------------- ----- --------- ---------
10 Tom Manager a 10500 2546238
12 Anoop Manager a 20500 2708089
14 Raju Programer b 80500 2705836
15 Sabu Analyst c 70500 2705134
16 Shalih clerk d 5500 2441232
17 Narju Manager a 15500 2787896
6 rows selected.

Step8:
Delete And Drop Table

SQL> Delete from Employee
2 Where Salary>8000;

5 rows deleted.

SQL> delete from Employee;

1 row deleted.

SQL> Drop table Employee;

Table dropped.

Step9:
Some Other operations

*)Select null row

SQL> select Eno,ename
2 from employee
3 where eno is null;

no rows selected

*)Simple Calculation

SQL> select 4*3 from dual;

4*3
---------
12
*)Print system date

SQL> select sysdate from dual;

SYSDATE
---------
25-SEP-08

Step10:
Having And Group By Clause

SQL> select job,count(*)

2 from Employee

3 group by job

4 having count(*)<3;

JOB COUNT(*)

-------------------- ---------

Analyst 1

Programer 1

clerk 1

-->