Difference between revisions of "SELECT"
(3 intermediate revisions by the same user not shown) | |||
Line 2: | Line 2: | ||
*The column names on the select line control which columns you get | *The column names on the select line control which columns you get | ||
*The FROM clause controls which table you access | *The FROM clause controls which table you access | ||
+ | <div class=params>schema:scott</div> | ||
The table <code>games</code> shows the year and the city hosting the Olympic Games. | The table <code>games</code> shows the year and the city hosting the Olympic Games. | ||
<table border='1'> | <table border='1'> | ||
− | <caption> | + | <caption>games</caption> |
− | <tr> <th> | + | <tr> <th>yr</th> <th>city</th> <th>y1</th></tr> |
− | + | <tr> <td align='right'>2000</td> <td>Sydney</td> <td></td></tr> | |
− | + | <tr> <td align='right'>2004</td> <td>Athens</td> <td>2000</td></tr> | |
− | + | <tr> <td align='right'>2008</td> <td>Beijing</td><td>2004</td> </tr> | |
− | + | <tr> <td align='right'>2012</td> <td>London</td><td>2008</td> </tr> | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | <tr> <td align='right'> | ||
− | <tr> <td align='right'> | ||
− | <tr> <td align='right'> | ||
− | <tr> <td align='right'> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
</table> | </table> | ||
<div class=qu> | <div class=qu> | ||
Line 31: | Line 18: | ||
<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 | + | 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); | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | INSERT INTO | ||
− | INSERT INTO | ||
− | |||
− | INSERT INTO | ||
− | |||
− | |||
− | INSERT INTO | ||
− | |||
</div> | </div> | ||
<div class=def> | <div class=def> | ||
− | SELECT | + | SELECT yr, city FROM games |
</div> | </div> | ||
</div> | </div> |
Revision as of 22:59, 18 June 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
schema:scott
The table games
shows the year and the city hosting the Olympic Games.
yr | city | y1 |
---|---|---|
2000 | Sydney | |
2004 | Athens | 2000 |
2008 | Beijing | 2004 |
2012 | London | 2008 |
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,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:
- SELECT Tutorial - practice using the SELECT command
- SELECT ... WHERE - the WHERE clause allows you to get some rows but not others