Introduction to Views

Go Up to Working with Views

Database users typically need to access a particular subset of the data that is stored in the database. Further, the data requirements within an individual user or group are often quite consistent. Views provide a way to create a customized version of the underlying tables that display only the clusters of data that a given user or group of users is interested in.

Once a view is defined, you can display and operate on it as if it were an ordinary table. A view can be derived from one or more tables, or from another view. Views look just like ordinary database tables, but they are not physically stored in the database. The database stores only the view definition, and uses this definition to filter the data when a query referencing the view occurs.

Important: It is important to understand that creating a view does not generate a copy of the data stored in another table; when you change the data through a view, you are changing the data in the actual underlying tables. Conversely, when the data in the base tables is changed directly, the views that were derived from the base tables are automatically updated to reflect the changes. Think of a view as a movable “window” or frame through which you can see the actual data. The data definition is the “frame.” For restrictions on operations using views, see Types of Views: Read-only and Update-able.

A view can be created from:

• A vertical subset of columns from a single table For example, the table, JOB, in the employee.ib database has 8 columns: JOB_CODE, ­JOB_GRADE, JOB_COUNTRY, JOB_TITLE, MIN_SALARY, ­MAX_SALARY, JOB_REQUIREMENT, and LANGUAGE_REQ. The following view displays a list of salary ranges (subset of columns) for all jobs (all rows) in the JOB table:

CREATE VIEW JOB_SALARY_RANGES AS
 SELECT JOB_CODE, MIN_SALARY, MAX_SALARY FROM JOB;

• A horizontal subset of rows from a single table The next view displays all of the columns in the JOB table, but only the subset of rows where the MAX_SALARY is less than $15,000:

CREATE VIEW LOW_PAY AS
 SELECT * FROM JOB
 WHERE MAX_SALARY < 15000;

• A combined vertical and horizontal subset of columns and rows from a single table  The next view displays only the JOB_CODE and ­JOB_TITLE columns and only those jobs where MAX_SALARY is less than $15,000:

CREATE VIEW ENTRY_LEVEL_JOBS AS
 SELECT JOB_CODE, JOB_TITLE FROM JOB
 WHERE MAX_SALARY < 15000;

• A subset of rows and columns from multiple tables (joins) The next example shows a view created from both the JOB and EMPLOYEE tables. The EMPLOYEE table contains 11 columns: EMP_NO, ­FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE, DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY, FULL_NAME. It displays two columns from the JOB table, and two columns from the EMPLOYEE table, and returns only the rows where SALARY is less than $15,000:

CREATE VIEW ENTRY_LEVEL_WORKERS AS
 SELECT JOB_CODE, JOB_TITLE, FIRST_NAME, LAST_NAME
 FROM JOB, EMPLOYEE
 WHERE JOB.JOB_CODE = EMPLOYEE.JOB_CODE AND SALARY < 15000;