Sunday, August 10, 2014

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

1 comment:

  1. I do agree with all the ideas you have presented in your post. They’re really convincing and will certainly work. Still, the posts are very short for newbies. Could you please extend them a little from next time?..Keep this great work
    Web development Company in India Company | Web development Company Bangalore
    Web Development Company in Bangalore | Responsive Web development

    ReplyDelete