Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.
Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert, Update and delete from views, just as any other table.
Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.
The following sections explain how to create, replace, and drop views using SQL commands.
Creating Views
Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name and location we have to give a join query like this.
select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
From emp e, dept d where e.deptno=d.deptno;
So everytime we want to see emp details and department names where they are working we have to give a long join query. Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below
create view emp_det as select e.empno,
e.ename,e.sal,e.deptno,d.dname,d.loc
from emp e, dept d where e.deptno=d.deptno;
Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.
select * from emp_det;
This will show same result as you have type the long join query. Now you can treat this EMP_DET view same as any other table.
For example, suppose all the employee working in Department No. 10 belongs to accounts department and most of the time you deal with these people. So every time you have to give a DML or Select statement you have to give a WHERE condition like .....WHERE DEPTNO=10. To avoid this, you can create a view as given below
CREATE VIEW accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Now to see the account people you don’t have to give a query with where condition you can just type the following query.
Select * from accounts_staff;
Select sum(sal) from accounst_staff;
Select max(sal) from accounts_staff;
As you can see how views make things easier.
The query that defines the ACCOUNTS_STAFF view references only rows in department 10. Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATEstatements issued against the view are not allowed to create or result in rows that the view cannot select.
Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view:
INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);
However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using theACCOUNTS_STAFF view:
INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);
