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_nameExample:
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.
- Straightforward VIEW
- Complex VIEW
Contrasts 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 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;