Saturday, January 9, 2010

 

SQL- RAILWAY RESERVATION SYSTEM




RAILWAY RESERVATION SYSTEM



>>>>>>>>>>>>>>>>>>>>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<<<<<<<<<<<<<<<<<<<<

 

 

SQL> declare

  2 

  2  tname char(15);

  3  tot number(3);

  4  resv number(3);

  5  cursor cur is select * from train_details;

  6 

  6  begin

  7 

  7  insert into train_details values('&train_name',&total_seats,0);

  8 

  8  open cur;

  9  loop

 10  fetch cur into tname,tot,resv;

 11  if cur%found then

 12  for i in 1..tot

 13  loop

 14  insert into reservation_status values(tname,i,'n',null);

 15  end loop;

 16  else

 17  exit;

 18  end if;

 19  end loop;

 20  commit;

 21  close cur;

 22  end;

 23  /

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





Labels: , ,


Comments: Post a Comment

Subscribe to Post Comments [Atom]





<< Home

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]