Views

1. A view in SQL is defined using the create view command:

   

   where <query expression> is any legal query expression.

   The view created is given the name .

2. To create a view all-customer of all branches and their customers:

   

3. Having defined a view, we can now use it to refer to the virtual relation it creates. View names can appear anywhere a relation name can.

4. We can now find all customers of the SFU branch by writing

   

5. The view update anomaly previously mentioned in Chapter 3 exists also in SQL.

6. An example will illustrate: consider a clerk who needs to see all information in the borrow relation except amount.

   Let the view loan-info be given to the clerk:

   

   Since SQL allows a view name to appear anywhere a relation name may appear, the clerk can write:

   

   This insertion is represented by an insertion into the actual relation borrow, from which the view is constructed. However, we have no value for amount.

   This insertion results in (``SFU'',3,``Ruth'',null) being inserted into the relation.

   As we saw, when a view is defined in terms of several relations, serious problems can result. As a result, many SQL-based systems impose the constraint that a modification is permitted through a view only if the view in question is defined in terms of one relation in the database.