Creating And Using Views in A Database

Views are logical subset of data based on one or more tables.

A view is used primarily to hide the table structure from unauthorised users. The table based on which a VIEW is created is called as base table. Since the view behaves like a table, it is also called as virtual table.

Depending on the base table a view can be of two types:

  1. Simple View: Based on one base table.
  2. Complex View: Based on multiple base table.

Creating a View

Syntax:
CREATE [OR REPLACE][FORCE|NO FORCE] VIEW <VIEW_NAME>
AS <SELECT STATEMENT>
[WITH CHECK OPTION]
[WITH READ ONLY];
Example:
CREATE OR REPLACE VIEW VEMP
AS SELECT * FROM EMP;

Above example will create a simple view based on a single table. Through the above view any record can be inserted, updated, deleted, selected from EMP base table but no structure change is allowed.


Operations on a View

Shown below are some operations on the view we just created:

SELECT * FROM VEMP;

INSERT INTO VEMP(NAME, EMPNO)
VALUES('ROB',4);

ALTER TABLE VEMP
ADD(DEPTNO NUMBER(4));

DESC VEMP;

The statement marked in red tried to add a new column, this is an invalid statement since structure change is not allowed.

DESC command will also work on a VIEW, since a view is considered a virtual table. The desc command displays the structure of the VIEW.


View based on a condition

In the view created below any record can be inserted through the view to the base table but only those records can be updated, deleted and selected, the condition based on which the view has been created i.e. only the records where job is manager.

CREATE OR REPLACE VIEW VEMP AS
SELECT * FROM EMP
WHERE JOB='MANAGER'

A view can also be based on another view:

CREATE OR REPLACE VIEW FVIEW
AS SELECT * FROM AVIEW;

With Check Option

With Check Option is used to restrict insertion of records into the base table, when a view is created based on a conditions.

Example:
CREATE OR REPLACE VIEW VEMP
AS SELECT * FROM EMP
WHERE JOB = ‘SALESMAN’
WITH CHECK OPTION;

In the above view created only those records can be inserted, updated, deleted or selected where job is salesman.

INSERT INTO VEMP
VALUES (3, ‘CC’, ‘MANAGER’, 4500);

INSERT INTO VEMP
VALUES (3, ‘CC’, ‘SALESMAN’, 4500);

The statement in red above is invalid, since only SALESMAN records can be inserted.


With Read Only

With Read Only is used to create read only view, no update, insert or delete is allowed in such views, only data selection is allowed.

Example:
CREATE OR REPLACE VIEW REMP
AS SELECT * FROM SYSEMP8
WITH READ ONLY;

Force Option

Normally a view can only be created if its base table exists. Force option is used to create a view where the base table does not exist. This is particularly useful in cases when a lot of tables and views are needed to be created and we do not want to bother about the sequence.

Example:
CREATE FORCE VIEW FVIEW AS
SELECT * FROM EMP;

Note: The VIEW without a base table can be functional only when the base table is available.


Complex View

A complex view contains a join statement, as it is based on multiple tables.

Example:
CREATE VIEW COMPVIEW AS
SELECT EMPNO, ENAME, SAL, DNAME, LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

Complex views are mostly meant for reporting purposes. It is not advisable to perform DML operations on these kind of views.


Deleting a View

We can use the DROP statement to delete a view.

Syntax:
DROP <VIEW_NAME>;
POPULAR ARTICLES

Creating Conditional Beans in Spring

The concept of condition beans enables Spring to restrict the creation of any bean depending on the evaluation of a condition. These beans get created only when a preset condition is evaluated as true

View Article

Accepting Request Param and Path Variable in Spring Controller

Spring MVC provides various ways through which a client browser can pass data to the Controller. In this article we will discuss about accepting Request Parameters and Path Variables in Spring Contr..

View Article

Generate Namespace & Schema Information using JAXB

Most xml documents used in enterprise applications makes use of namespace to avoid element name conflicts. This article talks about generating these namespace and schema information when marshaling...

View Article

Switching Database Profile using Spring Profiles

We are most likely to have separate db configuration for different environment like development and production environment. Spring profiles provide a convenient way to switch db profiles at runtime.

View Article

SQL and its Sub-Languages

SQL (Structured Query Language) is a language understood by most modern databases. It is an ANSI (American National Standard Institute) standard language which is used to manipulate databases.

View Article

Introducing JUnit Rule

Junit Rules allows developers to add additional functionalities that can applied to all test methods in a test class. It is similar to the concept of custom test runners but with reduced restrictions.

View Article

Addressing Ambiguity in Spring Autowiring

Spring autowiring is powerful concept, but we should be very cautious while using it. We may end up in creating ambiguity while autowiring beans, which will cause autowiring to fail.

View Article

Creating and Using Synonym in Oracle Database

Synonyms are database objects used to provide duplicate names to existing objects in the database. It is just an alternate name used to hide the original name of the object.

View Article

Creating and Using Sequence in Oracle Database

A sequence is used to auto-generate numbers in ascending or descending order which can serve as a primary key or a part of it (in case of composite key).

View Article

Creating and Manipulating Constraints in Oracle Database

Constraints are used to impose certain rules on columns to avoid invalid data entry into the table. If any of the constraint is violated the operation fails.

View Article

Integrating Log4J with Perf4J for Performance Logging

Perf4j is an open source logging framework used primarily for monitoring performance statistics in java applications. Log4j has the ability to integrate with perf4j to capture performance data.

View Article

Tagging in GIT

Tagging allows us to mark a specific point in the commit history or snapshot. A tag is typically used to mark a project release. This article shows how to create tags in Git.

View Article