Using the Code Tables

Print lists the 7,469 distinct language identifiers used in the current Ethnologue database. Of these, 367 represent extinct languages and the remainder are listed with "living" status.  The following shows the entries for the first six languages identifiers:

LangID CountryID LangStatus Name
------ --------- ---------- ------------- 
aaa    NG        L          Ghotuo
aab    NG        L          Alumu-Tesu
aac    PG        L          Ari
aad    PG        L          Amal
aae    IT        L          Albanian, Arbëreshë
aaf    IN        L          Aranadan

We see that aaa and aab denote living languages spoken in Nigeria, aac and aad denote living languages spoken in Papua New Guinea, and so on. When a language is actually spoken in more than one country, the CountryId gives the country that is considered primary; usually the country of origin or country where most of the speakers are located. lists the two-letter identifier and name for 236 countries of the world. The codes are from the international standard known as ISO 3166-1 (1997. Codes for the representation of names of countries and their subdivisionsPart 1: Country codes. Geneva: International Organization on Standardization. The following shows the entries for the first five codes in the list:

CountryID Name                  Area
--------- --------------------- ----------
AD        Andorra               Europe
AE        United Arab Emirates  Asia
AF        Afghanistan           Asia
AG        Antigua and Barbuda   Americas
AI        Anguilla              Americas

The table can be used to narrow the search for an identifier to a particular country. The user would choose a country from the country list in order to select the appropriate country code. That code would then be used in a SQL query to restrict the language identifier list to just entries for that country. For instance, if the user were interested only in Afghanistan, the following SQL query would return just the table rows for that country:

SELECT * FROM LanguageCodes WHERE CountryID='AF'

Alternatively, the following link to the Ethnologue web site could be used to generate a report listing all the languages for Afghanistan: documents 46,836 distinct names used for the 7,469 languages. The entries in this index of names indicate in which country each name is used. The table thus contains 58,073 records since many of the names are used in more than one country and some are used with more than one language or dialect. The following shows the entries in the name index for the first three language identifiers:

LangID CountryID NameType Name  
------ --------- -------- ------------- 
aaa    NG        L        Ghotuo
aab    NG        D        Alumu
aab    NG        D        Tesu
aab    NG        DA       Arum
aab    NG        L        Alumu-Tesu
aab    NG        LA       Alumu
aab    NG        LA       Arum-Cesu
aab    NG        LA       Arum-Chessu
aab    NG        LA       Arum-Tesu
aac    PG        D        Serea
aac    PG        L        Ari

We see that aaa has just one name, Ghotuo; aab has four alternate names, two dialect names, and an alternate dialect name in addition to its primary name; aac has a dialect name in addition to the primary name of Ari.

The table would be used to implement a search by name. For instance, the following query returns the three-letter codes for all the languages that use the name xyz:

WHERE Name='xyz'

Note that DISTINCT is used since the same language could be known by the same name in multiple countries. To allow the user to verify that a proposed identifier is indeed the right one, the software would offer the following link to the Ethnologue web site to see a report giving detailed information about the selected language (where abc is the proposed three-letter identifier):

Another application of the table is to find all the countries in which a given language is spoken. For instance, the following query returns the names of all the countries in which language abc is spoken:

FROM CountryCodes AS C
JOIN LanguageIndex AS L ON C.CountryID=L.CountryID
WHERE L.LangID='abc'

In this case DISTINCT must be used since a language could have multiple names in a given country.

Finally, the table can be used to find all the languages spoken in a particular country. Whereas the query illustrated previously retrieves all languages whose primary country is Afghanistan, the following query retrieves all languages spoken in Afghanistan:

WHERE CountryID='AF'