views by suchenfz

VIEWS: 4 PAGES: 10

									Views

CIT 381
           What is a View?
• A “virtual table”
• Defined by a query
• Does not exist on disk
    Access Queries are Views
SELECT office, city, region, sales
FROM offices;

      office   city          region    sales
         11    New York      Eastern   $692,637.00
         12    Chicago       Eastern   $735,042.00
         13    Atlanta       Eastern   $367,911.00
         15    Eugene        Western   $100,000.00
         21    Los Angeles   Western   $835,915.00
         22    Denver        Western   $186,042.00




  Here we just typed in the info for Eugene.
Now it appears in the offices table.


          OFFICE   CITY          REGION    MGR   TARGET        SALES
              11   New York      Eastern   108   $575,000.00   $692,637.00
              12   Chicago       Eastern   106   $800,000.00   $735,042.00
              13   Atlanta       Eastern   104   $350,000.00   $367,911.00
              15   Eugene        Western                       $100,000.00
              21   Los Angeles   Western   105   $725,000.00   $835,915.00
              22   Denver        Western   108   $300,000.00   $186,042.00
…And a Query is Treated Like a
          Table
The previous query was saved as “officeinfo”

SELECT city, region
FROM officeinfo;
                           city          region
                           New York      Eastern
                           Chicago       Eastern
                           Atlanta       Eastern
                           Eugene        Western
                           Los Angeles   Western
                           Denver        Western
    Views in SQL Standard

CREATE VIEW officeinfo AS
    SELECT office, city, region, sales
    FROM offices;

This gets “registered” and can be queried or
inserted into.

SELECT city, region
FROM officeinfo;
        Insertion into a View
• One can insert into a view, within reason
• This causes rows to be inserted into the
  underlying table
• NULLS will appear in underlying table
• Constraints must be satisfied there as well
  (as in “city … NOT NULL”)
• Cannot insert into a view if based on join.
               Joined Views
CREATE VIEW repinfo AS
    SELECT name, city, region, quota, salesrep.sales
    FROM salesreps, offices
    WHERE rep_office=office;

% This is not an updatable view – one cannot insert
into it nor update it.
Restrictions to Updatable Views
• No SELECT DISTINCT
• FROM clause must specify updatable table
• SELECT contains simple attributes – no
  aggregates, expressions
• No subquery in WHERE clause
• No GROUP BY, HAVING
             Use of Views
• Security
• Simplicity
• Insulation from change

								
To top