Example 1: Create a view named MA_PROJ over the PROJECT table that contains only those rows with a project number (PROJNO) starting with the letters 'MA'.
CREATE VIEW MA_PROJ AS SELECT * FROM PROJECT WHERE SUBSTR(PROJNO, 1, 2) = 'MA'
Example 2: Create a view as in example 1, but select only the columns for project number (PROJNO), project name (PROJNAME) and employee in charge of the project (RESPEMP).
CREATE VIEW MA_PROJ2 AS SELECT PROJNO, PROJNAME, RESPEMP FROM PROJECT WHERE SUBSTR(PROJNO, 1, 2) = 'MA'
Example 3: Create a view as in example 2, but, in the view, call the column for the employee in charge of the project IN_CHARGE.
CREATE VIEW MA_PROJ (PROJNO, PROJNAME, IN_CHARGE) AS SELECT PROJNO, PROJNAME, RESPEMP FROM PROJECT WHERE SUBSTR(PROJNO, 1, 2) = 'MA'
Example 4: Create a view named PRJ_LEADER that contains the first four columns (PROJNO, PROJNAME, DEPTNO, RESPEMP) from the PROJECT table together with the last name (LASTNAME) of the person who is responsible for the project (RESPEMP). Obtain the name from the EMPLOYEE table by matching EMPNO in EMPLOYEE to RESEMP in PROJECT.
CREATE VIEW PRJ_LEADER AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME FROM PROJECT, EMPLOYEE WHERE RESPEMP = EMPNO
Example 5: Create a view as in example 4, but in addition to the columns PROJNO, PROJNAME, DEPTNO, RESEMP and LASTNAME, show the total pay (SALARY + BONUS +COMM) of the employee who is responsible. Also select only those projects with mean staffing (PRSTAFF) greater than one.
CREATE VIEW PRJ_LEADER (PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, TOTAL_PAY) AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, SALARY+BONUS+COMM FROM PROJECT, EMPLOYEE WHERE RESPEMP = EMPNO AND PRSTAFF > 1
Example 6: Create a recursive view that returns a similar result as a common table expression, see Single level explosion.
CREATE RECURSIVE VIEW RPL (PART, SUBPART, QUANTITY) AS SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01' UNION ALL SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART SELECT DISTINCT * FROMRPL ORDER BY PART, SUBPART, QUANTITY
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.