VIEWS

Security reasons tend to prevent users from accessing the entire information from tables.   View is a schema object used to extract data from one or more than one table/views. Viareews  created when data redundancy is to be the minimum while maintain data security. Views won't store data physically. The definition of a view contains a "select" statement & it is stored in the system catalog

when a reference to a view is made the definition of the view is fetched from the system catelog and the required tables are opened for data and seleted to manipulate. Upon each request to the view made the above process of fetching the definition form the catelog, opening the table required in main memory and view has to be constructed by extracting the required portion of data from the tables. Then the query on the view returns the active data set.

 Syntax for creating a view:

CREATE VIEW view_name AS SELECT column_list FROM table_name(s) WHERE column_name = expression list GROUP BY grouping_condition HAVING predicate;

 * NOTE: Definition of the view cannot contain ORDER BY clause

 Ex:

SELECT empno,ename,job,dname departmentname, loc location from emp,dept

where emp.dept = dept.deptno;

 One can select, rename the columns as from normal tables as mentioned in the above query consisting of ORDER BY, GROUP BY and HAVING clauses.

 Ex:

create view v1 as select *from emp;

select count(*), avg(sal), deptno from v1 group by deptno having deptno not in (10,20);

 Updatable Views:

Views which support data manipulation operations like INSERT, DELETE and UPDATE are called Updatable views. When updatable views are manipulated the under lying tables willbe effected.

 Crieteria to be followed by views to be updatable:

 Views must be defined from single table.

Definitionof the view should contain the primary key(s) and all mandatory(not null) columns of the underlying table(s).

User can update, delete rows from the views even though the definition of the view does not contain primary key column(s) and not null column(s).

 If the view is created from more than one table which don't have a refrencing relationship between them, even inclusion of primary key column(s), mandatory column(s) in the definition of the view INSERT and DELETE operations are not allowed.

 Destroying a view:

 DROP VIEW view_name;

Pointers to creating views:

First work on the select statement and make sure that you are getting the correct result. Then add the create view viewname command to that select statement.

LAB MANUAL HELP:

Q1 ans:

You get the information from two tables, employee and programme if you need just the pcode and three tables employee, programme and progsec if you need the progrm name also. So just do a join query involving the three tables and add the extra conditions and create the view.

Q2 ans:

Very similar to the first query, only thing is you get the information from employee and grade tables. Perfom a join on these two tables.

Q3 ans:

You have to use ROWNUM to get the row number and

MOD(numerator,denominator) to get the remainder.

This is a little bit more complicated. You need to make use of ROWNUM. ROWNUM is a special case where it is generated on at runtime. That is, for example run the query SELECT * FROM EMP;

The first tuple of EMP is selected to the output and 1 is assigned to ROWNUM, then the second tuple qualifies and 2 is assigned to ROWNUM, ie ROWNUM is incremented. ROWNUM starts from 1.So first a tuple is qualified and then a ROWNUM is assigned to it. So this means that you have to first materialize the rownumbers and the query on it. You can materialize row numbers on the fly. That is, the following query will not work:

>SELECT *

FROM EMP

WHERE MOD(ROWNUM,2) =0 //for even rows.

This won't work, because the first row fails the condition and the processing is stopped.

So try out different queries with rownum to get a feel for it first and then try to answer the query.

Q4) Very simple follow as for 1 and 2.

Q5)