Difference between revisions of "SELECT"
| 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> | + | <caption>Author</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 13: | Line 13: | ||
</table> | </table> | ||
<table border='1'> | <table border='1'> | ||
| − | <caption> | + | <caption>Book</caption> |
<tr> <th>BOOK_ID</th> <th>AUTHOR_ID</th> <th>NAME</th><th>TYPE</th></tr> | <tr> <th>BOOK_ID</th> <th>AUTHOR_ID</th> <th>NAME</th><th>TYPE</th></tr> | ||
<tr> <td align='right'>1</td> <td>4</td> <td>School adventures</td><td>Roman</td></tr> | <tr> <td align='right'>1</td> <td>4</td> <td>School adventures</td><td>Roman</td></tr> | ||
| Line 33: | Line 33: | ||
</div> | </div> | ||
<div class=setup> | <div class=setup> | ||
| − | CREATE TABLE | + | CREATE TABLE Author(AUTHOR_ID INT, NAME VARCHAR(20)); |
| − | INSERT INTO games( | + | INSERT INTO Author(AUTHOR_ID,NAME) VALUES (1,'Shevchenko'); |
| − | INSERT INTO games( | + | INSERT INTO Author(AUTHOR_ID,NAME) VALUES (2,'Franko'); |
| − | INSERT INTO games( | + | INSERT INTO Author(AUTHOR_ID,NAME) VALUES (3,'Lesya'); |
| − | INSERT INTO games( | + | INSERT INTO Author(AUTHOR_ID,NAME) VALUES (4,'Nestaiko'); |
| + | INSERT INTO Author(AUTHOR_ID,NAME) VALUES (5,'Vasyliv'); | ||
| + | </div> | ||
| + | <div class=setup> | ||
| + | CREATE TABLE Book(BOOK_ID INT, AUTHOR_ID INT, NAME VARCHAR(30),TYPE VARCHAR(20)); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (1,4,'School adventures','Roman'); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (2,4,'Forest song','Roman'); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (3,4,'Cobzar','Roman'); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (4,4,'Stolen happiness','Roman'); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (5,4,'Moon rabbits','Roman'); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (6,4,'Sun rabbits','Roman'); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (7,4,'Fox Mykyta','Roman'); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (8,4,'Cool baby','Roman'); | ||
| + | INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (9,4,'MuhoosfromVasyukivka','Roman'); | ||
</div> | </div> | ||
<div class=def> | <div class=def> | ||
Revision as of 14:12, 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.
| AUTHOR_ID | NAME |
|---|---|
| 1 | Shevchenko |
| 2 | Franko |
| 3 | Lesya |
| 4 | Nestaiko |
| 5 | Vasyliv |
| BOOK_ID | AUTHOR_ID | NAME | TYPE |
|---|---|---|---|
| 1 | 4 | School adventures | Roman |
| 2 | 3 | Forest song | Poema |
| 3 | 1 | Cobzar | Virshi |
| 4 | 2 | Stolen happiness | Povist |
| 5 | 4 | Moon rabbits | Roman |
| 6 | 4 | Sun rabbits | Roman |
| 7 | 2 | Fox Mykyta | Poema |
| 8 | 3 | Cool baby | Poema |
| 9 | 4 | Muhoos 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 Author(AUTHOR_ID INT, NAME VARCHAR(20)); INSERT INTO Author(AUTHOR_ID,NAME) VALUES (1,'Shevchenko'); INSERT INTO Author(AUTHOR_ID,NAME) VALUES (2,'Franko'); INSERT INTO Author(AUTHOR_ID,NAME) VALUES (3,'Lesya'); INSERT INTO Author(AUTHOR_ID,NAME) VALUES (4,'Nestaiko'); INSERT INTO Author(AUTHOR_ID,NAME) VALUES (5,'Vasyliv');
CREATE TABLE Book(BOOK_ID INT, AUTHOR_ID INT, NAME VARCHAR(30),TYPE VARCHAR(20)); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (1,4,'School adventures','Roman'); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (2,4,'Forest song','Roman'); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (3,4,'Cobzar','Roman'); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (4,4,'Stolen happiness','Roman'); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (5,4,'Moon rabbits','Roman'); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (6,4,'Sun rabbits','Roman'); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (7,4,'Fox Mykyta','Roman'); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (8,4,'Cool baby','Roman'); INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (9,4,'MuhoosfromVasyukivka','Roman');
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
