Blog

Views are database objects that are built by the query. Views can be built using a single table or multiple tables by way of join. Their definition is stored permanently in data dictionary but they don’t store copy of the data. Data for the view is built dynamically.

A view may contain a subset of rows of the table or a subset of columns of the table.

Create a View

Views are created using CREATE VIEW statement.

Syntax

Following is the syntax for creating a view.

CREATE/REPLACE VIEW <viewname> 
AS  
<select query>; 

Example

Consider the following Employee table.

The following example creates a view on Employee table.

CREATE VIEW Employee_View 
AS 
SELECT 
EmployeeNo, 
FirstName, 
LastName, 
FROM  
Employee;

Using Views

You can use regular SELECT statement to retrieve data from Views.

Example

The following example retrieves the records from Employee_View;

SELECT EmployeeNo, FirstName, LastName FROM Employee_View;

When the above query is executed, it produces the following output.

*** Query completed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 second.  
 EmployeeNo            FirstName                       LastName 
-----------  ------------------------------  --------------------------- 
    101                  Mike                           James 
    104                  Alex                           Stuart 
    102                  Robert                         Williams 
    105                  Robert                         James 
    103                  Peter                          Paul 

Modifying Views

An existing view can be modified using REPLACE VIEW statement.

Following is the syntax to modify a view.

REPLACE VIEW <viewname> 
AS  
<select query>;

Example

The following example modifies the view Employee_View for adding additional columns.

REPLACE VIEW Employee_View 
AS 
SELECT 
EmployeeNo, 
FirstName, 
BirthDate,
JoinedDate 
DepartmentNo 
FROM  
Employee; 

Drop View

An existing view can be dropped using DROP VIEW statement.

Syntax

Following is the syntax of DROP VIEW.

DROP VIEW <viewname>; 

Example

Following is an example to drop the view Employee_View.

DROP VIEW Employee_View; 

Advantages of Views

• Views provide additional level of security by restricting the rows or columns of a table.
• Users can be given access only to views instead of base tables.
• Simplifies the use of multiple tables by pre-joining them using Views.