homework help 30836

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;