-- Database 1: World
-- Warm-up Query: Countries in Asia
-- List all the full names, population, and gnp of all countries in Asia, ordered in descending
-- order by gnp, breaking ties ordering by population in ascending order. 
SELECT name, population, gnp
FROM countries
WHERE continent = 'Asia'
ORDER BY gnp DESC, population DESC;

-- English- and French-Speaking Countries
-- List the full names of all countries and their continents where either English or French is 
-- spoken as an official language. Do this as a single query.
SELECT c.name, c.continent
FROM countries c
JOIN languages l on l.country_code = c.code
WHERE (l.language = 'English' OR l.language = 'French') AND l.official = 'T'
ORDER BY c.name;

-- Languages in France
-- List all of the languages that are spoken in France, as well as the expected number of speakers
-- for that language (population / percentage * 100.0). Order the reuslts by
-- percentage descending, breaking ties by language ascending. 
-- Round your value for estimated speakers by the nearest integer.
-- Note that you can omit the alias for the two tables
-- since there are no column name conflicts, but it may be more readable to
-- use alias names to distinguish them in your query.
SELECT l.language, l.official, l.percentage,
  ROUND(c.population / l.percentage * 100) AS 'estimated_speakers' 
FROM languages l, countries c
WHERE l.country_code = c.code AND c.name = 'France'
ORDER BY percentage DESC, language ASC;

-- Cities in China
-- List all of the cities in China, whose population is more than 0.2% of China's population. The
-- results should include the name of the city, the district, the population, and the percentage of
-- the city's population based on the total population of China. Organize results by percentage
-- where the city with the highest population ratio comes first in the result. 
SELECT c.name, c.district, c.population, 
       c.population / countries.population as percentage
FROM countries, cities c
WHERE c.country_code = countries.code
AND countries.name = "China"
AND c.population / countries.population > 0.002
ORDER BY percentage DESC;

-- High Population
-- List the full names of all countries that contain at least 2 cities of at least 5,000,000 people.
SELECT DISTINCT c.name
FROM countries c, cities ci1, cities ci2
WHERE ci1.country_code = c.code
AND ci2.country_code = c.code
AND ci1.id < ci2.id 
AND ci1.population >= 5000000 
AND ci2.population >= 5000000;