Views in DBMS

View

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 created to display the information requested by the end user rather than the entire table.

Advantage of View over database table :

• We can combine several tables into a single table by using views.

• Views conceal the intricacy of the data.

• Because the database just includes the view definition , views use less space in the database than tables for data storage.

• The subset of such data, which is present in the database's tables , is indicated by views.

1. Creating Views in DBMS 

Database views are established with the CREATE VIEW statement. Views can be generated using a single table, many tables, or another view. To build a view, a user must have the proper system privileges for the specific implementation.

Syntax 

CREATE VIEW view-name AS
SELECT column1, column2,....
FROM table-name
WHERE condition ;

Example :

CREATE VIEW Students-CSE AS
SELECT ROLL_NO., Name
FROM Students
WHERE Branch = ' CSE '

2. Updating a View 

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

Syntax

CREATE OR REPLACE VIEW view-name AS
SELECT column1, column2....
FROM table-name
WHERE condition;

The following SQL query adds the "Mobile" column to the "Students-CSE" view:

Example

CREATE OR REPLACE VIEW STUDENTS_CSE AS
SELECT Roll No. , Name , Mobile 
FROM Students
WHERE Branch = ' CSE ' ;

CREATE VIEW creates a view from a collection of tables, views, or both. 

REPLACE VIEW redefines an existing view, or if the provided view doesn't exist,

3. Inserting a row in a view 

We can insert a row in a View in the same manner we would in a table. To insert a row in a View, we can use SQL's INSERT INTO statement.

Syntax

INSERT INTO view-name (column1, column2, ...)
VALUES (value1, value2 ,...);

Example 

INSERT INTO Students-CSE (Roll-no. , Name , Mobile )
VALUES (521 , 'ram' , 998765896 );

4. Deleting a row in a view 

Rows can be deleted from a view in the same way that they can from a table. To remove rows from a view, we can use the SQL DELETE statement.

Syntax

DELETE FROM view-name

WHERE condition ;

Example 

DELETE FROM Students-cse

WHERE Name = "ram" ;

5. Querying a View

We can query the view as follows 

Syntax 

SELECT *FROM view-name

EXAMPLE 

SELECT * FROM STUDENTS_CSE ;

6. Dropping a view 

To remove a view from a database, use the DROP VIEW statement.

Syntax 

DROP FROM view-name 

Example

DROP FROM Students-CSE ;