This statement defines a view, a logical table based on one or more tables or views. A view contains no data. The tables upon which a view is based are called base tables.
If the CREATE VIEW or DROP VIEW command does not specify the qualifier and owner, the current qualifier returned by IP_GETINFO is used as the qualifier and the user name is used as the owner. The ORDER BY clause is not allowed in a view definition.
Syntax
CREATE VIEW [qualifier.][owner.]view_name [(view_column_name,...)] AS subquery
Examples
View that exposes some of the columns of emp table:
CREATE VIEW emp_info AS SELECT empno, ename FROM emp;
View that exposes derived columns:
CREATE VIEW emp_payroll(empno, total_pay) AS SELECT empno, sal+comm FROM emp;
CREATE VIEW dept_info(deptno, emp_count) AS SELECT deptno, COUNT(*) FROM emp
GROUP BY deptno;
View that exposes a restricted result set of the base table:
CREATE VIEW emp_high_pay(empno, sal) AS SELECT emp, sal FROM emp
WHERE sal > 100000
View that hides data complexity by exposing the result of a JOIN query:
CREATE VIEW emp_full_info AS (e.empno, d.deptno, d.deptname FROM emp e, dept d
