Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
m (Fixed the two internal links at the bottom)
Line 3: Line 3:
 
*The FROM clause controls which table you access
 
*The FROM clause controls which table you access
 
<div class=params>schema:scott</div>
 
<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>games</caption>
+
<caption>EMP</caption>
−
<tr> <th>yr</th> <th>city</th> <th>y1</th></tr>
+
<tr> <th>ENAME</th> <th>JOB</th> <th>SAL</th></tr>
−
<tr> <td align='right'>2000</td> <td>Sydney</td>  <td></td></tr>
+
<tr> <td align='right'>SMITH</td> <td>CLERK</td>  <td>800</td></tr>
−
<tr> <td align='right'>2004</td> <td>Athens</td> <td>2000</td></tr>
+
<tr> <td align='right'>ALLEN</td> <td>SALESMAN</td> <td>1600</td></tr>
−
<tr> <td align='right'>2008</td> <td>Beijing</td><td>2004</td> </tr>
+
<tr> <td align='right'>WARD</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'>MARTIN</td> <td>SALESMAN</td><td>1250</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>games</code> and the columns are
+
In this example the table is <code>EMP</code> and the columns are
−
<code>yr</code> and <code>city</code>.
+
<code>ENAME</code> and <code>JOB</code>.
 
<div class=tidy>
 
<div class=tidy>
−
DROP TABLE games;
+
DROP TABLE EMP;
 
</div>
 
</div>
 
<div class=setup>
 
<div class=setup>
−
CREATE TABLE games(yr INT, city VARCHAR(20),y1 INT);
+
CREATE TABLE EMP(ENAME INT, JOB VARCHAR(20),SAL INT);
−
INSERT INTO games(city,yr) VALUES ('Sydney',2000);
+
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('SMITH','CLERK',800);
−
INSERT INTO games(city,yr,y1) VALUES ('Athens',2004,2000);
+
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('ALLEN',SALESMAN,1600);
−
INSERT INTO games(city,yr,y1) VALUES ('Beijing',2008,2004);
+
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('WARD',SALESMAN,1250);
−
INSERT INTO games(city,yr,y1) VALUES ('London',2012,2008);
+
INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('MARTIN',SALESMAN,1250);
 +
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 yr, city FROM games
+
SELECT ENAME, JOB FROM games
 
</div>
 
</div>
 
</div>
 
</div>

Revision as of 07:57, 3 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
schema:scott

The table EMP shows the year and the city hosting the Olympic Games.

EMP
ENAME JOB SAL
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN1250
MARTIN SALESMAN1250
BLAKE MANAGER2850
CLARK MANAGER2450
SCOTT ANALYST3000
KING PRESIDENT5000

The SELECT statement returns results from a table. In this example the table is EMP and the columns are ENAME and JOB.

DROP TABLE EMP;

CREATE TABLE EMP(ENAME INT, JOB VARCHAR(20),SAL INT); INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('SMITH','CLERK',800); INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('ALLEN',SALESMAN,1600); INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('WARD',SALESMAN,1250); INSERT INTO EMP(ENAME,JOB, SAL) VALUES ('MARTIN',SALESMAN,1250); 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);

SELECT ENAME, JOB FROM games

See also:

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

Variants
Actions
Reference
Toolbox
Google AdSense