Difference between revisions of "Archived SELECT from WORLD Tutorial"

From SQLZOO
Jump to: navigation, search
(Q4 Added mention that SA is a continent.)
 
(9 intermediate revisions by 5 users not shown)
Line 14: Line 14:
 
</div>
 
</div>
  
==Country Profiles==
+
==Country Profile==
 
In this tutorial you will use the SELECT command on the table <code>World</code>:
 
In this tutorial you will use the SELECT command on the table <code>World</code>:
  
Line 28: Line 28:
 
==Warming up==
 
==Warming up==
 
<div class='qu'>
 
<div class='qu'>
[[Read_the_notes_about_this_table. |Read the notes about this table.]] Observe the result of running a simple SQL command.
+
[[Read_the_notes_about_this_table. |Read the notes about this table.]] Observe the result of running a simple SQL command.  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT name, continent, population FROM world
 
SELECT name, continent, population FROM world
Line 41: Line 41:
 
[[WHERE_filters |How to use WHERE to filter records.]]
 
[[WHERE_filters |How to use WHERE to filter records.]]
 
<div class='qu'>
 
<div class='qu'>
Show the name for the countries  that have a population of at least 200 million. (200 million is 200000000, there are eight zeros)
+
Show the name for the countries  that have a population of at least 200 million.
 +
200 million is 200000000, there are eight zeros.
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT name FROM world
 
SELECT name FROM world
Line 55: Line 56:
  
 
<div class='qu'>
 
<div class='qu'>
Give the name and the per capita GDP for those countries with a population of at least 200 million.  
+
Give the <code>name</code> and the '''per capita GDP''' for those countries with a <code>population</code> of at least 200 million.  
 
<div title="HELP:How to calculate per capita GDP" class='hint'>
 
<div title="HELP:How to calculate per capita GDP" class='hint'>
 
per capita GDP is the GDP divided by the population GDP/population  
 
per capita GDP is the GDP divided by the population GDP/population  
Line 69: Line 70:
  
 
<div class='qu'>
 
<div class='qu'>
Show the <code>name</code> and <code>population</code> in millions for the countries of 'South America'
+
Show the <code>name</code> and <code>population</code> in millions for the countries of the <code>continent</code> '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 92: Line 93:
  
 
<div class='qu'>
 
<div class='qu'>
Identify the countries which have names including the word 'United'
+
Show the countries which have a <code>name</code> that includes the word 'United'
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 102: Line 103:
 
</div>
 
</div>
 
<div>
 
<div>
<div class="lsclear">Clear your results</div>
 
 
<p><div class="quizlink">[[BBC QUIZ]]</div></p>
 
<p><div class="quizlink">[[BBC QUIZ]]</div></p>
 
</div>
 
</div>

Latest revision as of 13:50, 22 September 2014

namecontinentarea populationgdp
AfghanistanAsia6522302550010020343000000
AlbaniaEurope28748 2831741 12960000000
AlgeriaAfrica2381741 37100000 188681000000
AndorraEurope46878115 3712000000
AngolaAfrica1246700 20609294 100990000000
...

Contents

[edit] Country Profile

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

Summary

[edit] 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

[edit] 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 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%'

What Next

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense