How To Create View

Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also,Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.

Creating Complex View

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;

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, dept d where 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;

Now you can treat this EMP_DET view same as any other table

Modifying a View :-

With the OR REPLACE option, a view can be created even if one exists with this name already, thus replacing the old version of the view for its owner. This means that the view can be altered without dropping, re-creating, and regranting object privileges.

CREATE OR REPLACE VIEW empvu80
AS SELECT employee_id, first_name,last_name,
salary, department_id
FROM employees
WHERE department_id = 80;
View created.

Related Post:-