SQL

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;