A view in SQL is a logical subset of data from one or more tables. View is used to restrict data access.
Syntax for creating a View,
CREATE or REPLACE view view_name AS SELECT column_name(s) FROM table_name WHERE condition
Example of Creating a View
Consider following Sale 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 View
CREATE or REPLACE view sale_view as select * from Sale where customer = 'Alex';
The data fetched from select statement will be stored in another object called sale_view. We can use create seperately and replace too but using both together works better.
Example of Displaying a View
Syntax of displaying a view is similar to fetching data from table using Select statement.
SELECT * from sale_view;
Force View Creation
force keyword is used while creating a view. This keyword force to create View even if the table does not exist. After creating a force View if we create the base table and enter values in it, the view will be automatically updated.
Syntax for forced View is,
CREATE or REPLACE force view view_name AS SELECT column_name(s) FROM table_name WHERE condition
Update a View
Update command for view is same as for tables.
Syntax to Update a View is,
UPDATE view-name set value WHERE condition;
If we update a view it also updates base table data automatically.
Read-Only View
We can create a view with read-only option to restrict access to the view.
Syntax to create a view with Read-Only Access
CREATE or REPLACE force view view_name AS SELECT column_name(s) FROM table_name WHERE condition with read-only
The above syntax will create view for read-only purpose, we cannot Update or Insert data into read-only view. It will throw an error.
Types of View
There are two types of view,
- Simple View
- Complex View
Simple View | Complex View |
---|---|
Created from one table | Created from one or more table |
Does not contain functions | Contain functions |
Does not contain groups of data | Contains groups of data |
A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.
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 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.
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.
The basic CREATE VIEW syntax is as follows −
CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query.
Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+ | 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 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Following is an example to create a view from the CUSTOMERS table. This view would be used to have customer name and age from the CUSTOMERS table.
SQL > CREATE VIEW CUSTOMERS_VIEW AS SELECT name, age FROM CUSTOMERS;
Now, you can query CUSTOMERS_VIEW in a similar way as you query an actual table. Following is an example for the same.
SQL > SELECT * FROM CUSTOMERS_VIEW;
This would produce the following result.
+----------+-----+ | name | age | +----------+-----+ | Ramesh | 32 | | Khilan | 25 | | kaushik | 23 | | Chaitali | 25 | | Hardik | 27 | | Komal | 22 | | Muffy | 24 | +----------+-----+
The 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.
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.
So, if a view satisfies all the above-mentioned rules then you can update that view. The following code block has an example to update the age of Ramesh.
SQL > UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh';
This would ultimately update the base table CUSTOMERS and the same would reflect in the view itself. Now, try to query the base table and the SELECT statement would produce the following result.
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 35 | 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 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
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 cannot insert rows in the CUSTOMERS_VIEW because we have not included all the NOT NULL columns in this view, otherwise you can insert rows in a view in a 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.
Following is an example to delete a record having AGE = 22.
SQL > DELETE FROM CUSTOMERS_VIEW WHERE age = 22;
This would ultimately delete a row from the base table CUSTOMERS and the same would reflect in the view itself. Now, try to query the base table and the SELECT statement would produce the following result.
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 35 | 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 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Dropping Views
Obviously, where you have a view, you need a way to drop the view if it is no longer needed. The syntax is very simple and is given below −
DROP VIEW view_name;
Following is an example to drop the CUSTOMERS_VIEW from the CUSTOMERS table.
DROP VIEW CUSTOMERS_VIEW;