CREATE a VIEW

From SQLZoo
Jump to navigation Jump to search

CREATE a VIEW.

schema:scott

A view is a named SELECT statement.

In this example we show only European countries, we also show the population in millions.

Note how the columns of the table may be named.

DROP VIEW v_europe
DROP TABLE world; 
CREATE TABLE world(
   name VARCHAR(50) NOT NULL
   ,region VARCHAR(60)
   ,population DECIMAL(11)	
   ,PRIMARY KEY (name)
   );
INSERT INTO world VALUES ('China','Asia',1261);
INSERT INTO world VALUES ('France','Europe',59);
INSERT INTO world VALUES ('India','Asia',1014);
INSERT INTO world VALUES ('Italy','Europe',57);
INSERT INTO world VALUES ('Japan','Asia',126);
INSERT INTO world VALUES ('Russia','Asia',146);
INSERT INTO world VALUES ('Spain','Europe',39);
CREATE VIEW v_europe AS
 SELECT name,
        population AS pop
  FROM world 
  WHERE region='Europe';

SELECT * FROM v_europe
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects