Views in DBMS

Creating a view

A view is created by selecting fields from one or more tables present in a database

Syntax

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

Note: 

Whenever a user creates a view, database engine recreates the data using the views SQL statement i.e. view always shows upto date data

Consider the tables StudentDetails and StudentMarks

StudentDetails

 StudentMarks

Simple Views in DBMS: Creating a view from a single table 

In this example, we will create a view named as DetailsView  from a single table StudentDetails

CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

The data present in a view can be seen just like a normal table select query

SELECT * FROM DetailsView;

Output:

Complex view: Creating a view from multiple tables

• In this example will create a view named MarksView   by taking data from both the table’s student details and student marks
• To create a View from multiple tables just simply include multiple tables in the SELECT statement.

CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

To display data of View Marks:

SELECT * FROM MarksView;

Output:

Deleting views in DBMS

• You can simply delete a view by using the Drop statement
• That view is not used anymore

Syntax: 

DROP VIEW view_name;

Example 

DROP VIEW MarksView;

Updating views

Views are updated only if certain conditions are met otherwise if any one of the conditions are not met views will not be updated

Criteria for View Updating 

• The select statement used in the create view statement should not include group by clause or order by clause
• The select statement must not contain distinct keyword
• A view should not be created from nested or Complex queries
• A view should be created from a single table but if the view is created from more than one table then it is not allowed for updating

CREATE OR REPLACE VIEW 

Create or replace view statement is used to add or remove fields from existing views

Syntax:

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

Update the view MarksView and add the field AGE to this View from StudentMarks Table,

CREATE OR REPLACE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS, StudentMarks. AGE
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

Fetch all the data from MarksView now as:

SELECT * FROM MarksView;

Output

Inserting a row into a view 

We can use insert into statement of SQL to insert a row in a view just like inserting a row in an ordinary table

Syntax: 

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

Example 

INSERT INTO DetailsView(NAME, ADDRESS)
VALUES("Preity","Hyderabad");

Fetch all the data from DetailsView now as,

SELECT * FROM DetailsView;

Output

Deleting a row from a view 

• A row in a view can be deleted just like simply deleting rows from a Table using delete statement
• But remember a row in a view can be deleted only if the row is actually deleted in the original table from which it is created

Syntax: 

DELETE FROM view_name
WHERE condition;

Example

DELETE FROM DetailsView
WHERE NAME="Preity";

Fetch all the data from DetailsView now as,

SELECT * FROM DetailsView;

Output:

Advantages and disadvantages of views 

Advantages 

• Enforce Business Rules: By placing complicated or misunderstood business logic into the view, you can be sure to present a unified portrayal of the data which increases use and quality.
• Consistency: Once defined their calculations are referenced from the view rather than being restated in separate queries.  This makes for less mistakes and easier maintenance of code.
• Security: For example, you can restrict access to the employee table, that contains social security numbers, but allow access to a view containing name and phone number.
• Simplicity: Databases with many tables possess complex relationships, which can be difficult to navigate if you aren’t comfortable using Joins.
• Space: Views take up very little space, as the data is stored once in the source table. 

Limitations 

• Modifications: Not all views support INSERT, UPDATE, or DELETE operations. Complex multi-table views are generally read-only.
• Performance: Hugely complex job for the database engine.  That is because each time a view is referenced, the query used to define it, is rerun.