Views in DBMS

A view is a table whose rows are not explicitly stored, a view is a virtual table based on the result-set of an SQL statement. 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.

A view is generated to show the information that the end-user requests the data according to specified needs rather than complete information of the table.

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.

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.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

CREATE VIEW Students_CSE AS
SELECT Roll_no,Name
FROM Students
WHERE Branch = 'CSE';

Updating a View

A view can be updated with the CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

The following SQL adds the "Mobile" column to the "Students_CSE" view:

CREATE OR REPLACE VIEW Students_CSE AS
SELECT Roll_no,Name,Mobile
FROM Students
WHERE Branch = 'CSE';

CREATE VIEW defines a view on a set of tables or views or both.
REPLACE VIEW redefines an existing view or, if the specified view does not exist,

Inserting a row in a view

We can insert a row in a View in a same way as we do in a table. We can use the INSERT INTO statement of SQL to insert a row in a View.

INSERT INTO view_name(column1, column2, ...)
VALUES(value1,value2,.....);

INSERT INTO Students_CSE(Roll_no,Name,Mobile)
VALUES(521,'ram',9988776655);

Deleting a row in a view

Deleting rows from a view is also as simple as deleting rows from a table. We can use the DELETE statement of SQL to delete rows from a view.

DELETE FROM view_name
WHERE condition;

DELETE FROM Students_CSE
WHERE Name="ram";

Querying a View

We can query the view as follows

SELECT * FROM view_name

SELECT * FROM Students_CSE;

Dropping a View

In order to delete a view in a database, we can use the DROP VIEW statement.

DROP FROM view_name

DROP FROM Students_CSE;