SQLZoo SELECT from WORLD: Solutions and Explanations

SQLZoo's World database is a great way to practice SELECT queries. Each exercise builds on the last, starting with basic selection and moving to computed columns, filtering, and formatting.

Here are the solutions with notes on what each one teaches.

Basic selection

All countries with name, continent, and population:

SELECT name, continent, population
FROM world

Countries with at least 200 million people:

SELECT name
FROM world
WHERE population > 200000000

Computed columns

Per-capita GDP for large countries:

SELECT name, gdp/population
FROM world
WHERE population > 200000000

Division in SELECT creates a computed column. No need for application-layer math.

Population in millions for South America:

SELECT name, population/1000000
FROM world
WHERE continent = 'South America'

Filtering with IN and LIKE

Specific countries by name:

SELECT name, population
FROM world
WHERE name IN ('France', 'Germany', 'Italy')

Countries containing "United" in the name:

SELECT name
FROM world
WHERE name LIKE '%United%'

% matches any characters. LIKE '%United%' finds "United Kingdom", "United States", "United Arab Emirates".

Combining conditions with OR

Countries that are big by area or by population:

SELECT name, population, area
FROM world
WHERE population > 250000000 OR area > 3000000

Exclusive OR (XOR)

Big by area or big by population, but not both:

SELECT name, population, area
FROM world
WHERE (population > 250000000 AND area < 3000000)
   OR (population < 250000000 AND area > 3000000)

SQL doesn't have an XOR operator. You build it with AND/OR logic. Australia (big area, small population) and Indonesia (big population, small area) are included. China (both big) is excluded.

Rounding with ROUND

Population in millions and GDP in billions for South America, rounded to 2 decimal places:

SELECT name,
       ROUND(population/1000000.0, 2),
       ROUND(gdp/1000000000.0, 2)
FROM world
WHERE continent = 'South America'

The .0 in the divisor forces floating-point division. Without it, integer division truncates the decimals.

Per-capita GDP for trillion-dollar economies

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

ROUND(value, -3) rounds to the nearest thousand. Useful for making large numbers readable.

The takeaway

These exercises cover the most common SELECT patterns: filtering, computed columns, pattern matching, boolean logic, and formatting. Master these and you can handle most day-to-day SQL work.