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>authors</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>book</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 games(yr INT, city VARCHAR(20),y1 INT);
+
CREATE TABLE Author(AUTHOR_ID INT, NAME VARCHAR(20));
−
INSERT INTO games(city,yr) VALUES ('Sydney',2000);
+
INSERT INTO Author(AUTHOR_ID,NAME) VALUES (1,'Shevchenko');
−
INSERT INTO games(city,yr,y1) VALUES ('Athens',2004,2000);
+
INSERT INTO Author(AUTHOR_ID,NAME) VALUES (2,'Franko');
−
INSERT INTO games(city,yr,y1) VALUES ('Beijing',2008,2004);
+
INSERT INTO Author(AUTHOR_ID,NAME) VALUES (3,'Lesya');
−
INSERT INTO games(city,yr,y1) VALUES ('London',2012,2008);
+
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
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 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:

Retrieved from "http://sqlzoo.net/w/index.php?title=SELECT&oldid=13234"