SQL CREATE VIEW Statement

A view is a virtual table whose definition is stored in the database. But, unlike tables, views do not actually contain any data. Instead, it provides a way to store commonly used complex queries in the database. However, you can use the view in a SQL SELECT statement to access the data just as you would use a normal or base table.

Views can also be used as a security mechanism by allowing users to access data through the view, rather than giving them direct access to the entire base tables.

Syntax

Views are created using the CREATE VIEW statement.

CREATE VIEW view_name AS select_statement;

Updating Data Through a View

Theoretically, you can also perform INSERT, UPDATE, and DELETE on views in addition to the SELECT statement. However, not all views are updatable i.e. capable of modifying the data of an underlying source table. There are some restrictions on the updatability.

Generally a view is not updatable if it contains any of the following:

  • The DISTINCT, GROUP BY or HAVING clauses.
  • Aggregate functions such as AVG(), COUNT(), SUM(), MIN(), MAX(), and so forth.
  • The UNION, UNION ALL, CROSSJOIN, EXCEPT or INTERSECT operators.
  • Subquery in the WHERE clause that refers to a table in the FROM clause.

If a view satisfies these conditions, you can modify the source table using that view.

The following statement will update the salary of the employee whose emp_id is equal to 1.

Example

UPDATE emp_dept_view SET salary = '6000' 
WHERE emp_id = 1;

Dropping a View

Similarly, if you no longer need a view, you can use the DROP VIEW statement to drop it from the database, as shown in the following syntax:

DROP VIEW view_name;

The following command will drop the view emp_dept_view from the database.

Example

DROP VIEW emp_dept_view;