solutions.sql

-- a) the most populated city in each country.
SELECT CountryCode, MAX(Population) FROM city GROUP BY CountryCode;

-- b) the second most populated city in each country.
SELECT
    city.name, MAX(city.population)
FROM city
LEFT OUTER JOIN (
    SELECT *, MAX(population)
        FROM city group by CountryCode
    ) AS c
    ON c.id = city.id
WHERE c.id is NULL
GROUP BY city.CountryCode;

-- c) the most populated city in each continent.
SELECT city.Name, country.Continent, MAX(city.Population)
FROM city
JOIN country on country.Code = city.CountryCode
GROUP BY country.Continent;

-- d) the most populated country in each continent.
SELECT Name, MAX(Population) FROM country GROUP BY Continent;

-- e) the most populated continent.
SELECT Continent, SUM(population) AS s
FROM country
GROUP BY Continent
ORDER BY s DESC LIMIT 1;

-- f) the number of people speaking each language.
SELECT Language, SUM(country.Population * l.Percentage / 100) AS s
FROM country
INNER JOIN countrylanguage AS l ON l.countryCode = country.Code
GROUP BY l.Language;

-- g) the most spoken language in each continent.
SELECT Language, Continent, MAX(speakers) FROM (
    SELECT Language, Continent, SUM(Population * Percentage / 100) AS speakers FROM country
        INNER JOIN countrylanguage AS l
        ON l.CountryCode = country.Code
        GROUP BY Language, Continent
        ORDER BY speakers DESC
) AS t
GROUP BY Continent;

-- h) number of languages that they are official language of at least one country.
SELECT language, COUNT(*) as `count`
FROM countrylanguage
WHERE IsOfficial = 'T'
GROUP BY language;

-- i) the most spoken official language based on each continent. (the language that has the highest number of people talking as their mother tongue)
SELECT Language, Continent, MAX(speakers) FROM (
    SELECT Language, Continent, SUM(Population * Percentage / 100) AS speakers FROM country
        INNER JOIN countrylanguage AS l
        ON l.CountryCode = country.Code
        WHERE IsOfficial = 'T'
        GROUP BY Language, Continent
        ORDER BY speakers DESC
) AS t
GROUP BY Continent;

-- j) the country with the most (number of) unofficial languages based on each continent. (no matter how many people talking that language)
SELECT Continent, t.Name, MAX(t.counter) FROM (
    SELECT CountryCode, country.Name, country.Continent, count(*) AS counter FROM countrylanguage
    INNER JOIN country ON country.Code = countrylanguage.countryCode
    WHERE IsOfficial = 'F' GROUP BY CountryCode ORDER BY counter DESC
) AS t GROUP BY Continent;

-- k) the countries that their capital is not the most populated city in the country.
SELECT country.Name, C.Name, capital, ID, MAX(C.Population) FROM (
    SELECT * FROM city ORDER BY Population DESC) as C
INNER JOIN country ON country.Code = C.CountryCode
GROUP BY CountryCode
HAVING capital != C.ID;

-- l) the countries with population smaller than Russia but bigger than Denmark.
SELECT country.Name FROM country
JOIN country AS russia ON russia.Code = "RUS"
JOIN country AS denmark ON denmark.code = "DNK"
WHERE country.population < russia.population AND country.population > denmark.population;