SQL Views

A view is a database object that is a logical representation of a table. It is delivered from a table but has no storage of its own and often may be used in the same manner as a table.

A view takes the output of the query and treats it as a table, therefore a view can be thought of as a stored query or a virtual table.

TYPES

Simple view

Complex view

Simple view can be created from one table where as complex view can be created from multiple tables.

WHY VIEWS?

Provides additional level of security by restricting access to a predetermined set of rows and/or columns of a table.

Ø Hide the data complexity.

Ø Simplify commands for the user.

VIEWS WITHOUT DML

Read only view

View with group by

View with aggregate functions

View with rownum

Partition view

View with distinct

Ex:

      SQL> Create view dept_v as select *from dept with read only;

      SQL> Create view dept_v as select deptno, sum(sal) t_sal from emp group by deptno;

      SQL> Create view stud as select rownum no, name, marks from student;

      SQL> Create view student as select *from student1 union select *from student2;

      SQL> Create view stud as select distinct no,name from student;

VIEWS WITH DML

View with not null column  --  insert with out not null column not possible

                                                        --  update not null column to null is not possible

                                                             --  delete possible

View with out not null column which was in base table -- insert not possible                                                                                                                                      

                                         -- update, delete possible

View with expression -- insert , update not possible

                                               -- delete possible

View with  functions (except aggregate) -- insert, update not possible

                                                                              -- delete possible

View was created but the underlying table was dropped then we will get the message like “ view has errors ”.

View was created but the base table has been altered but still the view was with the               initial definition, we have to replace the view to affect the changes.

Complex view (view with more than one table) -- insert not possible

                                                                                                  -- update, delete possible (not always)

CREATING VIEW WITHOUT HAVING THE BASE TABLE

SQL> Create force view stud as select *From student;

-- Once the base table was created then the view is validated.

VIEW WITH CHECK OPTION CONSTRAINT

SQL> Create view stud as select *from student where marks = 500 with check option constraint

         Ck;

       - Insert possible with marks value as 500

       - Update possible excluding marks column

       - Delete possible

DROPPING VIEWS

SQL> drop view dept_v;