Y7Df6ugY

1-- START TRANSACTION;
2
3SET foreign_key_checks=0;
4
5DROP DATABASE IF EXISTS quiz;
6CREATE DATABASE quiz;
7USE quiz;
8
9-- TABLES.
10
11CREATE TABLE player (
12    id INTEGER PRIMARY KEY AUTO_INCREMENT,
13    nick_name VARCHAR(255) UNIQUE NOT NULL,
14    rounds_total INTEGER NOT NULL DEFAUlt 0,
15    high_score INTEGER NOT NULL DEFAULT 0
16) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17
18CREATE TABLE country (
19    id INTEGER PRIMARY KEY AUTO_INCREMENT,
20    c_name VARCHAR(255) UNIQUE NOT NULL,
21    capital_name VARCHAR(255) NOT NULL,
22    c_code CHAR(2) UNIQUE DEFAULT NULL
23) ENGINE=InnoDB DEFAULT CHARSET=utf8;
24
25-- PROCEDURES.
26
27DELIMITER //
28
29CREATE PROCEDURE set_player
30(IN _nick_name VARCHAR(255))
31BEGIN
32    INSERT INTO player(nick_name)
33    VALUES(_nick_name);
34END //
35
36CREATE PROCEDURE update_player
37(IN _id INTEGER, _rounds_total INTEGER, IN _high_score INTEGER)
38BEGIN
39    UPDATE player
40    SET rounds_total = _rounds_total, high_score = _high_score
41    WHERE id = _id;
42END //
43
44CREATE PROCEDURE get_player_by_id
45(IN _id INTEGER)
46BEGIN
47    SELECT *
48    FROM player
49    WHERE id = _id;
50END //
51
52CREATE PROCEDURE get_player_by_id
53(IN _id INTEGER)
54BEGIN
55    SELECT *
56    FROM player
57    WHERE id = _id;
58END //
59
60CREATE PROCEDURE get_player_by_nick_name
61(IN _nick_name VARCHAR(255))
62BEGIN
63    SELECT *
64    FROM player
65    WHERE nick_name = _nick_name;
66END //
67
68CREATE PROCEDURE get_players_by_high_score
69()
70BEGIN
71    SELECT *
72    FROM player
73    ORDER BY high_score DESC, rounds_total DESC;
74END //
75
76CREATE PROCEDURE get_players_by_high_score_named
77()
78BEGIN
79    SELECT nick_name AS Nickname, rounds_total as Rounds, high_score AS Highscore
80    FROM player
81    ORDER BY high_score DESC, rounds_total DESC;
82END //
83
84CREATE PROCEDURE get_random_countries
85(IN _n INTEGER)
86BEGIN
87    SELECT c_name, capital_name, c_code
88    FROM country
89    ORDER BY RAND()
90    LIMIT _n;
91END //
92
93DELIMITER ;
94
95-- INSERTIONS.
96
97-- INSERT INTO player(nick_name)
98-- VALUES
99-- ('user1'),
100-- ('user2'),
101-- ('user3'),
102-- ('user4');
103
104-- INSERT INTO player(nick_name, rounds_total, high_score)
105-- VALUES
106-- ('user5', 66, 1200);
107
108-- 233 countries
109INSERT INTO country(c_name, capital_name, c_code)
110VALUES
111('Afghanistan', 'Kabul', 'af'),
112('Albania', 'Tirana', 'al'),
113('Algeria', 'Algiers', 'dz'),
114('American Samoa', 'Pago Pago', 'as'),
115('Andorra', 'Andorra la Vella', 'ad'),
116('Angola', 'Luanda', 'ao'),
117('Anguilla', 'The Valley', 'ai'),
118('Antigua and Barbuda', 'St John''s', 'ag'),
119('Argentina', 'Buenos Aires', 'ar'),
120('Armenia', 'Yerevan', 'am'),
121('Aruba', 'Oranjestad', 'aw'),
122('Australia', 'Canberra', 'au'),
123('Austria', 'Vienna', 'at'),
124('Azerbaijan', 'Baku', 'az'),
125('The Bahamas', 'Nassau', 'bs'),
126('Bahrain', 'Manama', 'bh'),
127('Bangladesh', 'Dhaka', 'bd'),
128('Barbados', 'Bridgetown', 'bb'),
129('Belarus', 'Minsk', 'by'),
130('Belgium', 'Brussels', 'be'),
131('Belize', 'Belmopan', 'bz'),
132('Benin', 'Porto Novo', 'bj'),
133('Bermuda', 'Hamilton', 'bm'),
134('Bhutan', 'Thimphu', 'bt'),
135('Bolivia', 'Sucre', 'bo'),
136('Bosnia and Herzegovina', 'Sarajevo', 'ba'),
137('Botswana', 'Gaborone', 'bw'),
138('Bouvet Island', '-', 'bv'),
139('Brazil', 'Brasilia', 'br'),
140('British Virgin Islands', 'Road Town', 'vg'),
141('Brunei', 'Bandar Seri Begawan', 'bn'),
142('Bulgaria', 'Sofia', 'bg'),
143('Burkina Faso', 'Ouagadougou', 'bf'),
144('Burundi', 'Bujumbura', 'bi'),
145('Cambodia', 'Phnom Penh', 'kh'),
146('Cameroon', 'Yaound', 'cm'),
147('Canada', 'Ottawa', 'ca'),
148('Cape Verde', 'Praia', 'cv'),
149('Cayman Islands', 'George Town', 'ky'),
150('Central African Republic', 'Bangui', 'cf'),
151('Chad', 'N''Djamena', 'td'),
152('Chile', 'Santiago', 'cl'),
153('China', 'Beijing', 'cn'),
154('Hong Kong', 'Victoria City', 'hk'),
155('Macau', 'Macau', 'mo'),
156('Christmas Island', 'Flying Fish Cove', 'cx'),
157('Cocos (Keeling) Islands', 'Bantam', 'cc'),
158('Colombia', 'Santa Fe de Bogot', 'co'),
159('Comoros', 'Moroni', 'km'),
160('Congo', 'Brazzaville', 'cg'),
161('Democratic Republic of the Congo', 'Kinshasa', 'cd'),
162('Cook Islands', 'Avarua', 'ck'),
163('Costa Rica', 'San Jos', 'cr'),
164('Cote D''Ivoire', 'Yamoussoukro', 'ci'),
165('Croatia', 'Zagreb', 'hr'),
166('Cuba', 'Havana', 'cu'),
167('Cyprus', 'Nicosia', 'cy'),
168('Czech Republic', 'Prague', 'cz'),
169('Denmark', 'Copenhagen', 'dk'),
170('Djibouti', 'Djibouti', 'dj'),
171('Dominica', 'Roseau', 'dm'),
172('Dominican Republic', 'Santo Domingo', 'do'),
173('Ecuador', 'Quito', 'ec'),
174('Egypt', 'Cairo', 'eg'),
175('El Salvador', 'San Salvador', 'sv'),
176('Equatorial Guinea', 'Malabo', 'gq'),
177('Eritrea', 'Asmara', 'er'),
178('Estonia', 'Tallinn', 'ee'),
179('Ethiopia', 'Addis Ababa', 'et'),
180('Falkland Islands', 'Stanley', 'fk'),
181('Faeroe Islands', 'Thorshavn', 'fo'),
182('Fiji', 'Suva', 'fj'),
183('Finland', 'Helsinki', 'fi'),
184('France', 'Paris', 'fr'),
185('French Guiana', 'Cayenne', 'gf'),
186('French Polynesia', 'Papeete', 'pf'),
187('Gabon', 'Libreville', 'ga'),
188('The Gambia', 'Banjul', 'gm'),
189('Georgia', 'Tbilisi', 'ge'),
190('Germany', 'Berlin', 'de'),
191('Ghana', 'Accra', 'gh'),
192('Gibraltar', 'Gibraltar', 'gi'),
193('Greece', 'Athens', 'gr'),
194('Greenland', 'Nuuk', 'gl'),
195('Grenada', 'St George''s', 'gd'),
196('Guadeloupe', 'Basse Terre', 'gp'),
197('Guam', 'Hagtana', 'gu'),
198('Guatemala', 'Guatemala City', 'gt'),
199('Guinea', 'Conakry', 'gn'),
200('Guinea-Bissau', 'Bissau', 'gw'),
201('Guyana', 'Georgetown', 'gy'),
202('Haiti', 'Port-au-Prince', 'ht'),
203('The Hoy See (Vatican City State)', 'Vatican City', 'va'),
204('Honduras', 'Tegucigalpa', 'hn'),
205('Hungary', 'Budapest', 'hu'),
206('Iceland', 'Reykjavik', 'is'),
207('India', 'New Delhi', 'in'),
208('Indonesia', 'Jakarta', 'id'),
209('Iran', 'Tehran', 'ir'),
210('Iraq', 'Baghdad', 'iq'),
211('Ireland', 'Dublin', 'ie'),
212('Israel', 'Jerusalem', 'il'),
213('Italy', 'Rome', 'it'),
214('Jamaica', 'Kingston', 'jm'),
215('Japan', 'Tokyo', 'jp'),
216('Jordan', 'Amman', 'jo'),
217('Kazakhstan', 'Astana', 'kz'),
218('Kenya', 'Nairobi', 'ke'),
219('Kiribati', 'Tarawa', 'ki'),
220('North Korea', 'Pyongyang', 'kp'),
221('South Korea', 'Seoul', 'kr'),
222('Kuwait', 'Kuwait City', 'kw'),
223('Kyrgyzstan', 'Bishkek', 'kg'),
224('Laos', 'Vientiane', 'la'),
225('Latvia', 'Riga', 'lv'),
226('Lebanon', 'Beirut', 'lb'),
227('Lesotho', 'Maseru', 'ls'),
228('Liberia', 'Monrovia', 'lr'),
229('Libya', 'Tripoli', 'ly'),
230('Liechtenstein', 'Vaduz', 'li'),
231('Lithuania', 'Vilnius', 'lt'),
232('Luxembourg', 'Luxembourg', 'lu'),
233('Former Yugoslav Republic of Macedonia', 'Skopje', 'mk'),
234('Madagascar', 'Antananarivo', 'mg'),
235('Malawi', 'Lilongwe', 'mw'),
236('Malaysia', 'Kuala Lumpur', 'my'),
237('Maldives', 'Male', 'mv'),
238('Mali', 'Bamako', 'ml'),
239('Malta', 'Valletta', 'mt'),
240('Marshall Islands', 'Majuro', 'mh'),
241('Martinique', 'Fort-de-France', 'mq'),
242('Mauritania', 'Nouakchott', 'mr'),
243('Mauritius', 'Port Louis', 'mu'),
244('Mayotte', 'Mamoudzou', 'yt'),
245('Mexico', 'Mexico City', 'mx'),
246('Micronesia', 'Palikir', 'fm'),
247('Moldova', 'Chisinau', 'md'),
248('Monaco', 'Monaco', 'mc'),
249('Mongolia', 'Ulan Bator', 'mn'),
250('Montserrat', 'Plymouth', 'ms'),
251('Morocco', 'Rabat', 'ma'),
252('Mozambique', 'Maputo', 'mz'),
253('Myanmar', 'Yangon', 'mm'),
254('Namibia', 'Windhoek', 'na'),
255('Nauru', 'Yaren', 'nr'),
256('Nepal', 'Kathmandu', 'np'),
257('Netherlands', 'Amsterdam', 'nl'),
258('Netherlands Antilles', 'Willemstad', 'an'),
259('New Caledonia', 'Noumea', 'nc'),
260('New Zealand', 'Wellington', 'nz'),
261('Nicaragua', 'Managua', 'ni'),
262('Niger', 'Niamey', 'ne'),
263('Nigeria', 'Abuja', 'ng'),
264('Niue', 'Alofi', 'nu'),
265('Norfolk Island', 'Kingston', 'nf'),
266('Northern Marianas', 'Garapan', 'mp'),
267('Norway', 'Oslo', 'no'),
268('Oman', 'Muscat', 'om'),
269('Pakistan', 'Islamabad', 'pk'),
270('Palau', 'Koror', 'pw'),
271('Panama', 'Panama City', 'pa'),
272('Papua New Guinea', 'Port Moresby', 'pg'),
273('Paraguay', 'Asuncion', 'py'),
274('Peru', 'Lima', 'pe'),
275('Philippines', 'Manila', 'ph'),
276('Pitcairn Islands', 'Adamstown', 'pn'),
277('Poland', 'Warsaw', 'pl'),
278('Portugal', 'Lisbon', 'pt'),
279('Puerto Rico', 'San Juan', 'pr'),
280('Qatar', 'Doha', 'qa'),
281('Reunion', 'Saint-Denis', 're'),
282('Romania', 'Bucharest', 'ro'),
283('Russia', 'Moscow', 'ru'),
284('Rwanda', 'Kigali', 'rw'),
285('Saint Helena', 'Jamestown', 'sh'),
286('Saint Kitts and Nevis', 'Basseterre', 'kn'),
287('Saint Lucia', 'Castries', 'lc'),
288('Saint Pierre and Miquelon', 'Saint-Pierre', 'pm'),
289('Saint Vincent and the Grenadines', 'Kingstown', 'vc'),
290('Samoa', 'Apia', 'ws'),
291('San Marino', 'San Marino', 'sm'),
292('Sao tome and principle', 'Sao Tome & Principe Dobra', 'st'),
293('Saudi Arabia', 'Riyadh', 'sa'),
294('Senegal', 'Dakar', 'sn'),
295('Serbia', 'Belgrade', 'rs'),
296('Seychelles', 'Victoria', 'sc'),
297('Sierra Leone', 'Freetown', 'sl'),
298('Singapore', 'Singapore', 'sg'),
299('Slovakia', 'Bratislava', 'sk'),
300('Slovenia', 'Ljubljana', 'si'),
301('Solomon Islands', 'Honiara', 'sb'),
302('Somalia', 'Mogadishu', 'so'),
303('South Africa', 'Pretoria', 'za'),
304('South Sudan', 'Juba', 'ss'),
305('Spain', 'Madrid', 'es'),
306('Sri Lanka', 'Colombo', 'lk'),
307('Sudan', 'Khartoum', 'sd'),
308('Suriname', 'Paramaribo', 'sr'),
309('Svalbard and Jan Mayen', 'Longyearbyen', 'sj'),
310('Swaziland', 'Mbabane', 'sz'),
311('Sweden', 'Stockholm', 'se'),
312('Switzerland', 'Berne', 'ch'),
313('Syria', 'Damascus', 'sy'),
314('Taiwan', 'Taipei', 'tw'),
315('Tajikistan', 'Dushanbe', 'tj'),
316('Tanzania', 'Dodoma', 'tz'),
317('Thailand', 'Bangkok', 'th'),
318('Timor-Leste', 'Dili', 'tl'),
319('Togo', 'Lome', 'tg'),
320('Tokelau', 'Fakaofo', 'tk'),
321('Tonga', 'Nuku''alofa', 'to'),
322('Trinidad and Tobago', 'Port of Spain', 'tt'),
323('Tunisia', 'Tunis', 'tn'),
324('Turkey', 'Ankara', 'tr'),
325('Turkmenistan', 'Ashgabat', 'tm'),
326('Turks and Caicos Islands', 'Cockburn Town', 'tc'),
327('Tuvalu', 'Fongafale', 'tv'),
328('Uganda', 'Kampala', 'ug'),
329('Ukraine', 'Kiev', 'ua'),
330('United Arab Emirates', 'Abu Dhabi', 'ae'),
331('United Kingdom', 'London', 'gb'),
332('United States', 'Washington DC', 'us'),
333('Uruguay', 'Montevideo', 'uy'),
334('Uzbekistan', 'Tashkent', 'uz'),
335('Vanuatu', 'Port Vila', 'vu'),
336('Venezuela', 'Caracas', 've'),
337('Vietnam', 'Hanoi', 'vn'),
338('US Virgin Islands', 'Charlotte Amalie', 'vi'),
339('Wallis and Futuna', 'Mata-Utu', 'wf'),
340('Western Sahara', 'Al aaiun', 'eh'),
341('Yemen', 'San''a', 'ye'),
342('Zambia', 'Lusaka', 'zm'),
343('Zimbabwe', 'Harare', 'zw');
344
345-- COMMIT;