Skip to content

Instantly share code, notes, and snippets.

@d630
Created August 29, 2018 03:52
Show Gist options
  • Save d630/07671e38a44e799130acc7f7ab429b62 to your computer and use it in GitHub Desktop.
Save d630/07671e38a44e799130acc7f7ab429b62 to your computer and use it in GitHub Desktop.
Quiz
-- START TRANSACTION;
SET foreign_key_checks=0;
DROP DATABASE IF EXISTS quiz;
CREATE DATABASE quiz;
USE quiz;
-- TABLES.
CREATE TABLE player (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
nick_name VARCHAR(255) UNIQUE NOT NULL,
rounds_total INTEGER NOT NULL DEFAUlt 0,
high_score INTEGER NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE country (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(255) UNIQUE NOT NULL,
capital_name VARCHAR(255) NOT NULL,
c_code CHAR(2) UNIQUE DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- PROCEDURES.
DELIMITER //
CREATE PROCEDURE set_player
(IN _nick_name VARCHAR(255))
BEGIN
INSERT INTO player(nick_name)
VALUES(_nick_name);
END //
CREATE PROCEDURE update_player
(IN _id INTEGER, _rounds_total INTEGER, IN _high_score INTEGER)
BEGIN
UPDATE player
SET rounds_total = _rounds_total, high_score = _high_score
WHERE id = _id;
END //
CREATE PROCEDURE get_player_by_id
(IN _id INTEGER)
BEGIN
SELECT *
FROM player
WHERE id = _id;
END //
CREATE PROCEDURE get_player_by_id
(IN _id INTEGER)
BEGIN
SELECT *
FROM player
WHERE id = _id;
END //
CREATE PROCEDURE get_player_by_nick_name
(IN _nick_name VARCHAR(255))
BEGIN
SELECT *
FROM player
WHERE nick_name = _nick_name;
END //
CREATE PROCEDURE get_players_by_high_score
()
BEGIN
SELECT *
FROM player
ORDER BY high_score DESC, rounds_total DESC;
END //
CREATE PROCEDURE get_players_by_high_score_named
()
BEGIN
SELECT nick_name AS Nickname, rounds_total as Rounds, high_score AS Highscore
FROM player
ORDER BY high_score DESC, rounds_total DESC;
END //
CREATE PROCEDURE get_random_countries
(IN _n INTEGER)
BEGIN
SELECT c_name, capital_name, c_code
FROM country
ORDER BY RAND()
LIMIT _n;
END //
DELIMITER ;
-- INSERTIONS.
-- INSERT INTO player(nick_name)
-- VALUES
-- ('user1'),
-- ('user2'),
-- ('user3'),
-- ('user4');
-- INSERT INTO player(nick_name, rounds_total, high_score)
-- VALUES
-- ('user5', 66, 1200);
-- 233 countries
INSERT INTO country(c_name, capital_name, c_code)
VALUES
('Afghanistan', 'Kabul', 'af'),
('Albania', 'Tirana', 'al'),
('Algeria', 'Algiers', 'dz'),
('American Samoa', 'Pago Pago', 'as'),
('Andorra', 'Andorra la Vella', 'ad'),
('Angola', 'Luanda', 'ao'),
('Anguilla', 'The Valley', 'ai'),
('Antigua and Barbuda', 'St John''s', 'ag'),
('Argentina', 'Buenos Aires', 'ar'),
('Armenia', 'Yerevan', 'am'),
('Aruba', 'Oranjestad', 'aw'),
('Australia', 'Canberra', 'au'),
('Austria', 'Vienna', 'at'),
('Azerbaijan', 'Baku', 'az'),
('The Bahamas', 'Nassau', 'bs'),
('Bahrain', 'Manama', 'bh'),
('Bangladesh', 'Dhaka', 'bd'),
('Barbados', 'Bridgetown', 'bb'),
('Belarus', 'Minsk', 'by'),
('Belgium', 'Brussels', 'be'),
('Belize', 'Belmopan', 'bz'),
('Benin', 'Porto Novo', 'bj'),
('Bermuda', 'Hamilton', 'bm'),
('Bhutan', 'Thimphu', 'bt'),
('Bolivia', 'Sucre', 'bo'),
('Bosnia and Herzegovina', 'Sarajevo', 'ba'),
('Botswana', 'Gaborone', 'bw'),
('Bouvet Island', '-', 'bv'),
('Brazil', 'Brasilia', 'br'),
('British Virgin Islands', 'Road Town', 'vg'),
('Brunei', 'Bandar Seri Begawan', 'bn'),
('Bulgaria', 'Sofia', 'bg'),
('Burkina Faso', 'Ouagadougou', 'bf'),
('Burundi', 'Bujumbura', 'bi'),
('Cambodia', 'Phnom Penh', 'kh'),
('Cameroon', 'Yaound', 'cm'),
('Canada', 'Ottawa', 'ca'),
('Cape Verde', 'Praia', 'cv'),
('Cayman Islands', 'George Town', 'ky'),
('Central African Republic', 'Bangui', 'cf'),
('Chad', 'N''Djamena', 'td'),
('Chile', 'Santiago', 'cl'),
('China', 'Beijing', 'cn'),
('Hong Kong', 'Victoria City', 'hk'),
('Macau', 'Macau', 'mo'),
('Christmas Island', 'Flying Fish Cove', 'cx'),
('Cocos (Keeling) Islands', 'Bantam', 'cc'),
('Colombia', 'Santa Fe de Bogot', 'co'),
('Comoros', 'Moroni', 'km'),
('Congo', 'Brazzaville', 'cg'),
('Democratic Republic of the Congo', 'Kinshasa', 'cd'),
('Cook Islands', 'Avarua', 'ck'),
('Costa Rica', 'San Jos', 'cr'),
('Cote D''Ivoire', 'Yamoussoukro', 'ci'),
('Croatia', 'Zagreb', 'hr'),
('Cuba', 'Havana', 'cu'),
('Cyprus', 'Nicosia', 'cy'),
('Czech Republic', 'Prague', 'cz'),
('Denmark', 'Copenhagen', 'dk'),
('Djibouti', 'Djibouti', 'dj'),
('Dominica', 'Roseau', 'dm'),
('Dominican Republic', 'Santo Domingo', 'do'),
('Ecuador', 'Quito', 'ec'),
('Egypt', 'Cairo', 'eg'),
('El Salvador', 'San Salvador', 'sv'),
('Equatorial Guinea', 'Malabo', 'gq'),
('Eritrea', 'Asmara', 'er'),
('Estonia', 'Tallinn', 'ee'),
('Ethiopia', 'Addis Ababa', 'et'),
('Falkland Islands', 'Stanley', 'fk'),
('Faeroe Islands', 'Thorshavn', 'fo'),
('Fiji', 'Suva', 'fj'),
('Finland', 'Helsinki', 'fi'),
('France', 'Paris', 'fr'),
('French Guiana', 'Cayenne', 'gf'),
('French Polynesia', 'Papeete', 'pf'),
('Gabon', 'Libreville', 'ga'),
('The Gambia', 'Banjul', 'gm'),
('Georgia', 'Tbilisi', 'ge'),
('Germany', 'Berlin', 'de'),
('Ghana', 'Accra', 'gh'),
('Gibraltar', 'Gibraltar', 'gi'),
('Greece', 'Athens', 'gr'),
('Greenland', 'Nuuk', 'gl'),
('Grenada', 'St George''s', 'gd'),
('Guadeloupe', 'Basse Terre', 'gp'),
('Guam', 'Hagtana', 'gu'),
('Guatemala', 'Guatemala City', 'gt'),
('Guinea', 'Conakry', 'gn'),
('Guinea-Bissau', 'Bissau', 'gw'),
('Guyana', 'Georgetown', 'gy'),
('Haiti', 'Port-au-Prince', 'ht'),
('The Hoy See (Vatican City State)', 'Vatican City', 'va'),
('Honduras', 'Tegucigalpa', 'hn'),
('Hungary', 'Budapest', 'hu'),
('Iceland', 'Reykjavik', 'is'),
('India', 'New Delhi', 'in'),
('Indonesia', 'Jakarta', 'id'),
('Iran', 'Tehran', 'ir'),
('Iraq', 'Baghdad', 'iq'),
('Ireland', 'Dublin', 'ie'),
('Israel', 'Jerusalem', 'il'),
('Italy', 'Rome', 'it'),
('Jamaica', 'Kingston', 'jm'),
('Japan', 'Tokyo', 'jp'),
('Jordan', 'Amman', 'jo'),
('Kazakhstan', 'Astana', 'kz'),
('Kenya', 'Nairobi', 'ke'),
('Kiribati', 'Tarawa', 'ki'),
('North Korea', 'Pyongyang', 'kp'),
('South Korea', 'Seoul', 'kr'),
('Kuwait', 'Kuwait City', 'kw'),
('Kyrgyzstan', 'Bishkek', 'kg'),
('Laos', 'Vientiane', 'la'),
('Latvia', 'Riga', 'lv'),
('Lebanon', 'Beirut', 'lb'),
('Lesotho', 'Maseru', 'ls'),
('Liberia', 'Monrovia', 'lr'),
('Libya', 'Tripoli', 'ly'),
('Liechtenstein', 'Vaduz', 'li'),
('Lithuania', 'Vilnius', 'lt'),
('Luxembourg', 'Luxembourg', 'lu'),
('Former Yugoslav Republic of Macedonia', 'Skopje', 'mk'),
('Madagascar', 'Antananarivo', 'mg'),
('Malawi', 'Lilongwe', 'mw'),
('Malaysia', 'Kuala Lumpur', 'my'),
('Maldives', 'Male', 'mv'),
('Mali', 'Bamako', 'ml'),
('Malta', 'Valletta', 'mt'),
('Marshall Islands', 'Majuro', 'mh'),
('Martinique', 'Fort-de-France', 'mq'),
('Mauritania', 'Nouakchott', 'mr'),
('Mauritius', 'Port Louis', 'mu'),
('Mayotte', 'Mamoudzou', 'yt'),
('Mexico', 'Mexico City', 'mx'),
('Micronesia', 'Palikir', 'fm'),
('Moldova', 'Chisinau', 'md'),
('Monaco', 'Monaco', 'mc'),
('Mongolia', 'Ulan Bator', 'mn'),
('Montserrat', 'Plymouth', 'ms'),
('Morocco', 'Rabat', 'ma'),
('Mozambique', 'Maputo', 'mz'),
('Myanmar', 'Yangon', 'mm'),
('Namibia', 'Windhoek', 'na'),
('Nauru', 'Yaren', 'nr'),
('Nepal', 'Kathmandu', 'np'),
('Netherlands', 'Amsterdam', 'nl'),
('Netherlands Antilles', 'Willemstad', 'an'),
('New Caledonia', 'Noumea', 'nc'),
('New Zealand', 'Wellington', 'nz'),
('Nicaragua', 'Managua', 'ni'),
('Niger', 'Niamey', 'ne'),
('Nigeria', 'Abuja', 'ng'),
('Niue', 'Alofi', 'nu'),
('Norfolk Island', 'Kingston', 'nf'),
('Northern Marianas', 'Garapan', 'mp'),
('Norway', 'Oslo', 'no'),
('Oman', 'Muscat', 'om'),
('Pakistan', 'Islamabad', 'pk'),
('Palau', 'Koror', 'pw'),
('Panama', 'Panama City', 'pa'),
('Papua New Guinea', 'Port Moresby', 'pg'),
('Paraguay', 'Asuncion', 'py'),
('Peru', 'Lima', 'pe'),
('Philippines', 'Manila', 'ph'),
('Pitcairn Islands', 'Adamstown', 'pn'),
('Poland', 'Warsaw', 'pl'),
('Portugal', 'Lisbon', 'pt'),
('Puerto Rico', 'San Juan', 'pr'),
('Qatar', 'Doha', 'qa'),
('Reunion', 'Saint-Denis', 're'),
('Romania', 'Bucharest', 'ro'),
('Russia', 'Moscow', 'ru'),
('Rwanda', 'Kigali', 'rw'),
('Saint Helena', 'Jamestown', 'sh'),
('Saint Kitts and Nevis', 'Basseterre', 'kn'),
('Saint Lucia', 'Castries', 'lc'),
('Saint Pierre and Miquelon', 'Saint-Pierre', 'pm'),
('Saint Vincent and the Grenadines', 'Kingstown', 'vc'),
('Samoa', 'Apia', 'ws'),
('San Marino', 'San Marino', 'sm'),
('Sao tome and principle', 'Sao Tome & Principe Dobra', 'st'),
('Saudi Arabia', 'Riyadh', 'sa'),
('Senegal', 'Dakar', 'sn'),
('Serbia', 'Belgrade', 'rs'),
('Seychelles', 'Victoria', 'sc'),
('Sierra Leone', 'Freetown', 'sl'),
('Singapore', 'Singapore', 'sg'),
('Slovakia', 'Bratislava', 'sk'),
('Slovenia', 'Ljubljana', 'si'),
('Solomon Islands', 'Honiara', 'sb'),
('Somalia', 'Mogadishu', 'so'),
('South Africa', 'Pretoria', 'za'),
('South Sudan', 'Juba', 'ss'),
('Spain', 'Madrid', 'es'),
('Sri Lanka', 'Colombo', 'lk'),
('Sudan', 'Khartoum', 'sd'),
('Suriname', 'Paramaribo', 'sr'),
('Svalbard and Jan Mayen', 'Longyearbyen', 'sj'),
('Swaziland', 'Mbabane', 'sz'),
('Sweden', 'Stockholm', 'se'),
('Switzerland', 'Berne', 'ch'),
('Syria', 'Damascus', 'sy'),
('Taiwan', 'Taipei', 'tw'),
('Tajikistan', 'Dushanbe', 'tj'),
('Tanzania', 'Dodoma', 'tz'),
('Thailand', 'Bangkok', 'th'),
('Timor-Leste', 'Dili', 'tl'),
('Togo', 'Lome', 'tg'),
('Tokelau', 'Fakaofo', 'tk'),
('Tonga', 'Nuku''alofa', 'to'),
('Trinidad and Tobago', 'Port of Spain', 'tt'),
('Tunisia', 'Tunis', 'tn'),
('Turkey', 'Ankara', 'tr'),
('Turkmenistan', 'Ashgabat', 'tm'),
('Turks and Caicos Islands', 'Cockburn Town', 'tc'),
('Tuvalu', 'Fongafale', 'tv'),
('Uganda', 'Kampala', 'ug'),
('Ukraine', 'Kiev', 'ua'),
('United Arab Emirates', 'Abu Dhabi', 'ae'),
('United Kingdom', 'London', 'gb'),
('United States', 'Washington DC', 'us'),
('Uruguay', 'Montevideo', 'uy'),
('Uzbekistan', 'Tashkent', 'uz'),
('Vanuatu', 'Port Vila', 'vu'),
('Venezuela', 'Caracas', 've'),
('Vietnam', 'Hanoi', 'vn'),
('US Virgin Islands', 'Charlotte Amalie', 'vi'),
('Wallis and Futuna', 'Mata-Utu', 'wf'),
('Western Sahara', 'Al aaiun', 'eh'),
('Yemen', 'San''a', 'ye'),
('Zambia', 'Lusaka', 'zm'),
('Zimbabwe', 'Harare', 'zw');
-- COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment