📊 Day 19 - Data Download

Airline Flight Delays & Aircraft Utilization

Download all 3 tables with complete messy data (25+ rows per table)

Data Cleaning TIMESTAMPDIFF Multi-Table JOINs Performance Metrics CTEs

Table 1: flights

flight_id aircraft_id route_id flight_date scheduled_departure actual_departure delay_minutes status
FL001 A101R0012024-01-1508:00:0008:00:000ON TIME
fl002A102 r002 2024-01-1509:15:0009:30:0015delayed
FL003A101R0032024-01-1614:00:0014:45:0045DELAYED
fl004 A103 R0012024-01-1607:30:0007:30:000On Time
FL005A102r0042024-01-1712:00:00NULLNULLCANCELLED
fl006A104R0022024-01-1710:30:0010:50:0020DELAYED
FL007 A101 r0012024-01-1815:00:0015:35:0035delayed
fl008A105R0052024-01-1811:45:0011:55:0010DELAYED
FL009A103R0032024-01-1913:20:0013:20:000ON TIME
fl010A102 r004 2024-01-1916:00:0016:25:0025Delayed
FL011A101R0022024-01-2009:00:0009:50:0050DELAYED
fl012A104r0012024-01-2014:30:0014:30:000On Time
FL013 A105 R0052024-01-2112:15:0012:20:005delayed
fl014A101R0032024-01-2117:00:0017:40:0040DELAYED
FL015A103 r002 2024-01-2208:30:0008:30:000ON TIME
fl016A102R0042024-01-2211:00:0011:20:0020Delayed
FL017A104r0012024-01-2315:45:0016:00:0015delayed
fl018 A101 R0052024-01-2310:00:0010:45:0045DELAYED
FL019A105R0022024-01-2413:30:0013:40:0010DELAYED
fl020A103 r003 2024-01-2416:15:0016:15:000On Time
FL021A101R0012024-01-2509:30:0010:00:0030delayed
fl022A102r0042024-01-2514:00:0014:25:0025DELAYED
FL023 A104 R0022024-01-2611:30:0011:30:000ON TIME
fl024A105R0052024-01-2615:00:0015:18:0018Delayed
FL025A101 r003 2024-01-2712:45:0013:30:0045DELAYED

Table 2: aircraft

aircraft_id model manufacturer year_manufactured seat_capacity last_maintenance
A101737-800Boeing20181892024-01-10
A102A320Airbus2019180NULL
A103737-900Boeing20202202024-01-05
A104A321Airbus20172202023-12-28
A105737-800Boeing20211892024-01-12

Table 3: routes

route_id origin destination distance_km flight_duration_min
R001MumbaiDelhi1400150
R002DelhiBangalore1740180
R003MumbaiBangalore980140
R004DelhiKolkata1350165
R005ChennaiHyderabad62595

📌 How to Import into MySQL/PostgreSQL

MySQL:

1. Create database: CREATE DATABASE airline_db;

2. Import CSV: LOAD DATA INFILE 'path/flights.csv' INTO TABLE flights FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;


PostgreSQL:

1. Create database: CREATE DATABASE airline_db;

2. Import CSV: \COPY flights FROM 'path/flights.csv' WITH (FORMAT csv, HEADER true);


Note: Create tables first with appropriate column types before importing!

← Back to Challenge