CREATE a VIEW

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

A view is called a query in Microsoft Access. Use DROP TABLE to remove a view.

CREATE and DROP Operations

CREATE a new table

DROP a table

Composite primary key

CREATE a foreign key

CREATE a VIEW

Autonumber fields

ALTER TABLE ... ADD COLUMN

ALTER TABLE ... DROP COLUMN

ALTER TABLE ... ADD constraint

CREATE TABLE problems: Invalid column name.

CREATE TABLE problems: Insufficient privileges.

CREATE TABLE problems: Table already exists.

DROP TABLE problems: Foreign key references.

CREATE TABLE problems: Foreign key references.

Rename column