Views

Views

Views in SQL are considered as a virtual table. A VIEW in SQL is a logical subset of data from one or more tables. View is used to restrict data access.

Views, allow users to do the following:

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

Creating a View:

Database views are created using the CREATE VIEW statement. 

Syntax:

CREATE or replace VIEW view_name AS  

SELECT column1, column2.....  

FROM table_name  

WHERE condition;  

Example: Sales Table:

oid	order_name	previous_balance	customer
11	ord1	2000	Alex
12	ord2	1000	Adam
13	ord3	2000	Abhi
14	ord4	1000	Adam
15	ord5	2000	Alex

SQL Query to Create a View from the above table:

CREATE or REPLACE VIEW saleview

AS

SELECT * FROM Sales WHERE customer = 'Alex';

The syntax for displaying the data in a view is similar to fetching data from a table using a SELECT statement.

SELECT * FROM saleview;

Dropping Views

A view can be deleted using the Drop View statement.

Syntax

DROP VIEW view_name;  

Example:

To delete the View salesview, use drop view as:

DROP VIEW MarksView;  

Updating a View

A view can be updated under certain conditions which are given below

• 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.