12일차 # 10-7: Creating a View
SQL> create or replace view d10vu (emp_no, name, ann_sal, job, d_no)
as select empno, ename, sal*12, job, deptno
from emps
where deptno = 10
from emps
*
ERROR at line 3:
ORA-00942: table or view does not exist
SQL> create or replace view d10vu (emp_no, name, ann_sal, job, d_no)
as select empno, ename, sal*12, job, deptno
from emp
where deptno = 10
View created.
SQL> desc d10vu
Name Null? Type
----------------------------------------------------------------- -------- -----------------
EMP_NO NOT NULL NUMBER(4)
NAME VARCHAR2(10)
ANN_SAL NUMBER
JOB VARCHAR2(9)
D_NO NUMBER(2)
SQL> create or replace view d20vu
as select empno, ename, sal*12, job, deptno
from emp
where deptno = 20
as select empno, ename, sal*12, job, deptno
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
SQL> create or replace view d20vu
as select empno, ename, sal*12 as ann_sal, job, deptno
from emp
where deptno = 20
View created.
SQL> desc d20vu
Name Null? Type
----------------------------------------------------------------- -------- -----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
ANN_SAL NUMBER
JOB VARCHAR2(9)
DEPTNO NUMBER(2)
SQL> select empno as emp_no, ename as name, sal*12 as ann_sal, job, deptno
from emp
where deptno = 10;
EMP_NO NAME ANN_SAL JOB DEPTNO
---------- ---------- ---------- --------- ----------
7782 CLARK 29400 MANAGER 10
7839 KING 60000 PRESIDENT 10
7934 MILLER 15600 CLERK 10
SQL> select *
from d10vu;
EMP_NO NAME ANN_SAL JOB D_NO
---------- ---------- ---------- --------- ----------
7782 CLARK 29400 MANAGER 10
7839 KING 60000 PRESIDENT 10
7934 MILLER 15600 CLERK 10
SQL> select * from d20vu;
EMPNO ENAME ANN_SAL JOB DEPTNO
---------- ---------- ---------- --------- ----------
7369 SMITH 9600 CLERK 20
7566 JONES 35700 MANAGER 20
7788 SCOTT 36000 ANALYST 20
7876 ADAMS 13200 CLERK 20
7902 FORD 36000 ANALYST 20