CREATE VIEW

From SQLZOO
Revision as of 21:09, 22 October 2012 by Peter (talk | contribs) (Reverted edits by Miacardenas (talk) to last revision by Connor)
Jump to navigation Jump to search

CREATE VIEW

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

games
yrcity
2004Athens
2008Beijing
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