Notes

-- Create the travel table
CREATE TABLE travel (
agent_name VARCHAR(100),
agent_id VARCHAR(100),
flight_city VARCHAR(100),
booking_assignment VARCHAR(100),
passenger_id VARCHAR(100),
flight_no INT,
PRIMARY KEY (passenger_id),
FOREIGN KEY (passenger_id) REFERENCES passenger(passenger_id)
);

-- Create the passenger table
CREATE TABLE passenger (
passenger_id VARCHAR(100),
passenger_name VARCHAR(100),
phone_no INT,
flight_no INT,
flight_name VARCHAR(100),
arrival_date DATE,
departure_date DATE,
PRIMARY KEY (passenger_id),
FOREIGN KEY (flight_no) REFERENCES travel(flight_no)
);

-- Insert data into the passenger table
INSERT INTO passenger (passenger_id, passenger_name, phone_no, flight_no, flight_name, arrival_date, departure_date)
VALUES
('P001', 'John Doe', 1234567890, 101, 'Flight 101', '2023-11-10', '2023-11-15'),
('P002', 'Jane Smith', 9876543210, 102, 'Flight 102', '2023-11-12', '2023-11-18'),
('P003', 'Bob Johnson', 5551234567, 103, 'Flight 103', '2023-11-15', '2023-11-20');

-- Insert data into the travel table
INSERT INTO travel (agent_name, agent_id, flight_city, booking_assignment, passenger_id, flight_no)
VALUES
('Travel Agency 1', 'A001', 'City A', 'Booking A', 'P001', 101),
('Travel Agency 2', 'A002', 'City B', 'Booking B', 'P002', 102),
('Travel Agency 3', 'A003', 'City C', 'Booking C', 'P003', 103);

1)
SELECT
t.agent_id,
p.passenger_id,
p.passenger_name,
p.phone_no,
t.flight_city,
t.booking_assignment,
p.arrival_date,
p.departure_date
FROM
travel t
JOIN
passenger p ON t.passenger_id = p.passenger_id;
2)
SELECT
t.agent_id,
t.agent_name as travel_agency,
p.passenger_id,
p.passenger_name,
p.phone_no,
t.flight_city,
t.booking_assignment,
p.arrival_date,
p.departure_date
FROM
travel t
JOIN
passenger p ON t.passenger_id = p.passenger_id;
3)SELECT
t.agent_id,
t.flight_no,
AVG(COUNT(t.passenger_id)) AS avg_bookings
FROM
travel t
GROUP BY
t.agent_id, t.flight_no;
4)SELECT
p.passenger_id,
p.passenger_name,
p.phone_no,
t.agent_id,
t.agent_name,
t.flight_city,
t.booking_assignment,
t.flight_no,
p.arrival_date,
p.departure_date
FROM
passenger p
JOIN
travel t ON p.passenger_id = t.passenger_id;
5)SELECT
p.passenger_id,
p.passenger_name,
p.phone_no,
t.agent_id,
t.agent_name,
t.flight_city,
t.booking_assignment,
t.flight_no,
p.arrival_date,
p.departure_date
FROM
passenger p
JOIN
travel t ON p.passenger_id = t.passenger_id
WHERE
p.flight_city = t.flight_city
AND p.arrival_date = t.arrival_date;