sqlzoo

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
				...

Rounding

Show thenameandpopulationin millions and the GDP in billions for the countries of thecontinent'South America'. Use theROUNDfunction to show the values to two decimal places.

For South America show population in millions and GDP in billions both to 2 decimal places.

SELECT name, ROUND(population/1000000, 2), ROUND(gdp/1000000000, 2)
FROM world
WHERE continent = 'South America';

Note: ROUND(X,D) rounds the argument X_to D_decimal places.

Trillion dollar economies

Show thenameand per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.

Show per-capita GDP for the trillion dollar countries to the nearest $1000.

SELECT name, ROUND(gdp/population, -3)
FROM world
WHERE gdp > 1000000000000;

Note: ROUND(X) rounds the argument X to the nearest number. (四舍五入)

Matching name and capital

Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.

SELECT name, capital
FROM world
WHERE LEFT(name,1) = LEFT(capital,1) 
AND name != capital;

Note: LEFT(string, num) return the leftmost num characters in string.

All the vowels

Find the country that has all the vowels and no spaces in its name.

SELECT name
FROM world
WHERE name LIKE '%a%' 
AND name LIKE '%e%' 
AND name LIKE '%i%' 
AND name LIKE '%o%' 
AND name LIKE '%u%' 
AND name NOT LIKE '% %';

Percentages of Germany

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

SELECT 
    name, 
    CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name = 'Germany')), '%')
FROM world
WHERE continent = 'Europe';

Note:

• To use % sign, need to use CONCAT() function

• To get the right percentage number, need to use ROUND() function.

• Remember *100 when you compute the percentage number, to take in account of the following % sign.

Bigger than every country in Europe

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

# list GDP of all the country in Europe in a subquery
# if GDP is null, return 0 instead
SELECT name
FROM world
WHERE gdp > ALL(
                SELECT ifnull(GDP, 0) 
                FROM world 
                WHERE continent = 'Europe'
                );

# bigger than every country means bigger than the biggest
# this runs faster than the last code
SELECT name
FROM world
WHERE gdp > (SELECT MAX(gdp) 
                FROM world 
                WHERE continent = 'Europe');

Largest in each continent

Find the largest country (by area) in each continent, show the continent, the name and the area:

The difficulty of this question is how to show name. Using GROUP BY, we can easily show continent and MAX(area). But we can't show name in GROUP BY clauses.

Solution 1:

Use a subquery to list the max area in each continent, then select those matches.

SELECT continent, name, area
FROM world
WHERE (continent, area) IN (SELECT continent, MAX(area) 
                                FROM world 
                                GROUP BY continent);

Solution 2:

Do a left join between the GROUP BY table and the original table, and then return output we want.

SELECT 
    aggr_table.continent AS continent, 
    world.name AS name, 
    aggr_table.max_area AS area
FROM 
    (SELECT continent, MAX(area) AS max_area
        FROM world
        GROUP BY continent
    ) AS aggr_table
    LEFT JOIN world
    ON aggr_table.max_area = world.area
    AND aggr_table.continent = world.continent
 ;

Solution 3:

Use correlated subqueries

SELECT continent, name, area
FROM world AS w1
WHERE area >= ALL(
                SELECT area
                FROM world AS w2
                WHERE w2.continent = w1.continent AND area > 0 
);

Note: need area > 0 condition because area may be NULL. If it's NULL, the corresponding continent won't be returned.

First country of each continent (alphabetically)

Solution 1: List each continent and the name of the country that comes first alphabetically.

SELECT continent, MIN(name)
FROM world
GROUP BY continent;

Solution 2: use correlated subquery

SELECT continent, name
FROM world AS w1
WHERE name <= all(SELECT name 
                FROM world AS w2 
                WHERE w2.continent = w1.continent);

Continents with all small population countries

Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

SELECT name, continent, population
FROM world
WHERE continent IN (
                SELECT continent
                FROM world
                GROUP BY continent
                HAVING MAX(population) <= 25000000
);

Countries larger than all their neighbours

Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

SELECT name, continent
FROM world AS w1
WHERE population/3 > (
                SELECT MAX(population)
                FROM world AS w2
                WHERE w2.continent = w1.continent AND w2.name != w1.name
);

# alternative
SELECT name, continent
FROM world as w1
WHERE population > 3*(SELECT population 
                FROM world as w2 
                WHERE w1.continent = w2.continent 
                ORDER BY population desc 
                LIMIT 1, 1);