use world;

show tables;

select * from country;
select * from city;
select * from countrylanguage;

-- COMANDOS DQL (DATA QUERY LANGUAGE)

select 
continent, name from country
order by name desc;

select 
continent, name, population
from country
where
population >= 100000000;

select 
continent, name, population
from country
where
population between 200000000 and 300000000;

select
continent, name, population
from country
where
population >= 200000000 and population <= 300000000;

select 
continent, name 
from country
where
continent = "europe";

select 
name from country
where
name like "l%";

select 
name from country
where
name like "_e%l";

select 
continent, name from country
where
continent = 'south america';

select 
continent, name, indepyear
from country
where
indepyear is not null;

-- funções de agrupamento --

-- count(), sum(), max(), min(), avg()

select
count(*) from country;

select
continent, count(*)
from country
group by continent;

select
continent, count(*)
from country
where
lifeexpectancy >= 75
group by continent;

select
language, count(*)
from countrylanguage
where
language = 'portuguese' and isofficial = "t"
group by language;

select
sum(population)
from country;

select 
continent, sum(population)
from country
group by continent;

INSTRUÇÕES PARA ENTREGA
- copiar os exercícios da lista para o MySQL Workbench
- escrever o código sql abaixo do enunciado
- após terminar, salvar o arquivo com o seu nome.sql
- enviar pelo MS-TEAMS