Oracle VIEW - to display Sub Table from a Table

Hi, Today i will discuss about important topic - VIEW.

In Oracle, view is a virtual table that does not physically exist. It is stored in Oracle data dictionary and do not store any data. It can be executed when called.

A view is created by a query joining one or more tables.

CREATE VIEW

To create view , we follow same query as create table. see the syntax.

Syntax:

CREATE VIEW view_name AS 
SELECT columns  
FROM tables  
WHERE conditions;  

Example:

CREATE VIEW student_from_delhi AS
SELECT * FROM students
WHERE student_city = 'Delhi';

View created.

To see created View:

SELECT * FROM student_from_delhi;

UPDATE VIEW:

Syntax:

CREATE OR REPLACE VIEW view_name AS 
 SELECT columns  
FROM table 
WHERE conditions; 

Example:

CREATE or REPLACE VIEW student_from_delhi AS
SELECT * FROM students
WHERE student_city = 'Delhi' AND student_type='Day Scholar' ;

DROP VIEW:

The DROP VIEW statement is used to remove or delete the VIEW completely.

Syntax:

DROP VIEW view_name;  

Example:

DROP VIEW student_from_delhi;  

View dropped.

If you want to see VIEW "student_from_delhi" after DROP, It will give following output:

ORA-00942: table or view does not exist

Thank you for reading. Feel Free to ask you Query in comment section.