Examples of Views

Go Up to Types of Views: Read-only and Update-able

The following statement creates an update-able view:

CREATE VIEW EMP_MNGRS (FIRST, LAST, SALARY) AS
 SELECT FIRST_NAME, LAST_NAME, SALARY
 FROM EMPLOYEE
 WHERE JOB_CODE = 'Mngr';

The next statement uses a nested query to create a view, so the view is read-only:

CREATE VIEW ALL_MNGRS AS
 SELECT FIRST_NAME, LAST_NAME, JOB_COUNTRY FROM EMPLOYEE
 WHERE JOB_COUNTRY IN
 (SELECT JOB_COUNTRY FROM JOB
 WHERE JOB_TITLE = 'manager');

The next statement creates a view that joins two tables, and so it is also read-only:

CREATE VIEW PHONE_LIST AS
 SELECT EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, LOCATION, PHONE_NO
 FROM EMPLOYEE, DEPARTMENT
 WHERE EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO.