CIA World Factbook
This tutorial introduces SQL as a query language. We will be
using the SELECT command on the table cia:
cia(name, region, area, population, gdp)
Exercises
Using the SELECT statement.
Before attempting the following questions read this!
How to use WHERE to filter records.
| 2a |
Show the name for the four countries that have a
population of at least 200 million. (200 million is 200000000, there are
eight zeros)
SELECT name FROM cia WHERE population>200000000
|
|
| 2b |
Give the name and the
per capita
GDP for those countries with a population of at least 200
million.
per capita GDP is the GDP divided by the population GDP/population
SELECT name, gdp/population FROM cia
WHERE population>200000000
|
|
| 2c |
Show the name and population in millions for the
countries of 'South America'
SELECT name, round(population/1000000) FROM cia
WHERE region='South America'
|
|
| 2d |
Show the population for 'France', 'Germany', 'Italy'
SELECT name, population FROM cia
WHERE name IN ('France','Germany','Italy')
|
|
| 2e |
Identify the countries which have names including the
word 'United'
SELECT name FROM cia
WHERE name LIKE '%United%'
|
|
That's enough for the first tutorial.
The next tutorial concerns nested SELECT
statements using the same
cia table.