Views

A view is basically a query built upon one or more tables. Retrieving data from view is done in the same manner as it is done on Tables.

Syntax:

CREATE OR REPLACE VIEW <view_name>
(  Alias1
,  Alias2
.
.
,  AliasN
)
AS
(
   Subquery
)
[WITH {CHECK OPTION | READ ONLY}
 CONSTRAINT <constraint_name>
]

Ex: CREATE VIEW keyword
Creating a simple view

CREATE OR REPLACE VIEW myview
AS
(
SELECT empno
     , ename
     , job
  FROM emp
)

Inserting values using View

You can perform DML operations on VIEW.

Ex: INSERT keyword on VIEW
Insert a record into EMP table using the view

INSERT INTO myview
     VALUES ( 6661
            , 'Obama'
            , 'President'
            )

Ex: UPDATE keyword on VIEW
Update a record in EMP table using the view

UPDATE myview
   SET ename = 'Osama'
 WHERE empno = 6661

Ex: SELECT keyword on VIEW

SELECT *
  FROM myview
 WHERE empno = 6661

     EMPNO ENAME      JOB    
---------- ---------- ---------
      6661 Osama      President
1 row selected.

Note: The REPLACE keyword modifies the view and recreates if it already exists.

Dropping a View

The view is dropped using the keyword DROP. Only the view NOT the table will be dropped
    
Syntax
DROP VIEW <view_name>;

Ex: DROP keyword on VIEW

Removes the VIEW from database

DROP VIEW myview;