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