aula-mysql-i9.sql

1.
select * from city order by population desc limit 5;

+------+-----------------+-------------+-------------+------------+
| ID   | Name            | CountryCode | District    | Population |
+------+-----------------+-------------+-------------+------------+
| 1024 | Mumbai (Bombay) | IND         | Maharashtra |   10500000 |
| 2331 | Seoul           | KOR         | Seoul       |    9981619 |
|  206 | S Paulo         | BRA         | S Paulo     |    9968485 |
+------+-----------------+-------------+-------------+------------+

2.
select Name, GNP, GNPOld, (GNP - GNPOld) as GNPDiff from country order by GNP desc; # having GNPDiff > 0;
select Name, GNP, GNPOld, (GNP - GNPOld) as GNPDiff from country where (GNP - GNPOld) > 0 order by GNP desc limit 3;
select Name, GNP, GNPOld from country where GNP > GNPOld order by GNP desc limit 3;

+---------------+------------+------------+-----------+
| Name          | GNP        | GNPOld     | GNPDiff   |
+---------------+------------+------------+-----------+
| United States | 8510700.00 | 8110900.00 | 399800.00 |
| Germany       | 2133367.00 | 2102826.00 |  30541.00 |
| France        | 1424285.00 | 1392448.00 |  31837.00 |
+---------------+------------+------------+-----------+

3.
select GovernmentForm, Population from country group by GovernmentForm order by sum(Population) desc limit 5;

+-------------------------+------------+
| GovernmentForm          | Population |
+-------------------------+------------+
| Federal Republic        |   37032000 |
| Republic                |   12878000 |
| PeoplesRepublic         | 1277558000 |
| Constitutional Monarchy |      68000 |
| Socialistic Republic    |   11201000 |
+-------------------------+------------+

4.
select ANY_VALUE(`GovernmentForm`), count(`GovernmentForm`) from country group by GovernmentForm order by count(`GovernmentForm`) desc limit 5;

+-------------------------------+-------------------------+
| ANY_VALUE(`GovernmentForm`)   | count(`GovernmentForm`) |
+-------------------------------+-------------------------+
| Republic                      |                     122 |
| Constitutional Monarchy       |                      29 |
| Federal Republic              |                      15 |
| Dependent Territory of the UK |                      12 |
| Monarchy                      |                       5 |
+-------------------------------+-------------------------+

5.
select country.Continent,
(select maxCity.Name from city as maxCity where maxCity.Population = max(city.Population)) as maxCityName,
max(city.Population) as maxPopulation,
(select maxCountry.Population from country as maxCountry where maxCountry.Code = (select maxCity.CountryCode from city as maxCity where maxCity.Population = max(city.Population))) as countryPopulation,
(maxPopulation / countryPopulation) as percentage
from country
join city on country.Code = city.CountryCode
group by country.Continent;

+---------------+------------------+---------------+------+-------------------+
| Continent     | maxCityName      | maxPopulation | code | countryPopulation |
+---------------+------------------+---------------+------+-------------------+
| North America | Ciudad de Mico   |       8591309 | MEX  |          98881000 |
| Asia          | Mumbai (Bombay)  |      10500000 | IND  |        1013662000 |
| Africa        | Cairo            |       6789479 | EGY  |          68470000 |
| Europe        | Moscow           |       8389200 | RUS  |         146934000 |
| South America | S Paulo          |       9968485 | BRA  |         170115000 |
| Oceania       | Sydney           |       3276207 | AUS  |          18886000 |
+---------------+------------------+---------------+------+-------------------+

6.


7.
select count(distinct District) from city;

+--------------------------+
| count(distinct District) |
+--------------------------+
|                     1367 |
+--------------------------+

8.
  select countrylanguage.Language, count(country.Name) as NumberLanguages
    from countrylanguage
    join country
      on countrylanguage.CountryCode = country.Code
group by countrylanguage.Language
  having NumberLanguages >= 10
order by count(country.Name) desc;

  select countrylanguage.Language, count(0) as NumberLanguages
    from countrylanguage
group by countrylanguage.Language
  having NumberLanguages >= 10
order by count(countrylanguage.Language) desc;

9.
  select country.Name, GROUP_CONCAT(countrylanguage.Language ORDER BY countrylanguage.Language),
         count(countrylanguage.Language) as languageCount
    from country
    join countrylanguage
      on country.Code = countrylanguage.CountryCode
   where country.Continent = 'Europe'
     and country.Name like '%c%'
     and country.Name not like '%a%'
group by country.Name
  having languageCount > 2;

10.
  select country.Name, GROUP_CONCAT(countrylanguage.Language order by countrylanguage.Language)
    from country
    join countrylanguage
      on country.Code = countrylanguage.CountryCode
   where country.Name = 'Sweden'
group by country.Name;

11.
   select country.Name, count(countrylanguage.Language) as languageCount
     from country
left join countrylanguage
       on country.Code = countrylanguage.CountryCode
 group by country.Name
   having languageCount = 0;

12.
  select country.Name, country.Population,
         sum(city.Population) as sumCityPopulation, (sum(city.Population) / country.Population) * 100 as Percentage
    from country
    join city
      on country.Code = city.CountryCode
group by country.Name
  having country.Population >= sumCityPopulation
     and sumCityPopulation / country.Population >= 0.8;

13.
# estou testando com espanhol / francês porque não vi nenhum país espanhol / inglês
   select country.Continent, country.Name, langSpanish.Language, langAnother.Language
     from country
     join countrylanguage as langSpanish
       on country.Code = langSpanish.CountryCode
      and langSpanish.Language = 'Spanish'
left join countrylanguage as langAnother
       on country.Code = langAnother.CountryCode
      and langAnother.Language = 'French'
    where country.Continent = 'Europe'
      and langAnother.Language is null;

   select country.Continent, country.Name, langSpanish.Language, langAnother.Language
     from country
     join countrylanguage as langSpanish
       on country.Code = langSpanish.CountryCode
      and langSpanish.Language = 'Spanish'
left join countrylanguage as langAnother
       on country.Code = langAnother.CountryCode
      and langAnother.Language = 'English'
    where country.Continent = 'Europe'
      and langAnother.Language is null;

14. (????)
# Todas cidades, países e continentes.
select concat(city.Name, country.Continent) as CityToContinent, city.Name, country.Continent
  from city
  join country
    on city.CountryCode = country.Code;

# ----

# Todas as cidades que aparecem em três ou mais países.
  select Name, count(Name) as countries
    from city
group by Name
  having countries >= 3;

# Todos os continentes das cidades que aparecem em três ou mais países. Usa a query de cima como subquery.
select city.Name, country.Continent
  from city
  join country
    on city.CountryCode = country.Code
 where city.Name in
       (select Name
          from city
      group by Name
        having count(Name) >= 3);

# Cidades e continentes agrupados pelo nome da cidade e continente (apenas as cidades que aparecem em três ou mais países).
  select city.Name, country.Continent
    from city
    join country
      on city.CountryCode = country.Code
   where city.Name in
         ( select Name
             from city
         group by Name
           having count(Name) >= 3)
group by city.Name, country.Continent
order by city.Name;

# ----

# Cidades e continentes agrupados pelo nome da cidade e continente (todas as cidades).
  select city.Name as CityName, country.Continent as CityContinent
    from city
    join country
      on city.CountryCode = country.Code
group by city.Name, country.Continent
order by city.Name;

# Todas as cidades que aparecem em 3 ou mais continentes. Usa a query de cima como subquery.
  select CityName, count(CityContinent) as CityContinentNumber
    from ( select city.Name as CityName, country.Continent as CityContinent
             from city
             join country
               on city.CountryCode = country.Code
         group by city.Name, country.Continent
         order by city.Name) as CityContinents
group by CityName
  having CityContinentNumber >= 3;

15.
# Todas as cidades que aparecem em dois ou mais países.
  select Name, count(Name) as countries
    from city
group by Name, CountryCode
  having countries >= 3;

SET sql_mode = '';