Welcome to Database Laboratory A. Y. 2025- 26
Welcome to Database Laboratory A. Y. 2025- 26
Passenger(
passenger_id INT PRIMARY KEY,
passenger_name VARCHAR(100),
passport_no VARCHAR(20),
country VARCHAR(50)
);
Flight(
flight_id INT PRIMARY KEY,
flight_number VARCHAR(20),
source VARCHAR(50),
destination VARCHAR(50),
departure_time DATETIME
);
Booking(
booking_id INT PRIMARY KEY,
passenger_id INT,
flight_id INT,
booking_date DATE,
seat_no VARCHAR(10),
FOREIGN KEY (passenger_id) REFERENCES Passenger(passenger_id),
FOREIGN KEY (flight_id) REFERENCES Flight(flight_id)
);
A JOIN clause is used to combine rows from two or more tables based on a related column (usually Primary Key and Foreign Key).
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN
SELF JOIN
Returns only matching records from both tables.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
SELECT P.passenger_name, F.flight_number
FROM Passenger P
INNER JOIN Booking B ON P.passenger_id = B.passenger_id
INNER JOIN Flight F ON B.flight_id = F.flight_id;
👉 Shows only passengers who have booked flights.
SELECT B.booking_id, P.passenger_name,
F.source, F.destination
FROM Booking B
INNER JOIN Passenger P ON B.passenger_id = P.passenger_id
INNER JOIN Flight F ON B.flight_id = F.flight_id;
Returns all records from left table + matched records from right table.
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
SELECT P.passenger_name, B.booking_id
FROM Passenger P
LEFT JOIN Booking B
ON P.passenger_id = B.passenger_id;
👉 Passengers without booking will show NULL in booking_id.
Returns all records from right table + matched from left table.
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
SELECT F.flight_number, P.passenger_name
FROM Passenger P
RIGHT JOIN Booking B ON P.passenger_id = B.passenger_id
RIGHT JOIN Flight F ON B.flight_id = F.flight_id;
Returns all records from both tables (matched + unmatched).
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
SELECT P.passenger_name, B.booking_id
FROM Passenger P
FULL OUTER JOIN Booking B
ON P.passenger_id = B.passenger_id;
👉 Works in PostgreSQL, SQL Server
👉 In MySQL use UNION of LEFT + RIGHT JOIN.
Returns Cartesian Product (all combinations).
SELECT columns
FROM table1
CROSS JOIN table2;
SELECT P.passenger_name, F.flight_number
FROM Passenger P
CROSS JOIN Flight F;
👉 If 10 passengers & 5 flights → 50 rows.
A table joined with itself.
SELECT F1.flight_number, F2.flight_number, F1.source
FROM Flight F1
JOIN Flight F2
ON F1.source = F2.source
AND F1.flight_id <> F2.flight_id;
SELECT P.passenger_name, F.destination
FROM Passenger P
INNER JOIN Booking B ON P.passenger_id = B.passenger_id
INNER JOIN Flight F ON B.flight_id = F.flight_id
WHERE F.destination = 'Mumbai';
SELECT F.flight_number, COUNT(B.passenger_id) AS Total_Passengers
FROM Flight F
LEFT JOIN Booking B ON F.flight_id = B.flight_id
GROUP BY F.flight_number;
SELECT F.flight_number, COUNT(B.passenger_id) AS Total_Passengers
FROM Flight F
INNER JOIN Booking B ON F.flight_id = B.flight_id
GROUP BY F.flight_number
HAVING COUNT(B.passenger_id) > 5;
Staff(
staff_id INT PRIMARY KEY,
staff_name VARCHAR(100),
role VARCHAR(50),
flight_id INT,
FOREIGN KEY (flight_id) REFERENCES Flight(flight_id)
);
SELECT F.flight_number,
P.passenger_name,
S.staff_name,
S.role
FROM Flight F
INNER JOIN Booking B ON F.flight_id = B.flight_id
INNER JOIN Passenger P ON B.passenger_id = P.passenger_id
LEFT JOIN Staff S ON F.flight_id = S.flight_id;
SELECT P.passenger_name
FROM Passenger P
JOIN Booking B ON P.passenger_id = B.passenger_id;
SELECT passenger_name
FROM Passenger
WHERE passenger_id IN
(SELECT passenger_id FROM Booking);