“It is a oracle data base object”
“A view is a window through which one can see the contents of a database table as well as can manipulate the contents of the table.”
Advantages of Views:
- The view is derived from a single table
- In Oracle 8 and higher versions manipulation of view is also possible though it is derived from multiple database tables, by using a data base trigger called “instead of”.
- A view looks like a table with some restrictions, so a view is also known as a virtual table.
- A view occupies no storage space, because view contains no data of their own, i.e. view always depends on database table or tables where the actual data resides.
- These are used provide security to the database tables by hiding some part of the data of a table by defining some projections and restrictions.
- These are used to define the complex queries in the compiled version.
- 1,900 views are there in the data base dictionary.
SQL> create View
SQL> create view
>, , , -----------) Col
Select , ------ from
, , ----------
- The “ ORDER BY” clause is invalid in the views of definition query, but the same “ORDER BY” clause can be used while querying the view.
- This (the above rule) is valid up to Oracle 8, but in 8i we can use “ORDER BY” clause in the view definition also.
Create a view to display only the information of employees who arte managers to others.
SQL> create view v1
Select * from emp
Empno in (select distince MGR from emp);
Create a view to display employee number, employee name, salary, TA (10%), DA (20%), HRA (30%), gross, LIC (1%), PF (2%), net deduction, net allowance, gross and net of all employees.
Using and displaying the View:
SQL> select * from emp_sal_info;
Create a view to display the empno, ename,salary, deptno, dname, grade, experience, experience in terms of *** of all the employees.
SQL> create view Emp_Details
Select empno, ename, sal, dept.deptno, dname, grade, months_between (sysdate, hiredate)/12, LPAD(‘*’, months_between (sysdate, hiredate)/12, ‘*’)
From emp, dept, salgrade
Emp.deptno=dept.deptno and sal between losal and hisal;
Create a view to display the deptno, max salary, total salary, and average salary, number of employees with labels.
SQL> create view Emp_sal_details
Select max (sal) maximum salary, avg(sal) Average Salary, Count (empno) number of employees, Sum(sal) total salary, deptno from emp
Group by deptno;
Create a view to display the employee details in the following format.
Smith is drawing a salary of 1800 working as CLERK, who joined on Wednesday 02-03-1982.
SQL> create view v1
Select ename || ‘is drawing a salary of Rs.’ || ‘, is working as ‘ || job ||’ || to_char )hiredate, ‘day’, DDTH month yyyy’) from emp;
Is it possible to create without having a base table?
If you try to create a view without a bas3e table you will get an error message like “table or view does not exist”
This is because; a view is logical presentations of a table.
A view uses the scope of the data, which is present in the underlying table.
Is it possible to create an index on the columns of a view?
A view is a virtual table only it contains no data. Only queries will be present in a view.
if you try this you will get error like “ A view is not appropriate here.”
SQL> create index x on v (Sal);