Difference between revisions of "SELECT from WORLD Tutorial"

 Language: English  • 日本語 • 中文
namecontinentarea populationgdp
AfghanistanAsia6522302550010020343000000
AlbaniaEurope28748 2831741 12960000000
AlgeriaAfrica2381741 37100000 188681000000
AndorraEurope46878115 3712000000
AngolaAfrica1246700 20609294 100990000000
...

Country Profile

In this tutorial you will use the SELECT command on the table `world`:

```SELECT name, continent, population FROM world
```
```SELECT name, continent, population FROM world
```

How to use WHERE to filter records. Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

```SELECT name FROM world
WHERE population>250000000
```
```SELECT name FROM world
WHERE population>200000000
```

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 world
WHERE population > 200000000
```

Show the `name` and `population` in millions for the countries of the `continent` 'South America'. Divide the population by 1000000 to get population in millions.

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

Show the `name` and `population` for France, Germany, Italy

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

Show the countries which have a `name` that includes the word 'United'

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

Two ways to be big: A country is big if it has an area of more than 3 million sq km or it has a population of more than 250 million.

Show the countries that are big by area or big by population. Show name, population and area.

```
```
```select name,population,area
from world
where area>3000000
or population>250000000
```

Exclusive OR. Show the countries that are big by area or big by population but not both. Show name, population and area.

• Australia has a big area but a small population, it should be included.
• Indonesia has a big population but a small area, it should be included.
• China has a big population and big area, it should be excluded.
• United Kingdom has a small population and a small area, it should be excluded.
```
```
```select name, population,area
from world
where
(population>250000000 or area>3000000)
and not(population>250000000 and area>3000000)
```

Show the `name` and `population` in millions and the GDP in billions for the countries of the `continent` 'South America'. Use the ROUND function to show the values to two decimal places.

For South America show population in millions and GDP in billions to 2 decimal places.
Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions.
```
```
```SELECT name, ROUND(population/1000000,2),
ROUND(gdp/1000000000,2)
FROM world
WHERE continent='South America'
```

Show the 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
```

Harder Questions

The CASE statement shown is used to substitute North America for Caribbean in the third column.

Show the name - but substitute Australasia for Oceania - for countries beginning with N.
```SELECT name, continent,
CASE WHEN continent='Caribbean' THEN 'North America'
ELSE continent END
FROM world
WHERE name LIKE 'J%'
```
```SELECT name, CASE WHEN continent='Oceania' THEN 'Australasia'
ELSE continent END
FROM world
WHERE name LIKE 'N%'
```
Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B
```
```
```SELECT name, CASE WHEN continent IN ('Europe','Asia') THEN 'Eurasia'
WHEN continent IN ('North America','South America','Caribbean') THEN 'America'
ELSE continent END
FROM world
WHERE name BETWEEN 'A' AND 'C'
```

Put the continents right...

• Oceania becomes Australasia
• Countries in Eurasia and Turkey go to Europe/Asia
• Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America
• Order by country name in ascending order
Show the name, the original continent and the new continent of all countries.
```
```
```SELECT name,continent,
CASE WHEN continent = 'Eurasia' OR name='Turkey' THEN 'Europe/Asia'
WHEN continent IN ('Oceania') THEN 'Australasia'
WHEN continent = 'Caribbean' AND name LIKE 'B%' THEN 'North America'
WHEN continent = 'Caribbean' THEN 'South America'
ELSE continent END
FROM world
ORDER BY name
```