-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql.js
242 lines (207 loc) · 6.46 KB
/
sql.js
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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
var sqlite3 = require('sqlite3');
var db = new sqlite3.Database('./gold_medals.sqlite');
/*
Returns a SQL query string that will create the Country table with four columns: name (required), code (required), gdp, and population.
*/
const createCountryTable = () => {
return `CREATE TABLE Country (
name TEXT NOT NULL,
code TEXT NOT NULL,
gdp INTEGER,
population INTEGER
);`;
};
/*
Returns a SQL query string that will create the GoldMedal table with ten columns (all required): id, year, city, season, name, country, gender, sport, discipline, and event.
*/
const createGoldMedalTable = () => {
return `CREATE TABLE GoldMedal (
id INTEGER PRIMARY KEY,
year INTEGER NOT NULL,
city TEXT NOT NULL,
season TEXT NOT NULL,
name TEXT NOT NULL,
country TEXT NOT NULL,
gender TEXT NOT NULL,
sport TEXT NOT NULL,
discipline TEXT NOT NULL,
event TEXT NOT NULL
);`;
};
/*
Returns a SQL query string that will find the number of gold medals for the given country.
*/
const goldMedalNumber = country => {
return `SELECT
COUNT(*) as count
FROM
GoldMedal
WHERE country = "${country}";`;
};
/*
Returns a SQL query string that will find the year where the given country
won the most medals in a spceified season, along with the number of medals aliased to 'count'.
*/
const mostSeason = (country, season) => {
return `SELECT
year,
COUNT(*) as count
FROM
GoldMedal
WHERE country = "${country}"
AND season = "${season}"
GROUP BY year
ORDER BY count DESC
LIMIT 1;`;
}
/*
Returns a SQL query string that will find the year where the given country
won the most summer medals, along with the number of medals aliased to 'count'.
*/
const mostSummerWins = country => {
return mostSeason(country, 'Summer');
};
/*
Returns a SQL query string that will find the year where the given country
won the most winter medals, along with the number of medals aliased to 'count'.
*/
const mostWinterWins = country => {
return mostSeason(country, 'Winter');
};
/*
Returns a SQL query string that will find the given category in which the given country
won the most medals, along with the number of medals aliased to 'count'.
*/
const bestCategory = (country, category) => {
return `SELECT
${category},
COUNT(*) as count
FROM
GoldMedal
WHERE country = "${country}"
GROUP BY ${category}
ORDER BY count DESC
LIMIT 1;`;
}
/*
Returns a SQL query string that will find the year where the given country
won the most medals, along with the number of medals aliased to 'count'.
*/
const bestYear = country => {
return bestCategory(country, 'year');
};
/*
Returns a SQL query string that will find the discipline this country has
won the most medals, along with the number of medals aliased to 'count'.
*/
const bestDiscipline = country => {
return bestCategory(country, 'discipline');
};
/*
Returns a SQL query string that will find the sport this country has
won the most medals, along with the number of medals aliased to 'count'.
*/
const bestSport = country => {
return bestCategory(country, 'sport');
};
/*
Returns a SQL query string that will find the event this country has
won the most medals, along with the number of medals aliased to 'count'.
*/
const bestEvent = country => {
return bestCategory(country, 'event');
};
/*
Returns a SQL query string that will find the number of medalists of a specified gender.
*/
const genderedMedalists = (country, gender) => {
return `SELECT
COUNT(distinct name) as count
FROM
GoldMedal
WHERE country = "${country}"
AND gender = "${gender}"
ORDER BY count DESC
LIMIT 1;`;
}
/*
Returns a SQL query string that will find the number of male medalists.
*/
const numberMenMedalists = country => {
return genderedMedalists(country, "Men");
};
/*
Returns a SQL query string that will find the number of female medalists.
*/
const numberWomenMedalists = country => {
return genderedMedalists(country, "Women");
};
/*
Returns a SQL query string that will find the athlete with the most medals.
*/
const mostMedaledAthlete = country => {
return `SELECT
name,
COUNT(*) as count
FROM
GoldMedal
WHERE country = "${country}"
GROUP BY name
ORDER BY count DESC
LIMIT 1;`;
};
/*
Returns a SQL query string that will find the medals a country has won
optionally ordered by the given field in the specified direction.
*/
const orderedMedals = (country, field, sortAscending) => {
const suffix = typeof field !== 'undefined' ? `
ORDER BY ${field} ${sortAscending ? 'ASC' : 'DESC'};` : `;`;
return `SELECT
*
FROM
GoldMedal
WHERE country = "${country}"` + suffix;
};
/*
Returns a SQL query string that will find the sports a country has
won medals in. It should include the number of medals, aliased as 'count',
as well as the percentage of this country's wins the sport represents,
aliased as 'percent'. Optionally ordered by the given field in the specified direction.
*/
const orderedSports = (country, field, sortAscending) => {
const suffix = typeof field !== 'undefined' ? `
ORDER BY ${field} ${sortAscending ? 'ASC' : 'DESC'};` : `;`;
console.log(`SELECT
sport,
COUNT(*) as count,
CAST(COUNT(*) AS FLOAT) / CAST((SELECT COUNT(*) FROM GoldMedal WHERE country = "${country}") AS FLOAT) as percent
FROM
GoldMedal
WHERE country = "${country}"
GROUP BY sport` + suffix);
return `SELECT
sport,
COUNT(*) as count,
CAST(COUNT(*) AS FLOAT) / CAST((SELECT COUNT(*) FROM GoldMedal WHERE country = "${country}") AS FLOAT) as percent
FROM
GoldMedal
WHERE country = "${country}"
GROUP BY sport` + suffix;
};
module.exports = {
createCountryTable,
createGoldMedalTable,
goldMedalNumber,
mostSummerWins,
mostWinterWins,
bestDiscipline,
bestSport,
bestYear,
bestEvent,
numberMenMedalists,
numberWomenMedalists,
mostMedaledAthlete,
orderedMedals,
orderedSports
};