Views

• Views are dynamic windows into the database.
• Views provide users with different views of the database.
• A view may include columns from different tables and also may include derived information such as aggregates.
• A view has a name and to a user it appears exactly as if it were a table.
• Views provide a mechanism to limit access to sensitive data by providing the users to see only a part of the information.

CREATE VIEW view-name [(col-list)]
  AS select-statement

DROP VIEW view-name

SQL> CREATE VIEW dallas_cust AS 
       select *
       from customers
       where city = 'Dallas';

View created.

SQL> select * 
     from dallas_cust;

CID  CNAME         CITY                     DISCNT
---- ------------- -------------------- ----------
c002 Basics        Dallas                       12
c003 Allied        Dallas                        8

SQL> CREATE VIEW tot_sales_per_agent AS
       select agents.aid, aname, sum(dollars) TOT_SALES
       from agents,orders
       where agents.aid = orders.aid
       group by agents.aid,aname;

View Created
SQL> select * 
     from tot_sales_per_agent;

AID ANAME          TOT_SALES
--- ------------- ----------
a01 Smith               1400
a02 Jones                180
a03 Brown               4228
a04 Gray                 450
a05 Otasi               2144
a06 Smith               1400