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;