SQL Tutorial: Building an Airline System Project 🛫🚀

Welcome to our SQL Tutorial! In this project, we'll be creating an Airline System 🛬 that will help you understand and practice SQL in a practical, real-world context. By the end of this lesson, you'll have a solid grasp of SQL for beginners and intermediates alike. Let's get started!

What is SQL? 📝

SQL (Structured Query Language) is a language used to communicate with and manipulate databases. Think of it as the English that databases understand.

Creating Tables 🎯

To organize our data, we'll create tables. Let's create tables for our Airline System:

Flights
Aircrafts
Airports
Bookings

Flights Table 🛬

sql

CREATE TABLE Flights (
    FlightID INT PRIMARY KEY,
    AircraftID INT,
    DepartureAirportID INT,
    ArrivalAirportID INT,
    DepartureTime TIME,
    ArrivalTime TIME
);

📝 Note: The PRIMARY KEY ensures that each flight has a unique ID.

Inserting Data 📝

Now that we have our tables, let's insert some data:

sql

INSERT INTO Flights (FlightID, AircraftID, DepartureAirportID, ArrivalAirportID, DepartureTime, ArrivalTime)
VALUES (1, 1, 1, 2, '10:00:00', '12:00:00');

💡 Pro Tip: To insert multiple rows at once, use the INSERT INTO statement with the VALUES keyword followed by a list of values in parentheses, separated by commas.

Querying Data 📝

To retrieve data, we'll use SELECT statements:

sql

SELECT * FROM Flights;

This query will return all records from the Flights table. You can also filter data using conditions:

sql

SELECT * FROM Flights WHERE DepartureAirportID = 1;

This query will return only flights departing from the first airport.

Relationships Between Tables 💡

To link tables together, we use foreign keys. Let's add foreign keys to the Flights table:

sql

ALTER TABLE Flights
ADD FOREIGN KEY (AircraftID) REFERENCES Aircrafts(AircraftID);
ALTER TABLE Flights
ADD FOREIGN KEY (DepartureAirportID) REFERENCES Airports(AirportID);
ALTER TABLE Flights
ADD FOREIGN KEY (ArrivalAirportID) REFERENCES Airports(AirportID);

Now, when we insert or update data in the Flights table, SQL will ensure that the values in the Aircrafts and Airports tables exist.

Quiz 🎯

Let's continue building our Airline System and learn more about SQL! 🛫🚀