You should submit your SQL Script in the assignment submission ( a file with extension *.sql - no other file types will be accepted. Please do not submit word documents, or post your SQL in the assignment text area. The submission must be a SQL file with your code on it ).

You can add comments in between your SQL code using " -- ". For example:

-- My script below is going to select some data from two tables

SELECT * FROM Planes;

SELECT * FROM Airports;

Please state your response to each assignment question with a comment, followed by your SQL script. Notice semicolons at the end of each SQL statement must be used. I should be able to run and re-run your script multiple times without errors. Points will be removed if the script does not have:

1) A comment with an answer for each question, followed by the SQL script

2) semicolons for each SQL statement, or syntax errors

3) breaks when running twice or more

Here is files_---------------------

-- loadflights.sql

DROP TABLE IF EXISTS airlines; DROP TABLE IF EXISTS airports; DROP TABLE IF EXISTS flights; DROP TABLE IF EXISTS planes; DROP TABLE IF EXISTS weather;

CREATE TABLE airlines ( carrier varchar(2) PRIMARY KEY, name varchar(30) NOT NULL ); LOAD DATA LOCAL INFILE '/home/javier.guillen/Downloads/flights/airlines.csv' INTO TABLE airlines FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 ROWS;

CREATE TABLE airports ( faa char(3), name varchar(100), lat double precision, lon double precision, alt integer, tz integer, dst char(1) ); LOAD DATA LOCAL INFILE '/home/javier.guillen/Downloads/flights/airports.csv' INTO TABLE airports FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 ROWS;

CREATE TABLE flights ( year integer, month integer, day integer, dep_time integer, dep_delay integer, arr_time integer, arr_delay integer, carrier char(2), tailnum char(6), flight integer, origin char(3), dest char(3), air_time integer, distance integer, hour integer, minute integer );

LOAD DATA LOCAL INFILE '/home/javier.guillen/Downloads/flights/flights.csv' INTO TABLE flights FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 ROWS (year, month, day, @dep_time, @dep_delay, @arr_time, @arr_delay, @carrier, @tailnum, @flight, origin, dest, @air_time, @distance, @hour, @minute) SET dep_time = nullif(@dep_time,''), dep_delay = nullif(@dep_delay,''), arr_time = nullif(@arr_time,''), arr_delay = nullif(@arr_delay,''), carrier = nullif(@carrier,''), tailnum = nullif(@tailnum,''), flight = nullif(@flight,''), air_time = nullif(@air_time,''), distance = nullif(@distance,''), hour = dep_time / 100, minute = dep_time % 100 ;

CREATE TABLE planes ( tailnum char(6), year integer, type varchar(50), manufacturer varchar(50), model varchar(50), engines integer, seats integer, speed integer, engine varchar(50) );

LOAD DATA LOCAL INFILE '/home/javier.guillen/Downloads/flights/planes.csv' INTO TABLE planes FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 ROWS (tailnum, @year, type, manufacturer, model, engines, seats, @speed, engine) SET year = nullif(@year,''), speed = nullif(@speed,'') ;

CREATE TABLE weather ( origin char(3), year integer, month integer, day integer, hour integer, temp double precision, dewp double precision, humid double precision, wind_dir integer, wind_speed double precision, wind_gust double precision, precip double precision, pressure double precision, visib double precision );

LOAD DATA LOCAL INFILE '/home/javier.guillen/Downloads/flights/weather.csv' INTO TABLE weather FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 ROWS (origin, @year, @month, @day, @hour, @temp, @dewp, @humid, @wind_dir, @wind_speed, @wind_gust, @precip, @pressure, @visib) SET year = nullif(@year,''), month = nullif(@month,''), day = nullif(@day,''), hour = nullif(@hour,''), temp = nullif(@temp,''), dewp = nullif(@dewp,''), humid = nullif(@humid,''), wind_dir = FORMAT(@wind_dir, 0), wind_speed = nullif(@wind_speed,''), wind_gust = nullif(@wind_gust,''), precip = nullif(@precip,''), pressure = nullif(@pressure,''), visib = FORMAT(@visib,0) ;

SET SQL_SAFE_UPDATES = 0; UPDATE planes SET engine = SUBSTRING(engine, 1, CHAR_LENGTH(engine)-1);

SELECT 'airlines', COUNT(*) FROM airlines UNION SELECT 'airports', COUNT(*) FROM airports UNION SELECT 'flights', COUNT(*) FROM flights UNION SELECT 'planes', COUNT(*) FROM planes UNION SELECT 'weather', COUNT(*) FROM weather;