use world;

-- comando para exibir todas as tabelas de uma base de dados
show tables;

-- exibir todos os campos e registros de uma tabela
select * from country;
select * from city;
select * from countrylanguage;

-- exibir campos específicos de uma tabela
select continent, name from country;

select name, indepyear
from country
where
indepyear is null;

-- exibir dados específicos aplicando um filtro
select continent, name
from country
where
continent = 'asia';

select continent, name from country
where
continent like '%america%';

-- aplicando parâmetros numéricos

select  * from country
where
population >= 150000000 and
population <= 200000000;

select * from country
where
population between 150000000 and 200000000;

-- consulta de dados em 2 ou mais tabelas
select
country.name as 'país',
city.name as 'cidade'
from country
inner join city on code = countrycode
where
country.population between 150000000 and 200000000;

-- funções de grupo
-- count, sum, max, min, avg

select sum(population) from country;
select count(*) from country;

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

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

select continent, name, population
from country
where
population = (select max(population) from country);

-- like
select name from country
where
name like '_A%';

select name from country
where
name like 'A%';

select name from country
where
name like '%A';