View ( virtual table )

1. View (virtual table)

2. View • A VIEW is a virtual table • A view contains rows and columns, just like a real table. • The fields in a view are fields from one or more real tables in the database. • A view is a table whose rows are not explicitly stored in the database. • A view does not require a disk space

3. View • Advantages of view • Restrict access to the data because the view can display selective columns from the table • Make simple query to retrieve the results of complicated queriesiewithout writing join statements • One view can retrieve data from several tables • Views create with a check option, to prevent updating of other rows and columns • View allow same data to seen by different users in different ways at the same time

4. View • Views are two types • Simple Views & Complex Views • Simple View • Derived from only one table • Can perform DML operations through the view • Complex View • Derived from many tables • Does not always allow DML operations through the view

5. View • Syntax: • CREATE  [OR REPLACE]  VIEW view_name AS • Subquery • [WITH CHECK OPTION][WITH READ ONLY] • OR REPLACE:- is used to re-create the view if it already exists without having to drop it first. • Subquery:- is a complete SELECT statement • WITH CHECK OPTION :- specifies that only those rows that are accessible to the view can be inserted or updated • WITH READ ONLY:- ensure that no DML operations can be performed on this view

6. View • Example • CREATE VIEW EMP AS SELECT EMP_ID,EMP_NAME,AGE FROM EMPLOYEE WHERE AGE>45; • To describe the structure of the view by using • DESC EMP;

7. View • Retrieving data from a View • SELECT * FROM VIEW; • Rename the column of a view • CREATE VIEW EMP (ID,NAME,EMP_AGE)AS SELECT EMP_ID,EMP_NAME,AGE FROM EMPLOYEE WHERE AGE>45;

8. View • Creating a complex view • Contain values from more tables • If a view is created from multiple table, which where created using reference key then, through the primary key column as well as the NOT NULL column are included in the view • The view behavior will be as • An insert operation is not allowed • If a delete operation is executed on the view, the corresponding records, from the detail table will be deleted • The view can be used to modify the columns of the detail table included in the view

9. View • Syntax • Create view viewnane as select table1.column,.. table2.column,, from table1,table2 where expression1=expression2;

10. View • CUSTOMER CUSTOMER1 • If we want to create a view named comp_view • Create view comp_view select cid,cname,dname from customer C, customer1 C1 where C.deptno=C1.deptno; • Select * from comp_view;

11. View • Updatable & No updatable Views • Views on which data manipulation can be done are called updatable views • All views are not updatable • Conditions for updatable view are • Views must be derived from a single table • For insert operation:- then primary key column and all NOT NULL columns must be included in the view • Cannot update, if view is derived using GROUP BY clause • Cannot update, if view is derived using DISTINCT • Cannot update, if view is derived using nested Subquery • Cannot update, if view is derived from another view • The view definition must not include union, intersection, Minus clause

12. View • Simple View • Insert row in view • Insert into emp_view values(109,’appu’,900,’manager’); • This SQL insert the row in base table and the view both • Modify view • Update emp_view set salary=7000 where ename=‘appu’; • This SQL update both base table & view • Delete • Delete from emp_view where ename=‘appu’;

13. View • Complex view • Created from more than one table • create table tab1(rollnoint, name char(10) not null, age int not null, primary key(rollno)) • create table tab2(mid int, rollnoint references tab1(rollno), mark int, primary key(mid)) • create or replace view v1 as select tab1.rollno,tab1.name,tab2.mark from tab1,tab2 where tab1.rollno=tab2.rollno

14. View Complex view insert into v1 values(2,'raj',50) Display an error cannot modify a column which maps to a non key-preserved table update v1 set name='kittu' where rollno=1 Display an error cannot modify a column which maps to a non key-preserved table

15. View • Read only views • A view that cannot modify • Create view v2 as select name,age from tab1 where rollno=1with read only • Removing views • Drop view view name;

16. View