Creating Views
Suppose we have EMP and DEPT table. To see the empno, ename, sal,
deptno, department name and location we have to give a join query like this.
select
e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
From emp e, dept d where e.deptno=d.deptno;
So everytime we want to see emp details and department names where
they are working we have to give a long join query. Instead of giving this join
query again and again, we can create a view on these table by using a CREATE
VIEW command given below
create view emp_det
as
select e.empno,
e.ename,e.sal,e.deptno,d.dname,d.loc
from emp e JOIN dept d ON e.deptno=d.deptno;
Now to see the employee details and department names we don’t have
to give a join query, we can just type the following simple query.
select * from emp_det;
This will show same result as you have type the long join query.
Now you can treat this EMP_DET view same as any other table.
For example, suppose all the employee working in Department No. 10
belongs to accounts department and most of the time you deal with these people.
So every time you have to give a DML or Select statement you have to give a
WHERE condition like .....WHERE DEPTNO=10.
To avoid this, you can create a view as given below
