Create View

What is SQL View

A view in SQL is a virtual table that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query. A view can contain rows from an existing table (all or selected). A view can be created from one or many tables. Unless indexed, a view does not exist in a database.

The data in the view does not exist in the database physically. A view is typically created by the database administrator and is used to −

• Structure data in a way that users or classes of users find natural or intuitive.
• Restrict access to the data in such a way 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.

The SQL CREATE VIEW Statement

To create a view in a database, you can use the SQL CREATE VIEW statement.

Syntax

Following is the syntax of the SQL CREATE VIEW statement −

CREATEVIEW view_name ASSELECT column1, column2....FROM table_name
WHERE[condition];

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

CREATETABLE CUSTOMERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

Now, insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

Following query creates a view based on the above created table −

CREATEVIEW CUSTOMERS_VIEW ASSELECT*FROM CUSTOMERS;

Verification

You can verify the contents of a view using the select query as shown below −

SELECT*FROM CUSTOMERS_VIEW;

The view is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Create View With WHERE Clause

We can also create a view with only specific records from a table using the where clause along with the SQL CREATE VIEW statement as shown below −

CREATEVIEW BUYERS_VIEW asSELECT*FROM CUSTOMERS 
WHERE SALARY >3000;

Verification

Following are the contents of the above created view −

SELECT*FROM BUYERS_VIEW;

The view is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

The WITH CHECK OPTION Clause

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) specified by the WHERE clause.

If they do not satisfy the condition(s), the UPDATE or INSERT statements return an error. The following example creates the view named BUYERS_VIEW with the WITH CHECK OPTION clause.

CREATEVIEW MY_VIEW ASSELECT name, age
FROM  CUSTOMERS
WHERE age >=25WITHCHECKOPTION;

The WITH CHECK OPTION in this case should deny the entry and updates of the of records whose age value is greater than or equal to 25.

Verification

Following are the contents of the above created view −

SELECT*FROM MY_VIEW;

The view is displayed as follows −

NAME	AGE
Ramesh	32
Khilan	25
Chaitali	25
Hardik	27