Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
(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>Author</caption>
+
<caption>games</caption>
−
<tr> <th>AUTHOR_ID</th> <th>NAME</th> </tr>
+
<tr> <th>yr</th> <th>city</th> <th>y1</th></tr>
−
<tr> <td align='right'>1</td> <td>Shevchenko</td>  </tr>
+
<tr> <td align='right'>2000</td> <td>Sydney</td>  <td></td></tr>
−
<tr> <td align='right'>2</td> <td>Franko</td></tr>
+
<tr> <td align='right'>2004</td> <td>Athens</td> <td>2000</td></tr>
−
<tr> <td align='right'>3</td> <td>Lesya</td> </tr>
+
<tr> <td align='right'>2008</td> <td>Beijing</td><td>2004</td> </tr>
−
<tr> <td align='right'>4</td> <td>Nestaiko</td> </tr>
+
<tr> <td align='right'>2012</td> <td>London</td><td>2008</td> </tr>
−
<tr> <td align='right'>5</td> <td>Vasyliv</td></tr>
+
−
</table>
+
−
<table border='1'>
+
−
<caption>Book</caption>
+
−
<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'>2</td> <td>3</td> <td>Forest song</td><td>Poema</td></tr>
+
−
<tr> <td align='right'>3</td> <td>1</td><td>Cobzar</td><td>Virshi</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>
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 Author(AUTHOR_ID INT, NAME VARCHAR(20));
+
CREATE TABLE games(yr INT, city VARCHAR(20),y1 INT);
−
INSERT INTO Author(AUTHOR_ID,NAME) VALUES (1,'Shevchenko');
+
INSERT INTO games(city,yr) VALUES ('Sydney',2000);
−
INSERT INTO Author(AUTHOR_ID,NAME) VALUES (2,'Franko');
+
INSERT INTO games(city,yr,y1) VALUES ('Athens',2004,2000);
−
INSERT INTO Author(AUTHOR_ID,NAME) VALUES (3,'Lesya');
+
INSERT INTO games(city,yr,y1) VALUES ('Beijing',2008,2004);
−
INSERT INTO Author(AUTHOR_ID,NAME) VALUES (4,'Nestaiko');
+
INSERT INTO games(city,yr,y1) VALUES ('London',2012,2008);
−
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 Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (1,4,'School adventures','Roman');
+
−
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (2,4,'Forest song','Poema');
+
−
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (3,4,'Cobzar','Virshi');
+
−
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (4,4,'Stolen happiness','Povist');
+
−
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (5,4,'Moon rabbits','Roman');
+
−
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (6,4,'Sun rabbits','Roman');
+
−
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (7,4,'Fox Mykyta','Poema');
+
−
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (8,4,'Cool baby','Poema');
+
−
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (9,4,'MuhoosfromVasyukivka','Roman');
+
 
</div>
 
</div>
 
<div class=def>
 
<div class=def>
−
SELECT NAME FROM Autor
+
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.

games
yr city y1
2000 Sydney
2004 Athens 2000
2008 Beijing2004
2012 London2008

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:

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