Example 1: Defining a view based on a single table

    This example shows a view definition that uses three columns from
    a single table, EMPLOYEES.

    SQL> CREATE VIEW EMP_NAME
    cont>   AS SELECT
    cont>           FIRST_NAME,
    cont>           MIDDLE_INITIAL,
    cont>           LAST_NAME
    cont>      FROM EMPLOYEES;
    SQL> --
    SQL> -- Now display the rows from the view just created.
    SQL> SELECT * FROM EMP_NAME;
     FIRST_NAME   MIDDLE_INITIAL   LAST_NAME
     Alvin        A                Toliver
     Terry        D                Smith
                   .
                   .
                   .

    Example 2: Defining a view that does not allow you to insert or
    update rows that do not conform to the view's definition

    This example shows a view definition using the WITH CHECK OPTION
    clause.

    SQL>  CREATE VIEW ADMN_VIEW
    cont>        AS SELECT * FROM JOB_HISTORY
    cont>        WHERE DEPARTMENT_CODE = 'ADMN'
    cont>        WITH CHECK OPTION CONSTRAINT ADMN_VIEW_CONST;
    SQL> -- You cannot insert a row that does not
    SQL> -- conform to the view definition.
    SQL> --
    SQL>  INSERT INTO ADMN_VIEW (DEPARTMENT_CODE) VALUES ('MBMN');
    %RDB-E-INTEG-FAIL, violation of constraint ADMN_VIEW_CONST-
    caused operation to fail

    Example 3: Defining a view based on multiple tables

    You can also define a view using more than one table.

    SQL> CREATE VIEW CURRENT_SALARY
    cont>  AS SELECT
    cont>        E.LAST_NAME,
    cont>        E.FIRST_NAME,
    cont>        E.EMPLOYEE_ID,
    cont>        SH.SALARY_START,
    cont>        SH.SALARY_AMOUNT
    cont>      FROM
    cont>        SALARY_HISTORY SH, EMPLOYEES E
    cont>      WHERE
    cont>        SH.EMPLOYEE_ID = E.EMPLOYEE_ID
    cont>      AND
    cont>        SH.SALARY_END IS NULL           ;

    This example defines a view from the EMPLOYEES and SALARY_HISTORY
    tables. It uses the select expression to:

    o  Choose the columns derived from each table. Because no column
       names are specified before the select expression, the columns
       inherit the names from the source tables.

    o  Join the tables and limit the view to current salaries.

    Example 4: Defining a view with local column names

    SQL> CREATE VIEW EMP_JOB
    cont>      ( CURRENT_ID,
    cont>        CURRENT_NAME,
    cont>        CURRENT_JOB,
    cont>        SUPERVISOR   )
    cont>  AS SELECT
    cont>        E.EMPLOYEE_ID,
    cont>        E.LAST_NAME,
    cont>        J.JOB_TITLE,
    cont>        JH.SUPERVISOR_ID
    cont>     FROM
    cont>        EMPLOYEES E,
    cont>        JOB_HISTORY JH,
    cont>        JOBS J
    cont>     WHERE
    cont>        E.EMPLOYEE_ID = JH.EMPLOYEE_ID
    cont>     AND
    cont>        JH.JOB_CODE = J.JOB_CODE
    cont>     AND
    cont>        JH.JOB_END IS NULL      ;

    This view definition:

    o  Specifies local names for the columns in the view.

    o  Joins the EMPLOYEES and JOB_HISTORY tables. This join links
       rows in the EMPLOYEES table to rows in the JOB_HISTORY table.

    o  Joins the JOB_HISTORY and JOBS tables. This join lets the view
       contain job titles instead of job codes.

    o  Uses the JH.JOB_END IS NULL expression. This clause specifies
       that only the current JOB_HISTORY rows, where the JOB_END
       column is null, should be included in the view.

    The following query uses the view defined in the previous
    example:

    EXEC SQL
            DECLARE X CURSOR FOR
            SELECT  CURRENT_ID, CURRENT_NAME, CURRENT_JOB, SUPERVISOR
            FROM    EMP_JOB
    END-EXEC

    EXEC SQL
            OPEN X
    END-EXEC

    PERFORM WHILE SQLCODE NOT = 0

            EXEC SQL
                    FETCH X
                    INTO :ID, :NAME, :JOB, :SUPER
            END-EXEC

    END PERFORM

    EXEC SQL
            CLOSE X
    END-EXEC

    Example 5: Defining a view with a calculated column

    This example shows a view definition that derives a column
    through a calculation based on a column in an base table.

    SQL> CREATE VIEW SS_DEDUCTION
    cont>        ( IDENT,
    cont>          SALARY,
    cont>          SS_AMOUNT )
    cont>  AS SELECT
    cont>        E.EMPLOYEE_ID,
    cont>        SH.SALARY_AMOUNT,
    cont>        SH.SALARY_AMOUNT * 0.065
    cont>      FROM
    cont>        SALARY_HISTORY SH, EMPLOYEES E
    cont>      WHERE
    cont>        SH.EMPLOYEE_ID = E.EMPLOYEE_ID
    cont>      AND
    cont>        SH.SALARY_END IS NULL   ;

    Each time the view column SS_AMOUNT is selected, it computes a
    new value from the SALARY_AMOUNT column of the SALARY_HISTORY
    table.

    Example 6: Defining a view dependent on another view

    This example creates a view, DEPENDENT_VIEW, that refers to
    the CURRENT_JOB view in its definition to include current job
    information for employees in the engineering department.

    SQL> CREATE VIEW DEPENDENT_VIEW
    cont>   AS SELECT * FROM CURRENT_JOB
    cont>           WHERE DEPARTMENT_CODE = 'ENG';