use world;

show tables;
/* select básico - chama todos os registros e todos os campos */
select * from country;

/* select exibindo campos específicos */
select 
name, continent, region 
from country;

/* exibição de dados de acordo com parâmetros estabelecidos */
select
name, continent, region
from country
where
continent = 'north america' or
continent = 'south america';

/* parâmetros aproximados */
select
name, continent, region
from country
where
continent like '%america%';

select 
continent, name
from country
where
continent = 'asia' and
name not like '%tan';

/* parâmetro nulo ou não nulo */
select 
continent, name, lifeexpectancy
from country
where
lifeexpectancy is null;

/* operadores */
/* = | >= | <= | != | <> */
select 
continent, name, lifeexpectancy
from country
where
lifeexpectancy <> 75;

select 
continent, name, lifeexpectancy
from country
where
lifeexpectancy != 75;

/* parâmetros em intervalo */
select 
continent, name, lifeexpectancy
from country
where
lifeexpectancy >= 75 and
lifeexpectancy <= 80;

select 
continent, name, lifeexpectancy
from country
where
lifeexpectancy between 75 and 80;

/* EXTRAÇÃO DE DADOS À PARTIR DE 2 OU MAIS TABLES */

SELECT * FROM country;
select * from city;
select * from countrylanguage;

select * from countrylanguage
where
language = 'dutch';

/* JAMAIS FAZER DESTA FORMA!!!!!!!!! */
SELECT
country.name as 'país',
city.name as cidade
from country, city
where
code = countrycode;

/* FORMA DE MANUAL */
/* tipos de JOIN - INNER JOIN, LEFT JOIN, RIGHT JOIN */
/* ALIAS de campo e ALIAS  de tabela */
select
country.name as 'país',
city.name as 'cidade',
language as 'idioma'
from country
inner join city on code = city.countrycode
inner join countrylanguage on code = countrylanguage.countrycode
where
language = 'portuguese' and
isofficial = 't'
order by country.name asc, city.name asc;

select
country.name as 'país',
city.name as 'cidade',
language as 'idioma'
from country
inner join city on code = city.countrycode
inner join countrylanguage as cl on code = cl.countrycode
where
language = 'portuguese' and
isofficial = 't'
order by country.name asc, city.name asc;


/* funções de agrupamento */
/* count() | sum() | max() | min() | avg() */

select
sum(population) as 'total_populacao'
from country;

select
count(*) as 'total_populacao'
from country;

/* com group by */
select
continent as continente,
sum(population) 
from country
group by continent;

select
continent as continente,
sum(population) as total
from country
where
continent not like '%america%'
group by continent;