View

A view is a virtual table defined by a SQL SELECT statement. It presents any or all of the data in one or more tables or views. A view never stores data - when it is accessed, the SELECT that defines it is executed to derive the requested rows.

What is a view

• A view is a virtual table - it looks like a table but stores no data of its own.
• It can show all columns of a table, a subset of columns, or joined/derived data from several tables.
• Views are useful for security - show users only the columns they are allowed to see.
• Views are created with CREATE VIEW and removed with DROP VIEW - there is no ALTER VIEW, you drop and recreate.

CREATE VIEW - example

• A view showing only employees in departments starting with 'B':-
  
  CREATE VIEW V1 AS SELECT EMPNO, DEPTNO, LNAME, FNAME, EXT FROM MM01.EMPLOYEE WHERE DEPTNO LIKE 'B%';
• Reading the view runs the stored SELECT:-
  
  SELECT * FROM MM01.V1 ORDER BY DEPTNO, LNAME;
• WITH CHECK OPTION can be added so that INSERTs and UPDATEs through the view must satisfy the view's WHERE clause.