/    /  DBMS – Views

Views

Views are like virtual tables, which also contain rows and columns like that of a normal table in a database. A view can be created using more than one table in a database. A view can either have a specific row with a specification or may also contain all the rows.

  

Syntax : Create  view  V  as  < query expression > ‘View’  is a logical relation.

 

Let us understand this with a few examples.

 

Example-1 : Create a view ‘student’ containing SNo, Sname.

 

 Create  view  student  as

 (Select SNo, SName

 From STUD) ;

Output :   

student  (VIEW)

SNo  SName

 121 xyz

 123 pqr

    126 mnp

 128 abc

 130 jkl

 

Example-2 : Create a view containing  SNo, SName, CNo, CName.

 

 Create view  stud_course

 (Select  SNo, SName, CNo, CName

 From STUD, COURSE

 Where  STUD.CNo = COURSE.CNo ;

 

 

Output :    stud_course (VIEW)

  

SNo  SName CNo CName

 121  xyz  61 DBMS

 126  mnp  65 CO

  128      abc 67 OS

  

Example-3:  Find SName and the CName the student is studying

 using stud_course view

Select SName, CName 

   From stud_course ;

Output :  SName CName

 xyz  DBMS

    mnp CO

  abc OS

 

Example-4 :  Create a view containing SName and DName in which he is studying.

 

   Consider following relations :

R1 :  STUD R2 : DEPT

SNo SName DNo   DNo   DName

121 xyz  21 21 CSE

123 pqr  21 22 IT

126 mnp  22 23 ECE

128 abc 22 24 ME

130 jkl  23 25 EEE

———————————-                     —————————-

   Create a view stud_dept as

 (Select  SName, DName

 From STUD, DEPT

Where STUD.DNo = DEPT.DNo ;

 Output :  stud_dept  (VIEW)

   SName DName

   xyz  CSE

   pqr  CSE

   mnp  IT

   abc IT

   jkl  ECE

Example-5 :  Find the list of students studying in ECE department

   using stud_dept view.

 

Select  SName

From stud_dept

Where DName = ‘ECE’ ;.

 Output :  SName

 jkl 

 

There are three stages of Views: 

  • Materialized Views : If a view is stored in the memory, then it is called a ‘materialized view’.
  • Materialized View Maintenance : The process of keeping materialized view up-to-date is called ‘materialized view maintenance’.
  • Update of a View : Like a relation, one can update a view also.

  

Example-6 :  Insert a tuple into the view ‘stud_dept’.

  

Insert into stud_dept values (‘ghi’, ‘CIVIL’) 

 

Example-7 :  After inserting the above tuple, display the student name studying in the CIVIL department using the view ‘stud_dept’.

 

   Select SName

   From stud_dept 

   Where DName = ‘CIVIL’ ; 

 

Output :      SName

 ghi

 

Reference

Views