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 −

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 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 −

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

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 −

CREATE VIEW BUYERS_VIEW as SELECT * 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 −

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.

CREATE VIEW MY_VIEW AS
SELECT name, age
FROM  CUSTOMERS
WHERE age >= 25
WITH CHECK OPTION;

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 −