DROP DATABASE IF EXISTS airtravel;
CREATE DATABASE airtravel;
USE airtravel;

DROP TABLE IF EXISTS flights;
CREATE TABLE flights	(
	airlineAbbr CHAR(3),
    flightNr INT,
    depCityCode CHAR(3) NOT NULL,
    arrCityCode CHAR(3) NOT NULL,
    depTime TIME NOT NULL,
    arrTime TIME NOT NULL,
    PRIMARY KEY (airlineAbbr, flightNr)
);

DROP TABLE IF EXISTS airports;
CREATE TABLE airports (
    city VARCHAR(40) NOT NULL,
    state CHAR(2) NOT NULL,
    code CHAR(3),
    PRIMARY KEY(code)
    );
    
DROP TABLE IF EXISTS airlines;
CREATE TABLE airlines (
	name VARCHAR(60) NOT NULL,
    abbr CHAR(3) PRIMARY KEY,
    headquarter VARCHAR(60) NOT NULL
    );
    
INSERT INTO airlines (name, abbr, headquarter)
VALUES
('Trans-Wisconsin Airlines', 'TWA', 'Madison, WI'),
('PanWisc', 'PWI', 'Milwaukee, WI'), 
('EL', 'ELL', 'Chicago, IL'),
('Rockford-Chicago-Detroit', 'RCD', 'Springfield, IL'),
('Michigan Airlines', 'MAL', 'Detroit, MI');

INSERT INTO airports (city, code, state)
VALUES 
('Appleton', 'ATW', 'WI'),	
('Eau Claire',	'EAU', 'WI'),
('Green Bay', 'GRB', 'WI'),
('La Crosse', 'LSE', 'WI'),
('Madison', 'MAD', 'WI'),
('Milwaukee', 'MKE', 'WI'),
('Mosinee', 'CWA', 'WI'),
('Rhinelander', 'RHI', 'WI'),
('Detroit',	'DTW', 'MI'),
('Grand Rapids', 'GRR',	'MI'),
('Marquette', 'SAW', 'MI'),
('Muskegon', 'MKG', 'MI'),
('Chicago',	'MDW', 'IL'),
('Chicago',	'ORD', 'IL'),	
('Rockford', 'RFD', 'IL'),	
('Springfield', 'SPI', 'IL');	

INSERT INTO flights (airlineAbbr, flightNr, depCityCode, arrCityCode, depTime, arrTime)
VALUES
('TWA', 102, 'ATW', 'EAU', '06:30', '06:55'),
('TWA', 103, 'EAU', 'GRB', '07:10', '07:55'),
('TWA', 104, 'GRB', 'MKE', '08:27', '09:03'),
('TWA', 105, 'ATW', 'EAU', '10:30', '10:55'),
('TWA', 106, 'EAU', 'GRB', '11:10', '11:55'),
('TWA', 107, 'GRB', 'MKE', '12:27', '13:03'),
('TWA', 108, 'ATW', 'EAU', '13:30', '13:55'),
('TWA', 109, 'EAU', 'GRB', '14:10', '14:55'),
('TWA', 110, 'GRB', 'MKE', '15:27', '16:05'),
('TWA', 111, 'ATW', 'EAU', '18:30', '18:55'),
('TWA', 112, 'EAU', 'GRB', '19:10', '19:55'),
('TWA', 113, 'GRB', 'MKE', '20:27', '21:03'),
('TWA', 114, 'ATW', 'EAU', '22:00', '22:25'),
('TWA', 115, 'EAU', 'GRB', '22:45', '23:15'),
('TWA', 116, 'GRB', 'MKE', '23:24', '23:59'),
('TWA', 120, 'MKE', 'MAD', '05:00', '05:31'),
('TWA', 121, 'MAD', 'MKE', '06:10', '06:41'),
('TWA', 122, 'MKE', 'MAD', '07:00', '07:31'),
('TWA', 123, 'MAD', 'MKE', '08:10', '08:41'),
('TWA', 124, 'MKE', 'MAD', '11:00', '11:31'),
('TWA', 125, 'MAD', 'MKE', '12:10', '12:41'),
('TWA', 126, 'MKE', 'MAD', '14:00', '14:31'),
('TWA', 127, 'MAD', 'MKE', '15:10', '15:41'),
('TWA', 128, 'MKE', 'MAD', '19:00', '19:31'),
('TWA', 129, 'MAD', 'MKE', '20:10', '20:41'),
('TWA', 130, 'MKE', 'MAD', '22:00', '23:31'),
('TWA', 131, 'MAD', 'MKE', '23:10', '23:41'),
('TWA', 150, 'MAD', 'LSE', '11:55', '12:50'),
('TWA', 151, 'LSE', 'MAD', '13:55', '14:50'),
('TWA', 160, 'MKE', 'LSE', '09:55', '10:50'),
('TWA', 161, 'LSE', 'MKE', '11:55', '12:50'),
('TWA', 162, 'MKE', 'LSE', '16:55', '17:50'),
('TWA', 163, 'LSE', 'MKE', '19:55', '20:50'),
('TWA', 170, 'MKE', 'ATW', '05:10', '05:30'),
('TWA', 171, 'ATW', 'GRB', '05:55', '06:10'),
('TWA', 172, 'GRB', 'CWA', '06:20', '06:50'),
('TWA', 173, 'CWA', 'RHI', '07:20', '08:10'),
('TWA', 174, 'RHI', 'EAU', '08:40', '09:20'),
('TWA', 175, 'EAU', 'LSE', '09:50', '10:30'),
('TWA', 176, 'LSE', 'MKE', '11:10', '11:55'),
('TWA', 180, 'MKE', 'ATW', '12:10', '12:30'),
('TWA', 181, 'ATW', 'GRB', '12:55', '13:10'),
('TWA', 182, 'GRB', 'CWA', '13:20', '13:50'),
('TWA', 183, 'CWA', 'RHI', '14:20', '15:10'),
('TWA', 184, 'RHI', 'EAU', '15:40', '16:20'),
('TWA', 185, 'EAU', 'LSE', '16:50', '17:30'),
('TWA', 186, 'LSE', 'MKE', '18:10', '18:55'),
('TWA', 187, 'MKE', 'ATW', '21:10', '21:30'),
('TWA', 188, 'ATW', 'GRB', '21:55', '22:10'),
('TWA', 189, 'GRB', 'MKE', '22:50', '23:20'),
('TWA', 200, 'MAD', 'DET', '06:00', '07:15'),
('TWA', 201, 'DET', 'MAD', '08:00', '09:15'),
('TWA', 202, 'MAD', 'DET', '10:00', '11:15'),
('TWA', 203, 'DET', 'MAD', '12:00', '13:15'),
('TWA', 204, 'MAD', 'DET', '14:00', '15:15'),
('TWA', 205, 'DET', 'MAD', '16:00', '17:15'),
('TWA', 206, 'MAD', 'DET', '20:00', '21:15'),
('TWA', 207, 'DET', 'MAD', '22:00', '23:15'),
('TWA', 210, 'MAD', 'MDW', '06:10', '07:15'),
('TWA', 211, 'MDW', 'MAD', '08:10', '09:15'),
('TWA', 212, 'MAD', 'MDW', '06:10', '07:15'),
('TWA', 213, 'MDW', 'MAD', '08:10', '09:15'),
('TWA', 214, 'MAD', 'MDW', '10:10', '11:15'),
('TWA', 215, 'MDW', 'MAD', '12:10', '13:15'),
('TWA', 216, 'MAD', 'MDW', '20:10', '21:15'),
('TWA', 217, 'MDW', 'MAD', '22:10', '23:15'),
('TWA', 300, 'MAD', 'SPI', '10:30', '11:40'),
('TWA', 301, 'SPI', 'MAD', '14:30', '15:40'),
('TWA', 302, 'MAD', 'SPI', '16:30', '17:40'),
('TWA', 303, 'SPI', 'MAD', '19:30', '20:40'),
('PWI', 100, 'MKE', 'SAW', '07:30', '08:40'),
('PWI', 101, 'SAW', 'DTW', '09:10', '10:20'), 
('PWI', 102, 'DTW', 'MKE', '11:00', '12:05'),
('PWI', 103, 'MKE', 'SAW', '14:30', '15:40'),
('PWI', 104, 'SAW', 'DTW', '16:10', '17:20'), 
('PWI', 105, 'DTW', 'MKE', '18:00', '19:05'),
('PWI', 200, 'MKE', 'ORD', '09:00', '10:00'),
('PWI', 201, 'ORD', 'MKE', '11:10', '12:20'),
('PWI', 202, 'MKE', 'ORD', '14:00', '15:00'),
('PWI', 203, 'ORD', 'MKE', '16:10', '17:20'), 
('PWI', 204, 'MKE', 'ORD', '19:00', '20:00'),
('PWI', 205, 'ORD', 'MKE', '21:10', '22:20'),
('PWI', 300, 'MKE', 'ATW', '07:30', '07:55'),
('PWI', 301, 'ATW', 'EAU', '09:12', '09:55'),
('PWI', 302, 'EAU', 'RHI', '10:30', '11:05'), 
('PWI', 303, 'RHI', 'MKE', '11:40', '12:20'),
('PWI', 304, 'MKE', 'ATW', '13:30', '13:55'),
('PWI', 305, 'ATW', 'EAU', '14:12', '14:55'),
('PWI', 306, 'EAU', 'RHI', '15:30', '16:05'), 
('PWI', 307, 'RHI', 'MKE', '16:40', '17:20'),
('PWI', 400, 'RHI', 'MAD', '08:00', '09:10'),
('PWI', 401, 'MAD', 'RHI', '10:00', '11:10'),
('PWI', 402, 'RHI', 'MAD', '12:00', '13:10'),
('PWI', 403, 'MAD', 'RHI', '14:00', '15:10'),
('PWI', 404, 'RHI', 'MAD', '20:00', '21:10'),
('PWI', 405, 'MAD', 'RHI', '22:00', '23:10'),
('PWI', 500, 'RHI', 'MAD', '08:00', '09:10'),
('PWI', 501, 'MKE', 'RHI', '10:00', '11:10'),
('PWI', 502, 'RHI', 'MKE', '12:00', '13:10'),
('PWI', 503, 'MKE', 'RHI', '14:00', '15:10'),
('PWI', 504, 'RHI', 'MKE', '20:00', '21:10'),
('PWI', 505, 'MKE', 'RHI', '22:00', '23:10'),
('PWI', 600, 'ORD', 'EAU', '10:00', '11:10'),
('PWI', 601, 'EAU', 'ORD', '12:00', '13:10'),
('PWI', 700, 'DTW', 'MKE', '06:45', '07:55'),
('PWI', 701, 'MKE', 'DTW', '08:23', '09:33'),
('PWI', 702, 'DTW', 'MKE', '10:45', '11:55'),
('PWI', 703, 'MKE', 'DTW', '12:23', '13:33'),
('PWI', 704, 'DTW', 'MKE', '15:45', '16:55'),
('PWI', 705, 'MKE', 'DTW', '17:23', '18:33'),
('PWI', 706, 'DTW', 'MKE', '19:45', '20:55'),
('PWI', 707, 'MKE', 'DTW', '21:23', '22:33'),
('PWI', 800, 'MKE', 'LSE', '12:10', '13:05'),
('PWI', 801, 'LSE', 'MKE', '14:10', '15:00'),
('PWI', 820, 'MKE', 'CWA', '08:30', '09:45'),
('PWI', 821, 'CWA', 'MKE', '10:50', '12:05'),
('PWI', 822, 'MKE', 'CWA', '19:10', '20:05'),
('PWI', 823, 'CWA', 'MKE', '21:10', '22:20'),
('ELL', 100, 'ORD', 'MDW', '08:00', '08:20'),
('ELL', 101, 'MDW', 'ORD', '08:30', '08:50'),
('ELL', 102, 'ORD', 'MDW', '09:00', '09:20'),
('ELL', 103, 'MDW', 'ORD', '09:30', '09:50'),
('ELL', 104, 'ORD', 'MDW', '10:00', '10:20'),
('ELL', 105, 'MDW', 'ORD', '10:30', '10:50'),
('ELL', 106, 'ORD', 'MDW', '11:00', '11:20'),
('ELL', 107, 'MDW', 'ORD', '11:30', '11:50'),
('ELL', 108, 'ORD', 'MDW', '12:00', '12:20'),
('ELL', 109, 'MDW', 'ORD', '12:30', '12:50'),
('ELL', 110, 'ORD', 'MDW', '13:00', '13:20'),
('ELL', 111, 'MDW', 'ORD', '13:30', '13:50'),
('ELL', 112, 'ORD', 'MDW', '14:00', '14:20'),
('ELL', 113, 'MDW', 'ORD', '14:30', '14:50'),
('ELL', 114, 'ORD', 'MDW', '15:00', '15:20'),
('ELL', 115, 'MDW', 'ORD', '15:30', '15:50'),
('ELL', 116, 'ORD', 'MDW', '16:00', '16:20'),
('ELL', 117, 'MDW', 'ORD', '16:30', '16:50'),
('ELL', 118, 'ORD', 'MDW', '17:00', '17:20'),
('ELL', 119, 'MDW', 'ORD', '17:30', '17:50'),
('ELL', 120, 'ORD', 'MDW', '18:00', '18:20'),
('ELL', 121, 'MDW', 'ORD', '18:30', '18:50'),
('ELL', 122, 'ORD', 'MDW', '19:00', '19:20'),
('ELL', 123, 'MDW', 'ORD', '19:30', '19:50'),
('ELL', 124, 'ORD', 'MDW', '20:00', '20:20'),
('ELL', 125, 'MDW', 'ORD', '20:30', '20:50'),
('ELL', 126, 'ORD', 'MDW', '21:00', '21:20'),
('ELL', 127, 'MDW', 'ORD', '21:30', '21:50'),
('ELL', 128, 'ORD', 'MDW', '22:00', '22:20'),
('ELL', 129, 'MDW', 'ORD', '22:30', '22:50'),
('ELL', 130, 'ORD', 'MDW', '23:00', '23:20'),
('ELL', 131, 'MDW', 'ORD', '23:30', '23:50'),
('MAL', 100, 'DTW', 'MKE', '08:13', '09:17'),
('MAL', 101, 'MKE', 'DTW', '10:13', '11:17'),
('MAL', 102, 'DTW', 'MKE', '12:13', '13:17'),
('MAL', 103, 'MKE', 'DTW', '14:13', '15:17'),
('MAL', 104, 'DTW', 'MKE', '16:13', '17:17'),
('MAL', 105, 'MKE', 'DTW', '18:13', '19:17'),
('MAL', 106, 'DTW', 'MKE', '20:13', '21:17'),
('MAL', 107, 'MKE', 'DTW', '22:13', '23:17'),
('MAL', 200, 'DTW', 'MDW', '06:22', '07:32'),
('MAL', 201, 'MDW', 'DTW', '07:59', '09:07'),
('MAL', 202, 'DTW', 'MDW', '11:22', '12:32'),
('MAL', 203, 'MDW', 'DTW', '14:59', '16:07'),
('MAL', 204, 'DTW', 'MDW', '19:22', '20:32'),
('MAL', 205, 'MDW', 'DTW', '21:07', '22:34'),
('MAL', 340, 'DTW', 'SAW', '10:12', '11:02'),
('MAL', 341, 'SAW', 'EAU', '13:10', '13:55'),
('MAL', 342, 'EAU', 'DTW', '15:50', '17:10'),
('MAL', 410, 'DTW', 'MKG', '09:30', '10:12'),
('MAL', 411, 'MKG', 'MAD', '11:45', '12:35'),
('MAL', 412, 'MAD', 'DTW', '13:40', '14:50'),
('MAL', 413, 'DTW', 'MKG', '16:30', '17:12'),
('MAL', 414, 'MKG', 'MAD', '18:45', '20:35'),
('MAL', 415, 'MAD', 'DTW', '21:40', '22:50'),
('MAL', 460, 'DTW', 'GRR', '09:30', '10:15'),
('MAL', 461, 'GRR', 'DTW', '11:30', '12:15'),
('MAL', 462, 'DTW', 'GRR', '16:30', '17:15'),
('MAL', 463, 'GRR', 'DTW', '19:30', '20:15'),
('MAL', 520, 'DTW', 'MKG', '08:19', '09:03'),
('MAL', 521, 'MKG', 'SAW', '09:45', '10:43'),
('MAL', 522, 'SAW', 'GRR', '12:45', '13:24'),
('MAL', 523, 'GRR', 'DTW', '14:45', '15:07'),
('RCD', 220, 'RFD', 'ORD', '06:45', '07:30'),
('RCD', 221, 'ORD', 'RFD', '08:05', '08:50'),
('RCD', 222, 'RFD', 'ORD', '06:45', '07:30'),
('RCD', 223, 'ORD', 'RFD', '08:05', '08:50'),
('RCD', 224, 'RFD', 'ORD', '09:45', '10:30'),
('RCD', 225, 'ORD', 'RFD', '11:05', '11:50'),
('RCD', 226, 'RFD', 'ORD', '12:45', '13:30'),
('RCD', 227, 'ORD', 'RFD', '14:05', '14:50'),
('RCD', 228, 'RFD', 'ORD', '19:45', '20:30'),
('RCD', 229, 'ORD', 'RFD', '21:05', '21:50'),
('RCD', 240, 'SPI', 'ORD', '07:05', '07:55'),
('RCD', 241, 'ORD', 'SPI', '08:35', '09:25'),
('RCD', 242, 'SPI', 'ORD', '11:05', '11:55'),
('RCD', 243, 'ORD', 'SPI', '12:35', '12:25'),
('RCD', 244, 'SPI', 'ORD', '18:05', '18:55'),
('RCD', 245, 'ORD', 'SPI', '20:35', '21:15'),
('RCD', 310, 'MKE', 'ORD', '07:05', '08:10'),
('RCD', 311, 'ORD', 'MKE', '09:15', '10:25'),
('RCD', 312, 'MKE', 'ORD', '17:05', '18:10'),
('RCD', 313, 'ORD', 'MKE', '19:15', '20:25'),
('RCD', 400, 'SPI', 'MAD', '11:05', '12:10'),
('RCD', 401, 'MAD', 'DTW', '13:05', '14:10'),
('RCD', 402, 'DTW', 'SPI', '15:45', '17:10'),
('RCD', 500, 'RFD', 'MAD', '16:05', '17:10'),
('RCD', 501, 'MAD', 'DTW', '18:05', '19:10'),
('RCD', 502, 'DTW', 'RFD', '20:45', '22:10'),
('RCD', 620, 'ORD', 'CWA', '09:45', '10:55'), 
('RCD', 621, 'CWA', 'ORD', '11:40', '12:50'),
('RCD', 622, 'ORD', 'EAU', '13:45', '15:55'), 
('RCD', 623, 'EAU', 'ORD', '16:40', '17:50'),
('RCD', 624, 'ORD', 'GRB', '19:45', '20:55'), 
('RCD', 625, 'EAU', 'GRB', '21:40', '22:50');





















