Syntax for creating views in Oracle

The syntax for creating a view in Oracle is very important, because creating a view in Oracle makes it one of the most common operations. Before learning the syntax for creating a view in Oracle, let's take a look at what a view is.

A view is a logical table based on one or more tables or views. It does not contain data and allows you to query and modify the data in the table. A view-based table is called a base table.

A view is a select statement stored in the data dictionary. You can use Oracle to create views to extract logical sets or combinations of data.

Advantages of a view:

1. Access to the database because the view can selectively select a part of the database.

2. You can obtain results from complex queries through simple queries.

3. Maintain data independence and try to retrieve data from multiple tables.

4. Different views can be generated for the same data.

Views are classified into simple views and complex views.

View creation:

 
 

  
  
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name   
  
  
 
  
  
[(alias[, alias]...)]   
  
  
 
  
  
AS subquery  
  
  
[WITH CHECK OPTION [CONSTRAINT constraint]]   
  
  
[WITH READ ONLY]   
 
 

Where:
Or replace: If the created attempt already exists, ORACLE automatically recreates the view;
FORCE: this view is automatically created no matter whether the base table exists in ORACLE;
NOFORCE: this view is created only when the base table exists in ORACLE:
Alias: the alias defined for the columns generated by the view;
Subquery: a complete SELECT statement that defines aliases;
With check option:
The inserted or modified data rows must meet the view-defined constraints;
With read only:
This view does not support any DML operations.

For example:

 
 

  
  
CREATE  OR  REPLACE  VIEW  dept_sum_vw  
  
  
   (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; 
 
 

Statement for creating a tablespace in oracle

How to Implement Oracle index Reconstruction

Complete Oracle File System Parsing

ORACLE system table and data dictionary View

In-depth analysis of Oracle ERP system module