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;

/* CONSULTAS COM CONDICIONAIS */
/* CASE */
SELECT
name as 'país',
population as 'população',
CASE
	WHEN population >= 1000000 THEN 'Metrópole'
    WHEN population >= 500000 AND population < 1000000 THEN 'Cidade Grande'
    WHEN population >= 100000 AND population < 500000 THEN 'Cidade Média'
    ELSE 'Cidade Pequena'
END AS CategoriaTamanho
from city;

/* if else */

SELECT
name as 'país', 
indepyear as 'ano independência',
if(indepyear is not null, "Independente", "Não Independente") as 'status'
from country;

/* IN */

SELECT 
name as 'cidade', 
countrycode as 'país'
from city
where
countrycode in('bra', 'arg')
order by countrycode asc, cidade asc;

/* SAKILA */

use sakila;

show tables;

select * from customer;
select * from address;

select
first_name, last_name, address
from customer
inner join address on address.address_id = customer.address_id;

select
concat(first_name, ' ', last_name) as 'Cliente',
address as 'Endereço', 
city as 'Cidade', 
country as 'País'
from customer as cu
inner join address as ad on ad.address_id = cu.address_id
inner join city on city.city_id = ad.city_id
inner join country as co on co.country_id = city.country_id
order by country asc;

select * from film;
select * from category;
select * from film_category;

select 
title as 'filme',
name as 'categoria'
from film
inner join film_category as fc on film.film_id = fc.film_id
inner join category on category.category_id = fc.category_id;

select * from inventory;

select 
title as 'filme',
inv.film_id
from film
left join inventory as inv on film.film_id = inv.film_id
where
inv.film_id is null;

select
title as 'filme',
count(inv.film_id) as 'total',
store_id as 'loja'
from film
inner join inventory as inv on film.film_id = inv.film_id
group by inv.film_id, store_id
order by store_id asc, total desc;

select * from rental
where
return_date is null;

show tables;