-- Passenger table
CREATE TABLE Passenger (
passenger_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
date_of_birth DATE,
gender VARCHAR(10),
email VARCHAR(100),
phone_number VARCHAR(20)
);
-- Airline table
CREATE TABLE Airline (
airline_id INT PRIMARY KEY,
airline_name VARCHAR(100),
airline_code VARCHAR(10)
);
-- Airport table
CREATE TABLE Airport (
airport_id INT PRIMARY KEY,
airport_name VARCHAR(100),
airport_code VARCHAR(10),
city VARCHAR(100),
country VARCHAR(100)
);
-- Flight table
CREATE TABLE Flight (
flight_id INT PRIMARY KEY,
airline_id INT,
departure_airport INT,
arrival_airport INT,
departure_time DATETIME,
arrival_time DATETIME,
duration VARCHAR(5),
total_seats INT,
available_seats INT,
FOREIGN KEY (airline_id) REFERENCES Airline(airline_id),
FOREIGN KEY (departure_airport) REFERENCES Airport(airport_id),
FOREIGN KEY (arrival_airport) REFERENCES Airport(airport_id)
);
-- Booking table
CREATE TABLE Booking (
booking_id INT PRIMARY KEY,
passenger_id INT,
flight_id INT,
booking_date DATE,
seat_number VARCHAR(10),
booking_status VARCHAR(20),
FOREIGN KEY (passenger_id) REFERENCES Passenger(passenger_id),
FOREIGN KEY (flight_id) REFERENCES Flight(flight_id)
);
-- Payment table
CREATE TABLE Payment (
payment_id INT PRIMARY KEY,
booking_id INT,
payment_date DATE,
payment_amount DECIMAL(10, 2),
payment_status VARCHAR(20),
FOREIGN KEY (booking_id) REFERENCES Booking(booking_id)
);
-- Luggage table
CREATE TABLE Luggage (
luggage_id INT PRIMARY KEY,
booking_id INT,
weight DECIMAL(5, 2),
dimensions VARCHAR(50),
fee_amount DECIMAL(8, 2),
FOREIGN KEY (booking_id) REFERENCES Booking(booking_id)
);
-- Airplane table
CREATE TABLE Airplane (
airplane_id INT PRIMARY KEY,
airline_id INT,
airplane_model VARCHAR(100),
total_seats INT,
FOREIGN KEY (airline_id) REFERENCES Airline(airline_id)
);
-- Class table
CREATE TABLE Class (
class_id INT PRIMARY KEY,
class_name VARCHAR(50),
class_description VARCHAR(200)
);
-- Seat table
CREATE TABLE Seat (
seat_id INT PRIMARY KEY,
airplane_id INT,
class_id INT,
seat_number VARCHAR(10),
seat_status VARCHAR(20),
FOREIGN KEY (airplane_id) REFERENCES Airplane(airplane_id),
FOREIGN KEY (class_id) REFERENCES Class(class_id)
);
INSERT INTO Passenger (passenger_id, first_name, last_name, date_of_birth, gender, email, phone_number)
VALUES
(1, 'John', 'Doe', '1990-05-15', 'Male', '[email protected]', '+1 (555) 123-4567'),
(2, 'Jane', 'Smith', '1985-12-28', 'Female', '[email protected]', '+44 20 1234 5678'),
(3, 'Michael', 'Johnson', '1978-08-03', 'Male', '[email protected]', '+61 2 9876 5432'),
(4, 'Sarah', 'Williams', '1995-09-20', 'Female', '[email protected]', '+1 (987) 654-3210'),
(5, 'Robert', 'Brown', '1982-03-10', 'Male', '[email protected]', '+49 1234 567890'),
(6, 'Emily', 'Davis', '1992-07-14', 'Female', '[email protected]', '+44 7890 123456'),
(7, 'James', 'Wilson', '1989-11-02', 'Male', '[email protected]', '+1 (345) 678-9012'),
(8, 'Lily', 'Anderson', '1993-04-19', 'Female', '[email protected]', '+61 7 2345 6789'),
(9, 'Ethan', 'Martinez', '1987-06-28', 'Male', '[email protected]', '+44 7890 234567'),
(10, 'Chloe', 'Garcia', '1991-08-08', 'Female', '[email protected]', '+1 (987) 321-0987'),
(11, 'William', 'Rodriguez', '1984-12-31', 'Male', '[email protected]', '+61 3 8765 4321'),
(12, 'Sophia', 'Lee', '1994-03-26', 'Female', '[email protected]', '+44 7890 345678'),
(13, 'Benjamin', 'Lopez', '1983-09-11', 'Male', '[email protected]', '+1 (876) 543-2109'),
(14, 'Mia', 'Scott', '1998-01-05', 'Female', '[email protected]', '+61 2 7654 3210'),
(15, 'Alexander', 'Hall', '1986-10-17', 'Male', '[email protected]', '+44 7890 456789');
INSERT INTO Airline (airline_id, airline_name, airline_code)
VALUES
(1, 'Delta Airlines', 'DL'),
(2, 'British Airways', 'BA'),
(3, 'Emirates', 'EK'),
(4, 'United Airlines', 'UA'),
(5, 'Lufthansa', 'LH'),
(6, 'Air France', 'AF'),
(7, 'Qantas', 'QF'),
(8, 'Cathay Pacific', 'CX'),
(9, 'Singapore Airlines', 'SQ'),
(10, 'American Airlines', 'AA'),
(11, 'Qatar Airways', 'QR'),
(12, 'ANA All Nippon Airways', 'NH'),
(13, 'Turkish Airlines', 'TK'),
(14, 'Etihad Airways', 'EY'),
(15, 'Virgin Atlantic', 'VS');
INSERT INTO Airport (airport_id, airport_name, airport_code, city, country)
VALUES
(1, 'JFK International', 'JFK', 'New York', 'United States'),
(2, 'London Heathrow', 'LHR', 'London', 'United Kingdom'),
(3, 'Dubai International', 'DXB', 'Dubai', 'United Arab Emirates'),
(4, 'Los Angeles International', 'LAX', 'Los Angeles', 'United States'),
(5, 'Sydney Airport', 'SYD', 'Sydney', 'Australia'),
(6, 'Hong Kong International', 'HKG', 'Hong Kong', 'Hong Kong'),
(7, 'Singapore Changi', 'SIN', 'Singapore', 'Singapore'),
(8, 'Tokyo Haneda', 'HND', 'Tokyo', 'Japan'),
(9, 'Paris Charles de Gaulle', 'CDG', 'Paris', 'France'),
(10, 'Frankfurt am Main Airport', 'FRA', 'Frankfurt', 'Germany'),
(11, 'Istanbul Airport', 'IST', 'Istanbul', 'Turkey'),
(12, 'Doha Hamad International', 'DOH', 'Doha', 'Qatar'),
(13, 'Abu Dhabi International', 'AUH', 'Abu Dhabi', 'United Arab Emirates'),
(14, 'Amsterdam Schiphol', 'AMS', 'Amsterdam', 'Netherlands'),
(15, 'Incheon International', 'ICN', 'Seoul', 'South Korea');
INSERT INTO Flight (flight_id, airline_id, departure_airport, arrival_airport, departure_time, arrival_time, duration, total_seats, available_seats)
VALUES
(1, 1, 1, 3, '2023-08-05 08:00:00', '2023-08-05 16:30:00', '8:30', 200, 180),
(2, 2, 2, 1, '2023-08-06 12:45:00', '2023-08-06 21:15:00', '8:30', 180, 150),
(3, 3, 3, 2, '2023-08-07 15:20:00', '2023-08-07 23:30:00', '8:10', 220, 200),
(4, 4, 4, 5, '2023-08-08 07:30:00', '2023-08-08 19:45:00', '12:15', 250, 230),
(5, 5, 5, 3, '2023-08-09 14:10:00', '2023-08-09 23:50:00', '9:40', 180, 130),
(6, 6, 6, 4, '2023-08-10 10:20:00', '2023-08-10 18:45:00', '8:25', 200, 190),
(7, 7, 7, 1, '2023-08-11 12:00:00', '2023-08-11 20:30:00', '8:30', 220, 200),
(8, 8, 8, 7, '2023-08-12 09:15:00', '2023-08-12 18:00:00', '8:45', 190, 160),
(9, 9, 9, 2, '2023-08-13 07:40:00', '2023-08-13 15:20:00', '7:40', 230, 200),
(10, 10, 10, 11, '2023-08-14 15:30:00', '2023-08-14 23:50:00', '8:20', 200, 190),
(11, 11, 11, 15, '2023-08-15 13:20:00', '2023-08-15 23:00:00', '9:40', 210, 190),
(12, 12, 12, 14, '2023-08-16 11:00:00', '2023-08-16 21:30:00', '10:30', 180, 170),
(13, 13, 13, 6, '2023-08-17 08:30:00', '2023-08-17 17:45:00', '9:15', 230, 210),
(14, 14, 14, 10, '2023-08-18 17:00:00', '2023-08-18 23:30:00', '6:30', 180, 170),
(15, 15, 15, 8, '2023-08-19 13:45:00', '2023-08-19 22:15:00', '8:30', 190, 180);
INSERT INTO Booking (booking_id, passenger_id, flight_id, booking_date, seat_number, booking_status)
VALUES
(1, 5, 1, '2023-07-20', '12A', 'confirmed'),
(2, 8, 2, '2023-07-21', '8C', 'confirmed'),
(3, 1, 3, '2023-07-22', '20B', 'pending'),
(4, 3, 4, '2023-07-23', '6D', 'confirmed'),
(5, 10, 5, '2023-07-24', '15F', 'confirmed'),
(6, 4, 6, '2023-07-25', '10A', 'confirmed'),
(7, 6, 7, '2023-07-26', '14B', 'unconfirmed'),
(8, 12, 8, '2023-07-27', '5C', 'confirmed'),
(9, 2, 9, '2023-07-28', '7D', 'confirmed'),
(10, 15, 10, '2023-07-29', '11F', 'confirmed'),
(11, 7, 11, '2023-07-30', '3A', 'confirmed'),
(12, 11, 12, '2023-07-31', '18C', 'confirmed'),
(13, 13, 13, '2023-08-01', '22F', 'confirmed'),
(14, 9, 14, '2023-08-02', '9B', 'confirmed'),
(15, 14, 15, '2023-08-03', '17E', 'confirmed');
INSERT INTO Payment (payment_id, booking_id, payment_date, payment_amount, payment_status)
VALUES
(1, 1, '2023-07-20', 350.00, 'paid'),
(2, 2, '2023-07-21', 420.00, 'paid'),
(3, 3, '2023-07-22', 280.00, 'pending'),
(4, 4, '2023-07-23', 480.00, 'unpaid'),
(5, 5, '2023-07-24', 310.00, 'paid'),
(6, 6, '2023-07-25', 270.00, 'paid'),
(7, 7, '2023-07-26', 380.00, 'paid'),
(8, 8, '2023-07-27', 400.00, 'paid'),
(9, 9, '2023-07-28', 320.00, 'paid'),
(10, 10, '2023-07-29', 440.00, 'paid'),
(11, 11, '2023-07-30', 390.00, 'paid'),
(12, 12, '2023-07-31', 350.00, 'paid'),
(13, 13, '2023-08-01', 500.00, 'paid'),
(14, 14, '2023-08-02', 300.00, 'paid'),
(15, 15, '2023-08-03', 360.00, 'paid');
INSERT INTO Luggage (luggage_id, booking_id, weight, dimensions, fee_amount)
VALUES
(1, 1, 20.5, '30x20x10', 50.00),
(2, 2, 15.0, '25x15x8', 30.00),
(3, 4, 18.2, '28x18x9', 40.00),
(4, 5, 22.0, '32x22x12', 55.00),
(5, 6, 17.5, '29x19x11', 45.00),
(6, 7, 23.8, '31x21x13', 60.00),
(7, 8, 14.5, '24x16x7', 25.00),
(8, 9, 19.0, '27x17x10', 35.00),
(9, 10, 21.0, '30x20x11', 50.00),
(10, 11, 16.0, '26x15x9', 30.00),
(11, 12, 25.0, '33x23x14', 65.00),
(12, 13, 20.0, '30x20x10', 50.00),
(13, 14, 18.5, '29x19x9', 40.00),
(14, 15, 15.5, '26x17x8', 30.00),
(15, 3, 24.0, '34x22x15', 70.00);
INSERT INTO Airplane (airplane_id, airline_id, airplane_model, total_seats)
VALUES
(1, 1, 'Boeing 777', 300),
(2, 2, 'Airbus A380', 350),
(3, 3, 'Boeing 787 Dreamliner', 280),
(4, 4, 'Airbus A350', 320),
(5, 5, 'Boeing 747', 380),
(6, 6, 'Airbus A330', 260),
(7, 7, 'Boeing 767', 230),
(8, 8, 'Airbus A320', 200),
(9, 9, 'Boeing 737', 180),
(10, 10, 'Airbus A321', 220),
(11, 11, 'Boeing 787-9', 270),
(12, 12, 'Airbus A319', 190),
(13, 13, 'Boeing 777-300ER', 350),
(14, 14, 'Airbus A380plus', 360),
(15, 15, 'Boeing 787-10', 290);
INSERT INTO Class (class_id, class_name, class_description)
VALUES
(1, 'Economy', 'Standard economy class with amenities'),
(2, 'Business', 'Premium class with extra comfort'),
(3, 'First Class', 'Luxury class with exclusive facilities'),
(4, 'Premium Economy', 'Enhanced economy class with extra legroom and amenities'),
(5, 'Business Plus', 'Top-tier business class with added luxuries'),
(6, 'Economy Comfort', 'Economy class with additional leg space'),
(7, 'First Class Suites', 'Ultimate first-class experience with private suites'),
(8, 'Premium Business', 'Upgraded business class with extra services'),
(9, 'Economy Basic', 'No-frills economy class with limited amenities'),
(10, 'Business Flex', 'Flexible business class fare with enhanced benefits'),
(11, 'Economy Plus', 'Economy class with extra legroom'),
(12, 'First Class Lie-Flat Beds', 'First-class seats that recline into flat beds'),
(13, 'Premium Economy Flex', 'Flexible premium economy class with added amenities'),
(14, 'Economy Extra', 'Economy class with supplementary services'),
(15, 'Business Sleeperette', 'Business class seats with reclining sleeper features');
INSERT INTO Seat (seat_id, airplane_id, class_id, seat_number, seat_status)
VALUES
(1, 1, 1, '12A', 'available'),
(2, 1, 1, '12B', 'available'),
(3, 1, 1, '13A', 'available'),
(4, 1, 2, '8C', 'available'),
(5, 1, 2, '8D', 'available'),
(6, 1, 2, '9A', 'available'),
(7, 1, 3, '2A', 'available'),
(8, 1, 3, '1B', 'available'),
(9, 1, 3, '1C', 'available'),
(10, 2, 1, '18F', 'available'),
(11, 2, 1, '19A', 'available'),
(12, 2, 1, '19B', 'available'),
(13, 2, 2, '15C', 'available'),
(14, 2, 2, '16D', 'available'),
(15, 2, 2, '16E', 'available');
SELECT * FROM Passenger;
SELECT * FROM Airline;
SELECT * FROM Airport;
SELECT * FROM Flight;
SELECT * FROM Booking;
SELECT * FROM Payment;
SELECT * FROM Luggage;
SELECT * FROM Airplane;
SELECT * FROM Class;
SELECT * FROM Seat;
SELECT F.flight_id,
A1.airport_name AS departure_airport,
A2.airport_name AS arrival_airport,
F.departure_time,
F.arrival_time,
F.duration,
B.booking_date,
B.booking_status,
"P.payment_amount",
P.payment_status,
A.airline_name
FROM Flight F
JOIN Airport A1 ON F.departure_airport = A1.airport_id
JOIN Airport A2 ON F.arrival_airport = A2.airport_id
JOIN Booking B ON F.flight_id = B.flight_id
JOIN Payment P ON B.booking_id = B.booking_id
JOIN Airline A ON F.airline_id = A.airline_id
WHERE DATE (B.booking_date)= 25
AND DATE(F.departure_time) BETWEEN "desired_booking_date"
AND DATE_ADD(25, INTERVAL 7 DAY);
Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'MySQL' and start writing queries to learn and test online without worrying about tedious process of installation.
MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
ALTER TABLE Table_name ADD column_name datatype;
INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');
TRUNCATE table table_name;
DROP TABLE table_name;
RENAME TABLE table_name1 to new_table_name1;
--Line1;
/* Line1,
Line2 */
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: Column names are optional.
INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');
SELECT column1, column2, ...
FROM table_name
[where condition];
SELECT * FROM EMPLOYEE where dept ='sales';
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';
DELETE FROM table_name where condition;
DELETE from EMPLOYEE where empId='0001';
CREATE INDEX index_name on table_name(column_name);
CREATE UNIQUE INDEX index_name on table_name(column_name);
DROP INDEX index_name ON table_name;
Creating a View:
CREATE VIEW View_name AS
Query;
SELECT * FROM View_name;
ALTER View View_name AS
Query;
DROP VIEW View_name;
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */
DROP TRIGGER [IF EXISTS] trigger_name;
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;
CALL sp_name;
DROP PROCEDURE sp_name;
SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;
SELECT select_list from TABLE1 CROSS JOIN TABLE2;