Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
(24 intermediate revisions by 8 users not shown)
Line 1: Line 1:
==BBC Country Profiles==
+
A SELECT statement gets data from a table. Each table contains rows and columns - you can SELECT some columns and ignore others
This tutorial introduces SQL as a query language. We will be using the SELECT command on the table bbc:
+
*The column names on the select line control which columns you get
<table style='' border='1'><tr>
+
*The FROM clause controls which table you access
<th>name</th>
+
The table <code>games</code> shows the year and the city hosting the Olympic Games.
<th>region</th>
+
<table border='1'>
<th>area</th>
+
<caption>games</caption>
<th>population</th>
+
<tr> <th>yr</th> <th>city</th> <th>y1</th></tr>
<th>gdp</th>
+
<tr> <td align='right'>2000</td> <td>Sydney</td> <td></td></tr>
</tr>
+
<tr> <td align='right'>2004</td> <td>Athens</td> <td>2000</td></tr>
<tr>
+
<tr> <td align='right'>2008</td> <td>Beijing</td><td>2004</td> </tr>
<td>Afghanistan</td>
+
<tr> <td align='right'>2012</td> <td>London</td><td>2008</td> </tr>
<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>
==Exercises==
+
<div class=qu>
Using the <code>SELECT</code> statement.
+
The SELECT statement returns results from a <i>table</i>.
 
+
In this example the table is <code>games</code> and the columns are
==Warming up==
+
<code>yr</code> and <code>city</code>.
<div class='qu'>
+
<div class=tidy>
[[Read the notes about this table.]]
+
DROP TABLE games;
Issue the command:
+
</div>
SELECT name, region, population FROM bbc
+
<div class=setup>
Look at the output.
+
CREATE TABLE games(yr INT, city VARCHAR(20),y1 INT);
<source lang='sql' class='def'>
+
INSERT INTO games(city,yr) VALUES ('Sydney',2000);
SELECT name, region, population FROM bbc
+
INSERT INTO games(city,yr,y1) VALUES ('Athens',2004,2000);
</source>
+
INSERT INTO games(city,yr,y1) VALUES ('Beijing',2008,2004);
 
+
INSERT INTO games(city,yr,y1) VALUES ('London',2012,2008);
<source lang='sql' class='ans'>
+
</div>
SELECT name, region, population FROM bbc
+
<div class=def>
</source>
+
SELECT yr, city FROM games
 +
</div>
 
</div>
 
</div>
  
==Large Countries==
+
===See also:===
<div class='qu'>
+
*[[SELECT_basics | SELECT Tutorial]] - practice using the SELECT command
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  |SELECT ... WHERE]] - the WHERE clause allows you to get some rows but not others
<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 17:59, 22 January 2013

A SELECT statement gets data from a table. Each table contains rows and columns - you can SELECT some columns and ignore others

  • The column names on the select line control which columns you get
  • The FROM clause controls which table you access

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

games
yr city y1
2000 Sydney
2004 Athens 2000
2008 Beijing2004
2012 London2008

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),y1 INT); INSERT INTO games(city,yr) VALUES ('Sydney',2000); INSERT INTO games(city,yr,y1) VALUES ('Athens',2004,2000); INSERT INTO games(city,yr,y1) VALUES ('Beijing',2008,2004); INSERT INTO games(city,yr,y1) VALUES ('London',2012,2008);

SELECT yr, city FROM games

See also:

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense