SQL VIEWS


A view in SQL is a logical subset of data from one or more tables. View is used to restrict data access.


A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.

Views, which are kind of virtual tables, allow users to do the following:

  • Structure data in a way that users or classes of users find natural or intuitive.
  • Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
  • Summarize data from various tables which can be used to generate reports.

Creating Views

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.

To create a view, a user must have the appropriate system privilege according to the specific implementation.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Example

CREATE VIEW cust_view AS
SELECT name, age
FROM customer;
SELECT * FROM cust_view;

Updating a View

View can be updated under certain conditions:

  • The SELECT clause may not contain the keyword DISTINCT.
  • The SELECT clause may not contain summary functions.
  • The SELECT clause may not contain set functions.
  • The SELECT clause may not contain set operators.
  • The SELECT clause may not contain an ORDER BY clause.
  • The FROM clause may not contain multiple tables.
  • The WHERE clause may not contain subqueries.
  • The query may not contain GROUP BY or HAVING.
  • Calculated columns may not be updated.
  • All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.
UPDATE CUSTOMERS_VIEW
      SET AGE = 24
      WHERE name='Sachin';

Inserting Rows into a View

Rows of data can be inserted into a view. The same rules that apply to the UPDATE command also apply to the INSERT command.

Here we can not insert rows in CUSTOMERS_VIEW because we have not included all the NOT NULL columns in this view, otherwise you can insert rows in a view in similar way as you insert them in a table.


Deleting Rows into a View

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and INSERT commands apply to the DELETE command.

DELETE FROM cust_view
      WHERE age = 24;

Dropping Views

Obviously, where you have a view, you need a way to drop the view if it is no longer needed.

DROP VIEW view_name;

Share this article on