VIEWS

--> CREATE VIEW

CREATE VIEW e1 AS SELECT empno "Emp Id", ename Name,sal Salary FROM e;
CREATE OR REPLACE VIEW e1 AS SELECT empno "Emp Id", ename Name,sal Salary,job FROM e;
insert into e1 values(1001,'raju',4000,'sales');
SELECT * FROM e;
SELECT * FROM e1;

--> Drop view
DROP VIEW e1;
SELECT * FROM e;
SELECT * FROM e1;

--> Replace view
CREATE OR REPLACE VIEW e1 AS SELECT empno "Emp Id", ename Name,sal Salary,job FROM e;
insert into e1 values(1001,'raju',4000,'sales');
DELETE FROM e WHERE sal=4000;
SELECT * FROM e;
SELECT * FROM e1;

CREATE OR replace VIEW dss(deptname,Minsal,Maxsal,avgsal,salsum) AS SELECT d.dname,Min(e.sal),Max(e.sal),Round(Avg(e.sal)),Sum(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.dname;
SELECT * FROM dss;

CREATE VIEW d20 AS SELECT * FROM e WHERE deptno=20;
SELECT * FROM d20;

INSERT INTO d20(empno,deptno) VALUES(7777,50);
SELECT * FROM d20;
SELECT * FROM e;
ROLLBACK;

CREATE OR REPLACE VIEW sg(empno,ename,sal,losal,hisal) AS SELECT e.empno,e.ename,e.sal,s.losal,s.hisal FROM e e,s s WHERE e.sal BETWEEN s.losal AND s.hisal;
SELECT * FROM sg;
INSERT INTO sg(sal) VALUES(999);/* connat insert on virtual columns */

CREATE OR REPLACE VIEW ms AS SELECT e.ename,e.sal,i.deptno FROM emp e,(SELECT deptno, Max(sal) maxsal FROM emp GROUP BY deptno) i WHERE e.deptno=i.deptno AND e.sal
SELECT * FROM ms ;
/********************************************************************************/

Exercise on SQL Views

1. Creating a view called EMPLOYEE_V based on employee number, employee names and department numbers from the employees table. Change the headings for the employee name to EMPLOYEE.

Create view Employee_V(EmployeeNo,Employee,DeptNum)

AS select empno,ename,deptno

from employee;

2. Display the contents of the view EMPLOYEE_V.

Select * from employee_v;

3. Select the above created view text from data dictionary table.

select text from user_views where view_name='EMPLOYEE_V';

4. Using the above created view enter a query to display all employee names and department numbers.

Select EmployeeNo,DeptNum

from EMPLOYEE_V;

5. Create a view named called DEPT_V that contains the employee numbers, employee last names and department numbers for all employees in department 50.Label the view columns EMPNO, EMPLOYEE and DEPTNO.Do not allow an employee to be reassigned to another department through view.

Create or replace view dept_v(EMPNO,EMPLOYEE,DEPTNO)

AS select empno,enmae,deptno

from employee where deptno=50

with check option constraint deptv_ck;

6. Create a view called salary_info_v based on the employees last names, department names, salaries and salary grades for all employees. Use the tables employees, departments and job_grades tables.

create view salary_info_v

as select e.ename,d.dname,e.sal,s.grade

from emp e,dept d,salgrade s

where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

7. Example of DML operation on a view (Updatable view and non updatable view)

1. create or replace view emp_update_view

AS select empno,ename,sal,hiredate

from employee

where deptno=30

2. update emp_update_view

set ename='Vamsi';

where ename like 'BLAKE';

3. create or replace view emp_nonupdate_view(dname,maxsal,minsal,avgsal)

AS select d.dname,max(e.sal),min(e.sal),avg(e.sal)

from emp e,dept d

where e.deptno=d.deptno

group by(d.dname)

4. update emp_nonupdate_view

set deptname='ACC'

where deptname like 'ACCOUNTING';