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
CODE

<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
SQL

All fields

CREATE VIEW ViewName
AS
	SELECT * from data
SQL

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
CODE

Or during the select statement: 

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

Filter on a given field

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

Order the results

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

Limit the number of results

CREATE VIEW limited_table 
AS
	SELECT * FROM mytable 
	LIMIT 1000
SQL