VIEW

VIEW: view is a logical table based on one or more table or views

 The table upon which a view is base are called base table

Note: it doesnot contain any data itself

View are used for security purpose because they provide encapsulation of the name of table

Data is in the virtual table not stored permanantely

View display only selected data

View is also known as stored select statement

TYPES OF VIEW

Simple view

Complex view

Force view

Read only view

With check point view

 

     Emp1                 emp2

eid	ename	eadd
10	Ankit	delhi
20	raju	mumbai
30	Ram	delhi
40	ankush	noida

 

eid	ename	eadd
10
20
30
40

Create view view_name as select * from table name where condition;

Simple view:

The view which contain a sub query that retrieves from one base table or view is called simple view

Syntax;

Create view view_name as select * from table name;

 Create view emp_view as select *from emp;

View created

Select * from emp_view;

EID	ENAME	EADD
10	Ankit	mysore
20	ankush	sandore
30	anish	sandore
40	ashish	kolar
50	akhilesh	sandore
60	ajay	mysore

Select * from emp_view;

 

EID	ENAME	EADD
10	Ankit	mysore
20	ankush	sandore
30	anish	sandore
40	ashish	kolar
50	akhilesh	sandore
60	ajay	mysore
70	akash	rampur

 Select * from emp;

EID	ENAME	EADD
10	Ankit	mysore
20	ankush	sandore
30	anish	sandore
40	ashish	kolar
50	akhilesh	sandore
60	ajay	mysore
70	akash	rampur

 Insert into emp values(80,’gaurav’,’mysore’);

 Select * from emp_view;

EID	ENAME	EADD
10	Ankit	mysore
20	ankush	sandore
30	anish	sandore
40	ashish	kolar
50	akhilesh	sandore
60	ajay	mysore
70	akash	rampur
80	gaurav	mysore

Select * from emp;

EID	ENAME	EADD
10	Ankit	mysore
20	ankush	sandore
30	anish	sandore
40	ashish	kolar
50	akhilesh	sandore
60	ajay	mysore
70	akash	rampur
80	gaurav	mysore

 Create view emp_view as select * from emp where eadd=’mysore’;

Name is already used by an existing object

 Create or replace view emp_view as select * from emp where eadd=’mysore’;

View created

 Select * from emp_view;

EID	ENAME	EADD
10	ANKIT	MYSORE
60	AJAY	MYSORE
80	GAURAV	MYSORE

 Insert into emp_view values(90,’chandan’,rampur’);

Select * from emp_view;

EID	ENAME	EADD
10	ANKIT	MYSORE
60	AJAY	MYSORE
80	GAURAV	MYSORE

 Select * from emp;

EID	ENAME	EADD
10	Ankit	mysore
20	ankush	sandore
30	anish	sandore
40	ashish	kolar
50	akhilesh	sandore
60	ajay	mysore
70	akash	rampur
80	gaurav	mysore
90	chandan	rampur

 Insert into emp_view values(100,’sushil’,’mysore’);

1 row created

Select * from emp_view;

EID	ENAME	EADD
10	ANKIT	MYSORE
60	AJAY	MYSORE
80	GAURAV	MYSORE
100	SUSHIL	MYSORE

 SELECT * FROM EMP;

EID	ENAME	EADD
10	ANKIT	MYSORE
20	GOURAV	MYSORE
30	ALTAF	MYSORE
40	ASHISH	Kolapur
50	ABHLESH	Sandur
70	AKASH	Rampur
80	GOURAV	Mysore
90	CHANDAN	Rampur

 Update emp_view set ename=’altaf’ where eid=100;

1 row updated

Select * from emp_view;

 

eid	ename	eadd
10	ankit	mysore
60	ajay	mysore
80	gaurav	mysore
100	altaf	mysore

 select * from emp;

EID	ENAME	EADD
10	ANKIT	MYSORE
20	GOURAV	MYSORE
30	ALTAF	MYSORE
40	ASHISH	Kolapur
50	ABHLESH	Sandur
70	AKASH	Rampur
80	GOURAV	Mysore
90	CHANDAN	Rampur
100	ALTAF	Mysore

eid	ename	eadd
10	ankit	mysore
60	ajay	mysore
80	gaurav	mysore
100	altaf	mysore

commit;

update emp_view set ename=’pankaj’ where eadd=’rampur’;

0 row updated

Select * from emp;

EID	ENAME	EADD
10	ANKIT	MYSORE
20	GOURAV	MYSORE
30	ALTAF	MYSORE
40	ASHISH	Kolapur
50	ABHLESH	Sandur
70	AKASH	Rampur
80	GOURAV	Mysore
90	CHANDAN	Rampur
100	ALTAF	Mysore

 Delete from emp_view where eid=60;

1 row deleted

Select * from emp_view;

EID	ENAME	EADD
10	ANKIT	MYSORE
80	GOURAV	MYSORE
100	ALTAF	MYSORE

 Commit;

Select * from emp;

Delete  from emp_view where eadd=’kolapur’;

 

EID	ENAME	EADD
10	ANKIT	MYSORE
20	GOURAV	MYSORE
30	ALTAF	MYSORE
40	ASHISH	Kolapur
50	ABHLESH	Sandur
70	AKASH	Rampur
80	GOURAV	Mysore
90	CHANDAN	Rampur
100	ALTAF	Mysore

 COMPLEX VIEW:

Complex view contain sub-queries where sub-queries contain query

The view which is constructed with the help of more than(one base table) than it is called complex view

Syntax: create view view_name as select * from table_name1 union select * from table_name2;

Create view vname as select * from emp union select * from dept;

View created

Select * from EMP;

EID	ENAME	EADD
20	ANKUSH	SANDORE
30	ANISH	SANDORE
40	ASHISH	KERALE
50	AKHILESH	SANDORE

 Select * from DEPT;

DEPT_ID	DNAME	DOJ
20	Sale	25-aug-19
30	Hr	18-aug-21
40	Account	27-feb-20
50	loan	13-jan-18

 Select * from vname;

 

EID	ENAME	EADD
20	ANKUSH	SANDORE
30	ANISH	SANDORE
40	ASHISH	KERALE
50	AKHILESH	SANDORE
70	AKASH	RAMPURE
20	Sale	25-aug-19
30	Hr	18-aug-21
40	Account	27-feb-20
50	loan	13-jan-18

 READ ONLY VIEW;

It only allow to perform read operation on base table it restrict to perform write operation

Syntax:

Create view view_name as select * from table_name with read only;

Create view rview as select ename,eadd from emp with read only;

View created

 Select * from emp;

EID	ENAME	EADD
20	ANKUSH	SANDORE
30	ANISH	SANDORE
40	ASHISH	KERALA
50	AKHILESH	SANDORE
70	AKASH	RAMPUR

select ename eadd from rview

ENAME	EADD
ANKUSH	SANDORE
ANISH	SANDORE
ASHISH	KERALE
AKHILESH	SANDORE
AKASH	RAMPURE

Insert into rview(ename,eadd)values(‘gaurav’,’bangalore’);

Cannot perform a dml operation on aread only view

WITH CHECK POINT VIEW

In with check option view if the, given condition satisfy in selected column then allow to perform write operation otherwise restricted.

Syntax: create view view_name as select * from table_name where condition with check option.

 Create view cview as select * from emp where eadd=’sandore’ with check option;

View created

Select * from cview;

EID     ENAME         EADD

20 ankush         sandore

30 anish       sandore

50 akhilesh  sandore

Insert into cview values(90,’harsh’,’kolar’);

View with check option where clause violation

FORCE VIEW:

Force view is used basically for the situation when we create a view using a table but the table is not exists, at that time we use force view

Syntax: create force view view_name as select * from table_name;

Desc stu;

Object stu does not exist

Create view v as select * from stu;

Table or view does not exist

Create force view v as select * from stu;

View created with compilation errors.