Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
(Warming up)
Line 1: Line 1:
==BBC Country Profiles==
+
The table <code>games</code> shows the year and the city hosting the Olympic Games.
This tutorial introduces SQL as a  query language. We will be using the SELECT command on the table bbc:
+
<table border='1'>
<table style='' border='1'><tr>
+
<caption>games</caption>
<th>name</th>
+
<tr> <th>yr</th> <th>city</th> </tr>
<th>region</th>
+
<tr> <td align='right'>2000</td> <td>Sydney</td> </tr>
<th>area</th>
+
<tr> <td align='right'>2004</td> <td>Athens</td> </tr>
<th>population</th>
+
<tr> <td align='right'>2008</td> <td>Beijing</td> </tr>
<th>gdp</th>
+
<tr> <td align='right'>2012</td> <td>London</td> </tr>
</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>
 
</table>
 
+
<div class=qu>
==Warming up==
+
The SELECT statement returns results from a <i>table</i>.
<div class='qu'>
+
In this example the table is <code>games</code> and the columns are
[[Read the notes about this table.]] Observe the result of running a simple SQL command.
+
<code>yr</code> and <code>city</code>.
<source lang='sql' class='def'>
+
<div class=tidy>
SELECT name, region, population FROM bbc
+
DROP TABLE games;
</source>
+
</div>
 
+
<div class=setup>
<source lang='sql' class='ans'>
+
CREATE TABLE games(yr INT, city VARCHAR(20));
SELECT name, region, population FROM bbc
+
INSERT INTO games(city,yr) VALUES ('Sydney',2000);
</source>
+
INSERT INTO games(city,yr) VALUES ('Athens',2004);
 +
INSERT INTO games(city,yr) VALUES ('Beijing',2008);
 +
INSERT INTO games(city,yr) VALUES ('London',2012);
 +
</div>
 +
<div class=def>
 +
SELECT yr, city FROM games
 +
</div>
 
</div>
 
</div>
  
==Large Countries==
+
===See also:===
<div class='qu'>
+
*[[SELECT Tutorial]]
Show the name for the countries  that have a population of at least 200 million. (200 million is 200000000, there are eight zeros)
+
*[[SELECT ... WHERE]]
<source lang='sql' class='def'>
+
SELECT name FROM bbc
+
WHERE population>250000000
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name FROM bbc
+
WHERE population>200000000
+
</source>
+
</div>
+

Revision as of 20:57, 17 May 2012

The table games shows the year and the city hosting the Olympic Games.

games
yr city
2000 Sydney
2004 Athens
2008 Beijing
2012 London

The SELECT statement returns results from a table. In this example the table is games and the columns are yr and city.

DROP TABLE games;

CREATE TABLE games(yr INT, city VARCHAR(20)); INSERT INTO games(city,yr) VALUES ('Sydney',2000); INSERT INTO games(city,yr) VALUES ('Athens',2004); INSERT INTO games(city,yr) VALUES ('Beijing',2008); INSERT INTO games(city,yr) VALUES ('London',2012);

SELECT yr, city FROM games

See also:

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense