Consider the following query:
The query is a join over three tables, providing information about all emp and their departments.
It uses an alias in the SELECT clause to make sure that all columns in the query result have different names.
select e.empno , e.ENAME , e.init , d.dname , d.location , m.ENAME as MANAGER from emp e join departments d using (deptno) join emp m on (e.empno = d.mgr);
The following code shows how you can transform this query into a view definition.
create view empdept_v as -- This line is added select e.empno , e.ENAME , e.init , d.dname , d.location , m.ENAME as MANAGER from emp e join departments d using (deptno) join emp m on (m.empno = d.mgr);
This view is in your collection of database objects.
If we had not used an alias for m.ENAME, Oracle would give the following Oracle error message:
ORA-00957: duplicate column name