SQL – Explain VIEW with Examples

SQL - Explain VIEW with Examples

Right now, will find out about the VIEW in SQL (DBMS), what is VIEW? How and why it is made? We will gain proficiency with about VIEW with syntax and question examples.

The view is one of the database questions in SQL. It sensibly speaks to subsets of information from at least one table.

We can present an intelligent subset of information by making perspectives on tables. A view is a consistent table dependent on the table or another view.

A view is a window of the table . View consistently relies upon the base table. The view is put away as SELECT explanation in information word reference.

Focal points:

  • To confine information get to.
  • To make a complex question simple.
  • To give information independencies.
  • To speaks to various perspectives on the same information.

Syntax:

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

Where,

Or on the other hand REPLACE – reproduces the view on the off chance that it as of now exists.

Power – makes the perspectives whether or not or not the base component exist.

NOFORCE – makes the view just if the base table exist.

see – is the name of the view.

assumed name – indicates names for the articulation chose by the view’s inquiry.

subquery – is a finished SELECT explanation.

WITH CHECK OPTION – indicates just the columns available to the view can be embedded or refreshed.

imperative – is the name allocated to the CHECK OPTION limitation.

WITH READ ONLY – Ensures that no DML activities 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 recover information from VIEW?

Syntax:

    SELECT *
    FROM  view_name

Example:

select *
from sal5;

How to expel the VIEW?

Syntax

    DROP VIEW view_name;

Example:

DROP VIEW sal5;

Sorts of VIEW

There are two sorts of perspectives.

  1. Straightforward VIEW
  2. Complex VIEW

Contrasts between Simple VIEW and complex VIEW

Simple VIEWComplex 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 a 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 a simple view.We cannot apply INSERT, DELETE and UPDATE on complex view directly.
It does not include NOT NULL columns from the base table.NOT NULL columns that are not selected by a simple view can be included in a complex view.

INLINE VIEWS

  • An inline see is a sub-inquiry with a false name that we can use inside a SQL explanation.
  • A named sub-inquiry in the FROM statement of the fundamental question is an example of an inline see.
  • An inline see isn’t a blueprint object.

TOP-N ANALYSIS

Top-N inquiries are utilized for discovering n biggest and littlest value of the segment, similar to what are the ten top-rated items? What are the ten most noticeably awful selling items?

Syntax:

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

Example:

To locate the best three worker name and pay rates 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;

Recent Posts

error: Alert: Content is protected!!