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
Views