Views Creation Of View: create view viewname as select columnname, columnname from tablename

1. Views Creation Of View: create view viewname as select columnname, columnname from tablename where columnname=expressionlist; [group by grouping criteria] [having predicate] Example: create view vw_client as select * from client_master; create view vw_client1 as select client_no, addr, bal from client_master;

2. Renaming the Columns Of a View: create view vw_client1 as select client_no CID, addr Address, bal Balance from client_master; • Selecting A data Set From a View: Once view is created it is treated as a base table. select columnname, columnname from viewname; Select statement can have all cluases like where, order by etc. select client_no, addr, bal from client_master where city in(‘bombay’, ‘dehli’);

3. Types Of Views: The views are of two types. --Read only view --Updateable view • Read only view: If view is used to look at table data and nothing else it is called as Read only view. • Updateable view If view is used to look at table data as well as Insert, Update and delete table data it is called as updateable view.

4. For view to be updateable, it should meet following criteria. • Updateable views defined from single tables: 1. If the user wants to Insert records with the help of a view, then the Primary Key column/s and all not null columns must be included in the view. • The user can update, delete records with the help of a view even if the primary key column and not null columns are excluded from the view definition. insert into vw_client values(‘C1’, ‘Rakesh’, 10000) update vw_client set bal_due=15000 where client_no=‘c1’; delete from vw_client where client_no=‘c1’;

5. A view can be created from more than one table. These base tables will be linked by a join condition specified in the where clause of the view’s definition. • The behavior of view will vary for insert, update, delete and select table operations depending upon the following. --whether table were created using a referencing clause --whether the tavles were created without any referencing clause & are actually standalone tables not related in any way. • Views Defined from Multiple Tables (which have no referencing clause): If views are defined from multiple tables which were not created using referencing clause then though PK column & NOT NULL columns are included in the view definition the views behavior will be as follows— The insert, update, delete operations are not allowed. If attempted Oracle displays an error message.

6. Views Defined from Multiple Tables (which have been created with a referencing clause): If views are defined from multiple tables which were created using referencing clause then though PK column & NOT NULL columns are included in the view definition the views behavior will be as follows— -- An insert operation is not allowed. -- The delete or modify operations don’t affect the master table. -- The view can be used to modify the columns of the detail table included in the view. -- If the delete operation is executed on the view, the corresponding records from detail table will be deleted.

7. Example: sales_order(ordr_no, ordr_dt, cl_no, dely_addr, ) sales_order_details(detOrdrNO, product_no, qty_orderd, qty_diply) create view vw_sales as select ordr_no, ordr_dt, qty_orderd, qty_disp from sales_order, sales_order_details where sales_order.orderno=sales_order_details.detOrdrNo;

8. Common Restrictions On Updateable View: The following condition holds true irrespective of the view being created from a single table or multiple tables. • For the view to be updateable the view definition must not include i) Aggregate functions ii) Distinct, group by, or Having Clause iii) Sub – queries iv) Constants, strings, or value expr like sell_price * 1.05 v) Union, Intersect or minus clause • If a view is defined from another view, the second view should be updateable. • If the user tries to perform any of insert, update, delete operation on a view which is created from a non-updateable view Oracle returns the error message.

9. Destroying a View: drop view viewname; drop view vw_client;