flights_data_analytics/load_and_modify_data.sql at main · chanulee1/flights_data_analytics · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
DESCRIBE flights_2004_to_2008;

CREATE TABLE flights_test_2008 LIKE flights_2004_to_2008;
DESCRIBE flights_test_2008;

ALTER TABLE flights_test_2008
MODIFY WeatherDelay INT,
MODIFY NASDelay INT,
MODIFY SecurityDelay INT,
MODIFY LateAircraftDelay INT;

ALTER TABLE flights_test_2008
DROP COLUMN NewDepTime;

LOAD DATA LOCAL INFILE 'C:\\Users\\clee1\\learning_tech\\sql_practice\\airline_ontime_data\\2008.csv'
INTO TABLE flights_test_2008
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile=1;

SELECT * FROM flights_test_2007
WHERE CarrierDelay = 'NA';

SELECT DepTime, SEC_TO_TIME((DepTime DIV 100) * 3600 + (DepTime MOD 100) * 60) FROM flights_2004_to_2008;
SELECT DepTime, TIME_FORMAT(STR_TO_DATE(LPAD(DepTime, 4, '0'), '%H%i'), '%H:%i:%s') FROM flights_2004_to_2008;
SELECT DepTime, STR_TO_DATE(LPAD(DepTime, 4, '0'), '%H%i') FROM flights_2004_to_2008;
SELECT DepTime, (DepTime DIV 100) , (DepTime MOD 100) FROM flights_2004_to_2008;

SELECT SEC_TO_TIME((1342 DIV 100) * 3600 + (1342 MOD 100) * 60);

ALTER TABLE flights_2004_to_2008
ADD COLUMN NewDepTime TIME;
SELECT * FROM flights_2004_to_2008;

UPDATE flights_2004_to_2008
SET NewDepTime = (CASE WHEN DepTime = 'NA' THEN NULL ELSE STR_TO_DATE(LPAD(DepTime, 4, '0'), '%H%i') END);

SELECT DepTime,
CASE
	WHEN DepTime = 'NA' THEN NULL
	ELSE STR_TO_DATE(LPAD(DepTime, 4, '0'), '%H%i')
END AS NewDepTime
FROM flights_2004_to_2008;

UPDATE flights_2004_to_2008
SET NewDepTime = NULL;

SELECT * FROM flights_test_2007
WHERE CarrierDelay = 'NA';

SELECT * FROM flights_test_2008;
ALTER TABLE flights_test_2008
ADD COLUMN `Delayed` BOOL;

DESCRIBE flights_test_2008;

SELECT * FROM flights_test_2008
WHERE CarrierDelay + WeatherDelay + NASDelay + SecurityDelay + LateAircraftDelay = 0;

SELECT ArrDelay, CarrierDelay + WeatherDelay + NASDelay + SecurityDelay + LateAircraftDelay
FROM flights_test_2008;

ALTER TABLE flights_test_2008
RENAME TO flights_test_2004_to_2008;

ALTER TABLE flights_test_2004_to_2008
DROP COLUMN `Delayed`;

LOAD DATA LOCAL INFILE 'C:\\Users\\clee1\\learning_tech\\sql_practice\\airline_ontime_data\\2004.csv'
INTO TABLE flights_test_2004_to_2008
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

SELECT `Year`, COUNT(*) FROM flights_test_2004_to_2008
GROUP BY `Year`;

SELECT * FROM airports;

SELECT * FROM carriers;

DROP TABLE flights_2004_to_2008;

UPDATE carriers
SET `description` = 'US Airways Inc.'
WHERE `code` = 'US';

SELECT *
FROM carriers
WHERE `code` = 'US';