databases-week1-exercises.sql

gistfile1.txt

select name from city where Population > 8000000;
select name from city where name like "%land";
select name from city where Population > 500000 and Population < 1000000;
select name from country where Continent = "Europe";
select name from city where CountryCode = 'nld';
select Population from city where name = 'rotterdam';
select * from country where HeadOfState is null;
select name, population from city limit 10;
select * from country where name = LocalName and Continent = 'Africa';
select country.name from country join countrylanguage  on country.code = countrylanguage.CountryCode where countrylanguage.`Language` = 'Spanish';
select * from countrylanguage where IsOfficial = 't' and Percentage > 1 and Percentage < 10;
select distinct language from countrylanguage where Percentage > 90 ;
select country.name from country join countrylanguage on country.code = countrylanguage.CountryCode where countrylanguage.`Language` = 'Creole English' order by countrylanguage.Percentage desc;
select name, IndepYear, GovernmentForm from country where IndepYear is not null and GovernmentForm like   '%republic%' order by IndepYear asc limit 5;
select country.name, countrylanguage.`Language`, round(country.Population*(countrylanguage.Percentage/100)) from country join countrylanguage on country.Code = countrylanguage.CountryCode ;