Views

Views

A view is like a window through which data on a table can be viewed or changed. It is a virtual table that is – it does not have any data of its own, but derives the data from the table it is associated with. It manipulates data in the underlying base table.

Syntax :

Create [or Replace] View <view name>

[ ( column1, column2,----)] as Select <select list>

Replace Option :

This option is used to replace the definition of existing view with new definition. It can be replaced with another view only, but not with a table.

Advantages of Views

1. Views restrict access to the database. Selecting from a view can display a restricted portion of the data base
2. Views allows users to make simple queries to retrieve the results from complicated queries
3. As a view does not store any data, the redundancy problem does not arise
4. One view can be used to transparently retrieve data from several tables.
5. Views also allow the same data to be seen by different users in different ways

Types of View

There are two types of view

1. Simple View

2. Composite View

Simple View

When a view is create basing on a single table then it is called a simple view

Syntax :

Create [or replace ] view <view name> as Select <select list> from <table1>;

Examples :

1. Create a view, which shows all employees details whose salary is between 2000 and 3000

• Create view empsal as select * from emp where sal>=2000 and sal<=3000;

To see the details of a view, give the following command

Select * from <view name>

Ex : Select * from sample;

2. write a query which Creates a view, that shows all employees details who are belonging to dept 20

• Create or replace view empsal as select * from emp where deptno=20

Ex : Select * from empsal;

3. Create a view, which shows all employees details whose salary is between 2000 and 3000 and who are belonging to dept 20

• Create or replace view empsal as select * from emp where deptno=20 and Sal between 2000 and 3000;

We can change column names in a view at the time of creation

Create a view, which shows all employees details who belong to dept 20

• create or replace view emp20 as select * from emp where deptno=20
• create or replace view dept20(emp_no, emp_name, salary) as select empno, ename, sal from emp;

select * from dept20;

EMP_NO EMP_NAME SALARY

----------- ---------- --------------

7369 SMITH 800

7499 ALLEN 1600

7521 WARD 1250

7566 JONES 2975

7654 MARTIN 1250

We can Create a view, using another view

• create or replace view emp21 (ename,sal) as select empname,basic from emp20;

select * from emp21;

ENAME SAL

----------- -----

SMITH 800

JONES 2975

Mahesh 3000

ADAMS 1100

FORD 3000

Note :

1. If column names are not given then it defaultly takes all the given column names in the table
2. Number of columns in view ie in Bracket must be equal to number of columns in the select statement

Manipulating the Base Tables Through Views

The insert, update and delete commands can also be used with views. Using these commands with views is an indirect way of manipulating tables. Since a view nothing but a virtual table. Any manipulations ie deletion, updation will automatically affects the main table.

Example :

Create a view, which shows All students details Whose avg marks is >=60

• Create or replace view studview as select * from students where average>=60;

• select * from studview
• Update students set roll=100 where roll=1;
• Select * from studview;
• Insert into students values(101,'Nayana Tara');
• Select * from students;
• delete from studview where roll=101;
• select * from studview;
• Select * from students;

Note : What ever operations you perform on views will be affected to the table that is associated with the view

To Create Read Only Views

We can create read only view. When a view is created with any arithmetic expression orwith any group by functions then it cannot be modified or changed with insert or update or delete statements. So this can be called as a Read Only Views

Example :

Creates a view, with all department numbers and number of Employee’s working under each department

• create view deptgroup as select deptno,count(*) "no of emps" from emp group by deptno
• select * from deptgroup;

EPTNO no of emps

----- ----------

10 3

20 5

30 6

Note : When Ever We Apply Any Dml Operation On This View, We Will Get An Error Message

• insert into deptgroup values(40,2);

*ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

• Delete from deptgroup where deptno=10

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

Composite View

A view created by retrieving data from 2 or more tables. A composite view can not be updated or we can not apply any DML operations except select statement. Because it contains different columns from different tables.

Syntax :

Create or replace view <view name> as select <fields list> from <table1>,<table2>,……..

1. create view which should show all columns in emp and dept table whose deptno in emp is equal to deptno in dept table

• create or replace view empdept as select empno, ename, sal, dname, loc, dept.deptno from emp, dept where emp.deptno=dept.deptno

select * from empdept;

insert into empdept values(1000,'hello',5000,'Production','tirupati',20)

ERROR at line 1:

ORA-01776: cannot modify more than one base table through a join view

Note : Since here two table values are included. So at a time we cann’t insert or modify two tables data at a time

But if you give like this ,

update empdept set empno=1000 where empno=8900;

update empdept set empno=2000,ename='jj',sal=1000 where empno=1000

update empdept set dname='cmp',loc='tpt',deptno=30 where empno=2000

Note : Records will be updated, since you are updating values only on a single table related columns, if you apply any operations on both table columns then can not perform any operation

select * from empdept;

Dropping a view

Drop View : This command is used to drop any view from the database

Drop view <view name>

Example : Drop View empdept;

Note :

1. To show all Views details (both user defined and pre defined views)

• Select * from all_Views;

1. To show all User Defined Views details

• Select * from user_Views;
• Select view_name from all_Views;