/* 1. Vypiste 10 krajin s najvacsim poctom miest, celkovy pocet obyvatelov v tychto mestach a rozdiel poctu obyvatelov krajiny a obyvatelov miest (obyvatelia na vidieku) */
\echo 1. Vypiste 10 krajin s najvacsim poctom miest, celkovy pocet obyvatelov v tychto mestach a rozdiel poctu obyvatelov krajiny a obyvatelov miest (obyvatelia na vidieku)*/

select c.name, sum(t.population), c.population-sum(t.population) village
from country c, city t
where c.code = t.countrycode
group by c.code, c.name, c.population
order by count(t.id) desc
limit 10
;

/* 2. Najdite krajiny, kde sa hovori po anglicky, vypiste ake je ich hlavne mesto, jeho pocet obyvatelov a pocet obyvatelov v ostatnych mestach (t.j. pocet obyvatelov v mestach okrem hlavneho mesta krajiny) */
\echo 2. Najdite krajiny, kde sa hovori po anglicky, vypiste ake je ich hlavne mesto, jeho pocet obyvatelov a pocet obyvatelov v ostatnych mestach (t.j. pocet obyvatelov v mestach okrem hlavneho mesta krajiny)*/

select c.name as Country, t.name as Capital, t.population, sum(t2.population)-t.population other_cities
from country c, city t, city t2, countrylanguage l
where c.code = t.countrycode and c.capital = t.id and t2.countrycode = c.code and l.countrycode = c.code and l.language = 'English'
group by c.name, t.name, t.population
;

/* 3. Pre 10 najludnatejsich krajin vypiste vsetky ich okresy, pocet miest v danom okrese a pocet obyvatelov okresu. T.j. vypiste stvorice [Krajina, Okres, Pocet miest, Pocet obyvatelov]. Vystup nech je usporiadany podla poctu obyvatelov okresu a obsahuje max. 100 riadkov.  */

\echo 3. Pre 10 najludnatejsich krajin vypiste vsetky ich okresy, pocet miest v danom okrese a pocet obyvatelov okresu. T.j. vypiste stvorice [Krajina, Okres, Pocet miest, Pocet obyvatelov]. Vystup nech je usporiadany podla poctu obyvatelov okresu a obsahuje max. 100 riadkov. */

create temporary table top10 as (
  select *
  from country c
  order by population desc
  limit 10
);

select t.district, sum(t.id) as city_count, sum(t.population) as district_population
from top10 c, city t
where c.code = t.countrycode
group by t.district
order by sum(t.population)
limit 100
;

/* 4. Vypiste monarchie, v ktorych sa hovori aspon 3 jazykmi (pozor, monarchia je aj text "Monarch (Sultanate)", "Constitutional monarchy" a pod - pouzite like)  */

select distinct c.name
from country c, countrylanguage l1, countrylanguage l2, countrylanguage l3
where c.governmentform like '%onarch%' and c.code = l1.countrycode and c.code = l2.countrycode and c.code = l3.countrycode and l1.language <> l2.language and l1.language <> l3.language and l2.language <> l3.language
;