Difference between revisions of "CREATE VIEW"
From SQLZOO
| (9 intermediate revisions by 4 users not shown) | |||
| Line 10: | Line 10: | ||
<div class='ht'> | <div class='ht'> | ||
<div class=params>schema:scott</div> | <div class=params>schema:scott</div> | ||
| − | <source lang=sql class='tidy'> DROP TABLE games</source> | + | <source lang=sql class='tidy'> DROP TABLE games; |
| + | DROP TABLE old_games; | ||
| + | DROP TABLE og</source> | ||
<source lang=sql class='setup'> CREATE TABLE games( | <source lang=sql class='setup'> CREATE TABLE games( | ||
yr INTEGER PRIMARY KEY, | yr INTEGER PRIMARY KEY, | ||
| Line 22: | Line 24: | ||
In this example the VIEW old_games shows those games before 2006. | In this example the VIEW old_games shows those games before 2006. | ||
<source lang='sql' class='def e-oracle'> | <source lang='sql' class='def e-oracle'> | ||
| − | CREATE VIEW | + | CREATE VIEW og AS |
SELECT yr,city FROM scott.games | SELECT yr,city FROM scott.games | ||
WHERE yr<2006; | WHERE yr<2006; | ||
| − | SELECT * FROM | + | SELECT * FROM og; |
</source> | </source> | ||
| − | <source> | + | <source lang='sql' class='def'> |
| − | CREATE VIEW | + | CREATE VIEW og AS |
SELECT yr,city FROM games | SELECT yr,city FROM games | ||
WHERE yr<2006; | WHERE yr<2006; | ||
| − | SELECT * FROM | + | SELECT * FROM og; |
</source> | </source> | ||
</div> | </div> | ||
Latest revision as of 08:35, 18 December 2012
CREATE VIEW
The table games shows the year and the city hosting the Olympic Games.
| yr | city |
|---|---|
| 2004 | Athens |
| 2008 | Beijing |
schema:scott
DROP TABLE games; DROP TABLE old_games; DROP TABLE og
CREATE TABLE games( yr INTEGER PRIMARY KEY, city VARCHAR(20)); INSERT INTO games VALUES (2000,'Sydney'); INSERT INTO games VALUES (2004,'Athens'); INSERT INTO games VALUES (2008,'Beijing'); INSERT INTO games VALUES (2012,'London');
The CREATE VIEW names a SELECT query. That query may be used as if it were a table in many contexts. In this example the VIEW old_games shows those games before 2006.
CREATE VIEW og AS SELECT yr,city FROM scott.games WHERE yr<2006; SELECT * FROM og;
CREATE VIEW og AS SELECT yr,city FROM games WHERE yr<2006; SELECT * FROM og;
See also
