Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
Line 4: Line 4:
 
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>games</caption>
+
<caption>authors</caption>
 
<tr> <th>AUTHOR_ID</th> <th>NAME</th> </tr>
 
<tr> <th>AUTHOR_ID</th> <th>NAME</th> </tr>
 
<tr> <td align='right'>1</td> <td>Shevchenko</td>  </tr>
 
<tr> <td align='right'>1</td> <td>Shevchenko</td>  </tr>
Line 10: Line 10:
 
<tr> <td align='right'>3</td> <td>Lesya</td> </tr>
 
<tr> <td align='right'>3</td> <td>Lesya</td> </tr>
 
<tr> <td align='right'>4</td> <td>Nestaiko</td> </tr>
 
<tr> <td align='right'>4</td> <td>Nestaiko</td> </tr>
<tr> <td align='right'>5</td> <td>Tychyna</td></tr>
+
<tr> <td align='right'>5</td> <td>Vasyliv</td></tr>
 
</table>
 
</table>
 
<table border='1'>
 
<table border='1'>
<caption>games</caption>
+
<caption>book</caption>
<tr> <th>yr</th> <th>city</th> <th>y1</th></tr>
+
<tr> <th>BOOK_ID</th> <th>AUTHOR_ID</th> <th>NAME</th><th>TYPE</th></tr>
<tr> <td align='right'>2000</td> <td>Sydney</td>  <td></td></tr>
+
<tr> <td align='right'>1</td> <td>4</td>  <td>School adventures</td><td>Roman</td></tr>
<tr> <td align='right'>2004</td> <td>Athens</td> <td>2000</td></tr>
+
<tr> <td align='right'>2</td> <td>3</td> <td>Forest song</td><td>Poema</td></tr>
<tr> <td align='right'>2008</td> <td>Beijing</td><td>2004</td> </tr>
+
<tr> <td align='right'>3</td> <td>1</td><td>Cobzar</td><td>Virshi</td></tr>
<tr> <td align='right'>2012</td> <td>London</td><td>2008</td> </tr>
+
<tr> <td align='right'>4</td> <td>2</td><td>Stolen happiness</td> <td>Povist</td></tr>
 +
<tr> <td align='right'>5</td> <td>4</td>  <td>Moon rabbits</td><td>Roman</td></tr>
 +
<tr> <td align='right'>6</td> <td>4</td> <td>Sun rabbits</td><td>Roman</td></tr>
 +
<tr> <td align='right'>7</td> <td>2</td><td>Fox Mykyta</td> <td>Poema</td></tr>
 +
<tr> <td align='right'>8</td> <td>3</td><td>Cool baby</td> <td>Poema</td></tr>
 +
<tr> <td align='right'>9</td> <td>4</td><td>Muhoos from Vasyukivka</td> <td>Roman</td></tr>
 
</table>
 
</table>
 
<div class=qu>
 
<div class=qu>

Revision as of 14:59, 25 May 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.

authors
AUTHOR_ID NAME
1 Shevchenko
2 Franko
3 Lesya
4 Nestaiko
5 Vasyliv
book
BOOK_ID AUTHOR_ID NAMETYPE
1 4 School adventuresRoman
2 3 Forest songPoema
3 1CobzarVirshi
4 2Stolen happiness Povist
5 4 Moon rabbitsRoman
6 4 Sun rabbitsRoman
7 2Fox Mykyta Poema
8 3Cool baby Poema
9 4Muhoos from Vasyukivka Roman

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