Java7
SQL
What happens when you have repeating values
Mar 10th
Lets put together a business case study. Your company runs a job matching service and you keep the data of the people who are looking for jobs, you need to know their names and their current occupations. A very simple business domain indeed.
Lets suppose you come up with the idea of capturing all their data into one table called user. The user table contains an arbitrary ID, a name, and the person’s occupation. Your entire schema contains only one relation, the user table.
Problematic table design:
Entity Diagram
CREATE TABLE user(
id INT,
name VARCHAR(20),
occupation VARCHAR(20)
);
And you proceed to fill in the data of your customers in this fashion:
User Table
+----------+------------+----------------+
+ ID + Name + Occupation +
+----------+------------+----------------+
+ 1 + Ken + Truck Driver +
+ 2 + Ronald + Taxi Driver +
+ 3 + Keith + Truck Driver +
+----------+------------+----------------+
This table appears fine, valid and sufficient to support all your business operations
Scenario 1: You need to update the occupation ‘truck drivers’ to ‘truck operators’
Your truck drivers are complaining, they want to be called truck operators and not truck drivers. Your boss all agreeing tells you to go ahead with the changes.
Lets look at the table below with more sample data.
As you can see, Ken is a Truck Driver, so is Gary and Frank amongst many others too.
User Table
+----------+------------+----------------+
+ ID + Name + Occupation +
+----------+------------+----------------+
+ 1 + Ken + Truck Driver +
+ 2 + Ronald + Taxi Driver +
+ 3 + Keith + Truck Driver +
+ 4 + Gary + Trunk Driver +
+ 5 + Tom + Waiter +
+ 6 + Frank + Truck Driver +
+ 7 + June + Truck Driver +
+ 8 + HH. + Truck Driver +
+ 9 + Henry + Truck Driver +
+ 10 + June + Cashier +
+ 11 + Keith + Truck Driver +
+ 12 + Vijay + Truck Driver +
+ 13 + June + Truck Driver +
+ 14 + Jerry + Truck Driver +
+ 15 + Java + Truck Driver +
+----------+------------+----------------+
In this case, we may simply issue an SQL update DML (Data Manipulation Language) statement to change the business meaning of the data.
UPDATE user SET occupation = 'Truck Operator' WHERE occupation = 'Truck Driver';
This SQL statement updates all records with the occupation ‘Truck Driver’ to ‘Truck Operator’. The poor DBMS physically manipulates the database files to update the records ‘Truck Operator’. (I am not very sure if any DBMS vendor manages this aspect any differently but lets assume that is the way it works in a hypothetical system and in Oracle. Correct me, MySQL returns the message ‘x rows updated’ before returning the cursor back to you)
The result:
User Table
+----------+------------+------------------+
+ ID + Name + Occupation +
+----------+------------+------------------+
+ 1 + Ken + Truck Operator +
+ 2 + Ronald + Taxi Driver +
+ 3 + Keith + Truck Operator +
+ 4 + Gary + Trunk Operator +
+ 5 + Tom + Waiter +
+ 6 + Frank + Truck Operator +
+ 7 + June + Truck Operator +
+ 8 + HH. + Truck Operator +
+ 9 + Henry + Truck Operator +
+ 10 + June + Cashier +
+ 11 + Keith + Truck Operator +
+ 12 + Vijay + Truck Operator +
+ 13 + June + Truck Operator +
+ 14 + Jerry + Truck Operator +
+ 15 + Java + Truck Operator +
+----------+------------+------------------+
You are very lucky, your database serves an intranet application that is only in use by ten users during the daytime, you were very sure you could run this SQL patch in the evening when nobody appears to be around. Problem is solved.
Case 2: Same table with inconsistent data
You decided to go for a little holiday in Maldives, and in your absence hand over the task of inserting anymore new members to your colleague Ali and Ali’s a big screw up. Ali and does not differentiate between ‘Truck Operator’ and ‘Truck Operators’ it is the same to him.
When you came back from Maldives, eight new members were added, of which seven were Truck Operators but spelled quite incorrectly!
User Table
+----------+------------+------------------+
+ ID + Name + Occupation +
+----------+------------+------------------+
...
+ 16 + June + Cashier +
+ 17 + Keith + Truct Operator +
+ 18 + Vijay + Truck perator +
+ 19 + June + Truck Operators +
+ 20 + Jerry + Trunk Operator +
+ 21 + Java + truck Operator +
+ 22 + Jerry + Trunk Operator +
+ 23 + Java + truckerZ +
+----------+------------+------------------+
And in that instance, your boss wants you to run against the DBMS to find out the number of truck operator members there are.
SELECT * FROM user WHERE occupation = 'Truck Operator';
The SQL returned 12 instead of 19, which is incorrect. This is because the DBMS does not include in the count the likes of ‘Trunk Operator’ or ‘truckerZ’.
Clearly, at this point, your schema design is exhibiting some kind of anomaly. You can’t insert a new occupation without also specifying a new person. You can’t delete an occupation without also deleting the entire row – person. You can’t update an occupation without also updating all the occurring occupations, your data is not clean. You have a poorly normalized database that is exhibiting all kinds of the anomalies from the textbook, insert anomaly, update anomaly, delete anomaly.
The correction steps:
We walk through this without explicitly mentioning normalization, which is what it really is. (Which is seemingly hard to recap and interpret for application developers-like myself and beginners respectively) If this ‘Science’ has had you interested, you may look further into the topic ‘Normalization‘, and stop at 3NF :P.
We will break up the monolithic user table into two tables, user and occupation
CREATE TABLE user(
user_id INT,
name VARCHAR(20),
occupation_id INT
);
CREATE TABLE occupation(
occupation_id INT,
occupation VARCHAR(20)
);
We shall omit referential integrity for clarity.
Let us look at some sample data populated into the two tables.
User Table Occupation Table
+----------+--------+---------------+ +----------------+-----------------+
+ User ID + Name + Occupation ID + + Occupation ID + Occupation +
+----------+--------+---------------+ +----------------+-----------------+
+ 1 + Ken + 1 + + 1 + Truck Driver +
+ 2 + Ronald + 2 + + 2 + Taxi Driver +
+ 3 + Keith + 1 + + 3 + Baker +
+ 4 + June + 1 + +----------------+-----------------+
+ 5 + Henry + 3 +
+ 6 + Candy + 1 +
+ 7 + Gwen + 1 +
+----------+--------+---------------+
In the user table, other than containing the name of the person, we store another value – the occupation ID which is a primary key of the occupation table. It simply means that we do not store the literal occupation of the person in text like we used to in the above, instead we store it into another table called occupation. The occupation table now contains all the occupations we need, truck driver, taxi driver and baker.
Should we need to add another occupation, we will add it into the occupation table without exhibiting any insert anomaly.
When we need to update ‘Truck Driver’ to ‘Truck Operator’, we need only to update the occupation table, and not to the user table.
When we need to delete ‘Baker’ from the records, we not necessary delete poor ‘Henry’ like we would have in the above.
We will talk about how to improve this even further later where we examine what other problems may occur (hint: what if Henry has two jobs, a daytime and a night time job? what if June does not have a job). But thats all for right now. :P
Revision History
March 22 2011 – Formatted tables for clarity
Creating Oracle “AUTO_INCREMENT” column for your surrogate key
Mar 9th
Lets face it, nobody tries to create primary keys out from natural keys in today’s context, maybe due to the sheer number of database tables an application or system today requires.
Creating surrogate keys is simple as it is common to most people. Yet Oracle has made this task a non trivial one.
I have come upon the chance to create a new table supporting new business requirements. The guide steps through using Oracle SQL Developer.
Step 1: Create Table
Create a new table of your choice.
** The steps to create a table are omitted.
For the purpose of this guide, we are interested to make column STATUS_ID of table GL_RECORD_STATUS_MATRIX an auto incremented surrogate key. STATUS_ID is also the primary key of the table.
Step 2: Create Sequence
Auto incremental numbers come in the form of sequence in Oracle. It is necessary to create a sequence object.
Name your sequence. For my sequence, i have chosen “SEQ_GL_RECORD_STATUS_MATRIX” in the naming convention “SEQ” + <table name>.
Create your sequence with the following properties:
Increment 1
Min value 0
Max value 999999999
Start with 1
Step 3: Create Trigger
In the configuration dialog, name your trigger and select the table you wish to apply this trigger to. You may choose to name your sequence trigger in the convention of <table name> + “SEQ” or “SEQ” + <table name> depending on the naming scheme you prefer.
Select the “Row Level” option as we are applying this trigger on a row level.
Once you have created your trigger, SQL Developer should display the “code” of the trigger that you have just created.
CREATE OR REPLACE TRIGGER GL_RECORD_STATUS_MATRIX_SEQ
BEFORE INSERT ON GL_RECORD_STATUS_MATRIX
FOR EACH ROW
BEGIN
NULL;
END;
Note that between the BEGIN and END keywords is a NULL literal. This is where you fill in the “how” of this trigger.
CREATE OR REPLACE TRIGGER GL_RECORD_STATUS_MATRIX_SEQ
BEFORE INSERT ON GL_RECORD_STATUS_MATRIX
FOR EACH ROW
BEGIN
select SEQ_GL_RECORD_STATUS_MATRIX.nextval into :NEW.STATUS_ID from dual;
END;
Note that i have replaced the string literal “NULL” with the following:
select SEQ_GL_RECORD_STATUS_MATRIX.nextval into :NEW.STATUS_ID from dual;
This statement means, select the next value from the sequence i have just created, and insert into onto STATUS_ID of my table.
Compile the trigger when done.
Complete! Testing…
Test that your auto number is working by adding a new row to your table without explicitly providing a value to the STATUS_ID field. Oracle should insert the value of STATUS_ID automatically for you.
Recap…
Usually, it is not necessary to add a prefix to the name of the object that you are creating, for example, it is not necessary to name an employee table tbl_employee or table_employee. However, because Oracle does not quite allow us to create a sequence object with the same name as the table object in the same schema, we have no choice but to select another more definitive name for our sequence.
(Unable to create a sequence with the same name as the table)
A Foreign Key constraint must define at least one column
Mar 7th
Oracle is right! A foreign key constraint must define at least one column (specifically, key), how else would referential integrity work.
While working on a CR, i was required to add a foreign key cascade update and another cascade delete. I am using Oracle SQL Developer over Oracle 10g DBMS, and naturally i tried to deliver this patch through point and click. The problem came as that there were no columns visible for selection in the UI dialog for the table i was referencing to.
(There are supposed to be columns i can select from in the “Referenced Constraint” drop down menu)
Although my first instinct was that Oracle must have screwed up (yet again), but it was not. It fell back on relational theory. Read on.
On inspecting the table i was trying to reference – SERVICE_TYPE, i found the culprit – there was no primary key defined in that table!
The solution – define the rightful primary key for that table.
Solution
Define the primary key for the master table, or the table you are trying to reference your foreign key to.
Go back to your referencing table, and try to add the foreign key again. It works this time around.
This does not apply to Oracle DBMS only, rather anywhere (DBMS) with a “safe” checking mechanism akin to Java as a strongly typed language.
What to check for when such happens?
1. Check the table you are referencing, that a primary key is defined.
To quote Oracle Database 11g The Complete Reference on one good and short coverage of foreign keys.
The Foreign Key
A foreign key is a combination of columns with values based on the primary key values from another table. A foreign key constraint, also known as a referential integrity constraint, specifies that the values of the foreign key correspond to actual values of the primary key in the other table. In the BOOKSHELF table, for example, the CategoryName column refers to values for the CategoryName column in the CATEGORY table:
create table BOOKSHELF( Title VARCHAR2(100) primary key, Publisher VARCHAR2(20), CategoryName VARCHAR2(20), Rating VARCHAR2(2), constraint CATFK foreign key (CategoryName) references CATEGORY(CategoryName));You can refer to a primary or unique key, even in the same table. However, you can’t refer to a table in a remote database in the references clause. You can use the table form (which is used earlier to create a PRIMARY KEY on the TROUBLE table) instead of the column form to specify foreign keys with multiple columns.
Sometimes you may want to delete these dependent rows when you delete the row they depend on. In the case of BOOKSHELF and CATEGORY, if you delete a CategoryName from CATEGORY, you may want to make the matching BOOKSHELF CategoryName column values NULL. In another case, you might want to delete the whole row. The clause on delete cascade added to the references clause tells Oracle to delete the dependent row when you delete the corresponding row in the parent table. This action automatically maintains referential integrity. For more information on the clauses on delete cascade and references, consult “Integrity Constraint” in the Alphabetical Reference of this book.
Note that i have always referenced Oracle, or any “database product” as DBMS (Database Management System), and not by the term “Database”. A database is a flat file, Oracle is not a flat file, it is a Database Management System. This distinction is highlighted in the book by Thomas Connally and notably subject veteran Carolyn Begg in “Database Systems, A practical approach to Design, Implementation, and Management”.
MySQL Country ISO List
Oct 16th
This will create and then populate a MySQL table with a list of the names and ISO 3166 codes for countries.
Download standard iso_country_sql
Download my flavour iso_country_my_flavour.sql
# iso_country_list.sql## This will create and then populate a MySQL table with a list of the names and# ISO 3166 codes for countries in existence as of the date below.## Usage:# mysql -u username -ppassword database_name < ./iso_country_list.sql## For updates to this file, see http://27.org/isocountrylist/# For more about ISO 3166, see http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html## Created by getisocountrylist.pl on Sun Nov 2 14:59:20 2003.# Wm. Rhodes <iso_country_list@27.org>#CREATE TABLE IF NOT EXISTS country (iso CHAR(2) NOT NULL PRIMARY KEY,name VARCHAR(80) NOT NULL,printable_name VARCHAR(80) NOT NULL,iso3 CHAR(3),numcode SMALLINT);INSERT INTO country VALUES (‘AF’,'AFGHANISTAN’,'Afghanistan’,'AFG’,’004′);INSERT INTO country VALUES (‘AL’,'ALBANIA’,'Albania’,'ALB’,’008′);INSERT INTO country VALUES (‘DZ’,'ALGERIA’,'Algeria’,'DZA’,’012′);INSERT INTO country VALUES (‘AS’,'AMERICAN SAMOA’,'American Samoa’,'ASM’,’016′);INSERT INTO country VALUES (‘AD’,'ANDORRA’,'Andorra’,'AND’,’020′);INSERT INTO country VALUES (‘AO’,'ANGOLA’,'Angola’,'AGO’,’024′);INSERT INTO country VALUES (‘AI’,'ANGUILLA’,'Anguilla’,'AIA’,’660′);INSERT INTO country VALUES (‘AQ’,'ANTARCTICA’,'Antarctica’,NULL,NULL);INSERT INTO country VALUES (‘AG’,'ANTIGUA AND BARBUDA’,'Antigua and Barbuda’,'ATG’,’028′);INSERT INTO country VALUES (‘AR’,'ARGENTINA’,'Argentina’,'ARG’,’032′);INSERT INTO country VALUES (‘AM’,'ARMENIA’,'Armenia’,'ARM’,’051′);INSERT INTO country VALUES (‘AW’,'ARUBA’,'Aruba’,'ABW’,’533′);INSERT INTO country VALUES (‘AU’,'AUSTRALIA’,'Australia’,'AUS’,’036′);INSERT INTO country VALUES (‘AT’,'AUSTRIA’,'Austria’,'AUT’,’040′);INSERT INTO country VALUES (‘AZ’,'AZERBAIJAN’,'Azerbaijan’,'AZE’,’031′);INSERT INTO country VALUES (‘BS’,'BAHAMAS’,'Bahamas’,'BHS’,’044′);INSERT INTO country VALUES (‘BH’,'BAHRAIN’,'Bahrain’,'BHR’,’048′);INSERT INTO country VALUES (‘BD’,'BANGLADESH’,'Bangladesh’,'BGD’,’050′);INSERT INTO country VALUES (‘BB’,'BARBADOS’,'Barbados’,'BRB’,’052′);INSERT INTO country VALUES (‘BY’,'BELARUS’,'Belarus’,'BLR’,’112′);INSERT INTO country VALUES (‘BE’,'BELGIUM’,'Belgium’,'BEL’,’056′);INSERT INTO country VALUES (‘BZ’,'BELIZE’,'Belize’,'BLZ’,’084′);INSERT INTO country VALUES (‘BJ’,'BENIN’,'Benin’,'BEN’,’204′);INSERT INTO country VALUES (‘BM’,'BERMUDA’,'Bermuda’,'BMU’,’060′);INSERT INTO country VALUES (‘BT’,'BHUTAN’,'Bhutan’,'BTN’,’064′);INSERT INTO country VALUES (‘BO’,'BOLIVIA’,'Bolivia’,'BOL’,’068′);INSERT INTO country VALUES (‘BA’,'BOSNIA AND HERZEGOVINA’,'Bosnia and Herzegovina’,'BIH’,’070′);INSERT INTO country VALUES (‘BW’,'BOTSWANA’,'Botswana’,'BWA’,’072′);INSERT INTO country VALUES (‘BV’,'BOUVET ISLAND’,'Bouvet Island’,NULL,NULL);INSERT INTO country VALUES (‘BR’,'BRAZIL’,'Brazil’,'BRA’,’076′);INSERT INTO country VALUES (‘IO’,'BRITISH INDIAN OCEAN TERRITORY’,'British Indian Ocean Territory’,NULL,NULL);INSERT INTO country VALUES (‘BN’,'BRUNEI DARUSSALAM’,'Brunei Darussalam’,'BRN’,’096′);INSERT INTO country VALUES (‘BG’,'BULGARIA’,'Bulgaria’,'BGR’,’100′);INSERT INTO country VALUES (‘BF’,'BURKINA FASO’,'Burkina Faso’,'BFA’,’854′);INSERT INTO country VALUES (‘BI’,'BURUNDI’,'Burundi’,'BDI’,’108′);INSERT INTO country VALUES (‘KH’,'CAMBODIA’,'Cambodia’,'KHM’,’116′);INSERT INTO country VALUES (‘CM’,'CAMEROON’,'Cameroon’,'CMR’,’120′);INSERT INTO country VALUES (‘CA’,'CANADA’,'Canada’,'CAN’,’124′);INSERT INTO country VALUES (‘CV’,'CAPE VERDE’,'Cape Verde’,'CPV’,’132′);INSERT INTO country VALUES (‘KY’,'CAYMAN ISLANDS’,'Cayman Islands’,'CYM’,’136′);INSERT INTO country VALUES (‘CF’,'CENTRAL AFRICAN REPUBLIC’,'Central African Republic’,'CAF’,’140′);INSERT INTO country VALUES (‘TD’,'CHAD’,'Chad’,'TCD’,’148′);INSERT INTO country VALUES (‘CL’,'CHILE’,'Chile’,'CHL’,’152′);INSERT INTO country VALUES (‘CN’,'CHINA’,'China’,'CHN’,’156′);INSERT INTO country VALUES (‘CX’,'CHRISTMAS ISLAND’,'Christmas Island’,NULL,NULL);INSERT INTO country VALUES (‘CC’,'COCOS (KEELING) ISLANDS’,'Cocos (Keeling) Islands’,NULL,NULL);INSERT INTO country VALUES (‘CO’,'COLOMBIA’,'Colombia’,'COL’,’170′);INSERT INTO country VALUES (‘KM’,'COMOROS’,'Comoros’,'COM’,’174′);INSERT INTO country VALUES (‘CG’,'CONGO’,'Congo’,'COG’,’178′);INSERT INTO country VALUES (‘CD’,'CONGO, THE DEMOCRATIC REPUBLIC OF THE’,'Congo, the Democratic Republic of the’,'COD’,’180′);INSERT INTO country VALUES (‘CK’,'COOK ISLANDS’,'Cook Islands’,'COK’,’184′);INSERT INTO country VALUES (‘CR’,'COSTA RICA’,'Costa Rica’,'CRI’,’188′);INSERT INTO country VALUES (‘CI’,'COTE D\’IVOIRE’,'Cote D\’Ivoire’,'CIV’,’384′);INSERT INTO country VALUES (‘HR’,'CROATIA’,'Croatia’,'HRV’,’191′);INSERT INTO country VALUES (‘CU’,'CUBA’,'Cuba’,'CUB’,’192′);INSERT INTO country VALUES (‘CY’,'CYPRUS’,'Cyprus’,'CYP’,’196′);INSERT INTO country VALUES (‘CZ’,'CZECH REPUBLIC’,'Czech Republic’,'CZE’,’203′);INSERT INTO country VALUES (‘DK’,'DENMARK’,'Denmark’,'DNK’,’208′);INSERT INTO country VALUES (‘DJ’,'DJIBOUTI’,'Djibouti’,'DJI’,’262′);INSERT INTO country VALUES (‘DM’,'DOMINICA’,'Dominica’,'DMA’,’212′);INSERT INTO country VALUES (‘DO’,'DOMINICAN REPUBLIC’,'Dominican Republic’,'DOM’,’214′);INSERT INTO country VALUES (‘EC’,'ECUADOR’,'Ecuador’,'ECU’,’218′);INSERT INTO country VALUES (‘EG’,'EGYPT’,'Egypt’,'EGY’,’818′);INSERT INTO country VALUES (‘SV’,'EL SALVADOR’,'El Salvador’,'SLV’,’222′);INSERT INTO country VALUES (‘GQ’,'EQUATORIAL GUINEA’,'Equatorial Guinea’,'GNQ’,’226′);INSERT INTO country VALUES (‘ER’,'ERITREA’,'Eritrea’,'ERI’,’232′);INSERT INTO country VALUES (‘EE’,'ESTONIA’,'Estonia’,'EST’,’233′);INSERT INTO country VALUES (‘ET’,'ETHIOPIA’,'Ethiopia’,'ETH’,’231′);INSERT INTO country VALUES (‘FK’,'FALKLAND ISLANDS (MALVINAS)’,'Falkland Islands (Malvinas)’,'FLK’,’238′);INSERT INTO country VALUES (‘FO’,'FAROE ISLANDS’,'Faroe Islands’,'FRO’,’234′);INSERT INTO country VALUES (‘FJ’,'FIJI’,'Fiji’,'FJI’,’242′);INSERT INTO country VALUES (‘FI’,'FINLAND’,'Finland’,'FIN’,’246′);INSERT INTO country VALUES (‘FR’,'FRANCE’,'France’,'FRA’,’250′);INSERT INTO country VALUES (‘GF’,'FRENCH GUIANA’,'French Guiana’,'GUF’,’254′);INSERT INTO country VALUES (‘PF’,'FRENCH POLYNESIA’,'French Polynesia’,'PYF’,’258′);INSERT INTO country VALUES (‘TF’,'FRENCH SOUTHERN TERRITORIES’,'French Southern Territories’,NULL,NULL);INSERT INTO country VALUES (‘GA’,'GABON’,'Gabon’,'GAB’,’266′);INSERT INTO country VALUES (‘GM’,'GAMBIA’,'Gambia’,'GMB’,’270′);INSERT INTO country VALUES (‘GE’,'GEORGIA’,'Georgia’,'GEO’,’268′);INSERT INTO country VALUES (‘DE’,'GERMANY’,'Germany’,'DEU’,’276′);INSERT INTO country VALUES (‘GH’,'GHANA’,'Ghana’,'GHA’,’288′);INSERT INTO country VALUES (‘GI’,'GIBRALTAR’,'Gibraltar’,'GIB’,’292′);INSERT INTO country VALUES (‘GR’,'GREECE’,'Greece’,'GRC’,’300′);INSERT INTO country VALUES (‘GL’,'GREENLAND’,'Greenland’,'GRL’,’304′);INSERT INTO country VALUES (‘GD’,'GRENADA’,'Grenada’,'GRD’,’308′);INSERT INTO country VALUES (‘GP’,'GUADELOUPE’,'Guadeloupe’,'GLP’,’312′);INSERT INTO country VALUES (‘GU’,'GUAM’,'Guam’,'GUM’,’316′);INSERT INTO country VALUES (‘GT’,'GUATEMALA’,'Guatemala’,'GTM’,’320′);INSERT INTO country VALUES (‘GN’,'GUINEA’,'Guinea’,'GIN’,’324′);INSERT INTO country VALUES (‘GW’,'GUINEA-BISSAU’,'Guinea-Bissau’,'GNB’,’624′);INSERT INTO country VALUES (‘GY’,'GUYANA’,'Guyana’,'GUY’,’328′);INSERT INTO country VALUES (‘HT’,'HAITI’,'Haiti’,'HTI’,’332′);INSERT INTO country VALUES (‘HM’,'HEARD ISLAND AND MCDONALD ISLANDS’,'Heard Island and Mcdonald Islands’,NULL,NULL);INSERT INTO country VALUES (‘VA’,'HOLY SEE (VATICAN CITY STATE)’,'Holy See (Vatican City State)’,'VAT’,’336′);INSERT INTO country VALUES (‘HN’,'HONDURAS’,'Honduras’,'HND’,’340′);INSERT INTO country VALUES (‘HK’,'HONG KONG’,'Hong Kong’,'HKG’,’344′);INSERT INTO country VALUES (‘HU’,'HUNGARY’,'Hungary’,'HUN’,’348′);INSERT INTO country VALUES (‘IS’,'ICELAND’,'Iceland’,'ISL’,’352′);INSERT INTO country VALUES (‘IN’,'INDIA’,'India’,'IND’,’356′);INSERT INTO country VALUES (‘ID’,'INDONESIA’,'Indonesia’,'IDN’,’360′);INSERT INTO country VALUES (‘IR’,'IRAN, ISLAMIC REPUBLIC OF’,'Iran, Islamic Republic of’,'IRN’,’364′);INSERT INTO country VALUES (‘IQ’,'IRAQ’,'Iraq’,'IRQ’,’368′);INSERT INTO country VALUES (‘IE’,'IRELAND’,'Ireland’,'IRL’,’372′);INSERT INTO country VALUES (‘IL’,'ISRAEL’,'Israel’,'ISR’,’376′);INSERT INTO country VALUES (‘IT’,'ITALY’,'Italy’,'ITA’,’380′);INSERT INTO country VALUES (‘JM’,'JAMAICA’,'Jamaica’,'JAM’,’388′);INSERT INTO country VALUES (‘JP’,'JAPAN’,'Japan’,'JPN’,’392′);INSERT INTO country VALUES (‘JO’,'JORDAN’,'Jordan’,'JOR’,’400′);INSERT INTO country VALUES (‘KZ’,'KAZAKHSTAN’,'Kazakhstan’,'KAZ’,’398′);INSERT INTO country VALUES (‘KE’,'KENYA’,'Kenya’,'KEN’,’404′);INSERT INTO country VALUES (‘KI’,'KIRIBATI’,'Kiribati’,'KIR’,’296′);INSERT INTO country VALUES (‘KP’,'KOREA, DEMOCRATIC PEOPLE\’S REPUBLIC OF’,'Korea, Democratic People\’s Republic of’,'PRK’,’408′);INSERT INTO country VALUES (‘KR’,'KOREA, REPUBLIC OF’,'Korea, Republic of’,'KOR’,’410′);INSERT INTO country VALUES (‘KW’,'KUWAIT’,'Kuwait’,'KWT’,’414′);INSERT INTO country VALUES (‘KG’,'KYRGYZSTAN’,'Kyrgyzstan’,'KGZ’,’417′);INSERT INTO country VALUES (‘LA’,'LAO PEOPLE\’S DEMOCRATIC REPUBLIC’,'Lao People\’s Democratic Republic’,'LAO’,’418′);INSERT INTO country VALUES (‘LV’,'LATVIA’,'Latvia’,'LVA’,’428′);INSERT INTO country VALUES (‘LB’,'LEBANON’,'Lebanon’,'LBN’,’422′);INSERT INTO country VALUES (‘LS’,'LESOTHO’,'Lesotho’,'LSO’,’426′);INSERT INTO country VALUES (‘LR’,'LIBERIA’,'Liberia’,'LBR’,’430′);INSERT INTO country VALUES (‘LY’,'LIBYAN ARAB JAMAHIRIYA’,'Libyan Arab Jamahiriya’,'LBY’,’434′);INSERT INTO country VALUES (‘LI’,'LIECHTENSTEIN’,'Liechtenstein’,'LIE’,’438′);INSERT INTO country VALUES (‘LT’,'LITHUANIA’,'Lithuania’,'LTU’,’440′);INSERT INTO country VALUES (‘LU’,'LUXEMBOURG’,'Luxembourg’,'LUX’,’442′);INSERT INTO country VALUES (‘MO’,'MACAO’,'Macao’,'MAC’,’446′);INSERT INTO country VALUES (‘MK’,'MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF’,'Macedonia, the Former Yugoslav Republic of’,'MKD’,’807′);INSERT INTO country VALUES (‘MG’,'MADAGASCAR’,'Madagascar’,'MDG’,’450′);INSERT INTO country VALUES (‘MW’,'MALAWI’,'Malawi’,'MWI’,’454′);INSERT INTO country VALUES (‘MY’,'MALAYSIA’,'Malaysia’,'MYS’,’458′);INSERT INTO country VALUES (‘MV’,'MALDIVES’,'Maldives’,'MDV’,’462′);INSERT INTO country VALUES (‘ML’,'MALI’,'Mali’,'MLI’,’466′);INSERT INTO country VALUES (‘MT’,'MALTA’,'Malta’,'MLT’,’470′);INSERT INTO country VALUES (‘MH’,'MARSHALL ISLANDS’,'Marshall Islands’,'MHL’,’584′);INSERT INTO country VALUES (‘MQ’,'MARTINIQUE’,'Martinique’,'MTQ’,’474′);INSERT INTO country VALUES (‘MR’,'MAURITANIA’,'Mauritania’,'MRT’,’478′);INSERT INTO country VALUES (‘MU’,'MAURITIUS’,'Mauritius’,'MUS’,’480′);INSERT INTO country VALUES (‘YT’,'MAYOTTE’,'Mayotte’,NULL,NULL);INSERT INTO country VALUES (‘MX’,'MEXICO’,'Mexico’,'MEX’,’484′);INSERT INTO country VALUES (‘FM’,'MICRONESIA, FEDERATED STATES OF’,'Micronesia, Federated States of’,'FSM’,’583′);INSERT INTO country VALUES (‘MD’,'MOLDOVA, REPUBLIC OF’,'Moldova, Republic of’,'MDA’,’498′);INSERT INTO country VALUES (‘MC’,'MONACO’,'Monaco’,'MCO’,’492′);INSERT INTO country VALUES (‘MN’,'MONGOLIA’,'Mongolia’,'MNG’,’496′);INSERT INTO country VALUES (‘MS’,'MONTSERRAT’,'Montserrat’,'MSR’,’500′);INSERT INTO country VALUES (‘MA’,'MOROCCO’,'Morocco’,'MAR’,’504′);INSERT INTO country VALUES (‘MZ’,'MOZAMBIQUE’,'Mozambique’,'MOZ’,’508′);INSERT INTO country VALUES (‘MM’,'MYANMAR’,'Myanmar’,'MMR’,’104′);INSERT INTO country VALUES (‘NA’,'NAMIBIA’,'Namibia’,'NAM’,’516′);INSERT INTO country VALUES (‘NR’,'NAURU’,'Nauru’,'NRU’,’520′);INSERT INTO country VALUES (‘NP’,'NEPAL’,'Nepal’,'NPL’,’524′);INSERT INTO country VALUES (‘NL’,'NETHERLANDS’,'Netherlands’,'NLD’,’528′);INSERT INTO country VALUES (‘AN’,'NETHERLANDS ANTILLES’,'Netherlands Antilles’,'ANT’,’530′);INSERT INTO country VALUES (‘NC’,'NEW CALEDONIA’,'New Caledonia’,'NCL’,’540′);INSERT INTO country VALUES (‘NZ’,'NEW ZEALAND’,'New Zealand’,'NZL’,’554′);INSERT INTO country VALUES (‘NI’,'NICARAGUA’,'Nicaragua’,'NIC’,’558′);INSERT INTO country VALUES (‘NE’,'NIGER’,'Niger’,'NER’,’562′);INSERT INTO country VALUES (‘NG’,'NIGERIA’,'Nigeria’,'NGA’,’566′);INSERT INTO country VALUES (‘NU’,'NIUE’,'Niue’,'NIU’,’570′);INSERT INTO country VALUES (‘NF’,'NORFOLK ISLAND’,'Norfolk Island’,'NFK’,’574′);INSERT INTO country VALUES (‘MP’,'NORTHERN MARIANA ISLANDS’,'Northern Mariana Islands’,'MNP’,’580′);INSERT INTO country VALUES (‘NO’,'NORWAY’,'Norway’,'NOR’,’578′);INSERT INTO country VALUES (‘OM’,'OMAN’,'Oman’,'OMN’,’512′);INSERT INTO country VALUES (‘PK’,'PAKISTAN’,'Pakistan’,'PAK’,’586′);INSERT INTO country VALUES (‘PW’,'PALAU’,'Palau’,'PLW’,’585′);INSERT INTO country VALUES (‘PS’,'PALESTINIAN TERRITORY, OCCUPIED’,'Palestinian Territory, Occupied’,NULL,NULL);INSERT INTO country VALUES (‘PA’,'PANAMA’,'Panama’,'PAN’,’591′);INSERT INTO country VALUES (‘PG’,'PAPUA NEW GUINEA’,'Papua New Guinea’,'PNG’,’598′);INSERT INTO country VALUES (‘PY’,'PARAGUAY’,'Paraguay’,'PRY’,’600′);INSERT INTO country VALUES (‘PE’,'PERU’,'Peru’,'PER’,’604′);INSERT INTO country VALUES (‘PH’,'PHILIPPINES’,'Philippines’,'PHL’,’608′);INSERT INTO country VALUES (‘PN’,'PITCAIRN’,'Pitcairn’,'PCN’,’612′);INSERT INTO country VALUES (‘PL’,'POLAND’,'Poland’,'POL’,’616′);INSERT INTO country VALUES (‘PT’,'PORTUGAL’,'Portugal’,'PRT’,’620′);INSERT INTO country VALUES (‘PR’,'PUERTO RICO’,'Puerto Rico’,'PRI’,’630′);INSERT INTO country VALUES (‘QA’,'QATAR’,'Qatar’,'QAT’,’634′);INSERT INTO country VALUES (‘RE’,'REUNION’,'Reunion’,'REU’,’638′);INSERT INTO country VALUES (‘RO’,'ROMANIA’,'Romania’,'ROM’,’642′);INSERT INTO country VALUES (‘RU’,'RUSSIAN FEDERATION’,'Russian Federation’,'RUS’,’643′);INSERT INTO country VALUES (‘RW’,'RWANDA’,'Rwanda’,'RWA’,’646′);INSERT INTO country VALUES (‘SH’,'SAINT HELENA’,'Saint Helena’,'SHN’,’654′);INSERT INTO country VALUES (‘KN’,'SAINT KITTS AND NEVIS’,'Saint Kitts and Nevis’,'KNA’,’659′);INSERT INTO country VALUES (‘LC’,'SAINT LUCIA’,'Saint Lucia’,'LCA’,’662′);INSERT INTO country VALUES (‘PM’,'SAINT PIERRE AND MIQUELON’,'Saint Pierre and Miquelon’,'SPM’,’666′);INSERT INTO country VALUES (‘VC’,'SAINT VINCENT AND THE GRENADINES’,'Saint Vincent and the Grenadines’,'VCT’,’670′);INSERT INTO country VALUES (‘WS’,'SAMOA’,'Samoa’,'WSM’,’882′);INSERT INTO country VALUES (‘SM’,'SAN MARINO’,'San Marino’,'SMR’,’674′);INSERT INTO country VALUES (‘ST’,'SAO TOME AND PRINCIPE’,'Sao Tome and Principe’,'STP’,’678′);INSERT INTO country VALUES (‘SA’,'SAUDI ARABIA’,'Saudi Arabia’,'SAU’,’682′);INSERT INTO country VALUES (‘SN’,'SENEGAL’,'Senegal’,'SEN’,’686′);INSERT INTO country VALUES (‘CS’,'SERBIA AND MONTENEGRO’,'Serbia and Montenegro’,NULL,NULL);INSERT INTO country VALUES (‘SC’,'SEYCHELLES’,'Seychelles’,'SYC’,’690′);INSERT INTO country VALUES (‘SL’,'SIERRA LEONE’,'Sierra Leone’,'SLE’,’694′);INSERT INTO country VALUES (‘SG’,'SINGAPORE’,'Singapore’,'SGP’,’702′);INSERT INTO country VALUES (‘SK’,'SLOVAKIA’,'Slovakia’,'SVK’,’703′);INSERT INTO country VALUES (‘SI’,'SLOVENIA’,'Slovenia’,'SVN’,’705′);INSERT INTO country VALUES (‘SB’,'SOLOMON ISLANDS’,'Solomon Islands’,'SLB’,’090′);INSERT INTO country VALUES (‘SO’,'SOMALIA’,'Somalia’,'SOM’,’706′);INSERT INTO country VALUES (‘ZA’,'SOUTH AFRICA’,'South Africa’,'ZAF’,’710′);INSERT INTO country VALUES (‘GS’,'SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS’,'South Georgia and the South Sandwich Islands’,NULL,NULL);INSERT INTO country VALUES (‘ES’,'SPAIN’,'Spain’,'ESP’,’724′);INSERT INTO country VALUES (‘LK’,'SRI LANKA’,'Sri Lanka’,'LKA’,’144′);INSERT INTO country VALUES (‘SD’,'SUDAN’,'Sudan’,'SDN’,’736′);INSERT INTO country VALUES (‘SR’,'SURINAME’,'Suriname’,'SUR’,’740′);INSERT INTO country VALUES (‘SJ’,'SVALBARD AND JAN MAYEN’,'Svalbard and Jan Mayen’,'SJM’,’744′);INSERT INTO country VALUES (‘SZ’,'SWAZILAND’,'Swaziland’,'SWZ’,’748′);INSERT INTO country VALUES (‘SE’,'SWEDEN’,'Sweden’,'SWE’,’752′);INSERT INTO country VALUES (‘CH’,'SWITZERLAND’,'Switzerland’,'CHE’,’756′);INSERT INTO country VALUES (‘SY’,'SYRIAN ARAB REPUBLIC’,'Syrian Arab Republic’,'SYR’,’760′);INSERT INTO country VALUES (‘TW’,'TAIWAN, PROVINCE OF CHINA’,'Taiwan, Province of China’,'TWN’,’158′);INSERT INTO country VALUES (‘TJ’,'TAJIKISTAN’,'Tajikistan’,'TJK’,’762′);INSERT INTO country VALUES (‘TZ’,'TANZANIA, UNITED REPUBLIC OF’,'Tanzania, United Republic of’,'TZA’,’834′);INSERT INTO country VALUES (‘TH’,'THAILAND’,'Thailand’,'THA’,’764′);INSERT INTO country VALUES (‘TL’,'TIMOR-LESTE’,'Timor-Leste’,NULL,NULL);INSERT INTO country VALUES (‘TG’,'TOGO’,'Togo’,'TGO’,’768′);INSERT INTO country VALUES (‘TK’,'TOKELAU’,'Tokelau’,'TKL’,’772′);INSERT INTO country VALUES (‘TO’,'TONGA’,'Tonga’,'TON’,’776′);INSERT INTO country VALUES (‘TT’,'TRINIDAD AND TOBAGO’,'Trinidad and Tobago’,'TTO’,’780′);INSERT INTO country VALUES (‘TN’,'TUNISIA’,'Tunisia’,'TUN’,’788′);INSERT INTO country VALUES (‘TR’,'TURKEY’,'Turkey’,'TUR’,’792′);INSERT INTO country VALUES (‘TM’,'TURKMENISTAN’,'Turkmenistan’,'TKM’,’795′);INSERT INTO country VALUES (‘TC’,'TURKS AND CAICOS ISLANDS’,'Turks and Caicos Islands’,'TCA’,’796′);INSERT INTO country VALUES (‘TV’,'TUVALU’,'Tuvalu’,'TUV’,’798′);INSERT INTO country VALUES (‘UG’,'UGANDA’,'Uganda’,'UGA’,’800′);INSERT INTO country VALUES (‘UA’,'UKRAINE’,'Ukraine’,'UKR’,’804′);INSERT INTO country VALUES (‘AE’,'UNITED ARAB EMIRATES’,'United Arab Emirates’,'ARE’,’784′);INSERT INTO country VALUES (‘GB’,'UNITED KINGDOM’,'United Kingdom’,'GBR’,’826′);INSERT INTO country VALUES (‘US’,'UNITED STATES’,'United States’,'USA’,’840′);INSERT INTO country VALUES (‘UM’,'UNITED STATES MINOR OUTLYING ISLANDS’,'United States Minor Outlying Islands’,NULL,NULL);INSERT INTO country VALUES (‘UY’,'URUGUAY’,'Uruguay’,'URY’,’858′);INSERT INTO country VALUES (‘UZ’,'UZBEKISTAN’,'Uzbekistan’,'UZB’,’860′);INSERT INTO country VALUES (‘VU’,'VANUATU’,'Vanuatu’,'VUT’,’548′);INSERT INTO country VALUES (‘VE’,'VENEZUELA’,'Venezuela’,'VEN’,’862′);INSERT INTO country VALUES (‘VN’,'VIET NAM’,'Viet Nam’,'VNM’,’704′);INSERT INTO country VALUES (‘VG’,'VIRGIN ISLANDS, BRITISH’,'Virgin Islands, British’,'VGB’,’092′);INSERT INTO country VALUES (‘VI’,'VIRGIN ISLANDS, U.S.’,'Virgin Islands, U.s.’,'VIR’,’850′);INSERT INTO country VALUES (‘WF’,'WALLIS AND FUTUNA’,'Wallis and Futuna’,'WLF’,’876′);INSERT INTO country VALUES (‘EH’,'WESTERN SAHARA’,'Western Sahara’,'ESH’,’732′);INSERT INTO country VALUES (‘YE’,'YEMEN’,'Yemen’,'YEM’,’887′);INSERT INTO country VALUES (‘ZM’,'ZAMBIA’,'Zambia’,'ZMB’,’894′);INSERT INTO country VALUES (‘ZW’,'ZIMBABWE’,'Zimbabwe’,'ZWE’,’716′);
SQL Script For Country Table (DDL and DML)
Oct 7th
The following SQL scripts below create the standard country and country code.
- Includes most of if not all countries (238) as according to ISO and standards bodies
- Provides a country code (US), and the country in full (United States Of America), properly escaped.
- Runs in MySQL DBMS
I have sticked to using (and updating/refining the list) for most production settings.
Correct and current as of October 8, 2010.
# Dumping structure for table abc.country_matrix
DROP TABLE IF EXISTS `country_matrix`;
CREATE TABLE IF NOT EXISTS `country_matrix` (
`country_id` smallint(5) unsigned NOT NULL,
`country` varchar(100) NOT NULL,
`country_code` varchar(2) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Dumping data for table abc.country_matrix: 238 rows
/*!40000 ALTER TABLE `country_matrix` DISABLE KEYS */;
INSERT INTO `country_matrix` (`country_id`, `country`, `country_code`) VALUES (1, ‘Singapore’, ‘SG’), (2, ‘China’, ‘CN’), (3, ‘Malaysia’, ‘MY’), (4, ‘United States’, ‘US’), (5, ‘Canada’, ‘CA’), (6, ‘Albania’, ‘AL’), (7, ‘Algeria’, ‘DZ’), (8, ‘Amerin Samoa’, ‘DS’), (9, ‘Andorra’, ‘AD’), (10, ‘Angola’, ‘AO’), (11, ‘Anguilla’, ‘AI’), (12, ‘Antarctica’, ‘AQ’), (13, ‘Antigua and/or Barbuda’, ‘AG’), (14, ‘Argentina’, ‘AR’), (15, ‘Armenia’, ‘AM’), (16, ‘Aruba’, ‘AW’), (17, ‘Austria’, ‘AT’), (18, ‘Azerbaijan’, ‘AZ’), (19, ‘Bahamas’, ‘BS’), (20, ‘Bahrain’, ‘BH’), (21, ‘Bangladesh’, ‘BD’), (22, ‘Barbados’, ‘BB’), (23, ‘Belarus’, ‘BY’), (24, ‘Belgium’, ‘BE’), (25, ‘Belize’, ‘BZ’), (26, ‘Benin’, ‘BJ’), (27, ‘Bermuda’, ‘BM’), (28, ‘Bhutan’, ‘BT’), (29, ‘Bolivia’, ‘BO’), (30, ‘Bosnia and Herzegovina’, ‘BA’), (31, ‘Botswana’, ‘BW’), (32, ‘Bouvet Island’, ‘BV’), (33, ‘Brazil’, ‘BR’), (34, ‘British lndian Ocean Territory’, ‘IO’), (35, ‘Brunei Darussalam’, ‘BN’), (36, ‘Bulgaria’, ‘BG’), (37, ‘Burkina Faso’, ‘BF’), (38, ‘Burundi’, ‘BI’), (39, ‘Cambodia’, ‘KH’), (40, ‘Cameroon’, ‘CM’), (41, ‘Cape Verde’, ‘CV’), (42, ‘Cayman Islands’, ‘KY’), (43, ‘Central African Republic’, ‘CF’), (44, ‘Chad’, ‘TD’), (45, ‘Chile’, ‘CL’), (46, ‘Christmas Island’, ‘CX’), (47, ‘Cocos (Keeling) Islands’, ‘CC’), (48, ‘Colombia’, ‘CO’), (49, ‘Comoros’, ‘KM’), (50, ‘Congo’, ‘CG’), (51, ‘Cook Islands’, ‘CK’), (52, ‘Costa Rica’, ‘CR’), (53, ‘Croatia (Hrvatska)’, ‘HR’), (54, ‘Cuba’, ‘CU’), (55, ‘Cyprus’, ‘CY’), (56, ‘Czech Republic’, ‘CZ’), (57, ‘Denmark’, ‘DK’), (58, ‘Djibouti’, ‘DJ’), (59, ‘Dominica’, ‘DM’), (60, ‘Dominican Republic’, ‘DO’), (61, ‘East Timor’, ‘TP’), (62, ‘Ecudaor’, ‘EC’), (63, ‘Egypt’, ‘EG’), (64, ‘El Salvador’, ‘SV’), (65, ‘Equatorial Guinea’, ‘GQ’), (66, ‘Eritrea’, ‘ER’), (67, ‘Estonia’, ‘EE’), (68, ‘Ethiopia’, ‘ET’), (69, ‘Falkland Islands (Malvinas)’, ‘FK’), (70, ‘Faroe Islands’, ‘FO’), (71, ‘Fiji’, ‘FJ’), (72, ‘Finland’, ‘FI’), (73, ‘France’, ‘FR’), (74, ‘France, Metropolitan’, ‘FX’), (75, ‘French Guiana’, ‘GF’), (76, ‘French Polynesia’, ‘PF’), (77, ‘French Southern Territories’, ‘TF’), (78, ‘Gabon’, ‘GA’), (79, ‘Gambia’, ‘GM’), (80, ‘Georgia’, ‘GE’), (81, ‘Germany’, ‘DE’), (82, ‘Ghana’, ‘GH’), (83, ‘Gibraltar’, ‘GI’), (84, ‘Greece’, ‘GR’), (85, ‘Greenland’, ‘GL’), (86, ‘Grenada’, ‘GD’), (87, ‘Guadeloupe’, ‘GP’), (88, ‘Guam’, ‘GU’), (89, ‘Guatemala’, ‘GT’), (90, ‘Guinea’, ‘GN’), (91, ‘Guinea-Bissau’, ‘GW’), (92, ‘Guyana’, ‘GY’), (93, ‘Haiti’, ‘HT’), (94, ‘Heard and Mc Donald Islands’, ‘HM’), (95, ‘Honduras’, ‘HN’), (96, ‘Hong Kong’, ‘HK’), (97, ‘Hungary’, ‘HU’), (98, ‘Iceland’, ‘IS’), (99, ‘India’, ‘IN’), (100, ‘Indonesia’, ‘ID’), (101, ‘Iran (Islamic Republic of)’, ‘IR’), (102, ‘Iraq’, ‘IQ’), (103, ‘Ireland’, ‘IE’), (104, ‘Israel’, ‘IL’), (105, ‘Italy’, ‘IT’), (106, ‘Ivory Coast’, ‘CI’), (107, ‘Jamaica’, ‘JM’), (108, ‘Japan’, ‘JP’), (109, ‘Jordan’, ‘JO’), (110, ‘Kazakhstan’, ‘KZ’), (111, ‘Kenya’, ‘KE’), (112, ‘Kiribati’, ‘KI’), (113, ‘Korea, Democratic People\’\'\’\'s Republic of’, ‘KP’), (114, ‘Korea, Republic of’, ‘KR’), (115, ‘Kuwait’, ‘KW’), (116, ‘Kyrgyzstan’, ‘KG’), (117, ‘Lao People\’\'\’\'s Democratic Republic’, ‘LA’), (118, ‘Latvia’, ‘LV’), (119, ‘Lebanon’, ‘LB’), (120, ‘Lesotho’, ‘LS’), (121, ‘Liberia’, ‘LR’), (122, ‘Libyan Arab Jamahiriya’, ‘LY’), (123, ‘Liechtenstein’, ‘LI’), (124, ‘Lithuania’, ‘LT’), (125, ‘Luxembourg’, ‘LU’), (126, ‘Macau’, ‘MO’), (127, ‘Macedonia’, ‘MK’), (128, ‘Madagascar’, ‘MG’), (129, ‘Malawi’, ‘MW’), (130, ‘New Zealand’, ‘NZ’), (131, ‘Maldives’, ‘MV’), (132, ‘Mali’, ‘ML’), (133, ‘Malta’, ‘MT’), (134, ‘Marshall Islands’, ‘MH’), (135, ‘Martinique’, ‘MQ’), (136, ‘Mauritania’, ‘MR’), (137, ‘Mauritius’, ‘MU’), (138, ‘Mayotte’, ‘TY’), (139, ‘Mexico’, ‘MX’), (140, ‘Micronesia, Federated States of’, ‘FM’), (141, ‘Moldova, Republic of’, ‘MD’), (142, ‘Monaco’, ‘MC’), (143, ‘Mongolia’, ‘MN’), (144, ‘Montserrat’, ‘MS’), (145, ‘Morocco’, ‘MA’), (146, ‘Mozambique’, ‘MZ’), (147, ‘Myanmar’, ‘MM’), (148, ‘Namibia’, ‘NA’), (149, ‘Nauru’, ‘NR’), (150, ‘Nepal’, ‘NP’), (151, ‘Netherlands’, ‘NL’), (152, ‘Netherlands Antilles’, ‘AN’), (153, ‘New Caledonia’, ‘NC’), (154, ‘Nicaragua’, ‘NI’), (155, ‘Niger’, ‘NE’), (156, ‘Nigeria’, ‘NG’), (157, ‘Niue’, ‘NU’), (158, ‘Norfork Island’, ‘NF’), (159, ‘Northern Mariana Islands’, ‘MP’), (160, ‘Norway’, ‘NO’), (161, ‘Oman’, ‘OM’), (162, ‘Pakistan’, ‘PK’), (163, ‘Palau’, ‘PW’), (164, ‘Panama’, ‘PA’), (165, ‘Papua New Guinea’, ‘PG’), (166, ‘Paraguay’, ‘PY’), (167, ‘Peru’, ‘PE’), (168, ‘Philippines’, ‘PH’), (169, ‘Pitcairn’, ‘PN’), (170, ‘Poland’, ‘PL’), (171, ‘Portugal’, ‘PT’), (172, ‘Puerto Rico’, ‘PR’), (173, ‘Qatar’, ‘QA’), (174, ‘Reunion’, ‘RE’), (175, ‘Romania’, ‘RO’), (176, ‘Russian Federation’, ‘RU’), (177, ‘Rwanda’, ‘RW’), (178, ‘Saint Kitts and Nevis’, ‘KN’), (179, ‘Saint Lucia’, ‘LC’), (180, ‘Saint Vincent and the Grenadines’, ‘VC’), (181, ‘Samoa’, ‘WS’), (182, ‘San Marino’, ‘SM’), (183, ‘Sao Tome and Principe’, ‘ST’), (184, ‘Saudi Arabia’, ‘SA’), (185, ‘Senegal’, ‘SN’), (186, ‘Seychelles’, ‘SC’), (187, ‘Sierra Leone’, ‘SL’), (188, ‘Slovakia’, ‘SK’), (189, ‘Slovenia’, ‘SI’), (190, ‘Solomon Islands’, ‘SB’), (191, ‘Somalia’, ‘SO’), (192, ‘South Africa’, ‘ZA’), (193, ‘South Georgia South Sandwich Islands’, ‘GS’), (194, ‘Spain’, ‘ES’), (195, ‘Sri Lanka’, ‘LK’), (196, ‘St. Helena’, ‘SH’), (197, ‘St. Pierre and Miquelon’, ‘PM’), (198, ‘Sudan’, ‘SD’), (199, ‘Suriname’, ‘SR’), (200, ‘Svalbarn and Jan Mayen Islands’, ‘SJ’), (201, ‘Swaziland’, ‘SZ’), (202, ‘Sweden’, ‘SE’), (203, ‘Switzerland’, ‘CH’), (204, ‘Syrian Arab Republic’, ‘SY’), (205, ‘Taiwan’, ‘TW’), (206, ‘Tajikistan’, ‘TJ’), (207, ‘Tanzania, United Republic of’, ‘TZ’), (208, ‘Thailand’, ‘TH’), (209, ‘Togo’, ‘TG’), (210, ‘Tokelau’, ‘TK’), (211, ‘Tonga’, ‘TO’), (212, ‘Trinidad and Tobago’, ‘TT’), (213, ‘Tunisia’, ‘TN’), (214, ‘Turkey’, ‘TR’), (215, ‘Turkmenistan’, ‘TM’), (216, ‘Turks and Caicos Islands’, ‘TC’), (217, ‘Tuvalu’, ‘TV’), (218, ‘Uganda’, ‘UG’), (219, ‘Ukraine’, ‘UA’), (220, ‘United Arab Emirates’, ‘AE’), (221, ‘United Kingdom’, ‘GB’), (222, ‘Australia’, ‘AU’), (223, ‘Uruguay’, ‘UY’), (224, ‘Uzbekistan’, ‘UZ’), (225, ‘Vanuatu’, ‘VU’), (226, ‘Vatican City State’, ‘VA’), (227, ‘Venezuela’, ‘VE’), (228, ‘Vietnam’, ‘VN’), (229, ‘Virigan Islands (British)’, ‘VG’), (230, ‘Virgin Islands (U.S.)’, ‘VI’), (231, ‘Wallis and Futuna Islands’, ‘WF’), (232, ‘Western Sahara’, ‘EH’), (233, ‘Yemen’, ‘YE’), (234, ‘Yugoslavia’, ‘YU’), (235, ‘Zaire’, ‘ZR’), (236, ‘Zambia’, ‘ZM’), (237, ‘Zimbabwe’, ‘ZW’), (238, ‘Afghanistan’, ‘AF’);
