Difference between revisions of "SQLZOO:SELECT from WORLD Tutorial"

From SQLZOO
Jump to navigation Jump to search
(Formatting changes to standardise the questions and help students with column names.)
(Redirected page to SELECT from WORLD Tutorial)
 
(36 intermediate revisions by 10 users not shown)
Line 1: Line 1:
<div class="ref_section">
#REDIRECT [[SELECT_from_WORLD_Tutorial]]
<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 class="r">652230</td><td class="r">25500100</td><td class="r">20343000000</td></tr>
<tr><td>Albania</td><td>Europe</td><td class="r">28748 </td><td class="r">2831741 </td><td class="r">12960000000 </td></tr>
<tr><td>Algeria</td><td>Africa</td><td class="r">2381741 </td><td class="r">37100000 </td><td class="r">188681000000 </td></tr>
<tr><td>Andorra</td><td>Europe</td><td class="r">468</td><td class="r">78115 </td><td class="r">3712000000 </td></tr><tr>
<td>Angola</td><td>Africa</td><td class="r">1246700 </td><td class="r">20609294 </td><td class="r">100990000000 </td></tr>
<tr>
<td colspan='5'>...</td>
</tr>
</table>
</div>
 
==Country Profile==
In this tutorial you will use the SELECT command on the table <code>World</code>:
 
<div class='extra_space' style='width:1em; height:2.5em;'></div>
 
<div class="progress_panel"><div>
  <div class="summary">Summary</div>
  <div class="progressbarbg">
    <div class="progressbar"></div>
  </div>
</div></div>
 
==Warming up==
<div class='qu'>
[[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'>
SELECT name, continent, population FROM world
</source>
 
<source lang='sql' class='ans'>
SELECT name, continent, population FROM world
</source>
</div>
 
==Large Countries==
[[WHERE_filters |How to use WHERE to filter records.]]
<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.
<source lang='sql' class='def'>
SELECT name FROM world
WHERE population>250000000
</source>
 
<source lang='sql' class='ans'>
SELECT name FROM world
WHERE population>200000000
</source>
</div>
 
 
<div class='qu'>
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'>
per capita GDP is the GDP divided by the population GDP/population
</div>
<source lang='sql' class='def'>
</source>
 
<source lang='sql' class='ans'>
SELECT name, gdp/population FROM world
  WHERE population > 200000000
</source>
</div>
 
<div class='qu'>
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.
<source lang='sql' class='def'>
</source>
 
<source lang='sql' class='ans'>
SELECT name, population/1000000 FROM world
  WHERE continent='South America'
</source>
</div>
 
<div class='qu'>
Show the <code>name</code> and <code>population</code> for 'France', 'Germany', 'Italy'
<source lang='sql' class='def'>
</source>
 
<source lang='sql' class='ans'>
SELECT name, population FROM world
  WHERE name IN ('France','Germany','Italy')
</source>
</div>
 
<div class='qu'>
Show the countries which have a <code>name</code> that includes the word 'United'
<source lang='sql' class='def'>
</source>
 
<source lang='sql' class='ans'>
SELECT name FROM world
  WHERE name LIKE '%United%'
</source>
</div>
<div>
<p><div class="quizlink">[[BBC QUIZ]]</div></p>
</div>
<h2>What Next</h2>
<ul>
  <li>You can play a game:
  [http://sqlzoo.net/~andrew/brain/bt.htm Find the duplicate]
  </li>
  <li>You can to continue practising the the same techniques
    and gain more experience of the basic skills on the Nobel table.
  [[SELECT_from_Nobel_Tutorial |The <code>WHERE</code> statement using the
  <code>nobel</code> table.]]
  </li>
  <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
  <code>world</code> table.]]</li>
</ul>

Latest revision as of 12:20, 28 April 2016