CREATE VIEW

A view is a logical table that contains data selected from existing tables. The view can rename and rearrange columns, omit unwanted columns or rows, define columns by expressions, group results, and combine more than one table.

Views make it possible to view data that exists in parts of one or more tables. No data actually exists in a view.

Any SELECT statement that does not contain an ORDER BY clause can be used as the basis of a view; the selected columns and rows become the columns and rows of the view. In the following example, the NAME, ID, and JOB columns from the Q.STAFF table become the columns of the D42 view. The column names for D42 are LAST NAME, EMP. ID, and JOB. 
CREATE VIEW D42
 ("LAST NAME", "EMP. ID", JOB)
   AS SELECT NAME, ID, JOB
FROM Q.STAFF
WHERE DEPT = 42
Issue the command DISPLAY TABLE D42 to display this view: 
LAST NAME EMP. ID  JOB
--------- -------  -----
KOONITZ        90  SALES
PLOTZ         100  MGR
YAMAGUCHI     130  CLERK
SCOUTTEN      200  CLERK
There are two main reasons for using a view:
  • To simplify writing a query.
  • To prevent access to data. Anyone using the view D42 in the above example cannot see salary data.
Use a view by its name, like you use a table name. You can select from it, writing the same kind of SELECT statement as if it were a table. For example, you can run the following query for the D42 view: 
SELECT * FROM D42
WHERE JOB='CLERK'

With a few restrictions, you can insert, update, and delete rows in a view. Corresponding changes are made to the tables the view is based on.

There are a few things that you cannot do with a view:
  • You cannot insert, update, or delete data using a view if the view contains:
    • Data from more than one table
    • A column defined by a column function; for example, SUM(SALARY)
    • Data selected by the DISTINCT or GROUP BY keywords
  • You cannot update or insert data if the view contains a column defined by an expression (like SALARY/12). However, you can delete data in this case.
  • You cannot use the UNION keyword when creating a view.
  • You cannot join a view created using a GROUP BY clause to another table or view.