Difference between revisions of "SELECT from WORLD Tutorial"
From SQLZOO
(Created page with "<div class="ref_section"> <table class='db_ref'> <tr><th>name</th><th>continent</th><th>area</th> <th>population</th><th>gdp</th></tr> <tr><td>Afghanistan</td><td>Asia</td><td...") |
|||
| Line 69: | Line 69: | ||
<div class='qu'> | <div class='qu'> | ||
| − | Show the <code>name</code> and <code>population</code> in millions for the countries of ' | + | Show the <code>name</code> and <code>population</code> in millions for the countries of 'South America' |
Divide the population by 1000000 to get population in millions. | Divide the population by 1000000 to get population in millions. | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
| Line 76: | Line 76: | ||
<source lang='sql' class='ans'> | <source lang='sql' class='ans'> | ||
SELECT name, population/1000000 FROM world | SELECT name, population/1000000 FROM world | ||
| − | WHERE continent=' | + | WHERE continent='South America' |
</source> | </source> | ||
</div> | </div> | ||
| Line 103: | Line 103: | ||
<div> | <div> | ||
<div class="lsclear">Clear your results</div> | <div class="lsclear">Clear your results</div> | ||
| − | <p><div class="quizlink">[[ | + | <p><div class="quizlink">[[BBC QUIZ]]</div></p> |
</div> | </div> | ||
<h2>What Next</h2> | <h2>What Next</h2> | ||
| Line 110: | Line 110: | ||
[http://sqlzoo.net/~andrew/brain/bt.htm Find the duplicate] | [http://sqlzoo.net/~andrew/brain/bt.htm Find the duplicate] | ||
</li> | </li> | ||
| − | <li>You can to continue | + | <li>You can to continue practising the the same techniques |
and gain more experience of the basic skills on the Nobel table. | and gain more experience of the basic skills on the Nobel table. | ||
[[SELECT_from_Nobel_Tutorial |The <code>WHERE</code> statement using the | [[SELECT_from_Nobel_Tutorial |The <code>WHERE</code> statement using the | ||
<code>nobel</code> table.]] | <code>nobel</code> table.]] | ||
</li> | </li> | ||
| − | <li>You can learn about nested statements, these are instructive | + | <li>You can learn about nested statements, these are instructive and entertaining, but not essential for beginners. |
| − | + | ||
[[SELECT_within_SELECT_Tutorial |Nested <code>SELECT</code> statements using the | [[SELECT_within_SELECT_Tutorial |Nested <code>SELECT</code> statements using the | ||
<code>world</code> table.]]</li> | <code>world</code> table.]]</li> | ||
</ul> | </ul> | ||
Revision as of 21:21, 8 December 2012
| 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 |
| ... | ||||
Contents |
World Country Profiles
In this tutorial you will use the SELECT command on the table World:
Summary
Warming up
Read the notes about this table. Observe the result of running a simple SQL command.
SELECT name, continent, population FROM world
SELECT name, continent, population FROM world
Large Countries
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 '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')
Identify the countries which have names including the word 'United'
SELECT name FROM world WHERE name LIKE '%United%'
Clear your results
What Next
- You can play a game: Find the duplicate
- You can to continue practising the the same techniques
and gain more experience of the basic skills on the Nobel table.
The
WHEREstatement using thenobeltable. - You can learn about nested statements, these are instructive and entertaining, but not essential for beginners.
Nested
SELECTstatements using theworldtable.
