Difference between revisions of "SQLZOO:SELECT basics"

From SQLZOO
Jump to: navigation, search
(Redirected page to SELECT basics)
 
(118 intermediate revisions by 40 users not shown)
Line 1: Line 1:
<div class="ref_section">
+
#REDIRECT [[SELECT basics]]
<table class='db_ref'><tr>
 
<th>name</th>
 
<th>region</th>
 
<th>area</th>
 
<th>population</th>
 
<th>gdp</th>
 
</tr>
 
<tr>
 
<td>Afghanistan</td>
 
<td>South Asia</td>
 
<td align='right'>652225</td>
 
<td align='right'>26000000</td>
 
<td></td>
 
</tr>
 
<tr>
 
<td>Albania</td>
 
<td>Europe</td>
 
<td align='right'>28728</td>
 
<td align='right'>3200000</td>
 
<td align='right'>6656000000</td>
 
</tr>
 
<tr>
 
<td>Algeria</td>
 
<td>Middle East</td>
 
<td align='right'>2400000</td>
 
<td align='right'>32900000</td>
 
<td align='right'>75012000000</td>
 
</tr>
 
<tr>
 
<td>Andorra</td>
 
<td>Europe</td>
 
<td align='right'>468</td>
 
<td align='right'>64000</td>
 
<td></td>
 
</tr>
 
<tr>
 
<td colspan='5'>...</td>
 
</tr>
 
</table>
 
</div>
 
 
 
==Introducing the BBC Table of Countries==
 
  <p>This tutorial introduces SQL as a  query language. We will be using the SELECT command on the table '''bbc''':</p>
 
 
 
<div class="progress_panel"><div>
 
  <div class="summary">Summary</div>
 
  <div class="progressbarbg">
 
    <div class="progressbar"></div>
 
  </div>
 
</div></div>
 
 
 
  <div class='extra_space' style='width:1em; height:6em;'></div>
 
 
 
<div class='qu'>
 
The example shows the population of 'France'.
 
Strings should be in 'single quotes';
 
 
 
<p class='imper'>Show the population of Germany</p>
 
 
 
<source lang='sql' class='def'>
 
SELECT population FROM bbc
 
  WHERE name = 'France'
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT population FROM bbc
 
  WHERE name = 'Germany'
 
</source>
 
</div>
 
 
 
==Per Capita GDP==
 
<div class='qu'>
 
<div>This query shows the population density <code>population/area</code>
 
for each country where the area is over 5,000,000 km<sup>2</sup>.</div>
 
<div class='imper'>Show the per capita gdp: <code>gdp/population</code>
 
for each country where the area is over 5,000,000 km<sup>2</sup></div>
 
 
 
<source lang='sql' class='def'>
 
SELECT name, population/area FROM bbc
 
  WHERE area > 5000000
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT name, gdp/population FROM bbc
 
  WHERE area > 5000000
 
</source>
 
</div>
 
 
 
==Small and wealthy==
 
<div class='qu'>
 
Where to find some very small, very rich countries.<br/>
 
<b>We use <code>AND</code> to ensure that two or more conditions hold
 
true.</b>
 
<div>The example shows the countries where the population is small and the
 
gdp is high.</div>
 
<div class='imper'>Show the <b>name</b> and <b>region</b> where the area is less then 2000 and the gdp is more than 5000000000</div>
 
<source lang='sql' class='def'>
 
SELECT name , region
 
  FROM bbc
 
  WHERE population < 2000000
 
    AND gdp > 5000000000
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT name , region
 
  FROM bbc
 
  WHERE area < 2000
 
    AND gdp > 5000000000
 
</source>
 
</div>
 
 
 
==Scandinavia==
 
<div class='qu'>
 
Checking a list The word <b>IN</b> allows us to check if an item is in a list.
 
The example shows the name and population for the countries 'Ireland', 'Iceland' and 'Denmark'
 
<div class='imper'>Show the '''name''' and the '''population''' for 'Denmark', 'Finland', 'Norway', 'Sweden'
 
</div>
 
<source lang='sql' class='def'>
 
SELECT name, population FROM bbc
 
  WHERE name IN ('Ireland', 'Iceland',
 
                'Denmark')</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT name, population FROM bbc
 
  WHERE name IN ('Denmark', 'Finland',
 
                'Norway', 'Sweden')</source>
 
</div>
 
 
 
==Starts with G==
 
<div class='qu'>
 
What are the countries beginning with G?
 
'''The word''' <code>LIKE</code> '''permits pattern matching''' - % '''is the wildcard'''.
 
The examples shows countries beginning with D
 
<div class='imper'>Show each country that begins with G</div>
 
<source lang='sql' class='def'>
 
SELECT name FROM bbc
 
  WHERE name LIKE 'D%'
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT name FROM bbc
 
  WHERE name LIKE 'G%'
 
</source>
 
</div>
 
 
 
==Just the right size==
 
<div class='qu'>
 
Which countries are not too small and not too big?
 
<code>BETWEEN</code> allows range checking - note that it is inclusive.
 
<div class='imper'>'''Show the area in 1000 square km. Show''' ''area''/1000 '''instead of''' ''area''</div>
 
<source lang='sql' class='def'>
 
SELECT name, area FROM bbc
 
  WHERE area BETWEEN 207600 AND 244820
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT name, area/1000 FROM bbc
 
  WHERE area BETWEEN 207600 AND 244820
 
</source>
 
</div>
 
 
 
<div>
 
<div class="lsclear">Clear your results</div>
 
<p><div class="quizlink">[[SELECT Quiz]]</div></p>
 
</div>
 
<p>You are ready for tutorial one:[[SELECT_from_BBC_Tutorial |SELECT statements with WHERE.]]</p>
 
 
 
 
 
{{Languages}}
 

Latest revision as of 11:45, 25 May 2016

Redirect to: