Sample data for populating an RDBMS

Various data that can serve well for trying out sql, maybe in combination with other geographical data.

t_eu_country

20200631

Table t_eu_country

SQL script

20200530_t_eu_country.sql

Description

This table contains data of the EU member states, plus a few more countries and/or Fürstentums. Data was retrieved from the excellent "Interinstitutional Style Guide" from the "Publications Office of the EU". (see op.europa.eu .. source-131405805 or if this link is expired, do a search on op.europa.eu/en/home). Complementary data (eg name of Iceland) was gotten from the internet.

Table structure

create table t_eu_country (
    cceu varchar(4) primary key,
    cc2 varchar(4),
    cc3 varchar(4),
    short_name varchar(64),
    short_name_en varchar(64),
    official_name_en varchar(256),
    eu_entry_date date,
    eu_exit_date date,
    schengen_implementation_date date
);

create unique index i_eu_country_cc2 on t_eu_country (cc2);
create unique index i_eu_country_cc3 on t_eu_country (cc3);

Sample query

select  cceu, cc2, short_name, eu_entry_date 
from t_eu_country
where current_date between eu_entry_date and eu_exit_date;

 cceu | cc2 |   short_name    | eu_entry_date 
------+-----+-----------------+---------------
 BE   | BE  | Belgique/België | 1958-01-01
 DE   | DE  | Deutschland     | 1958-01-01
 FR   | FR  | France          | 1958-01-01
 IT   | IT  | Italia          | 1958-01-01
 LU   | LU  | Luxembourg      | 1958-01-01
 NL   | NL  | Nederland       | 1958-01-01
 DK   | DK  | Danmark         | 1973-01-01
 IE   | IE  | Éire/Ireland    | 1973-01-01
 EL   | GR  | Ελλάδα          | 1981-01-01
 ES   | ES  | España          | 1986-01-01
 PT   | PT  | Portugal        | 1986-01-01
 AT   | AT  | Österreich      | 1995-01-01
 FI   | FI  | Suomi/Finland   | 1995-01-01
 SE   | SE  | Sverige         | 1995-01-01
 CY   | CY  | Κύπρος          | 2004-05-01
 CZ   | CZ  | Česko           | 2004-05-01
 EE   | EE  | Eesti           | 2004-05-01
 HU   | HU  | Magyarország    | 2004-05-01
 LT   | LT  | Lietuva         | 2004-05-01
 LV   | LV  | Latvija         | 2004-05-01
 MT   | MT  | Malta           | 2004-05-01
 PL   | PL  | Polska          | 2004-05-01
 SI   | SI  | Slovenija       | 2004-05-01
 SK   | SK  | Slovensko       | 2004-05-01
 BG   | BG  | България        | 2007-01-01
 RO   | RO  | România         | 2007-01-01
 HR   | HR  | Hrvatska        | 2013-07-01
(27 rows)

Notes and watchouts

CCEU is not always CC2

Primary key cceu is the 2-letter code designated to a country, as used within the European institutions. It differs from other public data sets (eg. from UN), for these records:

select cceu, cc2, cc3, short_name from t_eu_country where cceu!=cc2;

 cceu | cc2 | cc3 |   short_name    
------+-----+-----+-----------------
 UK   | GB  | GBR | United  Kingdom
 EL   | GR  | GRC | Ελλάδα

There are more countries in this table, than just the EU members.

Or you could ask: what are Switzerland, Norway, Iceland and Liechtenstein doing here? Answer: They are part of the Schengen area.

select  cceu, short_name, schengen_implementation_date
from t_eu_country
where eu_entry_date='9999-12-31' 
;

 cceu |  short_name   | schengen_implementation_date 
------+---------------+------------------------------
 CH   | Switzerland   | 2008-12-12
 IS   | Ísland        | 2001-03-25
 LI   | Liechtenstein | 2011-12-19
 NO   | Norge         | 2001-03-25

So if you want a list only of the member states, always include this clause:

where current_date between eu_entry_date and eu_exit_date;