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.
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 theemployee.ib
database has 8 columns:JOB_CODE
,JOB_GRADE
,JOB_COUNTRY
,JOB_TITLE
,MIN_SALARY
,MAX_SALARY
,JOB_REQUIREMENT
, andLANGUAGE_REQ
. The following view displays a list of salary ranges (subset of columns) for all jobs (all rows) in theJOB
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 theMAX_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
andJOB_TITLE
columns and only those jobs whereMAX_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
andEMPLOYEE
tables. TheEMPLOYEE
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 theJOB
table, and two columns from theEMPLOYEE
table, and returns only the rows whereSALARY
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;