Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
Line 42: Line 42:
 
<div class=setup>
 
<div class=setup>
 
CREATE TABLE Book(BOOK_ID INT, AUTHOR_ID INT, NAME VARCHAR(30),TYPE VARCHAR(20));
 
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 Book(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','Poema');
+
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (2,4,'Forest song','Poema');
INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (3,4,'Cobzar','Virshi');
+
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (3,4,'Cobzar','Virshi');
INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (4,4,'Stolen happiness','Povist');
+
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (4,4,'Stolen happiness','Povist');
INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (5,4,'Moon rabbits','Roman');
+
INSERT INTO Book(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 Book(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','Poema');
+
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (7,4,'Fox Mykyta','Poema');
INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (8,4,'Cool baby','Poema');
+
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (8,4,'Cool baby','Poema');
INSERT INTO games(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (9,4,'MuhoosfromVasyukivka','Roman');
+
INSERT INTO Book(BOOK_ID,AUTHOR_ID,NAME,TYPE) VALUES (9,4,'MuhoosfromVasyukivka','Roman');
 
</div>
 
</div>
 
<div class=def>
 
<div class=def>

Revision as of 14:46, 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 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');

SELECT NAME FROM Autor

See also:

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense