-
Notifications
You must be signed in to change notification settings - Fork 0
/
setup_database.sql
222 lines (214 loc) · 7.47 KB
/
setup_database.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
214
215
216
217
218
219
220
221
222
/*
Creating CovidDeaths and CovidVaccinations tables
If you choose to use the publicly available dataset, make sure to adjust any discrepancies in table titles and data types.
*/
CREATE TABLE CovidDeaths (
iso_code VARCHAR(100),
continent VARCHAR(100),
location VARCHAR(100),
date DATE,
population BIGINT,
total_cases INT,
new_cases_smoothed INT,
total_deaths INT,
new_deaths INT,
new_deaths_smoothed FLOAT,
total_cases_per_million FLOAT,
new_cases_per_million FLOAT,
new_cases_smoothed_per_million FLOAT,
total_deaths_per_million FLOAT,
new_deaths_per_million FLOAT,
new_deaths_smoothed_per_million FLOAT,
reproduction_rate FLOAT,
icu_patients INT,
icu_patients_per_million FLOAT,
hosp_patients BIGINT,
hosp_patients_per_million FLOAT,
weekly_icu_admissions FLOAT,
weekly_icu_admissions_per_million FLOAT,
weekly_hosp_admissions FLOAT,
weekly_hosp_admissions_per_million FLOAT
);
CREATE TABLE CovidVaccinations (
iso_code VARCHAR(100),
continent VARCHAR(100),
location VARCHAR(100),
date DATE,
new_tests BIGINT,
total_tests BIGINT,
total_tests_per_thousand FLOAT,
new_tests_per_thousand FLOAT,
new_tests_smoothed BIGINT,
new_tests_smoothed_per_thousand FLOAT,
positive_rate FLOAT,
tests_per_case FLOAT,
tests_units VARCHAR(100),
total_vaccinations BIGINT,
people_vaccinated BIGINT,
people_fully_vaccinated BIGINT,
new_vaccinations BIGINT,
new_vaccinations_smoothed BIGINT,
total_vaccinations_per_hundred FLOAT,
people_vaccinated_per_hundred FLOAT,
people_fully_vaccinated_per_hundred FLOAT,
new_vaccinations_smoothed_per_million BIGINT,
stringency_index FLOAT,
population_density FLOAT,
median_age FLOAT,
aged_65_older FLOAT,
aged_70_older FLOAT,
gdp_per_capita FLOAT,
extreme_poverty FLOAT,
cardiovasc_death_rate FLOAT,
diabetes_prevalence FLOAT,
female_smokers FLOAT,
male_smokers FLOAT,
handwashing_facilities FLOAT,
hospital_beds_per_thousand FLOAT,
life_expectancy FLOAT,
human_development_index FLOAT
);
/*
Data import for CovidDeaths and CovidVaccinations tables
Note, the NULL handling for empty strings.
*/
-- Data Import for CovidDeaths
LOAD DATA LOCAL INFILE '/path/to/your/CovidDeaths.csv'
INTO TABLE CovidDeaths
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(
iso_code,
continent,
location,
date,
population,
total_casesnew_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, ''),
location = NULLIF(location, ''),
population = NULLIF(population, ''),
total_casesnew_cases = NULLIF(total_casesnew_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, '');
-- Data Import for CovidVaccinations
LOAD DATA LOCAL INFILE '/path/to/your/CovidVaccinations.csv'
INTO TABLE CovidVaccinations
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
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, ''),
location = NULLIF(location, ''),
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, ''),
hospital_beds_per_thousand = NULLIF(hospital_beds_per_thousand, ''),
life_expectancy = NULLIF(life_expectancy, ''),
human_development_index = NULLIF(human_development_index, '');