CREATE VIEW

Views allow the user to create a simplified version of a given SQL statement.

It is a logical table based on one table. A view contains no data in itself.

For example, you can:

• change the names of some selected columns.
• change the order of the columns.
• filter the result of a select SQL statement on a table.

Syntax

CREATE VIEW <ViewName> [(new_name1, new_name2)]
AS
	select_statement

<ViewName>

Name of the given view.

The view is created in the same schema of the underlying table.

Allowed sql_statements

Using a JOIN clause with other tables is not allowed. If you use a JOIN clause, the FROM clause only specifies one table at a time.

List of fields

Create a view that will display only selected fields.

CREATE VIEW ViewName 
AS
	SELECT column1, column2 from data

All fields

CREATE VIEW ViewName
AS
	SELECT * from data

Rename columns

When defining the field names of the view, you can rename fields of the original table:

CREATE VIEW ViewName(column1_new_name, column2_new_name)
AS
	SELECT column1, column2 from DATA

Or during the select statement:

CREATE VIEW ViewName
AS
	SELECT column1 as NewName1, column2 as NewName2 from DATA

Filter on a given field

CREATE VIEW ViewName
AS
	SELECT * FROM mytable WHERE [mytable.]column1 = 1

Order the results

CREATE VIEW new_name_for_old_data_table 
AS 
	SELECT * FROM mytable 
	ORDER BY [mytable.]column1 desc

Limit the number of results

CREATE VIEW limited_table 
AS
	SELECT * FROM mytable 
	LIMIT 1000