SQL Tutorial: Railway Reservation System Project 🚂📊

Welcome to the SQL Tutorial for our Railway Reservation System Project! In this lesson, we'll learn how to use SQL to manage a railway ticket booking system. By the end, you'll have a solid understanding of SQL, making you well-prepared for real-world projects.

Let's get started! 🚀

What is SQL? 💡

SQL (Structured Query Language) is a language used to communicate with databases. It allows us to create, modify, and query databases, making it essential for managing data in applications.

Database Structure 📝

To create our Railway Reservation System, we'll need several tables to store different types of data. Here are the tables we'll create:

RailwayStations (station_id, station_name, city)
Trains (train_id, train_name, train_type, source_station_id, destination_station_id)
Journeys (journey_id, train_id, departure_date, arrival_date)
Seats (seat_id, train_id, seat_number, seat_type, is_booked)
Bookings (booking_id, passenger_name, passenger_age, seat_id, booking_date)

Creating Tables 🎯

Now, let's create these tables in our database using SQL commands.

sql

CREATE TABLE RailwayStations (
    station_id INT PRIMARY KEY,
    station_name VARCHAR(100),
    city VARCHAR(100)
);

CREATE TABLE Trains (
    train_id INT PRIMARY KEY,
    train_name VARCHAR(100),
    train_type VARCHAR(100),
    source_station_id INT,
    destination_station_id INT,
    FOREIGN KEY (source_station_id) REFERENCES RailwayStations(station_id),
    FOREIGN KEY (destination_station_id) REFERENCES RailwayStations(station_id)
);

-- Continue creating the other tables with similar structure

Practical Application 📝

Now that we have our tables, let's look at some practical examples of how to use SQL to manage our Railway Reservation System.

Adding a new train:

sql

INSERT INTO Trains (train_id, train_name, train_type, source_station_id, destination_station_id)
VALUES (1, 'Train 1', 'Express', 1, 2);

Booking a seat:

sql

INSERT INTO Seats (seat_id, train_id, seat_number, seat_type, is_booked)
VALUES (1, 1, 1, 'First Class', 0);

INSERT INTO Bookings (booking_id, passenger_name, passenger_age, seat_id, booking_date)
VALUES (1, 'John Doe', 30, 1, '2022-01-01');

Queries 💡

Now that we have some data, let's look at how to query the data using SQL.

Find all trains going from station 1:

sql

SELECT train_id, train_name FROM Trains WHERE source_station_id = 1;

Check seat availability for a specific train:

sql

SELECT seat_number, seat_type FROM Seats WHERE train_id = 1 AND is_booked = 0;

Summary 📝

In this lesson, we learned about SQL and created a Railway Reservation System database. We added data to the database and queried that data using SQL commands.

Now, it's time for you to practice what you've learned! Try the quiz below to test your understanding. 📝💡

Quiz 🎯

Keep practicing, and happy coding! 🎉💪