here is the SQL. PLEASE ONLY START FROM # 6 TO #10 AS I DID #1 TO #5
# Code to create Airlines_2175 database for DATA 620
# Contains updated data line as follows:
# Updated 5/17/2017 by Carrie Beam (rolled forward to 2017)
?
Drop Database if exists airlines;
?
CREATE DATABASE Airlines;
?
USE Airlines;
# USE Airlines ; # this is the old one also
?
— A carrier is a commercial company offering flights to the public
CREATE TABLE Carriers (
Carrier_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
Carrier_Name VARCHAR(20)
);
?
— One carrier can fly many types of aircraft
CREATE TABLE Aircraft (
Plane_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
Manufacturer VARCHAR(10),
Model_Num VARCHAR(20),
Original_Purchase_Date DATE,
Last_Service DATE,
Carrier_ID INTEGER,
Number_of_Seats INTEGER,
FOREIGN KEY (Carrier_ID) REFERENCES Carriers(Carrier_ID)
);
?
— One airport facilitates many flights
CREATE TABLE Airports (
Airport_Code VARCHAR(3) PRIMARY KEY,
Airport_Location VARCHAR(25),
Year_Opened INTEGER,
Num_of_Terminals INTEGER
);
?
— Each flight involves many airports
CREATE TABLE Flights (
Flight_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
Plane_ID INTEGER,
Carrier_ID INTEGER,
Airport_Code_Origin VARCHAR(3),
Airport_Code_Destination VARCHAR(3),
Departure_DateTime DATETIME,
Arrival_DateTime DATETIME,
FOREIGN KEY (Plane_ID) REFERENCES Aircraft(Plane_ID),
/* FOREIGN KEY (Carrier_ID) REFERENCES Carriers(Carrier_ID), */
FOREIGN KEY (Airport_Code_Origin) REFERENCES Airports(Airport_Code),
FOREIGN KEY (Airport_Code_Destination) REFERENCES Airports(Airport_Code)
);
?
— Adding records to the carriers table
INSERT INTO Carriers (Carrier_Name)
VALUES (‘American’),
(‘Delta’),
(‘Southwest’),
(‘United’),
(‘Virgin Atlantic’);
?
— Adding records to the aircraft table
INSERT INTO Aircraft (Manufacturer, Model_Num, Original_Purchase_Date, Last_Service, Carrier_ID)
VALUES
(‘Boeing’,’787 Dreamliner’,’2014-10-10′,’2015-12-17′,3),
(‘Boeing’,’737-900′,’2003-03-23′,’2016-01-26′,3),
(‘Embraer’,’RJ-45′,’1999-11-24′,’2016-01-31′,3),
(‘Embraer’,’RJ-45′,’2002-10-02′,’2016-01-20′,1),
(‘Boeing’,’737-900′,’2000-04-16′,’2015-12-24′,2),
(‘Boeing’,’747-400′,’2001-10-25′,’2016-01-03′,3),
(‘Boeing’,’737-900′,’2009-12-02′,’2016-01-26′,3),
(‘Boeing’,’747-400′,’2005-05-14′,’2016-01-21′,2),
(‘Boeing’,’737-900′,’1999-10-20′,’2015-12-27′,5),
(‘Embraer’,’RJ-45′,’2009-05-05′,’2016-01-20′,3),
(‘Airbus’,’A300′,’1999-11-25′,’2016-01-03′,3),
(‘Embraer’,’RJ-45′,’2012-06-12′,’2016-01-17′,2),
(‘Embraer’,’RJ-45′,’2014-12-01′,’2015-12-29′,1),
(‘Boeing’,’737-900′,’2007-11-24′,’2016-01-07′,1),
(‘Embraer’,’RJ-45′,’2000-08-31′,’2016-01-08′,2),
(‘Airbus’,’A300′,’2011-11-11′,’2016-01-01′,1),
(‘Boeing’,’747-400′,’2008-06-27′,’2015-12-09′,1),
(‘Embraer’,’RJ-45′,’2008-11-25′,’2016-01-27′,1),
(‘Airbus’,’A330′,’2013-03-27′,’2015-12-23′,5),
(‘Embraer’,’RJ-45′,’2000-07-15′,’2016-01-13′,3),
(‘Boeing’,’747-400′,’2001-12-05′,’2015-12-06′,2),
(‘Boeing’,’737-900′,’2013-02-14′,’2015-12-16′,2),
(‘Boeing’,’727-200′,’2008-08-21′,’2016-01-20′,1),
(‘Airbus’,’A330′,’2001-12-02′,’2015-12-16′,5),
(‘Embraer’,’RJ-45′,’2008-02-18′,’2016-01-08′,3),
(‘Boeing’,’737-900′,’2007-04-02′,’2015-12-19′,4),
(‘Embraer’,’RJ-45′,’2005-12-22′,’2016-01-16′,1);
?
?
— Adding records to the Airports table
INSERT INTO Airports (Airport_Code, Airport_Location, Year_Opened, Num_of_Terminals)
VALUES
(‘BOI’,’Boise, ID’,1936,3),
(‘ATL’,’Atlanta, GA’,1926,9),
(‘SLC’,’Salt Lake City, UT’,1933,5),
(‘LAX’,’Los Angeles, CA’,1928,10),
(‘SEA’,’Seattle, WA’,1944,6),
(‘MIA’,’Miami, FL’,1945,9),
(‘ORD’,’Chicago, IL’,1942,8),
(‘JFK’,’New York, New York’,1943,12),
(‘DFW’,’Dallas-Fort Worth, TX’,1973,8),
(‘LAS’,’Las Vegas, NV’,1947,3),
(‘MCO’,’Orlando, FL’,1942,4),
(‘JAX’,’Jacksonville, FL’,1968,3),
(‘PIT’,’Pittsburgh, PA’,1946,4),
(‘PHL’,’Philadelphia, PA’,1927,6),
(‘SFO’,’San Francisco, CA’,1927,8),
(‘IAH’,’Houston, TX’,1957,10);
?
— Adding records to the Flights table
INSERT INTO Flights (Plane_ID, Carrier_ID, Airport_Code_Origin, Airport_Code_Destination, Departure_DateTime, Arrival_DateTime)
VALUES
(22,4,’ORD’,’PIT’,’2017-01-20 20:39′,’2017-01-21 00:46′),
(22,4,’IAH’,’PIT’,’2017-12-21 17:32′,’2017-12-21 19:35′),
(7,5,’MIA’,’JFK’,’2017-02-20 23:26′,’2017-02-21 04:04′),
(1,4,’MCO’,’BOI’,’2017-12-25 01:26′,’2017-12-25 06:16′),
(24,4,’PIT’,’JFK’,’2017-12-17 21:20′,’2017-12-18 03:01′),
(21,2,’MIA’,’SFO’,’2017-02-19 19:09′,’2017-02-19 19:38′),
(6,3,’SFO’,’PIT’,’2017-02-21 21:45′,’2017-02-21 23:18′),
(23,5,’JFK’,’DFW’,’2017-02-13 09:10′,’2017-02-13 10:59′),
(14,2,’MCO’,’SEA’,’2017-12-07 02:57′,’2017-12-07 03:00′),
(26,5,’BOI’,’PIT’,’2017-02-27 18:07′,’2017-02-28 01:18′),
(19,2,’SEA’,’BOI’,’2017-12-29 05:49′,’2017-12-29 07:56′),
(18,4,’PIT’,’ORD’,’2017-01-15 16:36′,’2017-01-15 18:22′),
(15,5,’SFO’,’SEA’,’2017-01-15 01:55′,’2017-01-15 05:03′),
(4,1,’ORD’,’LAS’,’2017-05-28 21:03′,’2017-05-29 00:34′),
(24,3,’BOI’,’DFW’,’2017-02-23 06:50′,’2017-02-23 08:57′),
(6,4,’SFO’,’JFK’,’2017-06-24 23:06′,’2017-06-25 04:25′),
(22,1,’SFO’,’LAS’,’2017-01-03 01:05′,’2017-01-03 02:59′),
(7,3,’MIA’,’SFO’,’2017-06-21 08:55′,’2017-06-21 09:11′),
(7,2,’IAH’,’LAS’,’2017-01-10 10:42′,’2017-01-10 11:19′),
(8,3,’LAX’,’ATL’,’2017-07-27 09:35′,’2017-07-27 11:05′),
(26,3,’SFO’,’DFW’,’2017-08-07 08:05′,’2017-08-07 11:24′),
(20,4,’JFK’,’ORD’,’2017-01-04 12:45′,’2017-01-04 15:41′),
(27,3,’ORD’,’ATL’,’2017-10-02 05:42′,’2017-10-02 07:18′),
(7,3,’ORD’,’DFW’,’2017-09-09 04:40′,’2017-09-09 05:13′),
(10,1,’PIT’,’JFK’,’2017-02-14 07:19′,’2017-02-14 10:43′),
(13,4,’LAX’,’JFK’,’2017-07-18 18:23′,’2017-07-18 21:05′),
(18,4,’PHL’,’MCO’,’2017-01-30 13:47′,’2017-01-30 16:49′),
(8,5,’JFK’,’PHL’,’2017-07-01 18:57′,’2017-07-01 19:04′),
(26,5,’SEA’,’SLC’,’2017-08-08 05:19′,’2017-08-08 05:48′),
(23,4,’MIA’,’LAX’,’2017-11-26 18:16′,’2017-11-26 23:35′),
(17,5,’MCO’,’SLC’,’2017-03-06 11:32′,’2017-03-06 12:38′),
(2,3,’JAX’,’PIT’,’2017-03-04 13:16′,’2017-03-04 13:32′),
(23,2,’IAH’,’DFW’,’2017-03-02 21:47′,’2017-03-03 00:29′),
(17,5,’LAX’,’SFO’,’2017-04-01 00:07′,’2017-04-01 02:25′),
(7,4,’JAX’,’LAX’,’2017-12-17 15:46′,’2017-12-17 18:00′),
(9,1,’JAX’,’PIT’,’2017-05-20 17:09′,’2017-05-20 17:28′),
(2,1,’PIT’,’ATL’,’2017-12-21 09:02′,’2017-12-21 13:53′),
(4,4,’SFO’,’DFW’,’2017-03-10 23:07′,’2017-03-11 03:33′),
(18,5,’JAX’,’MCO’,’2017-02-07 10:59′,’2017-02-07 13:13′),
(4,1,’ATL’,’PIT’,’2017-07-25 01:57′,’2017-07-25 03:25′),
(5,2,’LAX’,’SEA’,’2017-12-09 18:29′,’2017-12-09 21:40′),
(27,5,’JAX’,’ORD’,’2017-08-26 08:35′,’2017-08-26 14:25′),
(6,4,’MIA’,’IAH’,’2017-03-17 05:00′,’2017-03-17 08:24′),
(9,4,’IAH’,’LAS’,’2017-09-21 10:23′,’2017-09-21 11:17′),
(11,1,’ATL’,’PIT’,’2017-03-20 03:53′,’2017-03-20 04:36′),
(14,5,’LAX’,’PIT’,’2017-01-21 03:15′,’2017-01-21 05:23′),
(21,5,’ATL’,’SEA’,’2017-07-10 01:33′,’2017-07-10 04:07′),
(6,3,’IAH’,’LAX’,’2017-02-09 16:59′,’2017-02-09 22:30′),
(23,2,’PIT’,’MCO’,’2017-12-09 19:46′,’2017-12-09 20:43′),
(10,4,’LAX’,’SLC’,’2017-08-06 09:30′,’2017-08-06 12:28′),
(21,5,’BOI’,’PIT’,’2017-12-14 17:09′,’2017-12-14 20:07′),
(10,4,’PIT’,’SLC’,’2017-02-08 02:18′,’2017-02-08 04:11′),
(4,1,’LAS’,’DFW’,’2017-02-10 14:31′,’2017-02-10 16:37′),
(23,1,’MCO’,’BOI’,’2017-01-18 21:56′,’2017-01-18 22:35′),
(21,2,’SEA’,’JFK’,’2017-03-21 05:36′,’2017-03-21 10:02′),
(8,5,’SLC’,’JFK’,’2017-04-21 03:19′,’2017-04-21 05:34′),
(27,1,’JFK’,’SLC’,’2017-01-09 21:00′,’2017-01-10 01:37′),
(21,5,’JFK’,’DFW’,’2017-02-08 18:47′,’2017-02-08 23:48′),
(7,2,’IAH’,’SEA’,’2017-12-21 22:58′,’2017-12-22 01:54′),
(22,2,’MCO’,’PHL’,’2017-01-08 05:07′,’2017-01-08 06:36′),
(18,3,’LAS’,’BOI’,’2017-02-27 05:56′,’2017-02-27 09:42′),
(17,5,’LAX’,’MIA’,’2017-03-24 16:42′,’2017-03-24 21:56′),
(4,4,’MIA’,’JFK’,’2017-03-06 12:29′,’2017-03-06 15:01′),
(7,5,’LAS’,’JAX’,’2017-07-08 04:57′,’2017-07-08 12:43′),
(11,5,’DFW’,’ORD’,’2017-06-07 02:36′,’2017-06-07 07:13′),
(10,2,’JAX’,’SFO’,’2017-02-12 04:31′,’2017-02-12 05:31′),
(27,3,’PHL’,’JAX’,’2017-05-15 22:08′,’2017-05-15 23:43′),
(7,2,’SFO’,’PHL’,’2017-11-20 04:39′,’2017-11-20 10:15′),
(25,5,’JFK’,’IAH’,’2017-01-08 17:52′,’2017-01-08 19:48′),
(21,1,’MIA’,’JAX’,’2017-10-07 07:59′,’2017-10-07 11:55′),
(19,1,’SEA’,’BOI’,’2017-12-15 14:23′,’2017-12-15 18:05′),
(14,3,’PHL’,’ORD’,’2017-11-23 19:03′,’2017-11-23 22:04′),
(9,2,’SEA’,’BOI’,’2017-03-18 02:51′,’2017-03-18 05:07′),
(8,2,’JAX’,’DFW’,’2017-07-13 02:15′,’2017-07-13 04:42′),
(20,4,’MCO’,’DFW’,’2017-01-08 16:23′,’2017-01-08 17:19′),
(2,5,’IAH’,’BOI’,’2017-07-10 16:26′,’2017-07-10 20:55′),
(27,4,’LAX’,’ATL’,’2017-05-09 21:00′,’2017-05-09 23:11′),
(5,2,’BOI’,’ORD’,’2017-03-29 13:24′,’2017-03-29 14:57′),
(11,3,’LAX’,’ORD’,’2017-02-27 02:11′,’2017-02-27 04:08′),
(6,1,’LAX’,’JAX’,’2017-02-01 10:30′,’2017-02-01 14:49′),
(1,1,’BOI’,’LAX’,’2017-12-28 14:22′,’2017-12-28 16:57′),
(17,2,’LAX’,’MCO’,’2017-08-22 08:12′,’2017-08-22 15:13′),
(13,4,’MIA’,’ORD’,’2017-01-20 09:06′,’2017-01-20 12:52′),
(1,4,’MIA’,’MCO’,’2017-09-28 11:55′,’2017-09-28 13:47′),
(27,2,’BOI’,’JFK’,’2017-01-28 21:09′,’2017-01-28 23:46′),
(18,2,’LAS’,’PHL’,’2017-02-21 12:51′,’2017-02-21 15:18′),
(5,2,’PHL’,’LAS’,’2017-02-18 23:39′,’2017-02-19 00:12′),
(15,3,’DFW’,’JAX’,’2017-12-09 00:11′,’2017-12-09 05:38′),
(5,1,’ATL’,’MCO’,’2017-11-15 16:45′,’2017-11-15 20:41′),
(24,3,’SEA’,’LAX’,’2017-01-27 12:37′,’2017-01-27 18:48′),
(16,1,’MIA’,’LAS’,’2017-01-21 06:14′,’2017-01-21 11:55′),
(11,4,’DFW’,’PIT’,’2017-01-29 06:05′,’2017-01-29 10:53′),
(4,3,’IAH’,’JAX’,’2017-01-22 16:46′,’2017-01-23 00:12′),
(16,4,’LAS’,’MCO’,’2017-07-31 07:57′,’2017-07-31 10:14′),
(15,4,’IAH’,’ORD’,’2017-12-22 00:39′,’2017-12-22 02:25′),
(26,1,’MIA’,’LAS’,’2017-01-05 01:12′,’2017-01-05 05:39′),
(5,4,’IAH’,’LAS’,’2017-02-14 21:53′,’2017-02-15 02:00′),
(14,5,’SEA’,’PIT’,’2017-02-11 08:56′,’2017-02-11 12:51′),
(15,5,’BOI’,’MCO’,’2017-06-01 18:10′,’2017-06-01 22:26′),
(26,3,’SEA’,’IAH’,’2017-07-12 08:30′,’2017-07-12 09:42′),
(25,1,’DFW’,’PIT’,’2017-01-28 08:13′,’2017-01-28 11:05′),
(6,1,’PHL’,’SEA’,’2017-01-21 22:12′,’2017-01-22 00:05′),
(7,5,’LAS’,’JFK’,’2017-02-01 06:15′,’2017-02-01 10:08′),
(22,2,’BOI’,’JFK’,’2017-01-30 08:07′,’2017-01-30 10:26′),
(20,1,’MIA’,’JFK’,’2017-04-02 01:02′,’2017-04-02 01:32′),
(13,1,’PHL’,’ATL’,’2017-08-09 18:29′,’2017-08-09 19:29′),
(18,5,’DFW’,’JAX’,’2017-04-23 18:37′,’2017-04-24 00:01′),
(6,4,’PIT’,’IAH’,’2017-01-12 11:30′,’2017-01-12 18:33′),
(10,5,’MCO’,’ORD’,’2017-02-28 09:35′,’2017-02-28 13:05′),
(21,1,’PIT’,’MIA’,’2017-01-31 00:46′,’2017-01-31 05:04′),
(1,4,’JFK’,’MCO’,’2017-07-15 17:41′,’2017-07-15 23:21′),
(2,4,’PHL’,’ORD’,’2017-03-15 06:52′,’2017-03-15 07:40′),
(20,3,’PHL’,’ATL’,’2017-02-05 05:21′,’2017-02-05 06:18′),
(21,5,’SEA’,’PHL’,’2017-02-18 00:10′,’2017-02-18 02:33′),
(19,2,’SEA’,’MCO’,’2017-01-25 20:31′,’2017-01-25 21:36′),
(1,2,’DFW’,’SFO’,’2017-11-04 15:58′,’2017-11-04 16:04′),
(12,1,’ATL’,’JFK’,’2017-01-13 16:04′,’2017-01-13 21:30′),
(17,4,’PHL’,’JAX’,’2017-01-08 20:32′,’2017-01-08 22:29′),
(4,2,’ATL’,’SLC’,’2017-10-13 02:20′,’2017-10-13 06:18′),
(25,5,’SFO’,’DFW’,’2017-12-28 02:57′,’2017-12-28 03:14′),
(20,5,’SEA’,’PHL’,’2017-07-05 23:38′,’2017-07-06 00:24′),
(27,5,’LAX’,’SEA’,’2017-12-15 04:35′,’2017-12-15 04:59′),
(9,3,’PHL’,’JFK’,’2017-03-16 09:17′,’2017-03-16 11:14′),
(19,4,’SLC’,’PIT’,’2017-02-01 12:28′,’2017-02-01 16:17′),
(16,2,’JFK’,’PIT’,’2017-06-06 09:08′,’2017-06-06 13:25′),
(7,5,’ATL’,’MIA’,’2017-12-08 20:15′,’2017-12-08 22:39′),
(23,4,’SFO’,’SLC’,’2017-01-20 12:31′,’2017-01-20 16:18′),
(10,2,’SFO’,’BOI’,’2017-05-03 01:50′,’2017-05-03 02:23′),
(26,3,’LAS’,’PHL’,’2017-01-19 13:05′,’2017-01-19 13:10′),
(4,1,’IAH’,’LAS’,’2017-08-31 16:08′,’2017-08-31 16:29′),
(21,5,’JFK’,’SEA’,’2017-05-30 14:33′,’2017-05-30 15:25′),
(18,2,’JFK’,’MIA’,’2017-02-11 12:29′,’2017-02-11 17:50′),
(4,2,’PHL’,’ATL’,’2017-07-10 14:25′,’2017-07-10 21:58′),
(9,3,’PIT’,’JAX’,’2017-03-22 20:06′,’2017-03-22 20:18′),
(4,5,’BOI’,’SLC’,’2017-12-19 16:19′,’2017-12-19 18:18′),
(27,3,’BOI’,’MIA’,’2017-12-11 09:09′,’2017-12-11 11:23′),
(4,1,’BOI’,’JFK’,’2017-01-07 01:43′,’2017-01-07 04:36′),
(4,4,’DFW’,’LAS’,’2017-01-02 22:45′,’2017-01-03 00:47′),
(8,5,’JAX’,’ATL’,’2017-03-07 20:46′,’2017-03-07 23:58′),
(22,2,’MCO’,’PHL’,’2017-04-15 07:05′,’2017-04-15 11:51′),
(14,1,’SLC’,’PIT’,’2017-07-13 06:33′,’2017-07-13 09:57′),
(10,2,’PHL’,’SLC’,’2017-01-02 04:21′,’2017-01-02 04:42′),
(9,3,’SEA’,’MIA’,’2017-02-01 06:45′,’2017-02-01 10:15′),
(12,4,’IAH’,’ORD’,’2017-07-09 00:37′,’2017-07-09 03:00′),
(13,5,’BOI’,’SEA’,’2017-09-12 06:42′,’2017-09-12 10:14′),
(15,2,’MCO’,’ATL’,’2017-01-13 18:32′,’2017-01-14 01:59′),
(20,4,’PHL’,’ATL’,’2017-10-01 19:18′,’2017-10-01 22:17′),
(9,1,’JFK’,’PIT’,’2017-01-15 04:25′,’2017-01-15 04:56′),
(22,5,’SFO’,’IAH’,’2017-03-30 16:38′,’2017-03-30 18:51′),
(18,4,’LAS’,’BOI’,’2017-02-13 06:31′,’2017-02-13 08:20′),
(3,3,’JAX’,’ORD’,’2017-01-03 10:32′,’2017-01-03 13:20′),
(22,5,’DFW’,’JFK’,’2017-06-08 10:32′,’2017-06-08 13:46′),
(6,1,’LAX’,’PHL’,’2017-03-13 21:42′,’2017-03-14 02:54′),
(16,4,’DFW’,’LAX’,’2017-03-09 03:37′,’2017-03-09 10:29′),
(3,1,’IAH’,’MIA’,’2017-02-15 18:41′,’2017-02-15 23:48′),
(27,3,’LAX’,’SLC’,’2017-03-18 05:35′,’2017-03-18 08:32′),
(19,2,’LAX’,’DFW’,’2017-01-10 14:20′,’2017-01-10 16:07′),
(21,4,’JFK’,’PHL’,’2017-12-21 21:36′,’2017-12-21 22:35′),
(2,5,’IAH’,’MIA’,’2017-01-10 00:56′,’2017-01-10 01:03′),
(15,4,’SFO’,’PHL’,’2017-02-11 08:55′,’2017-02-11 10:40′),
(14,4,’ORD’,’JAX’,’2017-12-11 06:23′,’2017-12-11 06:49′),
(6,1,’MIA’,’DFW’,’2017-01-22 08:42′,’2017-01-22 13:30′),
(1,3,’MCO’,’LAX’,’2017-03-24 09:54′,’2017-03-24 16:04′),
(13,1,’LAS’,’PHL’,’2017-01-01 00:36′,’2017-01-01 05:32′),
(9,2,’SFO’,’ORD’,’2017-02-02 01:33′,’2017-02-02 04:40′),
(25,5,’MIA’,’ORD’,’2017-02-08 01:23′,’2017-02-08 03:38′),
(24,5,’IAH’,’PIT’,’2017-02-22 19:34′,’2017-02-22 22:27′),
(22,2,’LAX’,’ORD’,’2017-01-23 19:26′,’2017-01-24 00:37′),
(11,5,’ATL’,’ORD’,’2017-07-12 17:23′,’2017-07-12 19:30′),
(26,2,’JFK’,’MCO’,’2017-12-09 00:23′,’2017-12-09 03:27′),
(12,1,’MCO’,’SEA’,’2017-07-03 10:18′,’2017-07-03 10:57′),
(9,2,’SEA’,’LAX’,’2017-03-21 09:42′,’2017-03-21 10:59′),
(27,3,’LAX’,’ORD’,’2017-01-03 23:16′,’2017-01-04 04:10′),
(24,5,’BOI’,’ORD’,’2017-03-06 18:28′,’2017-03-06 21:13′),
(7,5,’SEA’,’MCO’,’2017-01-03 00:25′,’2017-01-03 04:09′),
(16,3,’BOI’,’PHL’,’2017-01-09 11:07′,’2017-01-09 13:20′),
(22,3,’JAX’,’PIT’,’2017-01-04 04:36′,’2017-01-04 06:13′),
(7,3,’IAH’,’JFK’,’2017-01-31 09:26′,’2017-01-31 10:20′),
(10,4,’LAX’,’IAH’,’2017-01-11 11:08′,’2017-01-11 12:58′),
(3,2,’LAS’,’LAX’,’2017-01-19 14:08′,’2017-01-19 16:29′),
(26,1,’SEA’,’DFW’,’2017-12-24 07:34′,’2017-12-24 11:23′),
(24,5,’LAX’,’MCO’,’2017-12-28 20:55′,’2017-01-01 02:05′),
(17,5,’ORD’,’JAX’,’2017-12-24 18:42′,’2017-12-24 19:41′),
(11,2,’JFK’,’MIA’,’2017-03-17 08:07′,’2017-03-17 08:38′),
(20,1,’ATL’,’PIT’,’2017-01-25 09:46′,’2017-01-25 10:51′),
(11,2,’MIA’,’LAX’,’2017-05-16 01:02′,’2017-05-16 02:47′),
(16,5,’SFO’,’JFK’,’2017-01-12 09:38′,’2017-01-12 10:58′),
(6,1,’DFW’,’JFK’,’2017-01-10 17:24′,’2017-01-10 18:31′),
(1,3,’SEA’,’PIT’,’2017-11-08 08:57′,’2017-11-08 13:09′),
(1,4,’LAX’,’ORD’,’2017-12-14 10:43′,’2017-12-14 12:00′),
(15,3,’JAX’,’SLC’,’2017-03-28 18:04′,’2017-03-28 22:56′),
(21,1,’SFO’,’ATL’,’2017-05-30 15:57′,’2017-05-30 19:37′),
(9,1,’PHL’,’JAX’,’2017-02-28 09:36′,’2017-02-28 14:02′),
(6,2,’IAH’,’MCO’,’2017-07-13 20:39′,’2017-07-14 01:28′),
(3,5,’DFW’,’JFK’,’2017-03-22 08:28′,’2017-03-22 10:10′),
(17,4,’JAX’,’LAS’,’2017-01-08 08:20′,’2017-01-08 09:04′),
(2,4,’LAS’,’MCO’,’2017-03-29 12:30′,’2017-03-29 18:13′),
(27,1,’ATL’,’DFW’,’2017-02-27 06:04′,’2017-02-27 09:04′),
(24,5,’PHL’,’DFW’,’2017-01-10 20:21′,’2017-01-10 21:08′),
(21,2,’IAH’,’LAX’,’2017-02-16 13:31′,’2017-02-16 16:09′),
(6,5,’ORD’,’MIA’,’2017-01-21 05:47′,’2017-01-21 10:08′),
(24,2,’IAH’,’SLC’,’2017-01-20 11:04′,’2017-01-20 18:51′),
(26,4,’LAX’,’SLC’,’2017-12-13 20:35′,’2017-12-14 02:03′),
(27,2,’PIT’,’PHL’,’2017-12-17 09:02′,’2017-12-17 09:07′),
(18,1,’SLC’,’DFW’,’2017-01-29 17:45′,’2017-01-29 23:46′),
(24,5,’PIT’,’MCO’,’2017-01-18 02:59′,’2017-01-18 07:47′),
(4,2,’JAX’,’JFK’,’2017-03-25 17:16′,’2017-03-25 20:54′),
(17,4,’SLC’,’SEA’,’2017-01-15 13:08′,’2017-01-15 13:43′),
(20,3,’SFO’,’DFW’,’2017-02-28 02:22′,’2017-02-28 05:56′),
(8,2,’DFW’,’IAH’,’2017-03-08 11:30′,’2017-03-08 11:34′),
(9,5,’SEA’,’IAH’,’2017-02-23 13:55′,’2017-02-23 16:44′),
(16,5,’ORD’,’ATL’,’2017-03-14 11:49′,’2017-03-14 15:23′),
(9,3,’ORD’,’SFO’,’2017-03-30 11:20′,’2017-03-30 16:23′),
(23,4,’JAX’,’MCO’,’2017-03-14 17:59′,’2017-03-14 18:09′),
(24,2,’ATL’,’PHL’,’2017-01-02 12:42′,’2017-01-02 16:47′),
(4,4,’JFK’,’DFW’,’2017-03-28 14:05′,’2017-03-28 20:03′),
(1,1,’SEA’,’ORD’,’2017-02-18 05:51′,’2017-02-18 08:35′),
(10,2,’MCO’,’JFK’,’2017-07-06 19:11′,’2017-07-06 22:35′),
(9,3,’LAX’,’ORD’,’2017-01-03 09:55′,’2017-01-03 12:03′),
(16,5,’JAX’,’BOI’,’2017-03-24 06:34′,’2017-03-24 07:36′),
(8,4,’PHL’,’IAH’,’2017-01-06 01:49′,’2017-01-06 05:34′),
(19,4,’LAX’,’IAH’,’2017-02-15 04:44′,’2017-02-15 05:37′),
(25,5,’MCO’,’SEA’,’2017-01-12 15:26′,’2017-01-12 16:45′),
(17,5,’LAS’,’MIA’,’2017-03-26 21:53′,’2017-03-26 22:20′),
(2,1,’PIT’,’LAX’,’2017-12-18 10:15′,’2017-12-18 13:15′),
(25,3,’PHL’,’SFO’,’2017-03-01 10:38′,’2017-03-01 12:15′),
(17,1,’DFW’,’PIT’,’2017-01-11 15:40′,’2017-01-11 19:04′),
(16,5,’SEA’,’MIA’,’2017-03-26 15:44′,’2017-03-26 22:43′),
(16,2,’ATL’,’IAH’,’2017-01-28 14:58′,’2017-01-28 15:56′),
(10,3,’PHL’,’SLC’,’2017-02-20 10:57′,’2017-02-20 11:57′),
(25,3,’SEA’,’LAS’,’2017-02-02 17:47′,’2017-02-02 19:47′),
(6,2,’MCO’,’MIA’,’2017-07-02 01:16′,’2017-07-02 04:27′),
(2,2,’SLC’,’IAH’,’2017-07-09 19:42′,’2017-07-09 20:34′),
(25,1,’MCO’,’JFK’,’2017-07-08 08:26′,’2017-07-08 11:57′),
(4,3,’MCO’,’LAX’,’2017-01-06 02:39′,’2017-01-06 04:39′),
(1,4,’ATL’,’PIT’,’2017-03-14 13:44′,’2017-03-14 18:07′),
(15,2,’ORD’,’PHL’,’2017-12-13 08:17′,’2017-12-13 08:47′),
(22,4,’ATL’,’JFK’,’2017-02-20 18:05′,’2017-02-20 22:41′),
(20,3,’LAS’,’PIT’,’2017-07-09 02:39′,’2017-07-09 04:39′),
(18,2,’SLC’,’DFW’,’2017-03-20 10:31′,’2017-03-20 11:49′),
(4,3,’JAX’,’ORD’,’2017-12-14 02:42′,’2017-12-14 05:43′),
(24,1,’PHL’,’BOI’,’2017-02-14 06:52′,’2017-02-14 08:29′),
(1,3,’PIT’,’JAX’,’2017-07-03 07:55′,’2017-07-03 09:43′),
(8,5,’PIT’,’JFK’,’2017-01-06 06:28′,’2017-01-06 07:11′),
(1,1,’SLC’,’PHL’,’2017-01-21 01:58′,’2017-01-21 08:51′),
(24,4,’IAH’,’MCO’,’2017-07-04 02:12′,’2017-07-04 02:28′),
(20,4,’JAX’,’JFK’,’2017-01-15 05:19′,’2017-01-15 06:20′),
(6,1,’MCO’,’PIT’,’2017-12-09 05:10′,’2017-12-09 05:12′),
(17,2,’ATL’,’PHL’,’2017-02-09 20:04′,’2017-02-09 23:59′),
(24,1,’MIA’,’BOI’,’2017-03-04 06:30′,’2017-03-04 08:45′),
(10,2,’LAS’,’JFK’,’2017-12-21 02:56′,’2017-12-21 03:47′),
(15,4,’JAX’,’JFK’,’2017-07-04 05:36′,’2017-07-04 10:27′);
?
/* Remove Carrier_ID from Flights table for circular reference removal */
?
Alter Table Flights drop column Carrier_ID;
?
— Append Update section to populate number of seats column in aircraft table
Update airlines.aircraft set number_of_seats=500 where plane_id in (1,3,5,7,11);
?
Update airlines.aircraft set number_of_seats=100 where plane_id in (12,13,14,15);
?
Update airlines.aircraft set number_of_seats=250 where plane_id in (2,4,6,8,10);
?
Update airlines.aircraft set number_of_seats=550 where plane_id in (16,18,20,22);
?
Update airlines.aircraft set number_of_seats=300 where plane_id in (17,19,21,23);
?
Update airlines.aircraft set number_of_seats=400 where plane_id in (24,25,26,27);
?
commit;
?