CREATE TABLE Flights (
    flight_id INT PRIMARY KEY,
    flight_number TEXT,
    destination TEXT,
    departure_date DATE
);

CREATE TABLE Passengers (
    passenger_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

CREATE TABLE Bookings (
    booking_id INT PRIMARY KEY,
    flight_id INT,
    passenger_id INT,
    FOREIGN KEY (flight_id) REFERENCES Flights(flight_id),
    FOREIGN KEY (passenger_id) REFERENCES Passengers(passenger_id)
);



-- Insert flights
INSERT INTO Flights (flight_id, flight_number, destination, departure_date) VALUES (1, 'FL123', 'New York', '2023-11-15');
INSERT INTO Flights (flight_id, flight_number, destination, departure_date) VALUES (2, 'FL456', 'Los Angeles', '2023-11-16');
INSERT INTO Flights (flight_id, flight_number, destination, departure_date) VALUES (3, 'FL789', 'Chicago', '2023-11-17');
INSERT INTO Flights (flight_id, flight_number, destination, departure_date) VALUES (4, 'FL101', 'San Francisco', '2023-11-18');
INSERT INTO Flights (flight_id, flight_number, destination, departure_date) VALUES (5, 'FL555', 'Miami', '2023-11-19');
INSERT INTO Flights (flight_id, flight_number, destination, departure_date) VALUES (6, 'FL222', 'Dallas', '2023-11-20');
INSERT INTO Flights (flight_id, flight_number, destination, departure_date) VALUES (7, 'FL999', 'Las Vegas', '2023-11-21');


-- Insert passengers
INSERT INTO Passengers (passenger_id, first_name, last_name, email) VALUES (1, 'Alice', 'Smith', 'alice@email.com');
INSERT INTO Passengers (passenger_id, first_name, last_name, email) VALUES (2, 'Bob', 'Johnson', 'bob@email.com');
INSERT INTO Passengers (passenger_id, first_name, last_name, email) VALUES (3, 'Charlie', 'Brown', 'charlie@email.com');
INSERT INTO Passengers (passenger_id, first_name, last_name, email) VALUES (4, 'David', 'Lee', 'david@email.com');
INSERT INTO Passengers (passenger_id, first_name, last_name, email) VALUES (5, 'Eva', 'Williams', 'eva@email.com');
INSERT INTO Passengers (passenger_id, first_name, last_name, email) VALUES (6, 'Frank', 'Davis', 'frank@email.com');
INSERT INTO Passengers (passenger_id, first_name, last_name, email) VALUES (7, 'Grace', 'Miller', 'grace@email.com');


-- Insert bookings
INSERT INTO Bookings (booking_id, flight_id, passenger_id) VALUES (101, 1, 1);
INSERT INTO Bookings (booking_id, flight_id, passenger_id) VALUES (102, 2, 2);
INSERT INTO Bookings (booking_id, flight_id, passenger_id) VALUES (103, 1, 3);
INSERT INTO Bookings (booking_id, flight_id, passenger_id) VALUES (104, 2, 4);
INSERT INTO Bookings (booking_id, flight_id, passenger_id) VALUES (105, 3, 5);
INSERT INTO Bookings (booking_id, flight_id, passenger_id) VALUES (106, 4, 6);

-- Write an SQL query to retrieve the first name, last name, and flight id for passengers with booked flights (inner join).

SELECT Passengers.first_name, Passengers.last_name, Bookings.flight_id
FROM Bookings
INNER JOIN Passengers
ON Passengers.passenger_id = Bookings.passenger_id;

-- Write an SQL query to retrieve the flight id, flight number, destination, and passenger id for flights with
-- bookings (inner join).

SELECT Flights.flight_id, flight_number, destination, passenger_id
FROM Bookings
INNER JOIN Flights
ON Flights.flight_id = Bookings.flight_id;

-- Write an SQL query to retrieve the first name, last name, flight id, flight number, and destination for passengers 
-- with booked flights (inner join – three table join).

SELECT first_name, last_name, Flights.flight_id, flight_number, destination
FROM Bookings
INNER JOIN Flights, Passengers
ON Passengers.passenger_id = Bookings.passenger_id;