Free Training >

SQL

< Previous	Next >

SQL Views

View are logical representation of data from one or more than one table

Types of view

Simple views
Complex views
Read-only views
With check-option views

Syntax to create view:

Create view < view_name>
As < select stmt >;

What is the need for view?

Lets say I want to display the rows from emp which satisfies following conditions
Condition 1: employee should be working in deptno 30
Condition 2: job should be SALESMAN
Condition 3: sal should be greater than 1400

So, your query should be
Select * from emp
Where deptno =30 AND job =SALESMAN AND sal > 1400;

If you always want to retrieve the data which satisfies the above three conditions, better to create a view so that your work is simplified.

Ex:
Create view v1
As select * from emp
Where deptno =30 AND job =SALESMAN AND sal > 1400;

View created.
In the above example , the name of the view is V1
Now, when you want to retrieve the data which satisfies the above three conditions,
You query will be very simple ie

Select * from v1;

Table which is used for creating the view is called as base table.
In the example, name of the view is v1 and the table emp is called base table.

Simple views

When views are created using one base table it is called simple view.
The above example is a simple view.

Few more examples

Create view v10
As select * from emp
Where deptno = 10;

Create view v20
As select * from emp
Where deptno = 20;

Create view v30
As select * from emp
Where deptno =30;

I am logically classifying the data of emp table into 3 views ie v10, v20, v30

So, when I write
Select * from v10 ;
I get the data of employees working in deptno 10.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7782	CLARK	MANAGER	7839	9-Jun-81	2450		10
7839	KING	PRESIDENT		17-Nov-81	5000		10
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

Similary

Select * from v20;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7788	SCOTT	ANALYST	7566	9-Dec-82	3000		20
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7902	FORD	ANALYST	7566	3-Dec-81	3000		20

Select * from v30;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	1-May-81	2850		30
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7900	JAMES	CLERK	7698	3-Dec-81	950		30

All the above views as they are created using one base table, it is Simple views.

Can we perform DML operations on simple view?
Lets try

Insert into v30 values ( 1111, AAA, CLERK , 7698 , 11-jan-1981, 1000, 100, 30);

The response is
1 row created.

So, we can perform DML operations on simple views.
When we perform DML operations on simple views, the base tables will be effected.
The above row which we have inserted into the view v30 , will be available in the emp table.

Similarly, when we update the data in the simple view , the base table is updated.
When we delete from the simple view, the data is deleted from the base table.

Note:

Views does not occupy memory.
When we write a select stmt on a view, we get the data from the base table.

Can we see the list of all the view?
Yes, the query is

Select view_name from user_views;
User_views is an example of data dictionary tables.

Look at this example

Create view v35
As select empno, ename , sal from emp
Where deptno =30;

Select * from v35;
Output

EMPNO	ENAME	SAL
7499	ALLEN	1600
7521	WARD	1250
7654	MARTIN	1250
7698	BLAKE	2850
7844	TURNER	1500
7900	JAMES	950

Now, I want to add new column JOB in the view V35.

Can we add or drop columns in a view?
Yes, it is possible by using create or replace clause

Ex
Create or replace view v35
As select empno, ename , sal, job
From emp
Where deptno = 30;

Select * from v35;

Output

EMPNO	ENAME	SAL	JOB
7499	ALLEN	1600	SALESMAN
7521	WARD	1250	SALESMAN
7654	MARTIN	1250	SALESMAN
7698	BLAKE	2850	MANAGER
7844	TURNER	1500	SALESMAN
7900	JAMES	950	CLERK

By using create or replace, we can add columns, remove columns as well as change where conditions of the view.

Complex Views

When views are created using multiple base tables, it is called complex views.

Ex:

Create view v40
As select e.empno, e.ename, e.sal, e.deptno, d.dname , d.loc
from emp e, dept d
where e.deptno = d.deptno;

When we write
Select * from v40;

EMPNO	ENAME	SAL	DEPTNO	DNAME	LOC
7782	CLARK	2450	10	ACCOUNTING	NEW YORK
7839	KING	5000	10	ACCOUNTING	NEW YORK
7934	MILLER	1300	10	ACCOUNTING	NEW YORK
7566	JONES	2975	20	RESEARCH	DALLAS
7902	FORD	3000	20	RESEARCH	DALLAS
7876	ADAMS	1100	20	RESEARCH	DALLAS
7369	SMITH	800	20	RESEARCH	DALLAS
7788	SCOTT	3000	20	RESEARCH	DALLAS
7521	WARD	1250	30	SALES	CHICAGO
7844	TURNER	1500	30	SALES	CHICAGO
7499	ALLEN	1600	30	SALES	CHICAGO
7900	JAMES	950	30	SALES	CHICAGO
7698	BLAKE	2850	30	SALES	CHICAGO
7654	MARTIN	1250	30	SALES	CHICAGO

The view v40, is created using more than one base table, it is a complex view.

Note:
We cannot perform DML operations on complex views.

When a view is created using arithmetic operations or functions or group by clause, it is also called as complex views.

Ex:
Create view v50
As select empno, ename , sal, sal*12 annual_sal , deptno from emp;

Select * from v50;

EMPNO	ENAME	SAL	ANNUAL_SAL	DEPTNO
7369	SMITH	800	9600	20
7499	ALLEN	1600	19200	30
7521	WARD	1250	15000	30
7566	JONES	2975	35700	20
7654	MARTIN	1250	15000	30
7698	BLAKE	2850	34200	30
7782	CLARK	2450	29400	10
7788	SCOTT	3000	36000	20
7839	KING	5000	60000	10
7844	TURNER	1500	18000	30
7876	ADAMS	1100	13200	20
7900	JAMES	950	11400	30
7902	FORD	3000	36000	20
7934	MILLER	1300	15600	10

Annual_Sal column is not present in the base table, we are calculating it in the view.

Lets have another example of complex view
Ex
Create view v60
As Select deptno, sum(sal) sum_sal
From emp
Group by deptno;

Select * from v60;
Output:

DEPTNO	SUM_SAL
30	9400
20	10875
10	8750

V60, is also an example of complex views. As we have used group functions / group by clause.

Note:
We cannot perform DML operations on complex views.

Read only views:

We can only read the view.
Reading is executing select stmt on the view.
We cannot perform write operations ( DML Operations) on these views.

Ex:
Create view v70
As select empno, ename , sal
from emp
with read only;

Now, we cannot perform insert, update and delete operations on View v70.
We can execute only select stmt on the view.

With Check-option view

DML operations are allowed only when where clause is satisfied.

Ex:
Create view v80
As select empno, ename , sal
From emp
Where sal > 2000
With check option;

Select * from v80;

Output

EMPNO	ENAME	SAL
7566	JONES	2975
7698	BLAKE	2850
7782	CLARK	2450
7788	SCOTT	3000
7839	KING	5000
7902	FORD	3000

Consider the two insert commands
Insert into v80 values ( 1111, AAAA, 1200); --error with check option where clause violation
Insert into v80 values ( 2222, BBBB, 2100); -- valid

Consider the two update commands

Update v80 set sal = 3100
Where empno = 7566; -- valid

Update v80 set sal = 1000
Where empno = 7698; -- error with check option where clause violation

When we perform DML operations on WITH CHECK OPTION views, it validates the where clause.

DML operations are allowed only when WHERE clause is satisfied.


< Previous	Next >