-- Create ranking and country tables

CREATE TABLE rankings (
  rank INTEGER PRIMARY KEY,
  country_id VARCHAR(3),
  confederation VARCHAR
);

CREATE TABLE countries (
  country_id VARCHAR(3) PRIMARY KEY,
  country_name VARCHAR
);

CREATE TABLE regions (
  confederation VARCHAR PRIMARY KEY,
  region VARCHAR
);

-- Populate rankings

INSERT INTO rankings VALUES
  (1,'ARG','CONMEBOL'),
  (2,'ESP','UEFA'),
  (3,'FRA','UEFA'),
  (4,'ENG','UEFA'),
  (5,'BRA','CONMEBOL'),
  (6,'NED','UEFA'),
  (7,'POR','UEFA'),
  (8,'BEL','UEFA'),
  (9,'ITA','UEFA'),
  (10,'GER','UEFA'),
  (11,'CRO','UEFA'),
  (12,'MAR','CAF'),
  (13,'URU','CONMEBOL'),
  (14,'COL','CONMEBOL'),
  (15,'JPN','AFC'),
  (16,'USA','CONCACAF'),
  (17,'MEX','CONCACAF'),
  (18,'IRN','AFC'),
  (19,'SEN','CAF'),
  (20,'SUI','UEFA'),
  (21,'DEN','UEFA'),
  (22,'AUT','UEFA'),
  (23,'KOR','AFC'),
  (24,'ECU','CONMEBOL'),
  (25,'UKR','UEFA'),
  (26,'AUS','AFC'),
  (27,'TUR','UEFA'),
  (28,'SWE','UEFA'),
  (29,'WAL','UEFA'),
  (30,'CAN','CONCACAF'),
  (31,'SRB','UEFA'),
  (32,'EGY','CAF'),
  (33,'PAN','CONCACAF'),
  (34,'POL','UEFA'),
  (35,'RUS','UEFA'),
  (36,'ALG','CAF'),
  (37,'HUN','UEFA'),
  (38,'NOR','UEFA'),
  (39,'CZE','UEFA'),
  (40,'GRE','UEFA'),
  (41,'CIV','CAF'),
  (42,'PER','CONMEBOL'),
  (43,'NGA','CAF'),
  (44,'SCO','UEFA'),
  (45,'ROU','UEFA'),
  (46,'SVK','UEFA'),
  (47,'VEN','CONMEBOL'),
  (48,'PAR','CONMEBOL'),
  (49,'TUN','CAF'),
  (50,'CMR','CAF'),
  (51,'SVN','UEFA'),
  (52,'CHI','CONMEBOL'),
  (53,'MLI','CAF'),
  (54,'CRC','CONCACAF'),
  (55,'QAT','AFC'),
  (56,'RSA','CAF'),
  (57,'UZB','AFC'),
  (58,'KSA','AFC'),
  (59,'IRQ','AFC'),
  (60,'IRL','UEFA'),
  (61,'COD','CAF'),
  (62,'JOR','AFC'),
  (63,'JAM','CONCACAF'),
  (64,'BFA','CAF'),
  (65,'UAE','AFC'),
  (66,'ALB','UEFA'),
  (67,'MKD','UEFA'),
  (68,'GEO','UEFA'),
  (69,'FIN','UEFA'),
  (70,'BIH','UEFA'),
  (71,'NIR','UEFA'),
  (72,'CPV','CAF'),
  (73,'MNE','UEFA'),
  (74,'ISL','UEFA'),
  (75,'HON','CONCACAF'),
  (76,'GHA','CAF'),
  (77,'OMA','AFC'),
  (78,'ISR','UEFA'),
  (79,'GAB','CAF'),
  (80,'BOL','CONMEBOL'),
  (81,'SLV','CONCACAF'),
  (82,'GUI','CAF'),
  (83,'HAI','CONCACAF'),
  (84,'BHR','AFC'),
  (85,'BUL','UEFA'),
  (86,'NZL','OFC'),
  (87,'ANG','CAF'),
  (88,'ZAM','CAF'),
  (89,'UGA','CAF'),
  (90,'CUW','CONCACAF'),
  (91,'LUX','UEFA'),
  (92,'EQG','CAF'),
  (93,'SYR','AFC'),
  (94,'CHN','AFC'),
  (95,'BEN','CAF'),
  (96,'MOZ','CAF'),
  (97,'KOS','UEFA'),
  (98,'BLR','UEFA'),
  (99,'THA','AFC'),
  (100,'TRI','CONCACAF'),
  (101,'PLE','AFC'),
  (102,'ARM','UEFA'),
  (103,'KGZ','AFC'),
  (104,'TJK','AFC'),
  (105,'COM','CAF'),
  (106,'GUA','CONCACAF'),
  (107,'TAN','CAF'),
  (108,'NAM','CAF'),
  (109,'VIE','AFC'),
  (110,'MTN','CAF'),
  (111,'KEN','CAF'),
  (112,'LBN','AFC'),
  (113,'KAZ','UEFA'),
  (114,'SDN','CAF'),
  (115,'MAD','CAF'),
  (116,'ZIM','CAF'),
  (117,'LBY','CAF'),
  (118,'PRK','AFC'),
  (119,'AZE','UEFA'),
  (120,'TOG','CAF'),
  (121,'EST','UEFA'),
  (122,'NIG','CAF'),
  (123,'IDN','AFC'),
  (124,'SLE','CAF'),
  (125,'CGO','CAF'),
  (126,'GAM','CAF'),
  (127,'IND','AFC'),
  (128,'GNB','CAF'),
  (129,'CYP','UEFA'),
  (130,'RWA','CAF'),
  (131,'MAS','AFC'),
  (132,'MWI','CAF'),
  (133,'NCA','CONCACAF'),
  (134,'KUW','AFC'),
  (135,'CTA','CAF'),
  (136,'BOT','CAF'),
  (137,'SUR','CONCACAF'),
  (138,'LVA','UEFA'),
  (139,'DOM','CONCACAF'),
  (140,'BDI','CAF'),
  (141,'FRO','UEFA'),
  (142,'TKM','AFC'),
  (143,'LTU','UEFA'),
  (144,'LBR','CAF'),
  (145,'SKN','CONCACAF'),
  (146,'PHI','AFC'),
  (147,'ETH','CAF'),
  (148,'LES','CAF'),
  (149,'SOL','OFC'),
  (150,'FIJ','OFC'),
  (151,'NCL','OFC'),
  (152,'GUY','CONCACAF'),
  (153,'HKG','AFC'),
  (154,'MDA','UEFA'),
  (155,'SWZ','CAF'),
  (156,'TAH','OFC'),
  (157,'PUR','CONCACAF'),
  (158,'YEM','AFC'),
  (159,'ATG','CONCACAF'),
  (160,'AFG','AFC'),
  (161,'SGP','AFC'),
  (162,'MYA','AFC'),
  (163,'VAN','OFC'),
  (164,'MDV','AFC'),
  (165,'LCA','CONCACAF'),
  (166,'TPE','AFC'),
  (167,'CUB','CONCACAF'),
  (168,'BER','CONCACAF'),
  (169,'MLT','UEFA'),
  (170,'SSD','CAF'),
  (171,'PNG','OFC'),
  (172,'VIN','CONCACAF'),
  (173,'AND','UEFA'),
  (174,'GRN','CONCACAF'),
  (175,'NEP','AFC'),
  (176,'BRB','CONCACAF'),
  (177,'CHA','CAF'),
  (178,'MRI','CAF'),
  (179,'BLZ','CONCACAF'),
  (180,'MSR','CONCACAF'),
  (181,'CAM','AFC'),
  (182,'BHU','AFC'),
  (183,'BAN','AFC'),
  (184,'DMA','CONCACAF'),
  (185,'BRU','AFC'),
  (186,'ASA','OFC'),
  (187,'MNG','AFC'),
  (188,'COK','OFC'),
  (189,'SAM','OFC'),
  (190,'LAO','AFC'),
  (191,'CAY','CONCACAF'),
  (192,'DJI','CAF'),
  (193,'MAC','AFC'),
  (194,'STP','CAF'),
  (195,'ARU','CONCACAF'),
  (196,'GIB','UEFA'),
  (197,'TLS','AFC'),
  (198,'PAK','AFC'),
  (199,'TGA','OFC'),
  (200,'SRI','AFC'),
  (201,'SOM','CAF'),
  (202,'GUM','AFC'),
  (203,'SEY','CAF'),
  (204,'BAH','CONCACAF'),
  (205,'LIE','UEFA'),
  (206,'TCA','CONCACAF'),
  (207,'VGB','CONCACAF'),
  (208,'VIR','CONCACAF'),
  (209,'AIA','CONCACAF'),
  (210,'SMR','UEFA');

-- Populate countries

INSERT INTO countries VALUES
  ('AFG','Afghanistan'),
  ('AIA','Anguilla'),
  ('ALB','Albania'),
  ('ALG','Algeria'),
  ('AND','Andorra'),
  ('ANG','Angola'),
  ('ARG','Argentina'),
  ('ARM','Armenia'),
  ('ARU','Aruba'),
  ('ASA','American Samoa'),
  ('ATG','Antigua and Barbuda'),
  ('AUS','Australia'),
  ('AUT','Austria'),
  ('AZE','Azerbaijan'),
  ('BAH','Bahamas'),
  ('BAN','Bangladesh'),
  ('BDI','Burundi'),
  ('BEL','Belgium'),
  ('BEN','Benin'),
  ('BER','Bermuda'),
  ('BFA','Burkina Faso'),
  ('BHR','Bahrain'),
  ('BHU','Bhutan'),
  ('BIH','Bosnia and Herzegovina'),
  ('BLR','Belarus'),
  ('BLZ','Belize'),
  ('BOL','Bolivia'),
  ('BOT','Botswana'),
  ('BRA','Brazil'),
  ('BRB','Barbados'),
  ('BRU','Brunei'),
  ('BUL','Bulgaria'),
  ('CAM','Cambodia'),
  ('CAN','Canada'),
  ('CAY','Cayman Islands'),
  ('CGO','Congo'),
  ('CHA','Chad'),
  ('CHI','Chile'),
  ('CHN','China'),
  ('CIV','Ivory Coast'),
  ('CMR','Cameroon'),
  ('COD','Democratic Republic of Congo'),
  ('COK','Cook Islands'),
  ('COL','Colombia'),
  ('COM','Comoros'),
  ('CPV','Cape Verde'),
  ('CRC','Costa Rica'),
  ('CRO','Croatia'),
  ('CTA','Central African Republic'),
  ('CUB','Cuba'),
  ('CUW','Curacao'),
  ('CYP','Cyprus'),
  ('CZE','Czech Republic'),
  ('DEN','Denmark'),
  ('DJI','Djibouti'),
  ('DMA','Dominica'),
  ('DOM','Dominican Republic'),
  ('ECU','Ecuador'),
  ('EGY','Egypt'),
  ('ENG','England'),
  ('EQG','Equatorial Guinea'),
  ('ESP','Spain'),
  ('EST','Estonia'),
  ('ETH','Ethiopia'),
  ('FIJ','Fiji'),
  ('FIN','Finland'),
  ('FRA','France'),
  ('FRO','Faroe Islands'),
  ('GAB','Gabon'),
  ('GAM','Gambia'),
  ('GEO','Georgia'),
  ('GER','Germany'),
  ('GHA','Ghana'),
  ('GIB','Gibraltar'),
  ('GNB','Guinea-Bissau'),
  ('GRE','Greece'),
  ('GRN','Grenada'),
  ('GUA','Guatemala'),
  ('GUI','Guinea'),
  ('GUM','Guam'),
  ('GUY','Guyana'),
  ('HAI','Haiti'),
  ('HKG','Hong Kong'),
  ('HON','Honduras'),
  ('HUN','Hungary'),
  ('IDN','Indonesia'),
  ('IND','India'),
  ('IRL','Republic of Ireland'),
  ('IRN','Iran'),
  ('IRQ','Iraq'),
  ('ISL','Iceland'),
  ('ISR','Israel'),
  ('ITA','Italy'),
  ('JAM','Jamaica'),
  ('JOR','Jordan'),
  ('JPN','Japan'),
  ('KAZ','Kazakhstan'),
  ('KEN','Kenya'),
  ('KGZ','Kyrgyzstan'),
  ('KOR','South Korea'),
  ('KOS','Kosovo'),
  ('KSA','Saudi Arabia'),
  ('KUW','Kuwait'),
  ('LAO','Laos'),
  ('LBN','Lebanon'),
  ('LBR','Liberia'),
  ('LBY','Libya'),
  ('LCA','St. Lucia'),
  ('LES','Lesotho'),
  ('LIE','Liechtenstein'),
  ('LTU','Lithuania'),
  ('LUX','Luxembourg'),
  ('LVA','Latvia'),
  ('MAC','Macau'),
  ('MAD','Madagascar'),
  ('MAR','Morocco'),
  ('MAS','Malaysia'),
  ('MDA','Moldova'),
  ('MDV','Maldives'),
  ('MEX','Mexico'),
  ('MKD','North Macedonia'),
  ('MLI','Mali'),
  ('MLT','Malta'),
  ('MNE','Montenegro'),
  ('MNG','Mongolia'),
  ('MOZ','Mozambique'),
  ('MRI','Mauritius'),
  ('MSR','Montserrat'),
  ('MTN','Mauritania'),
  ('MWI','Malawi'),
  ('MYA','Myanmar'),
  ('NAM','Namibia'),
  ('NCA','Nicaragua'),
  ('NCL','New Caledonia'),
  ('NED','Netherlands'),
  ('NEP','Nepal'),
  ('NGA','Nigeria'),
  ('NIG','Niger'),
  ('NIR','Northern Ireland'),
  ('NOR','Norway'),
  ('NZL','New Zealand'),
  ('OMA','Oman'),
  ('PAK','Pakistan'),
  ('PAN','Panama'),
  ('PAR','Paraguay'),
  ('PER','Peru'),
  ('PHI','Philippines'),
  ('PLE','Palestine'),
  ('PNG','Papua New Guinea'),
  ('POL','Poland'),
  ('POR','Portugal'),
  ('PRK','North Korea'),
  ('PUR','Puerto Rico'),
  ('QAT','Qatar'),
  ('ROU','Romania'),
  ('RSA','South Africa'),
  ('RUS','Russia'),
  ('RWA','Rwanda'),
  ('SAM','Samoa'),
  ('SCO','Scotland'),
  ('SDN','Sudan'),
  ('SEN','Senegal'),
  ('SEY','Seychelles'),
  ('SGP','Singapore'),
  ('SKN','St. Kitts and Nevis'),
  ('SLE','Sierra Leone'),
  ('SLV','El Salvador'),
  ('SMR','San Marino'),
  ('SOL','Solomon Islands'),
  ('SOM','Somalia'),
  ('SRB','Serbia'),
  ('SRI','Sri Lanka'),
  ('SSD','South Sudan'),
  ('STP','Sao Tome and Principe'),
  ('SUI','Switzerland'),
  ('SUR','Suriname'),
  ('SVK','Slovakia'),
  ('SVN','Slovenia'),
  ('SWE','Sweden'),
  ('SWZ','eSwatini'),
  ('SYR','Syria'),
  ('TAH','Tahiti'),
  ('TAN','Tanzania'),
  ('TCA','Turks and Caicos Islands'),
  ('TGA','Tonga'),
  ('THA','Thailand'),
  ('TJK','Tajikistan'),
  ('TKM','Turkmenistan'),
  ('TLS','Timor-Leste'),
  ('TOG','Togo'),
  ('TPE','Chinese Taipei'),
  ('TRI','Trinidad and Tobago'),
  ('TUN','Tunisia'),
  ('TUR','Turkey'),
  ('UAE','United Arab Emirates'),
  ('UGA','Uganda'),
  ('UKR','Ukraine'),
  ('URU','Uruguay'),
  ('USA','USA'),
  ('UZB','Uzbekistan'),
  ('VAN','Vanuatu'),
  ('VEN','Venezuela'),
  ('VGB','British Virgin Islands'),
  ('VIE','Vietnam'),
  ('VIN','St. Vincent and the Grenadines'),
  ('VIR','US Virgin Islands'),
  ('WAL','Wales'),
  ('YEM','Yemen'),
  ('ZAM','Zambia'),
  ('ZIM','Zimbabwe');  

-- Populate regions

INSERT INTO regions VALUES
  ('AFC','Asia'),
  ('CAF','Africa'),
  ('CONCACAF','North/Central America'),
  ('CONMEBOL','South America'),
  ('OFC','Oceania'),
  ('UEFA','Europe');

