10.1  Views

A view provides a different way of looking at the data in one or more tables. It is a 

named specification of a result table. 

Note: 

The specification is a 

SELECT

 statement that is run whenever the view is 

referenced in an SQL statement. 

A view has columns and rows just like a base table. All views can be used just 

like base tables for data retrieval. Whether a view can be used in an 

INSERT

, 

UPDATE

, or 

DELETE

 operation depends on its definition. 

You can use views to control access to sensitive data, because views allow 

multiple users to see different presentations of the same data. For example, 

several users may be accessing a table of employee data. A manager sees data 

about his employees but not employees in another department. A recruiting 

officer sees the hire dates of all employees, but not their salaries. A financial 

officer sees the salaries, but not the hire dates. Each of these users works with a 

view derived from the same base table. Each view appears to be a table and has 

its own name. 

When the column of a view is directly derived from the column of a base table, 

that view column inherits any constraints that apply to the base table column. For 

example, if a view includes a foreign key of its base table, 

INSERT

 and 

UPDATE

operations using that view are subject to the same referential constraints as in 

the base table. Also, if the base table of a view is a parent table, 

DELETE

 and 

UPDATE

 operations using that view are subject to the same rules as 

DELETE

 and 

UPDATE

 operations on the base table. 

A view can derive the data type of each column from the base table, or base the 

types on the attributes of a user defined structured type (

typed view

).

A view can become inoperative (for example, if the base table is dropped). If this 

occurs, the view is no longer available for SQL operations.

Example 10 1 shows some examples for views that could be used in our sample 

project.

Example 10 1   Example views for our sample project

   A view that shows all existing groups of products in our catalog

   and the number of items associated to them

create view GROUPS as

(select PRODGR, count(*) as CNT from ITSODB.CATALOG group by PRODGR)

318 

MySQL to DB2 UDB Conversion Guide