There are no notfications.

Database table of countries

Database table of countries
Initial release:
Current release:
Written in: SQL (UTF-8)
Size: 22701 bytes
Available in: English, Icelandic, Spanish
Licence: Database: ODbL
Database contents: DbCL
Documentation: CC BY 4.0
Download: Database-table-of-countries.zip

The database table of countries resource provides country codes, localised names, states of the European Union, subdivisions of the United Kingdom and territories of the United States.

Tables

european_union_states

The european_union_states table contains references to the country codes of the member states of the European Union in the iso_3166_1 table.

Table columns
Name Type Content Reference
state char(2) iso_3166_1.alpha_2_code

iso_3166_1

The iso_3166_1 table contains all codes (alpha 2, alpha 3 and numeric) of countries and territories considered by the ISO 3166-1 standard. The country names can be added to the table separately by executing the respective SQL scripts included in the resource.

Table columns
Name Type Content Reference
alpha_2_code char(2) ISO 3166-1 alpha 2 code.
alpha_3_code char(3) ISO 3166-1 alpha 3 code.
numeric_code char(3) ISO 3166-1 numeric code.

united_kingdom_subdivisions

The united_kingdom_subdivisions table contains all the ISO 3166-2:GB 3-letter codes of the subdivisions of the United Kingdom and their type. The subdivision names can be added to the table separately by executing the respective SQL scripts included in the resource.

Table columns
Name Type Content Reference
subdivision char(3) ISO 3166-2:GB 3-letter code.
type tinyint(3) united_kingdom_subdivision_types.identifier

united_kingdom_subdivision_types

The united_kingdom_subdivision_types table contains the different types of subdivisions of the United Kingdom.

Table columns
Name Type Content Reference
identifier tinyint(3) Unique row identifier.
subdivision_type varchar(8) Subdivision type description.

united_states_territories

The united_states_territories table contains all the ANSI INCITS 38 2-letter codes of the territories of the United States and their type. The territory names can be added to the table separately by executing the respective SQL scripts included in the resource.

Table columns
Name Type Content Reference
territory char(2) ANSI INCITS 38 2-letter code.
type tinyint(3) united_states_territory_types.identifier

united_states_territory_types

The united_states_territory_types table contains the different types of territories of the United States.

Table columns
Name Type Content Reference
identifier tinyint(3) Unique row identifier.
territory_type varchar(16) Territory type description.

Directory contents

Directory listing
  • Database table of countries directory
    • Data directory
      • Creation directory
        • european_union_states.sql file
        • iso_3166_1.sql file
        • iso_3166_1_eng.sql file
        • iso_3166_1_isl.sql file
        • iso_3166_1_spa.sql file
        • united_kingdom_subdivisions.sql file
        • united_kingdom_subdivisions_eng.sql file
        • united_kingdom_subdivisions_isl.sql file
        • united_kingdom_subdivisions_spa.sql file
        • united_kingdom_subdivision_types.sql file
        • united_states_territories.sql file
        • united_states_territories_eng.sql file
        • united_states_territories_isl.sql file
        • united_states_territories_spa.sql file
        • united_states_territory_types.sql file
      • Modification directory
        • iso_3166_1-remove-united-nations-member-states.sql file
        • iso_3166_1-remove-united-nations-nonmember-states.sql file
        • iso_3166_1-remove-united-nations-observer-states.sql file
        • united_states_territories-remove-federal-district.sql file
        • united_states_territories-remove-states.sql file
        • united_states_territories-remove-territory.sql file
    • Testing directory
      • european_union_states.sql file
      • united_kingdom_subdivisions.sql file
      • united_states_territories.sql file

Data (directory)

The Data directory contains all SQL scripts that create and modify the database tables.

Creation (directory)

The Creation directory contains SQL scripts that create tables and references. Unneeded data can be removed by the modification SQL scripts as all available data is inserted.

european_union_states.sql (file)

The european_union_states.sql file contains the european_union_states table.

iso_3166_1.sql (file)

The iso_3166_1.sql file contains the iso_3166_1 table. The iso_3166_1_*.sql files add the respective language columns to the european_union_states table.

united_kingdom_subdivisions.sql (file)

The united_kingdom_subdivisions.sql file contains the united_kingdom_subdivisions table. The united_kingdom_subdivisions_*.sql files add the respective language columns to the united_kingdom_subdivisions table.

united_kingdom_subdivision_types.sql (file)

The united_kingdom_subdivision_types.sql file contains the united_kingdom_subdivision_types table.

united_states_territories.sql (file)

The united_states_territories.sql file contains the united_states_territories table. The united_states_territories_*.sql files add the respective language columns to the united_states_territories table.

united_states_territory_types.sql (file)

The united_states_territory_types.sql file contains the united_states_territory_types table.

Modification (directory)

The Modification directory contains SQL scripts that modify the tables by removing unneeded data as the creation SQL scripts insert all available data.

iso_3166_1-remove-united-nations-*.sql (files)
member-states
Removes all UN member states from the iso_3166_1 table.
nonmember-states
Removes all UN nonmember states from the iso_3166_1 table.
observer-states
Removes all UN observer states from the iso_3166_1 table.
united_states_territories-remove-*.sql (files)
federal-districts
Removes all federal districts from the united_states_territories table.
states
Removes all states from the united_states_territories table.
territories
Removes all territories from the united_states_territories table.

Testing (directory)

The Testing directory contains files used for testing the solution during development.

Licence

The database table of countries resource by eMedia Intellect is licensed under Open Database License. Any rights in individual contents of the database are licensed under Database Contents License.