SQL Views- Tutorial using Oracle

Creating an horizontal View

 
  SQL> create view emp2 as select * from employee where d_no=123;

Creating an Vertical View

 
  SQL> create view emp1 as select name,ssn from employee;

Create a view by using column aliases

 
  create view emp11 as select ssn sec_number,name empname from employeej;

Selecting data from the view

 
  Select * from emp11;

Modifying a View

 
  create or replace view emp11 as select name from employeej;

Inserting data into views

 
  SQL> insert into satish.emp2 values ('harini',118,88888,112,123,'15-mar-1989');

Creating a complex view

 
  SQL> create view emp12 as select d.d_name,sum(e.salary) total_sum from employeej  where e.dno=d.dno group by d.d_name;

Granting permission to a user on the views created

 
  SQL> grant select on dep123 to sam;

  SQL> grant insert on dep123 to sam;
  
  SQL> grant update on dep123 to sam;

  SQL> grant select,update on dep123 to sam;

  SQL> grant select,insert,update on dep123 to sam;

  SQL> grant all privileges on dep123 to sam;

Revoking permissions

 
  SQL> revoke select on dep123 from sam;

Creating Views with Check Option

 
  create view satish.emp2 as select * from employee where d_no=123 with check option

Denying DML Operations

 
  CREATE OR REPLACE VIEW empvu10
  (employee_number, employee_name, job_title)
AS SELECT	employee_id, last_name, job_id
 FROM     employees
 WHERE    department_id = 10
 WITH READ ONLY ;

Removing a View

 
  DROP VIEW emp11;

Update views

 
  SQL> update satish.emp1 set name='kumar' where ssn=117;

Using Joins with Views

 
  create view emp3 (name,ssn,d_no,dep_name) as select e.name,e.ssn,e.d_no,d.name from employee e,department d where e.d_no=d.d_no;