-- DQL - DATA QUERY LANGUAGE
-- LINGUAGEM DE CONSULTA ESTRUTURADA
-- SELECT + CLÁUSULAS (WHERE)

use world;
show tables;

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

-- exibir todos os dados dos países asiáticos
select * from country
where
continent = 'asia';

-- exibir campos específicos em uma consulta 
select
region, continent, name, population
from country
where
continent = 'oceania';

select
countrycode, language, isofficial
from countrylanguage
where
isofficial = 't';

-- criando ALIAS para os campos de uma tabela
select
countrycode as 'ID',
language as 'idioma',
isofficial as 'oficial'
from countrylanguage;

-- exibir dados à partir de parâmetros numéricos
select
continent as 'continente',
name as 'país',
population as 'população'
from country
where
population >= 150000000 and
population <= 200000000;

select
continent as 'continente',
name as 'país', 
population as 'população'
from country
where
population between 150000000 and 200000000;

-- exibir dados com parâmetros combinados

select
name as 'país', 
population as 'população',
lifeexpectancy as 'expectativa de vida'
from country
where
continent = 'europe' and
population >= 50000000 and
lifeexpectancy >= 70;

-- consultas usando parâmetros aproximados
select
continent as 'continente', 
name as 'país'
from country
where
continent not like '%america%';

select
continent as 'continente',
name as 'país',
indepyear as 'ano independência'
from country
where
indepyear is not null;

-- FUNÇÕES DE AGRUPAMENTO
-- funções: count(), sum(), max(), min(), avg()
-- cláusulas: group by e having

select count(*) from country;

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

select sum(population) from country;

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

select region, avg(lifeexpectancy) from country
group by region;

select
continent as 'continente', 
name as 'pais', population
from country
where
continent = 'africa' and
population = (select max(population) from country 
				where continent = 'africa');

-- JOIN (CONSULTAS EM 2 OU MAIS TABLES)

select
name as 'país', 
language as 'idioma'
from country
inner join countrylanguage on code = countrycode;

select
name as 'país', 
count(countrycode) as 'total idiomas por país'
from country
inner join countrylanguage on code = countrycode
group by countrycode;

-- 8) exibir o continente, o país e a área do país 
-- ordenando do maior para o menor (usar order by)

select * from country;

SELECT
continent, 
name, 
surfacearea
from country
order by surfacearea desc;