Home » SQL

SQL - Explain VIEW with Examples

In this article, we are going to learn about the VIEW in SQL (DBMS), what is VIEW? How and why it is created? We will learn all about VIEW with syntax, and query examples.
Submitted by Anusha Sharma, on February 25, 2018

View is one of the database objects in SQL. It logically represents subsets of data from one or more table. We can presents logical subset of data by creating views of tables. A view is a logical table based on table or another view. A view is a window of table .View always depends on base table. The view is stored as SELECT statement in data dictionary.

Advantages

1. To restrict data access.
2. To make complex query easy.
3. To provide data independencies.
4. To represents different views of same data.

Syntax

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

Where,

• OR REPLACE - recreates the view if it already exists.
• FORCE - creates the views regardless of whether or not the base element exist.
• NOFORCE - creates the view only if the base table exist.
• view - is the name of the view.
• alias - specifies names for the expression selected by the view’s query.
• subquery - is a complete SELECT statement.
• WITH CHECK OPTION - specifies only the rows accessible to the view can be inserted or updated.
• constraint - is the name assigned to the CHECK OPTION constraint.
• WITH READ ONLY - Ensures that no DML operations can be performed on this view.

Example/Query

CREATE VIEW sal5
AS SELECT employee_id ID_NUMBER, last_name NAME ,salary*12 ANN_SALARY
FROM emp
WHERE dept_id=50;

How to retrieve data from VIEW?

Syntax

    SELECT *
    FROM  view_name

Example:

select *
from sal5;

How to remove the VIEW?

Syntax

    DROP VIEW view_name;

Example:

DROP VIEW sal5;

Types of VIEW

There are two types of views.

1. Simple VIEW
2. Complex VIEW

Differences between Simple VIEW and complex VIEW

Simple VIEW	Complex VIEW
It contains only one base table.	It contains one or more number of base tables
Group function cannot work here like MAX(),COUNT() etc.	Group function can work here.
It does not contain group of data.	It can contain groups of data.
DML operation can be performed through a Simple view.	DML operations may or may not be performed through a complex view.
INSERT, DELETE and UPDATE are directly possible on simple view.	We cannot apply INSERT ,DELETE and UPDATE on complex view directly.
It does not include NOT NULL columns from base table.	NOT NULL columns that are not selected by simple view can be included in complex view.

INLINE VIEWS

• An inline view is a sub-query with an alias that we can use within a SQL statement.
• A named sub-query in the FROM clause of the main query is an example of an inline view.
• An inline view is not a schema object.

TOP-N ANALYSIS

Top-N queries are used for finding n largest and smallest value of the column, like what are the ten best selling products? What are ten worst selling products?

Syntax:

SELECT [column_list] , ROWNUM
FROM (SELECT [COLUMN_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM  <= N;

Example: To find the top three earner name and salaries from the EMPLOYEES table:

SELECT ROWNUM as RANK,last_name,salary 
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;