>>>>>>>>>>>>>>>>>>>>CREATE TABLE<<<<<<<<<<<<<<<<<<<<
create table train_details(train_name char(15) primary key,total_seats number(3),reserved_seats number(3));
create table reservation_status(train_name char(15) references train_details(train_name),seat_id number(3),reserved char(2) check (reserved in('y','n')),customer_name char(15));
create table waiting_list(slno number(3),customer_name char(15) primary key,train_name char(15) references train_details(train_name));
>>>>>>>>>>>>>>>>>>>>ENTER TRAIN DETAILS<<<<<<<<<<<<<<<<<<<<
declare
tname char(15);
tot number(3);
resv number(3);
cursor cur is select * from train_details;
begin
insert into train_details values('&train_name',&total_seats,0);
open cur;
loop
fetch cur into tname,tot,resv;
if cur%found then
for i in 1..tot
loop
insert into reservation_status values(tname,i,'n',null);
end loop;
else
exit;
end if;
end loop;
commit;
close cur;
end;
/
Enter value for train_name: AA
Enter value for total_seats: 3
old 7: insert into train_details values('&train_name',&total_seats,0);
new 7: insert into train_details values('AA',3,0);
PL/SQL procedure successfully completed.
SQL> /
Enter value for train_name: BB
Enter value for total_seats: 2
old 7: insert into train_details values('&train_name',&total_seats,0);
new 7: insert into train_details values('BB',2,0);
PL/SQL procedure successfully completed.
SQL> select * from train_details;
TRAIN_NAME TOTAL_SEATS RESERVED_SEATS
--------------------------------------------------------------------------------------
AA 3 0
BB 2 0
SQL> select * from reservation_status;
TRAIN_NAME SEAT_ID RE CUSTOMER_NAME
--------------------------------------------------------------------------------------
AA 1 n
AA 2 n
AA 3 n
BB 1 n
BB 2 n
5 rows selected.
>>>>>>>>>>>>>>>>>>>>RESERVE A SEAT<<<<<<<<<<<<<<<<<<<<
declare
cname char(15);
tname char(15);
tot number(3);
resv number(3);
sid number(3);
sno number(3);
sl number(3);
begin
cname:=&cname;
tname:=&tname;
select total_seats into tot from train_details where train_name=tname;
select reserved_seats into resv from train_details where train_name=tname;
if tot>resv then
select MIN(seat_id) into sid from reservation_status where train_name=tname and reserved='n';
update reservation_status set reserved='y' where train_name=tname and seat_id=sid;
update reservation_status set customer_name=cname where train_name=tname and seat_id=sid;
update train_details set reserved_seats=reserved_seats+1 where train_name=tname;
else
select MAX(slno) into sno from waiting_list;
if sno is null then
sl:=1;
else
sl:=sno+1;
end if;
insert into waiting_list values(sl,cname,tname);
end if;
end;
SQL> /
Enter value for cname: 'Tintumon'
old 10: cname:=&cname;
new 10: cname:='Tintumon';
Enter value for tname: 'AA'
old 11: tname:=&tname;
new 11: tname:='AA';
PL/SQL procedure successfully completed.
SQL> /
Enter value for cname: 'Kuttoos'
old 10: cname:=&cname;
new 10: cname:='Kuttoos';
Enter value for tname: 'BB'
old 11: tname:=&tname;
new 11: tname:='BB';
PL/SQL procedure successfully completed.
SQL> /
Enter value for cname: 'Appu'
old 10: cname:=&cname;
new 10: cname:='Appu';
Enter value for tname: 'BB'
old 11: tname:=&tname;
new 11: tname:='BB';
PL/SQL procedure successfully completed.
SQL> /
Enter value for cname: 'Tuttu'
old 10: cname:=&cname;
new 10: cname:='Tuttu';
Enter value for tname: 'BB'
old 11: tname:=&tname;
new 11: tname:='BB';
PL/SQL procedure successfully completed.
SQL> select * from reservation_status;
TRAIN_NAME SEAT_ID RE CUSTOMER_NAME
---------------------------------------------------------------------------------------
AA 1 y Tintumon
AA 2 n
AA 3 n
BB 1 y Kuttoos
BB 2 y Appu
5 rows selected.
SQL> select * from waiting_list;
SLNO CUSTOMER_NAME TRAIN_NAME
--------------------------------------------------------------------------------
1 Tuttu BB
>>>>>>>>>>>>>>>>>>>>CANCEL A RESERVATION<<<<<<<<<<<<<<<<<<<<
declare
cname char(15);
tname char(15);
sid number(3);
sno number(3);
sl number(3);
begin
cname:=&cname;
tname:=&tname;
select seat_id into sid from reservation_status where train_name=tname and customer_name=cname;
select MIN(slno) into sno from waiting_list where train_name=tname;
if sno is not null then
select customer_name into cname from waiting_list where train_name=tname and slno=sno;
update reservation_status set customer_name=cname where train_name=tname and seat_id=sid;
delete from waiting_list where train_name=tname and slno=sno;
else
update reservation_status set reserved='n' where train_name=tname and seat_id=sid;
update reservation_status set customer_name=null where train_name=tname and seat_id=sid;
update train_details set reserved_seats=reserved_seats-1 where train_name=tname;
end if;
end;
/
Enter value for cname: 'Appu'
old 8: cname:=&cname;
new 8: cname:='Appu';
Enter value for tname: 'BB'
old 9: tname:=&tname;
new 9: tname:='BB';
PL/SQL procedure successfully completed.
SQL> select * from reservation_status;
TRAIN_NAME SEAT_ID RE CUSTOMER_NAME
------------------------------------------------------------------------------------------------
AA 1 y Tintumon
AA 2 n
AA 3 n
BB 1 y Kuttoos
BB 2 y Tuttu
5 rows selected.
SQL> select * from waiting_list;
SLNO CUSTOMER_NAME TRAIN_NAME
----------------------------------------------------------------------------------
SQL> select * from train_details;
TRAIN_NAME TOTAL_SEATS RESERVED_SEATS
------------------------------------------------------------------
AA 3 1
BB 2 2