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:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
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:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
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];