Views in DBMS

 VIEWS

INTRODUCTION

View is a virtual table created by a query by connecting one or additional tables.

The fields in a view are fields from one or additional real tables in the database.

To create a view we use the CREATE VIEW statement.

It is operated equally to the base table but does not contain any information of its own. 

What is a View in DBMS..?

Generally complications arises in SQL queries because of joins, Group By clauses and other referential dependencies in SQL. So for the aim of simplification virtual data can be used. In addition to this security challenges can be resolved by giving solely the required access to user and proscribing access to other data. This can be done with the help of views in DBMS.

For instance, a user needs only 2 columns of data, so the Database Administrator can create a virtual table of those 2 columns that the user needs with the help of view.

CREATING A SIMPLE VIEW

A simple view takes only one table and doesn’t take any inbuilt SQL functions like MAX( ),MIN( ) etc.

   SYNTAX

           CREATE [OR REPLACE] VIEW view_name AS  SELECT columns  FROM tables     

           [WHERE conditions];    

    In the example given below we are creating a simple view named ‘v’                          

    with  the columns named Roll_no, Name, branch and Total_marks from

    table named exam_details with the required condition.

         TO SEE CREATED VIEW

• We can use the following syntax to see a view:

     SELECT * FROM view_name;  

 

EXAMPLE

       

UPDATING A VIEW

We update a view to add more columns to the existing view. We can use ALTER VIEW statement for adding new columns, new columns will replace or get added to the view. We can use the following syntax to update a view:

             ALTER VIEW view_name AS  SELECT columns FROM table WHERE conditions; 

 

DELETING A VIEW

We can delete or drop a view using the DROP statement. DROP statement completely deletes the structure of the view. We can use the following syntax to delete a view:

             DROP VIEW [IF EXISTS] view_name;  

 EXAMPLE    

CREATING A COMPLEX VIEW

A view made of multiple tables and contains inbuilt SQL functions like MIN( ),MAX( ) or GROUP BY clause is called a complex view. In this type of  view data of multiple table is accessed using joins or cross products

We can use the following syntax:

               

 CREATE VIEW view_name  AS SELECT Table1.column1, Table1.column2,   

Table2.column3  FROM   Table1,Table2  WHERE  condition; 

In the example given below we chose a column named Roll_no and Name from table student_info and column named Total_marks and Result from table student_marks.

 

EXAMPLE

MANIPULATING DATA IN A VIEW

We can manipulate data in view without changing the data in the original table. Following conditions must be fulfilled for this purpose:

• The GROUP BY and ORDER BY clauses and the DISTINCT keyword should not be used in the SELECT statement.

• No Null value should be present in the view.

• Construction of the view should be done without Nested or complex queries.

• Manipulation can be done for the view of a single table, Multi table view can’t be manipulated.

Manipulation of data includes inserting or deleting a row from the view.

INSERTING A ROW IN A VIEW

We can use the following syntax to insert a row into view:

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

In the example given below we added a row with the entries as Sam, Nagpur, Computer for the columns Name, Native_City, Branch respectively for the view v1.

 

EXAMPLE    

 

DELETING A ROW IN A VIEW

We can use the following syntax to insert a row into view:

DELETE FROM ViewName WHERE condition;

In the example given below we delete the same row inserted in the above example.

EXAMPLE

THUS WE CAN CONCLUDE THAT VIEWS FIND A GREAT IMPORTANCE IN VIRTUAL TABLE CREATION FOR THE PURPOSE OF SECURITY AND TO AVOID COMPLICATIONS.