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

From SQLZOO
Jump to navigation Jump to search
(Undo revision 14005 by Yunfeng.huang (talk))
(Redirected page to SELECT from WORLD Tutorial)
 
(5 intermediate revisions by 5 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='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>
{{nosql|http://nosqlzoo.net/wiki/FIND_from_WORLD#Q1| SELECT name, continent, population}}
 
</div>
 
<div class='qu'>
[[WHERE_filters |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.
<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>
{{nosql|http://nosqlzoo.net/wiki/FIND_from_WORLD#Q2| SELECT with WHERE}}
</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 the <code>continent</code> '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>
 
<div class='qu'>
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.
<p class=imper>Show the countries that are big by area or big by population. Show name, population and area.</p>
<source lang='sql' class='def'>
</source>
 
<source lang='sql' class='ans'>
select name,population,area
from world
where area>3000000
or population>250000000
</source>
</div>
<div>
 
<div class='qu'>
USA, India, and China are big in population '''and''' big by area. Exclude these countries.
<p class=imper>Show the countries that are big by area or big by population but not both. Show name, population and area.</p>
<source lang='sql' class='def'>
</source>
<source lang='sql' class='ans'>
select name, population,area
from world
where
(population>250000000 or area>3000000)
and not(population>250000000 and area>3000000)
</source>
</div>
 
<div class='qu'>
Show the <code>name</code> and <code>population</code> in millions and the GDP in billions for the countries of the <code>continent</code> 'South America'. Use the [[ROUND]] function to show the values to two decimal places.
<div class=imper>For South America show population in millions and GDP in billions to 2 decimal places.</div>
<div class=hint title='Millions and billions'>Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions.</div>
<source lang='sql' class='def'>
</source>
<source lang='sql' class='ans'>
SELECT name, ROUND(population/1000000,2),
            ROUND(gdp/1000000000,2)
  FROM world
WHERE continent='South America'
</source>
</div>
 
<div class='qu'>
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.
<p class=imper>Show per-capita GDP for the trillion dollar countries to the nearest $1000.</p>
<source lang='sql' class='def'>
</source>
<source lang='sql' class='ans'>
select name, ROUND(gdp/population,-3)
from world
where
gdp>1000000000000
</source>
</div>
 
==Harder Questions==
<div class='qu'>
The [[CASE]] statement shown is used to substitute '''North America''' for '''Caribbean''' in  the third column.
<div class=imper>Show the name and the continent - but substitute '''Australasia''' for '''Oceania''' - for countries beginning with N.</div>
<source lang='sql' class='def'>
SELECT name, continent,
      CASE WHEN continent='Caribbean' THEN 'North America'
            ELSE continent END
  FROM world
WHERE name LIKE 'J%'
</source>
<source lang='sql' class='ans'>
SELECT name, CASE WHEN continent='Oceania' THEN 'Australasia'
                  ELSE continent END
  FROM world
WHERE name LIKE 'N%'
</source>
</div>
 
<div class='qu'>
<div class=imper>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</div>
<source lang='sql' class='def'>
</source>
<source lang='sql' class='ans'>
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'
</source>
</div>
 
<div class='qu'>
Put the continents right...
<ul>
  <li>Oceania becomes Australasia</li>
  <li>Countries in Eurasia and Turkey go to '''Europe/Asia'''</li>
  <li>Caribbean islands starting with 'B' go to '''North America''', other Caribbean islands go to '''South America'''</li>
</ul>
<div class=imper>Show the name, the original continent and the new continent of all countries.</div>
<source lang='sql' class='def'>
</source>
<source lang='sql' class='ans'>
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</source>
</div>
 
 
<h2>What Next</h2>
<ul>
  <li>[[BBC QUIZ]]</li>
  <li>You can play a game:
  [http://sqlzoo.net/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

Redirect to:

Retrieved from "https://sqlzoo.net/w/index.php?title=SQLZOO:SELECT_from_WORLD_Tutorial&oldid=14256"