SQL - VIEWS

The SQL VIEW statement is used to create a SQL VIEW which is a virtual table created based on the SQL statement. A view contains rows and columns just like a normal table. All SQL functions, WHERE, HAVING and JOINs statements can be used to create a SQL VIEW. The SQL VIEW statement can be used to create, update or delete a view.

Syntax

The syntax for using VIEW statement is given below:

/* Create a view */
CREATE VIEW view_name AS
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition(s);

/* Update a view */
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition(s);

/* Drop a view */
DROP VIEW view_name;

Example:

Consider a database table called Employee with the following records:

CREATE VIEW

The below mentioned SQL statement is used to create a view on Employee table which contains all records of employee whose salary is greater than 2800.

CREATE VIEW [Employee Salary GT 2800] AS
SELECT * FROM Employee WHERE Salary > 2800;

Query the VIEW

After creating the VIEW, it can be used as mentioned below:

SELECT * FROM [Employee Salary GT 2800];

This will produce the result as shown below:

CREATE OR REPLACE VIEW

The CREATE OR REPLACE VIEW statement is used to update the view if it exists otherwise creates a new view.

CREATE OR REPLACE VIEW [Employee Salary GT 2800] AS
SELECT * FROM Employee WHERE Salary > 2800;

DROP VIEW

The DROP VIEW is used to delete a view.

DROP VIEW [Employee Salary GT 2800];