Introduction
Views are virtual table that contains data from one or multiple tables. It can have rows and columns like the actual table in a database. Based on certain conditions, the view can contain either the whole table or some specific table rows.
Recommended topics, Coalesce in SQL and Tcl Commands in SQL
Create a view
We use CREATE VIEW statement to create a view. We can create views from single or multiple tables.
CREATE VIEW view_name AS SELECT column1, column2…….. FROM table_name WHERE [condition]; |
Example
We will create a view using the customer table given below in this example. The view will contain the name and age from the customer table.
SQL > CREATE VIEW CUSTOMERS_VIEW AS SELECT name,age FROM CUSTOMERS; |
Like table queries, we can query the view to see the data.
SQL> SELECT * FROM CUSTOMERS_VIEW |
Output
Update a View
Certain conditions are needed to update a view. If any requirements are not satisfied, the view is not updated.
Conditions:
- The view must have all NOT NULL values.
- The SELECT statement should not contain the DISTINCT keyword.
- The view should not be generated using nested queries or complex queries.
- The SELECT clause used to create the view should not include the GROUP BY clause or ORDER BY clause.
- We should create the view from a single table. If we create a view using multiple tables, we will not update the view.
To update a view, we use CREATE OR REPLACE VIEW statement.
CREATE OR REPLACE VIEW view_name AS SELECTcolumn1, column2,.... FROM table_name WHERE condition; |
Example
SQL > UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = ‘Ramesh’ ; |
The above statement will ultimately update the base table CUSTOMERS and the changes reflected in the CUSTOMERS_VIEW. We can see the difference by the SELECT statement.
Insert into a View
The same rules to the UPDATE command also apply to the INSERT command. We use INSERT INTO statement to insert a row in a view.
Syntax
INSERT INTO view_name(column1, coulmn2, column3,..) VALUES(value1, value2, value3..); |
Example
SQL > INSERT INTO CUSTOMERS_VIEW(ID, NAME, AGE,ADDRESS, SALARY) VALUES(8,’Rahul’,26,’Banglore’, 15000); |
Delete in a View
Syntax
DELETE FROM view_name WHERE condition; |
Example
In this example, we will delete records having AGE = 22
SQL > DELETE FROM CUSTOMERS_VIEW WHERE age = 22; |
We can use the SELECT statement to see the updated CUSTOME_VIEW.
The WITH CHECK OPTION
The use of the WITH CHECK OPTION ensures that all UPDATE and INSERTs will satisfy the conditions in the view definition.
If they do not satisfy the conditions, the UPDATE or INSERT returns an error.
Example
In this example, the WITH CHECK OPTION will prevent the entry of any NULL values in the view's AGE column because the view is defined such that there is no NULL value in the age column.
CREATE VIEW CUSTOMERS_VIEW AS SELECT name,age FROM CUSTOMERS WHERE age is NOT NULL WITH CHECK OPTION; |
Drop a view
We can delete a view using the DROP VIEW statement followed by the view name.
Syntax
DROP VIEW view_name; |
Reasons to use views:
Views are primarily used for the following reasons.
- SQL knowledge is required to create applications, which use a database for handling data. Views of the original table should be used in the application instead of using the tables themselves. In this way, when we refactor our database, our legacy code will see the original schema via the view without breaking the application.
- Views enhance reusability. We have not to write complex queries using joins repeatedly. Views convert all the complexity in a single line of query. Shorted and concise code are easier to integrate into our application. This will eliminate the chances of typos, and your code will be more readable.
- Views help in data security. We can use views to show only authorized information to users and hide sensitive data like credit card numbers.
Frequently Asked Questions
Q1. What are the uses of views?
Views are used for restricting data access and hiding data complexity. It also simplifies commands for the user. It allows users to select information from multiple tables without requiring them to know how to perform a join.
Q2. What are the disadvantages of views?
Views are virtual tables. So, it is not possible to create an index on views.
Q3.What are the types of views in DBMS?
Static views and dynamic views are two types of views present in DBMS. Dynamic views contain data from one or more tables and have all the columns from the specified tables. When related objects or extended objects are created or changed, dynamic views are automatically updated.