USE WORLD;

SHOW TABLES;

SELECT * FROM COUNTRY;
SELECT * FROM CITY;
SELECT * FROM COUNTRYLANGUAGE;

SELECT 
NAME AS "PAÍS", 
CONTINENT AS "CONTINENTE", 
REGION AS "REGIÃO"
FROM COUNTRY;

SELECT
NAME AS "PAÍS", 
INDEPYEAR AS "ANO INDEPENDÊNCIA", 
LIFEEXPECTANCY AS "EXPECT DE VIDA"
FROM COUNTRY;

select
country.name as "país", 
city.name as "cidade"
from country
inner join city on countrycode = code;

select
name, 
language, 
isofficial
from country
inner join countrylanguage on countrycode = code;


select
name, 
language, 
isofficial
from country
inner join countrylanguage on countrycode = code
where
isofficial = "F";

select
name, 
lifeexpectancy
from country
where
lifeexpectancy between 60 and 65;

select
name, 
lifeexpectancy
from country
where
lifeexpectancy >= 60 and
lifeexpectancy <= 65;

select
name, continent
from country
where 
continent = "north america" or
continent = "south america";

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

select
name, continent
from country
where 
continent not like "%america%";

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

/* 
1) exibir o nome do país, o continente e populacao dos países com mais 
de 150 milhões de habitantes

2) exibir o nome do país e os seus idiomas não oficiais

3) exibir o país, a região e expectativa de vida dos países com expectativa
de vida inferior a 50 anos

4) exibir o nome do país, nome da cidade e distrito somente 
dos países asiáticos
*/