Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create database cs2;
- use cs2;
- -- TrainList table
- CREATE TABLE TrainList (
- train_number INT PRIMARY KEY,
- train_name VARCHAR(255) NOT NULL,
- source VARCHAR(255) NOT NULL,
- destination VARCHAR(255) NOT NULL,
- ac_fair DECIMAL(10,2) NOT NULL,
- general_fair DECIMAL(10,2) NOT NULL,
- weekdays VARCHAR(255)
- );
- -- Train_Status table
- CREATE TABLE Train_Status (
- train_number INT,
- train_date DATE NOT NULL,
- total_ac_seats INT NOT NULL,
- total_general_seats INT NOT NULL,
- ac_seats_booked INT NOT NULL DEFAULT 0,
- general_seats_booked INT NOT NULL DEFAULT 0,
- PRIMARY KEY (train_number, train_date),
- FOREIGN KEY (train_number) REFERENCES TrainList(train_number)
- );
- -- Passenger table
- CREATE TABLE Passenger (
- ticket_id INT PRIMARY KEY AUTO_INCREMENT,
- train_number INT NOT NULL,
- booking_date DATE NOT NULL,
- name VARCHAR(255) NOT NULL,
- age INT,
- sex VARCHAR(10),
- address TEXT,
- status VARCHAR(20) NOT NULL,
- ticket_category VARCHAR(10) NOT NULL,
- FOREIGN KEY (train_number) REFERENCES TrainList(train_number)
- );
- -- inserting data ramesh boi
- INSERT INTO TrainList (train_number, train_name, source, destination, ac_fair, general_fair, weekdays)
- VALUES
- (1001, 'Gourav U Exp', 'Indore', 'Delhi', 1500.00, 1000.00, 'Monday, Wednesday, Friday'),
- (1002, 'Gourav U Ps', 'Delhi', 'Prayagraj', 2000.00, 1200.00, 'Tuesday, Thursday, Saturday'),
- (1003, 'Rajdhani Express', 'Kolkata', 'Chennai', 1800.00, 1100.00, 'Daily'),
- (1004, 'Kalyan Exp', 'Chennai', 'Bangalore', 1700.00, 1050.00, 'Monday, Friday'),
- (1005, 'Maharaja exp', 'Jaipur', 'Jodhpur', 50000.00, 20000.00, 'Wednesday, Saturday');
- INSERT INTO Train_Status (train_number, train_date,
- total_ac_seats, total_general_seats, ac_seats_booked,
- general_seats_booked)
- VALUES
- (1001, '2024-05-05', 50, 100, 10, 20),
- (1001, '2024-05-06', 50, 100, 15, 30),
- (1001, '2024-05-07', 50, 100, 20, 40),
- (1002, '2024-05-05', 60, 120, 5, 10),
- (1002, '2024-05-06', 60, 120, 10, 20),
- (1003, '2024-05-05', 40, 80, 8, 15),
- (1003, '2024-05-06', 40, 80, 12, 25),
- (1004, '2024-05-05', 45, 90, 6, 12),
- (1004, '2024-05-06', 45, 90, 8, 16),
- (1005, '2024-05-05', 100, 40, 90, 23),
- (1005, '2024-05-06', 101, 40, 95, 35);
- INSERT INTO Passenger (train_number, booking_date, name, age, sex, address, status, ticket_category)
- VALUES
- (1001, '2024-05-05', 'Gourav Upadhyay', 21, 'Male', '123 Bandra, Mumbai', 'Confirmed', 'AC'),
- (1001, '2024-05-05', 'Priya Singh', 25, 'Female', '456 Kirti Nagar, Delhi', 'Confirmed', 'General'),
- (1002, '2024-05-05', 'Ajay Kumar', 40, 'Male', '789 New Friends Colony, Delhi', 'Confirmed', 'AC'),
- (1002, '2024-05-05', 'Neha Gupta', 35, 'Female', '987 Ballygunge, Kolkata', 'Confirmed', 'General'),
- (1003, '2024-05-05', 'Ankit Patel', 28, 'Male', '654 Bhawanipur, Kolkata', 'Confirmed', 'AC'),
- (1003, '2024-05-05', 'Riya Jain', 22, 'Female', '321 Anna Nagar, Chennai', 'Confirmed', 'General'),
- (1004, '2024-05-05', 'Vivek Reddy', 45, 'Male', '234 Madhavaram, Chennai', 'Confirmed', 'AC'),
- (1004, '2024-05-05', 'Shreya Mishra', 20, 'Female', '567 Walnut Street, Bangalore', 'Confirmed', 'General'),
- (1005, '2024-05-05', 'Amit Kumar', 33, 'Male', '345 Jayanagar, Bangalore', 'Confirmed', 'AC'),
- (1005, '2024-05-05', 'Pooja Sharma', 27, 'Female', '876 Banjara Hills, Hyderabad', 'Confirmed', 'General');
- -- Booking procedure
- DELIMITER //
- CREATE PROCEDURE BookTicket (
- IN gu_train_number INT,
- IN gu_train_date DATE,
- IN gu_category VARCHAR(10),
- IN gu_name VARCHAR(255),
- IN gu_age INT,
- IN gu_sex VARCHAR(10),
- IN gu_address TEXT,
- OUT gu_ticket_id INT,
- OUT gu_booking_status VARCHAR(50)
- )
- BEGIN
- DECLARE total_seats INT;
- DECLARE total_booked_seats INT;
- -- Retrieve total and booked seats based on train number and date
- IF gu_category = 'AC' THEN
- SELECT total_ac_seats, ac_seats_booked INTO total_seats, total_booked_seats
- FROM Train_Status
- WHERE train_number = gu_train_number AND train_date = gu_train_date;
- ELSE
- SELECT total_general_seats, general_seats_booked INTO total_seats, total_booked_seats
- FROM Train_Status
- WHERE train_number = gu_train_number AND train_date = gu_train_date;
- END IF;
- -- CHECKIGN DOES SEAST AVAILABLE OR NOT
- IF total_booked_seats < total_seats THEN
- -- Insert passenger details into Passenger table
- INSERT INTO Passenger (train_number, booking_date, name, age, sex, address, status, ticket_category)
- VALUES (gu_train_number, gu_train_date, gu_name, gu_age, gu_sex, gu_address, 'Confirmed', gu_category);
- -- Retrieve the auto-generated ticket ID
- SELECT LAST_INSERT_ID() INTO gu_ticket_id;
- SET gu_booking_status = 'Booking Successful';
- ELSE
- SET gu_ticket_id = NULL;
- SET gu_booking_status = 'No seats available for the selected category';
- END IF;
- END //
- CALL BookTicket(
- 1005,
- '2024-05-05',
- 'AC',
- 'Rohit Upadhyay',
- 21,
- 'Male',
- '13 Palan Nagar near 60 feet road Indore',
- @ticket_id,
- @booking_status
- )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement