Oracle View summary (create, query, modify, delete, etc.)

View definition:

-- view is a virtual table.

-- The view is based on existing tables, and the tables on which the views are built are called base tables.
-- The statement that provides the data content to the view is a SELECT statement that can be interpreted as a stored SELECT statement.
-- view provides a different representation of the base table data to the user

Why use views?

-- Control data access
-- simplified query
-- Avoid repeated access to the same data

Create a view: Embed a subquery in the CREATE VIEW statement, which can be a complex SELECT statement

<span style= "FONT-SIZE:14PX;" >create or Replace view Empview as select employee_id emp_id,last_name name,department_namefrom Employees e,department s Dwhere e.department_id = d.department_id</span>

To define an alias for a column in a subquery when creating a view: Use aliases when selecting columns in a view

<span style= "FONT-SIZE:14PX;" >create VIEW  salvu50as SELECT  employee_id  id_number,             last_name    name,            salary*12    Ann_salaryfrom    employeeswhere   department_id = 50; View created.</span>

Query view: SELECT * from Salvu50;

Modify a view: Use the Create OR REPLACE view clause to modify the view

<span style= "FONT-SIZE:14PX;" >create OR REPLACE VIEW empvu80  (id_number, name, Sal, department_id) as SELECT  employee_id, first_name | | ' ' || Last_Name,            salary, department_id   from    employees   WHERE   department_id = 80; View created.</span>

The aliases for the columns in the CREATE VIEW clause should correspond to the columns in the subquery

Example of creating a complex view:

<span style= "FONT-SIZE:14PX;" >create Viewdept_sum_vu  (name, Minsal, Maxsal, avgsal) as SELECT d.department_name, MIN (e.salary),              MAX ( E.salary), AVG (e.salary)   from      employees E, Departments D   WHERE     e.department_id = d.department_id    GROUP by  D.department_name; View created.</span>

Rules for using DML in views:

DML operations can be performed in a simple view
Delete is not available when the view definition contains the following elements:
--Group functions
--group BY clause
--distinct keywords
--rownum Pseudo-Column

<span style= "FONT-SIZE:14PX;" >create or Replace view Sal_viewas selectavg (Salary) Avg_sal from Employeesgroup by department_id</span>

Update cannot be used when the view definition contains the following elements:
--Group functions
--group BY clause
--distinct keywords
--rownum Pseudo-Column
--the column is defined as an expression

Insert cannot be made when the view definition contains one of the following elements:
--Group functions
--group BY clause
--distinct keywords
--rownum Pseudo-Column
--the column is defined as an expression
--Table non-empty columns are not included in the view definition

To mask DML operations:

You can use the WITH READ only option to mask DML operations on a view
Any DML operation will return an Oracle server error

<span style= "FONT-SIZE:14PX;" >create OR REPLACE VIEW empvu10    (employee_number, Employee_Name, Job_title) as selectemployee_id, last_name, job_ ID   from     employees   WHERE    department_id = Ten with   READ only; View created.</span>

Delete view: Deleting a view simply deletes the definition of the view and does not delete the data from the base table

<span style= "FONT-SIZE:14PX;" >drop VIEW empvu80; View dropped.</span>

Top-n Analysis:

Top-n analysis of the maximum number of values queried:

<span style= "FONT-SIZE:14PX;" >select [column_list], ROWNUM  from   (SELECT [column_list] from         table        ORDER by  Top-n_column) WHERE  ROWNUM <=  n;</span>

Note:
The ROWNUM can only use < or <=, and >= will not be able to return any data.

<span style= "FONT-SIZE:14PX;" >select *from (select RowNum rn,employee_id,salaryfrom (select Employee_id,salary,last_namefrom Employeesorder by Salary desc)) where RN <=50 and RN >40</span>

Oracle View summary (create, query, modify, delete, etc.)