Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
(Undo revision 2771 by Connor (talk))
 
(30 intermediate revisions by 14 users not shown)
Line 6: Line 6:
 
<table border='1'>
 
<table border='1'>
 
<caption>games</caption>
 
<caption>games</caption>
<tr> <th>yr</th> <th>city</th> </tr>
+
<tr> <th>yr</th> <th>city</th></tr>
<tr> <td align='right'>2000</td> <td>Sydney</td> </tr>
+
<tr> <td align='right'>2000</td> <td>Sydney</td></tr>
<tr> <td align='right'>2004</td> <td>Athens</td> </tr>
+
<tr> <td align='right'>2004</td> <td>Athens</td></tr>
<tr> <td align='right'>2008</td> <td>Beijing</td> </tr>
+
<tr> <td align='right'>2008</td> <td>Beijing</td></tr>
<tr> <td align='right'>2012</td> <td>London</td> </tr>
+
<tr> <td align='right'>2012</td> <td>London</td></tr>
 +
<tr> <td align='right'>2016</td> <td>Rio</td></tr>
 
</table>
 
</table>
 
<div class=qu>
 
<div class=qu>
Line 18: Line 19:
 
<div class=tidy>
 
<div class=tidy>
 
DROP TABLE games;
 
DROP TABLE games;
 +
DROP TABLE games CASCADE;
 
</div>
 
</div>
 
<div class=setup>
 
<div class=setup>
CREATE TABLE games(yr INT, city VARCHAR(20));
+
CREATE TABLE games(yr INT, city VARCHAR(20),y1 INT);
INSERT INTO games(city,yr) VALUES ('ouroalpha',2000);
+
INSERT INTO games(city,yr) VALUES ('Sydney',2000);
 
INSERT INTO games(city,yr) VALUES ('Athens',2004);
 
INSERT INTO games(city,yr) VALUES ('Athens',2004);
 
INSERT INTO games(city,yr) VALUES ('Beijing',2008);
 
INSERT INTO games(city,yr) VALUES ('Beijing',2008);
 
INSERT INTO games(city,yr) VALUES ('London',2012);
 
INSERT INTO games(city,yr) VALUES ('London',2012);
 +
INSERT INTO games(city,yr) VALUES ('London',2012);
 +
INSERT INTO games(city,yr) VALUES ('Rio',2016);
 
</div>
 
</div>
 
<div class=def>
 
<div class=def>
Line 30: Line 34:
 
</div>
 
</div>
 
</div>
 
</div>
 
===See also:===
 
*[http://sqlzoo.net/w/index.php/SELECT_from_BBC_Tutorial SELECT Tutorial] - practice using the SELECT command
 
*[SELECT_.._WHERE  |SELECT ... WHERE] - the WHERE clause allows you to get some rows but not others
 

Latest revision as of 10:55, 23 September 2016

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
schema:scott

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

games
yr city
2000 Sydney
2004 Athens
2008 Beijing
2012 London
2016 Rio

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; DROP TABLE games CASCADE;

CREATE TABLE games(yr INT, city VARCHAR(20),y1 INT); 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); INSERT INTO games(city,yr) VALUES ('London',2012); INSERT INTO games(city,yr) VALUES ('Rio',2016);

SELECT yr, city FROM games