Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
 
(2 intermediate revisions by one 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>EMP</code> shows the year and the city hosting the Olympic Games.
+
 
<table border='1'>
 
<table border='1'>
−
<caption>EMP</caption>
+
<caption>games</caption>
−
<tr> <th>ENAME</th> <th>JOB</th> <th>SAL</th></tr>
+
<tr> <th>yr</th> <th>city</th> <th>y1</th></tr>
−
<tr> <td align='right'>SMITH</td> <td>CLERK</td>  <td>800</td></tr>
+
<tr> <td align='right'>2000</td> <td>Sydney</td>  <td></td></tr>
−
<tr> <td align='right'>ALLEN</td> <td>SALESMAN</td> <td>1600</td></tr>
+
<tr> <td align='right'>2004</td> <td>Athens</td> <td>2000</td></tr>
−
<tr> <td align='right'>WARD</td> <td>SALESMAN</td><td>1250</td> </tr>
+
<tr> <td align='right'>2008</td> <td>Beijing</td><td>2004</td> </tr>
−
<tr> <td align='right'>MARTIN</td> <td>SALESMAN</td><td>1250</td> </tr>
+
<tr> <td align='right'>2012</td> <td>London</td><td>2008</td> </tr>
−
<tr> <td align='right'>BLAKE</td> <td>MANAGER</td><td>2850</td> </tr>
+
−
<tr> <td align='right'>CLARK</td> <td>MANAGER</td><td>2450</td> </tr>
+
−
<tr> <td align='right'>SCOTT</td> <td>ANALYST</td><td>3000</td> </tr>
+
−
<tr> <td align='right'>KING</td> <td>PRESIDENT</td><td>5000</td> </tr>
+
 
</table>
 
</table>
 
<div class=qu>
 
<div class=qu>
 
The SELECT statement returns results from a <i>table</i>.
 
The SELECT statement returns results from a <i>table</i>.
−
In this example the table is <code>EMP</code> and the columns are
+
In this example the table is <code>games</code> and the columns are
−
<code>ENAME</code> and <code>JOB</code>.
+
<code>yr</code> and <code>city</code>.
 
<div class=tidy>
 
<div class=tidy>
−
DROP TABLE EMP;
+
DROP TABLE games;
 
</div>
 
</div>
 
<div class=setup>
 
<div class=setup>
−
CREATE TABLE EMP(ENAME INT, JOB VARCHAR(20),SAL INT);
+
CREATE TABLE games(yr INT, city VARCHAR(20),y1 INT);
−
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('SMITH','CLERK',800);
+
INSERT INTO games(city,yr) VALUES ('Sydney',2000);
−
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('ALLEN',SALESMAN,1600);
+
INSERT INTO games(city,yr,y1) VALUES ('Athens',2004,2000);
−
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('WARD',SALESMAN,1250);
+
INSERT INTO games(city,yr,y1) VALUES ('Beijing',2008,2004);
−
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('MARTIN',SALESMAN,1250);
+
INSERT INTO games(city,yr,y1) VALUES ('London',2012,2008);
−
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('BLAKE',MANAGER,2850);
+
−
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('CLARK',MANAGER,2450);
+
−
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('SCOTT',ANALYST,3000);
+
−
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('KING',PRESIDENT,5000);
+
 
</div>
 
</div>
 
<div class=def>
 
<div class=def>
−
SELECT ENAME, JOB FROM games
+
SELECT yr, city FROM games
 
</div>
 
</div>
 
</div>
 
</div>

Latest revision as of 16:59, 22 January 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.

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;

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

[edit] See also:

Retrieved from "http://sqlzoo.net/w/index.php?title=SELECT&oldid=7910"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense