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

From SQLZOO
Jump to: navigation, search
(Redirected page to SELECT from WORLD Tutorial)
 
(48 intermediate revisions by 16 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>
+
 
+
==World Country Profiles==
+
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 name and the per capita GDP for those countries with a population 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'>
+
Identify the countries which have names including 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>
+
<div class="lsclear">Clear your results</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