CREATE VIEW

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  
WHERE e.deptno = d.deptno;