-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdata_import.sql
214 lines (205 loc) · 7.11 KB
/
data_import.sql
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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
USE PortfolioProject;
CREATE TABLE covid_deaths(
iso_code TEXT,
continent TEXT,
location TEXT,
date DATE,
population BIGINT,
total_cases INT,
new_cases INT,
new_cases_smoothed DOUBLE,
total_deaths INT,
new_deaths INT,
new_deaths_smoothed DOUBLE,
total_cases_per_million DOUBLE,
new_cases_per_million DOUBLE,
new_cases_smoothed_per_million DOUBLE,
total_deaths_per_million DOUBLE,
new_deaths_per_million DOUBLE,
new_deaths_smoothed_per_million DOUBLE,
reproduction_rate DOUBLE,
icu_patients INT,
icu_patients_per_million DOUBLE,
hosp_patients INT,
hosp_patients_per_million DOUBLE,
weekly_icu_admissions INT,
weekly_icu_admissions_per_million DOUBLE,
weekly_hosp_admissions INT,
weekly_hosp_admissions_per_million DOUBLE
);
LOAD DATA LOCAL INFILE '/Users/poetntowncrier/Documents/sql_project/covid_deaths.csv'
INTO TABLE covid_deaths
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'
IGNORE 1 ROWS
(
iso_code,
@continent,
location,
date,
@population,
@total_cases,
@new_cases,
@new_cases_smoothed,
@total_deaths,
@new_deaths,
@new_deaths_smoothed,
@total_cases_per_million,
@new_cases_per_million,
@new_cases_smoothed_per_million,
@total_deaths_per_million,
@new_deaths_per_million,
@new_deaths_smoothed_per_million,
@reproduction_rate,
@icu_patients,
@icu_patients_per_million,
@hosp_patients,
@hosp_patients_per_million,
@weekly_icu_admissions,
@weekly_icu_admissions_per_million,
@weekly_hosp_admissions,
@weekly_hosp_admissions_per_million
)
SET
continent = NULLIF(@continent, ''),
population = NULLIF(@population, ''),
total_cases = NULLIF(@total_cases, ''),
new_cases = NULLIF(@new_cases, ''),
new_cases_smoothed = NULLIF(@new_cases_smoothed, ''),
total_deaths = NULLIF(@total_deaths, ''),
new_deaths = NULLIF(@new_deaths, ''),
new_deaths_smoothed = NULLIF(@new_deaths_smoothed, ''),
total_cases_per_million = NULLIF(@total_cases_per_million, ''),
new_cases_per_million = NULLIF(@new_cases_per_million, ''),
new_cases_smoothed_per_million = NULLIF(@new_cases_smoothed_per_million, ''),
total_deaths_per_million = NULLIF(@total_deaths_per_million, ''),
new_deaths_per_million = NULLIF(@new_deaths_per_million, ''),
new_deaths_smoothed_per_million = NULLIF(@new_deaths_smoothed_per_million, ''),
reproduction_rate = NULLIF(@reproduction_rate, ''),
icu_patients = NULLIF(@icu_patients, ''),
icu_patients_per_million = NULLIF(@icu_patients_per_million, ''),
hosp_patients = NULLIF(@hosp_patients, ''),
hosp_patients_per_million = NULLIF(@hosp_patients_per_million, ''),
weekly_icu_admissions = NULLIF(@weekly_icu_admissions, ''),
weekly_icu_admissions_per_million = NULLIF(@weekly_icu_admissions_per_million, ''),
weekly_hosp_admissions = NULLIF(@weekly_hosp_admissions, ''),
weekly_hosp_admissions_per_million = NULLIF(@weekly_hosp_admissions_per_million, '')
;
CREATE TABLE covid_vaccinations(
iso_code TEXT,
continent TEXT,
location TEXT,
date DATE,
new_tests INT,
total_tests INT,
total_tests_per_thousand DOUBLE,
new_tests_per_thousand DOUBLE,
new_tests_smoothed DOUBLE,
new_tests_smoothed_per_thousand DOUBLE,
positive_rate DOUBLE,
tests_per_case DOUBLE,
tests_units TEXT,
total_vaccinations INT,
people_vaccinated INT,
people_fully_vaccinated INT,
new_vaccinations INT,
new_vaccinations_smoothed DOUBLE,
total_vaccinations_per_hundred DOUBLE,
people_vaccinated_per_hundred DOUBLE,
people_fully_vaccinated_per_hundred DOUBLE,
new_vaccinations_smoothed_per_million INT,
stringency_index DOUBLE,
population_density DOUBLE,
median_age DOUBLE,
aged_65_older DOUBLE,
aged_70_older DOUBLE,
gdp_per_capita DOUBLE,
extreme_poverty DOUBLE,
cardiovasc_death_rate DOUBLE,
diabetes_prevalence DOUBLE,
female_smokers DOUBLE,
male_smokers DOUBLE,
handwashing_facilities DOUBLE,
hospital_beds_per_thousand DOUBLE,
life_expectancy DOUBLE,
human_development_index DOUBLE
);
LOAD DATA LOCAL INFILE '/Users/poetntowncrier/Documents/sql_project/covid_vaccinations.csv'
INTO TABLE covid_vaccinations
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'
IGNORE 1 ROWS
(
iso_code,
@continent,
location,
date,
@new_tests,
@total_tests,
@total_tests_per_thousand,
@new_tests_per_thousand,
@new_tests_smoothed,
@new_tests_smoothed_per_thousand,
@positive_rate,
@tests_per_case,
@tests_units,
@total_vaccinations,
@people_vaccinated,
@people_fully_vaccinated,
@new_vaccinations,
@new_vaccinations_smoothed,
@total_vaccinations_per_hundred,
@people_vaccinated_per_hundred,
@people_fully_vaccinated_per_hundred,
@new_vaccinations_smoothed_per_million,
@stringency_index,
@population_density,
@median_age,
@aged_65_older,
@aged_70_older,
@gdp_per_capita,
@extreme_poverty,
@cardiovasc_death_rate,
@diabetes_prevalence,
@female_smokers,
@male_smokers,
@handwashing_facilities,
@hospital_beds_per_thousand,
@life_expectancy,
@human_development_index
)
SET
continent = NULLIF(@continent, ''),
new_tests = NULLIF(@new_tests, ''),
total_tests = NULLIF(@total_tests, ''),
total_tests_per_thousand = NULLIF(@total_tests_per_thousand, ''),
new_tests_per_thousand = NULLIF(@new_tests_per_thousand, ''),
new_tests_smoothed = NULLIF(@new_tests_smoothed, ''),
new_tests_smoothed_per_thousand = NULLIF(@new_tests_smoothed_per_thousand, ''),
positive_rate = NULLIF(@positive_rate, ''),
tests_per_case = NULLIF(@tests_per_case, ''),
tests_units = NULLIF(@tests_units, ''),
total_vaccinations = NULLIF(@total_vaccinations, ''),
people_vaccinated = NULLIF(@people_vaccinated, ''),
people_fully_vaccinated = NULLIF(@people_fully_vaccinated, ''),
new_vaccinations = NULLIF(@new_vaccinations, ''),
new_vaccinations_smoothed = NULLIF(@new_vaccinations_smoothed, ''),
total_vaccinations_per_hundred = NULLIF(@total_vaccinations_per_hundred, ''),
people_vaccinated_per_hundred = NULLIF(@people_vaccinated_per_hundred, ''),
people_fully_vaccinated_per_hundred = NULLIF(@people_fully_vaccinated_per_hundred, ''),
new_vaccinations_smoothed_per_million = NULLIF(@new_vaccinations_smoothed_per_million, ''),
stringency_index = NULLIF(@stringency_index, ''),
population_density = NULLIF(@population_density, ''),
median_age = NULLIF(@median_age, ''),
aged_65_older = NULLIF(@aged_65_older, ''),
aged_70_older = NULLIF(@aged_70_older, ''),
gdp_per_capita = NULLIF(@gdp_per_capita, ''),
extreme_poverty = NULLIF(@extreme_poverty, ''),
cardiovasc_death_rate = NULLIF(@cardiovasc_death_rate, ''),
diabetes_prevalence = NULLIF(@diabetes_prevalence, ''),
female_smokers = NULLIF(@female_smokers, ''),
male_smokers = NULLIF(@male_smokers, ''),
handwashing_facilities = NULLIF(@handwashing_facilities, ''),
life_expectancy = NULLIF(@life_expectancy, ''),
human_development_index = NULLIF(@human_development_index, '')
;