-- Spojte všechny záznamy z tabulek "města" a "státy". Výsledek uložte jako nový VIEW. (JOIN, VIEW)
CREATE VIEW mesta_zeme AS
SELECT
city.id AS city_id,
city.name AS city_name,
city.countrycode AS city_countrycode,
city.district AS city_district,
city.population AS city_population,
country.code AS country_code,
country.name AS country_name,
country.continent AS country_continent,
country.region AS country_region,
country.surfacearea AS country_surfacearea,
country.indepyear AS country_indepyear,
country.population AS country_population,
country.lifeexpectancy AS country_lifeexpectancy,
country.gnp AS country_gnp,
country.gnpold AS country_gnpold,
country.localname AS country_localname,
country.governmentform AS country_governmentform,
country.headofstate AS country_headofstate,
country.capital AS country_capital,
country.code2 AS country_code2
 FROM city
  FULL JOIN country ON (country.code = city.countrycode);

--Vyberte město, počet jeho obyvatel a stát ve kterém se nachází. Výsledek uložte jako pohled. (JOIN, VIEW)
CREATE VIEW mesto_se_zemi AS
SELECT
city.name AS city_name,
city.population AS city_population,
country.name AS country_name,
 FROM city
  INNER JOIN country ON (country.code = city.countrycode);

--Zobrazte všechny kontinenty, které jsou v databázi. (DISTINCT)
SELECT DISTINCT continent FROM country;

--Zobrazte všechny neoficiální jazyky a jim příslušné země a seřaďte je podle procenta použití od největšího. (JOIN)
SELECT
language, percentage, name
 FROM countrylanguage
  INNER JOIN country ON (country.code = countrylanguage.countrycode)
  WHERE isofficial = false
  ORDER BY percentage DESC;

--Zobrazte kontinenty, jejich populaci a počet států, které v nich leží. (SUM)
SELECT continent AS jmeno, SUM(population) AS pocet_obyvatel, COUNT(code) AS pocet_statu FROM country
GROUP BY jmeno
ORDER BY pocet_obyvatel DESC;

--Jaké státy mají 3 a více oficiálních jazyků? (Je jich 8)
CREATE VIEW jazyky AS
SELECT name AS jmeno_statu, COUNT(language) AS pocet_jazyku
FROM countrylanguage
INNER JOIN country ON (country.code = countrylanguage.countrycode)
WHERE isofficial = true
GROUP BY jmeno_statu;

SELECT * FROM jazyky
WHERE pocet_jazyku >=3
ORDER BY pocet_jazyku DESC;

SELECT continent, COUNT(city.id) AS pocet_mest
FROM city
INNER JOIN country ON (country.code = city.countrycode)
GROUP BY continent
ORDER BY pocet_mest DESC;

--Jaké státy mají 3 a více oficiálních jazyků? (Je jich 8) - HAVING clause
-- The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SELECT
name AS jmeno_statu, COUNT(language) AS pocet_jazyku
 FROM countrylanguage
  INNER JOIN country ON (country.code = countrylanguage.countrycode)
  WHERE isofficial = true
  GROUP BY jmeno_statu
  having COUNT(language) >= 3
  ORDER BY pocet_jazyku DESC;

--Kolik měst v databáze je z Evropy?
SELECT SUM(country_population) AS pop_podle_stat, SUM(city_population) AS pop_podle_mest
FROM mesta_zeme
WHERE country_continent = 'Europe';

--Zobrazte populaci evropy a sumu populace jednotlivých měst v Evropě. Liší se nějak?
SELECT SUM(country_population) AS pop_podle_stat, SUM(city_population) AS pop_podle_mest
FROM mesta_zeme
WHERE country_continent = 'Europe';

SELECT SUM(country.population) AS pop_podle_stat, SUM(city.population) AS pop_podle_mest
 FROM city
  FULL JOIN country ON (country.code = city.countrycode)
WHERE continent = 'Europe';

--Zobrazte populaci evropy a sumu populace jednotlivých měst v Evropě. Alternativní řešení.
SELECT country.name AS country_name, country.population AS country_population, SUM(city.population) AS city_population
 FROM city
  FULL JOIN country ON (country.code = city.countrycode)
WHERE country_continent = 'Europe'
GROUP BY country_name, country_population
ORDER BY country_population DESC;