creating and updating view

here I explain how to create and manage view read it and practise it

+++++++Creating empcheck view+++++++
create view empcheck
as select empno,ename,job
from emp

++++++++++CHECK THE VIEW CREATED UNDER DATA ICTIONARY+++++++++++
SELECT OWNER,VIEW_NAME,TEXT FROM DBA_VIEWS WHERE OWNER='SCOTT';

+++++++replacing empcheck view+++++++++++++++++++
create or replace view empcheck
as select empno,ename,job
from emp
where empno>6000


+++++++++++checking+++++++++++++
desc empcheck

select * from empcheck;

+++++++++++Column Aliasing+++++++++
create or replace view empcheck
as select empno Employee_no,ename Employee_name,job Job
from emp
where empno>6000

++++++++Another type using column aliases+++++++++++++
create or replace view empcheck( Employee_no, Employee_name,Job )
as select empno,ename,job
from emp
where empno>6000

++++++++++++Creatng a Complex view++++++++++
create OR REPLACE view emp2
(name,minsal,maxsal,avgsal)
AS SELECT D.DNAME,MIN(E.SAL),MAX(E.SAL),AVG(E.SAL)
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
GROUP BY D.DNAME;

++++++++++++WITH CHECK CONSTRAINTS+++++++++++++
CREATE OR REPLACE VIEW EMP3
AS SELECT *
FROM EMP
WHERE ENAME='ALLEN'
WITH CHECK OPTION CONSTRAINT EMP3_CK;

SELECT * FROM EMP3;

UPDATE EMP3
SET ENAME='ARKA'
WHERE ENAME='ALLEN'

++++++++++++WITH READ ONLY++++++++++++++++++++
CREATE VIEW EMP3 (name, NO, JOB)
AS SELECT ENAME, EMPNO,JOB
FROM EMP
WITH READ ONLY;

CHECKING
DELETE FROM EMP3
WHERE NAME='SMITH'

++++++++++++TOP N ANANLYSYS++++++++++++++++
SELECT ENAME,SAL,ROWNUM AS RANK
FROM (SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM<=3