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.
CREATE VIEW D42 ("LAST NAME", "EMP. ID", JOB) AS SELECT NAME, ID, JOB FROM Q.STAFF WHERE DEPT = 42
DISPLAY TABLE D42to display this view:
LAST NAME EMP. ID JOB --------- ------- ----- KOONITZ 90 SALES PLOTZ 100 MGR YAMAGUCHI 130 CLERK SCOUTTEN 200 CLERK
- To simplify writing a query.
- To prevent access to data. Anyone using the view D42 in the above example cannot see salary data.
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.
- You cannot insert, update, or delete data using a view if the
- 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.