Write a query in mySQL to list all the pilots scheduled for a flight on a particular

Fantastic news! We've Found the answer you've been seeking!

Question:

Write a query in mySQL to list all the pilots scheduled for a flight on a particular date, and it needs to include their pilot number, pilot name, and the serial number of the airplane, as well as the plane type description.

Below are the tables and sample data:

CREATE TABLE airplane (
serial_number CHAR(12) PRIMARY KEY,
type_code CHAR(8) references airplane_type(type_code),
last_date_serviced DATE,
next_date_serviced DATE,
manufacture_year INT
);

CREATE TABLE airplane_type (
type_code CHAR(8) PRIMARY KEY,
type_desc VARCHAR(30),
capacity INT,
flight_range INT
);

CREATE TABLE pilot (
pilot_number INT PRIMARY KEY,
pilot_name VARCHAR(30),
birth_date DATE,
ssn CHAR(9),
salary INT,
cell CHAR(10)
);

CREATE TABLE flight (
flight_number CHAR(3) PRIMARY KEY,
origin_city CHAR(3) references city(city),
destination_city CHAR(3) references city(city),
departure_time TIME,
arrival_time TIME
);

CREATE TABLE pilot_certification (
pilot_number INT references pilot(pilot_number),
type_code CHAR(8) references airplane_type(type_code),
date_certified DATE,
PRIMARY KEY (pilot_number, type_code)
);

CREATE TABLE airport_city (
city_code CHAR(3) PRIMARY KEY,
city_name VARCHAR(30),
state CHAR(2),
airport_desc VARCHAR(30)
);

CREATE TABLE passenger (
passenger_id INT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
phone_number CHAR(10),
street_address VARCHAR(50),
city VARCHAR(30),
state CHAR(2),
zip_code CHAR(5),
email VARCHAR(50),
birth_date DATE
);

CREATE TABLE schedule_flight (
flight_number CHAR(3) references flight(flight_number),
flight_date DATE,
serial_number CHAR(12) references airplane(serial_number),
pilot_number INT references pilot(pilot_number),
PRIMARY KEY (flight_number, flight_date)
);

INSERT INTO airplane (serial_number, type_code, last_date_serviced, next_date_serviced, manufacture_year)
VALUES
('MSN30020B745', 'A3400600', '2022-04-01', '2023-04-11', '2001'),
('MSN30020B800', 'B3400600', '2022-04-02', '2023-04-12', '2002'),
('MSN30020B845', 'C3400600', '2022-04-03', '2023-04-13', '2003'),
('MSN30020B900', 'D3400600', '2022-04-04', '2023-04-14', '2004'),
('MSN30020B945', 'E3400600', '2022-04-05', '2023-04-15', '2005')

INSERT INTO airplane_type (type_code, type_desc, capacity, flight_range)
VALUES
('A3400600', 'Airbus A340neo', '501', '100001'),
('B3400600', 'Airbus B340neo', '502', '100002'),
('C3400600', 'Airbus C340neo', '503', '100003'),
('D3400600', 'Airbus D340neo', '504', '100004'),
('E3400600', 'Airbus E340neo', '505', '100005')

INSERT INTO pilot (pilot_number, pilot_name, birth_date, ssn, salary, cell)
VALUES
(101, 'Sandra Sekulich', '1970-01-10', '123450001', '100001', '8478560001'),
(102, 'Amanda Rubo', '1971-02-11', '123450002', '100002', '8478560002'),
(103, 'Jerry Aguilar', '1972-03-12', '123450003', '100003', '8478560003'),
(104, 'Dwayne Johnson', '1973-04-13', '123450004', '100004', '8478560004'),
(105, 'Chris Rock', '1970-05-14', '123450005', '100005', '8478560005')

INSERT INTO flight (flight_number, origin_city, destination_city, departure_time, arrival_time)
VALUES
(201, 'ORD', 'ATL', '06:00:00', '12:00:00'),
(202, 'AUS', 'BET', '07:00:00', '13:00:00'),
(203, 'BTM', 'CHS', '08:00:00', '14:00:00'),
(204, 'DRT', 'FAT', '09:00:00', '15:00:00'),
(205, 'HOU', 'KXA', '10:00:00', '16:00:00')

INSERT INTO pilot_certification (pilot_number, type_code, date_certified)
VALUES
(101, 'E3400600', '2022-01-01'),
(102, 'D3400600', '2022-01-02'),
(103, 'C3400600', '2022-01-03'),
(104, 'B3400600', '2022-01-04'),
(105, 'A3400600', '2022-01-05')

INSERT INTO airport_city (city_code, city_name, state, airport_desc)
VALUES
('ORD', 'Chicago', 'IL', 'Ohare IAP'),
('ATL', 'Atlanta', 'GA', 'Hartsfield Jackson IAP'),
('MIA', 'Miami', 'FL', 'Miami IAP'),
('HOU', 'Houston', 'TX', 'William P. Hobby IAP'),
('FAT', 'Fresno', 'CA', 'Fresno Yosemite IAP')

INSERT INTO passenger (passenger_id, first_name, last_name, phone_number, street_address, city, state, zip_code, email, birth_date)
VALUES
(301, 'Ann', 'Adams', '8476628461', '301 Main Street', 'Waukegan', 'IL', '60085', '..s@gmail.com', '1940-03-01'),
(302, 'Mary', 'Smith', '8476628462', '302 Main Street', 'Waukegan', 'IL', '60085', '..h@gmail.com', '1940-03-02'),
(303, 'Janice', 'Lee', '8476628463', '303 Main Street', 'Waukegan', 'IL', '60085', '..e@gmail.com', '1940-03-03'),
(304, 'Kay', 'Johnson', '8476628464', '304 Main Street', 'Waukegan', 'IL', '60085', '..n@gmail.com', '1940-03-04'),
(305, 'Frank', 'Jones', '8476628465', '305 Main Street', 'Waukegan', 'IL', '60085', '..s@gmail.com', '1940-03-05')

INSERT INTO schedule_flight (flight_number, flight_date, serial_number, pilot_number)
VALUES
(201, '2023-04-01', 'MSN30020B945', 101),
(202, '2023-04-02', 'MSN30020B900', 102),
(203, '2023-04-03', 'MSN30020B845', 103),
(204, '2023-04-04', 'MSN30020B800', 104),
(205, '2023-04-05', 'MSN30020B745', 105)
;