RAILWAY RESERVATION SYSTEM

an anonymous user · June 11, 2021 · SQL
CREATE TABLE employee (
  E_ID INTEGER PRIMARY KEY,
  E_NAME TEXT NOT NULL,
  GENDER TEXT NOT NULL,
  ADDRESS VARCHAR,
  PH_NO INTEGER,
  DOJ date,
  SALARY INTEGER
);

INSERT INTO employee VALUES (1, 'Ryan', 'M','patna',123456789,'12/04/2021',9000);
INSERT INTO employee VALUES (2, 'Rahul', 'M','gaya',123456789,'12/04/2021',19000);
INSERT INTO employee VALUES (3, 'surbhi','F','kanpur',123456789,'12/04/2021',15000);
INSERT INTO employee VALUES (4, 'chandan', 'M','puri',123456789,'12/04/2021',9500);
INSERT INTO employee VALUES (5, 'vikash', 'M','angul',123456789,'12/04/2021',12000);


------------------------------------------------------------------------
create table PASSENGER
(
P_ID INTEGER PRIMARY KEY,
P_NAME VARCHAR ,
SEAT_NO INTEGER,
GENDER VARCHAR,
PH_NUMBER INTEGER,
RES_STATUS VARCHAR,
E_ID INTEGER,
FOREIGN KEY(E_ID)REFERENCES EMPLOYEE(E_ID)
);

INSERT INTO passenger VALUES (101,'AMAN',20,'M',9772657863,'waiting',3);
INSERT INTO passenger VALUES (102,'VIKASH',21,'M',7353457863,'confirm',5);
INSERT INTO passenger VALUES (103,'SOURAV',22,'M',9972656763,'waiting',1);
INSERT INTO passenger VALUES (104,'PRIYANSHU',25,'M',6322657763,'confirm',4);
INSERT INTO passenger VALUES (105,'SALINI',30,'F',6372657583,'waiting',3);


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

create table STATION
(STATION_ID INTEGER PRIMARY KEY,
STATION_NAME VARCHAR ,
NO_OF_LINES INTEGER,
NO_OF_PLATFORMS INTEGER
);

INSERT INTO station VALUES (571,'patna',20,10);
INSERT INTO station VALUES (572,'gaya',8,4);
INSERT INTO station VALUES (573,'kanpur',7,3);
INSERT INTO station VALUES (574,'kiul',7,3);
INSERT INTO station VALUES (575,'barh',5,2);

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

create table train
(TRAIN_ID INTEGER PRIMARY KEY,
TRAIN_NAME VARCHAR,
STATION_ID INTEGER ,
FOREIGN KEY(STATION_ID)REFERENCES STATION(STATION_ID)
);

INSERT INTO train VALUES (1001,'JANSHATABDI EXP',571);
INSERT INTO train VALUES (1002,'RAJDHANI EXP',571);
INSERT INTO train VALUES (1003,'VIKRAMSHILA EXP',574);
INSERT INTO train VALUES (1004,'HAMSAFAR EXP',575);
INSERT INTO train VALUES (1005,'INTERCITY EXP',574);

--------------------------------------
create table FARE(
RECEIPT_NO INTEGER PRIMARY KEY,
TRAIN_ID VARCHAR ,
SOURCE VARCHAR,
FARE INTEGER,
TICKET_PNR INTEGER,
CLASS_ID VARCHAR,
FOREIGN KEY(CLASS_ID)REFERENCES CLASS(CLASS_ID)
);

INSERT INTO FARE VALUES (5551,1004,'ONLINE',800,60004,'CC');
INSERT INTO FARE VALUES (5552,1003,'ONLINE',400,60003,'SL');
INSERT INTO FARE VALUES (5553,1002,'OFFLINE',950,60001,'CC');
INSERT INTO FARE VALUES (5554,1005,'ONLINE',700,60002,'SL');

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

create table ticket(
TICKET_PNR INTEGER PRIMARY KEY,
CLASS_ID VARCHAR ,
SOURCE VARCHAR,
DESTINATION VARCHAR,
FARE INTEGER,
TRAIN_ID INTEGER,
FOREIGN KEY(TRAIN_ID)REFERENCES TRAIN(TRAIN_ID)
);

INSERT INTO ticket VALUES (60001,'CC','PATNA','KANPUR',950,1002);
INSERT INTO ticket VALUES (60002,'SL','BARH','KIUL',700,1005);
INSERT INTO ticket VALUES (60003,'SL','PATNA','GAYA',400,1003);
INSERT INTO ticket VALUES (60004,'CC','PATNA','KIUL',800,1004);
INSERT INTO ticket VALUES (60005,'SL','PATNA','BARH',200,1001);

------------------------------------------
create table CLASS(
CLASS VARCHAR,
JOURNEY_DATE date ,
NO_OF_SEATS INTEGER,
TRAIN_ID INTEGER,
FOREIGN KEY(TRAIN_ID)REFERENCES TRAIN(TRAIN_ID) 
);

INSERT INTO CLASS VALUES ('SL','15/05/2021',211,1001);
INSERT INTO CLASS VALUES ('CC','18/05/2021',101,1002);
INSERT INTO CLASS VALUES ('SL','20/05/2021',331,1001);
INSERT INTO CLASS VALUES ('CC','15/05/2021',51,1003);
INSERT INTO CLASS VALUES ('SL','15/05/2021',511,1005);

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

create table TIME(
REF_NO INTEGER,
DEP_TIME time,
ARR_TIME time,
TRAIN_ID INTEGER ,
STATION_ID INTEGER
);

INSERT INTO TIME VALUES (11121,'11:00','10:00',1001,571);
INSERT INTO TIME VALUES (11122,'01:00','12:00',1002,573);
INSERT INTO TIME VALUES (11123,'09:00','15:00',1003,574);
INSERT INTO TIME VALUES (11124,'12:00','10:00',1004,572);
INSERT INTO TIME VALUES (11125,'23:00','06:00',1005,575);


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

create table ROUTE(
ARR_TIME time,
DEP_TIME time,
STOP_NO INTEGER
);

INSERT INTO ROUTE VALUES ('11:00','10:00',21);
INSERT INTO ROUTE VALUES ('01:00','06:00',23);
INSERT INTO ROUTE VALUES ('13:00','10:00',25);
INSERT INTO ROUTE VALUES ('15:00','14:00',22);
INSERT INTO ROUTE VALUES ('10:00','20:00',24);

select * from ticket where TICKET_PNR = '60001';

Comments

Please sign up or log in to contribute to the discussion.