how to create view in oracle?

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