DBMS View: Read, Update, Create and Drop
View in DBMS: The View is a logical or virtual table that allows users to view or manipulate parts of the table. View is also a table which consists of rows and columns. Users can easily create the view in the database by selecting the fields from two or more tables in the same database.
Users can also execute the DDL and DML operations on View. Like, the tables, we can also do indexing in views. It is more difficult to normalize the database over the second normal form if views are not used.
Following are some of the advantages of View over database tables:
- Using Views, we can join multiple tables into a single virtual table.
- Views hide data complexity.
- In the database, views take less space than tables for storing data because the database contains only the view definition.
- Views indicate the subset of that data, which is contained in the tables of the database.
Types of DBMS View
In DBMS, the view can be categorized into the following two types:
1. Read-Only View
2. Updateable View
Read-Only View
It is a type of view that allows users only to access the data of view.
Updateable View
It is a type of view that allows users to access, insert, update or delete the data from the view.
Create the View in RDBMS
We have learned the meaning of the DBMS view. Now, we will discuss how to create a view in SQL. Using the following syntax, any user can easily create the view in SQL:
CREATE VIEW view_name AS SELECT column1, column2,.....,Column N FROM name_of_table WHERE condition;
Now, we will take the following example, which helps us to understand easily.
Example: This example uses the table Employee_Details, which contains three columns Emp_ID, Emp_Name, and Emp_Address.
Emp_ID | Emp_Name | Emp_Address |
101 | Anuj | Delhi |
102 | Aman | Mumbai |
103 | Ram | Goa |
104 | Satish | Delhi |
105 | Abhay | Mumbai |
106 | Anuj | Delhi |
Emloyee_Details
Now, we create the view as employee_view from the Emloyee_Details table.
CREATE VIEW Employee_View AS SELECT Emp_Name, Emp_ADDRESS FROM Employee_Details WHERE EMP_ID < 104;
If we want to see the data of the view, then we have to type the following query as same as typed for accessing the data of table:
SELECT * FROM Employee_View;
Emp_Name | Emp_Address |
Anuj | Delhi |
Aman | Mumbai |
Ram | Goa |
Drop the View
If any user wants to delete the view which is created in the database, then that user has to use the following syntax:
DROP VIEW view_name;
If we want to delete the above view, which is created using the Emoloyee_Details table, then we have to delete this:
DROP VIEW Employee_View;