world-logstash.sql

select
    co.Code 'code',
    co.name 'name',
    co.Continent 'continent',
    co.region 'region',
    co.SurfaceArea 'surface_area',
    co.IndepYear 'year_of_independence',
    co.Population 'population',
    co.LifeExpectancy 'life_expectancy',
    co.GovernmentForm 'government_form',
    co.Code2 'iso_code',
    capital.ID 'capital_id',
    capital.Name 'capital_name',
    capital.District 'capital_district',
    capital.Population 'capital_population',
    ci.Name 'cities_name',
    ci.id 'cities_id',
    ci.District 'cities_district',
    ci.Population 'cities_population',
    cl.Language 'languages_language',
    cl.IsOfficial 'languages_official',
    cl.Percentage 'languages_percentage'
from country co
left outer join city capital ON capital.ID = co.Capital
left outer join city ci ON ci.CountryCode = co.Code
left outer join countrylanguage cl on cl.CountryCode = co.Code