SQL - CREATE View

Creating a view is simply creating a virtual table using a query. A view is an SQL statement 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 which depends on the written SQL query to create a view. Unless indexed, a view does not exist in a database.

Views, which are a type 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 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.

Syntax

Following is the syntax of the CREATE View statement −

CREATE VIEW view_name AS
SELECT 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 −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

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

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

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

Create view first_view AS SELECT * FROM customers;

Verification

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

SELECT * from first_view;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

With Check Option

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.

If they do not satisfy the condition(s), the UPDATE or INSERT returns an error. The following code block has an example of creating same view CUSTOMERS_VIEW with the WITH CHECK OPTION.

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

The WITH CHECK OPTION in this case should deny the entry of any NULL values in the view s AGE column, because the view is defined by data that does not have a NULL value in the AGE column.

Using the WHERE clause

We can also create a view using the where clause as shown below −

CREATE VIEW test_view as SELECT * FROM customers where SALARY>3000;

Verification

Following are the contents of the above created view −

SELECT * FROM test_view;
+----+----------+-----+---------+------------+
| ID | NAME     | AGE | ADDRESS | SALARY     |
+----+----------+-----+---------+------------+
|  4 | Chaitap |  25 | Mumbai  |  6500.0000 |
|  5 | Hardik   |  27 | Bhopal  |  8500.0000 |
|  6 | Komal    |  22 | MP      |  4500.0000 |
|  7 | Muffy    |  24 | Indore  | 10000.0000 |
+----+----------+-----+---------+------------+