Database view detailed notes. The advantages of view, the execution rules of DML operation in view, how to reject DML operation in view, and what is built-in view

Posted by MagicMikey on Wed, 26 Jan 2022 19:53:21 +0100

view

Views are other objects in the database

1. What is a view

You can create a view of a table to represent a logical subset of data or a combination of data. A view is a logical table based on a table or another view. A view does not contain its own data. It is like a window through which you can view or change the data in the table. The table on which the view is based is called the base table.

2. Advantages of view

  • Views restrict data access because they can selectively display columns in a table.
  • Views can be used to construct simple queries to retrieve the results of complex queries. For example, views can be used to query information from multiple tables without users having to know how to write join statements.
  • Views provide data independence for specific users and applications. A view can retrieve data from several tables.

3. Type of view

Simple views and complex types

4. Simple view VS complex view

There are two categories of Views: simple and complex. The basic difference involves DML(NSERT, UPDATE and DELETE) operations.

Simple view:

  • Data from only one table
  • Contains no functions or data groups
  • Can perform DML operations through views

Complex view:

  • Data from multiple tables
  • Contains functions or data groups
  • DML operation through view is not allowed

5. Create a simple view

Example:

Create a view that contains the id, name and salary of the employee with department id 80.

create view emp80 as select e.employee_id,e.last_name,e.salary from employees e where e.department_id = 80;

Note: the attempt itself will not store data.

select * from emp80;

When we execute select to query the view, the data we get is still the data in the table, which is equivalent to that select indirectly executes the query statement executed when creating the view.

6. Create a view with the column names in the subquery

If a column alias is included in the query statement that creates the view, the column alias will be used as the column name of the view.

Example:

Create a view, including the employee id with department id 50, using ID_NUMBER names the column, including the employee's NAME. NAME is used to NAME the column, and ANN is used to include the employee's annual salary_ Salary names the column.

create view emp50 as select e.employee_id id_number,e.last_name name,12*e.salary ann_salary from employees e;

7. Retrieve data from view

Example 1:

Query the employee information with department id 80, including their id, name and salary

select * from emp80;
Return (partial):
EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        165 Lee                          6800.00
        166 Ande                         6400.00
        167 Banda                        6200.00
        168 Ozer                        11500.00
34 rows selected

Example 2:

Query the employee information with department id 50, including their id and salary.

select e.id_number,e.ann_salary from emp50 e;
Return (partial):
ID_NUMBER ANN_SALARY
--------- ----------
      201     156000
      202      72000
      203      78000
110 rows selected

Note: when we use the view to query data, we can only query the columns contained in the view, not the columns not in the view.

8. Create complex views

Example:

Create a view that contains the Department name of each department, the Department minimum salary, the Department maximum salary, and the Department average salary.

create view dept_name as select d.department_name,min(e.salary) min_sal,max(e.salary) max_sal,avg(e.salary) avg_sal from employees e,departments d where e.department_id = d.department_id group by d.department_name;

Note: if the view contains functions, you must alias the columns.

select * from dept_name;
Return (partial):
DEPARTMENT_NAME                   MIN_SAL    MAX_SAL    AVG_SAL
------------------------------ ---------- ---------- ----------
Development                          1000       1000       1000
Administration                       4400       4400       4400
Accounting                           8300      12008      10154
Executive                           17000      24000 19333.3333
12 rows selected

9. Specify the column name when defining the view

Example:

Create a view that contains the Department name, Department minimum salary, Department maximum salary, and department average salary for each department. Name the Department name as name The minimum salary is named minsal The highest salary is named maxsal and the average salary is named avgsal.

create view dept_name1(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;

Note: alias the column, which can follow the column or be written in front of the as when creating.

10. Execution rules of DML operation in view

If the view contains the following parts, you cannot modify the data.

  • Group function
  • GROUP BY clause
  • DISTINCT keyword
  • Columns defined with expressions

Example:

Delete the employee with employee ID 179 in emp80 view.

delete from emp80 e where e.employee_id = 179;

Note: there are no group functions, GROUP BY clauses, or DISTINCT keywords in the emp80 view.

11. Reject DML operation

Example:

Create a simple view containing all the data in the employees table, but the view rejects DML operations.

create view v_emp as select * from employees with read only;

Note: when creating a view, adding with read only after it indicates that the view is read-only and cannot be operated by DML.

Key: object names in the database cannot be duplicated, including view names and table names.

delete from v_emp v where v.DEPARTMENT_ID = 179;
return:
ORA-42399: Cannot execute on a read-only view DML operation

12. Create views through tools


Note: to create a view with a tool, click execute to create a view.

13. Delete view

Deleting a view will not lose data because the view is based on the basic table in the database and no data is stored in the view.

Example:

Delete emp90 view.

drop view emp90;

14. Built in view

  • A built-in view is a subquery with an alias (or correlation name) that can be used in an SQL statement.
  • The subquery specified in the FROM clause of a main query is a built-in view.

Built in Views: built in views are created by subqueries with aliases in the FROM clause. This sub query defines a data source that can be referenced in the main query.

Example:

Displays the name, salary, department number and the highest salary of those employees who are lower than the highest salary in their department.

select em.last_name,em.department_id,e.maxsal from employees em ,(select e.department_id, max(e.salary) maxsal from employees e group by e.department_id) e where em.department_id = e.department_id and em.salary < e.maxsal;
Return (partial):
LAST_NAME                 DEPARTMENT_ID     MAXSAL
------------------------- ------------- ----------
Smith                                80      14000
Bates                                80      14000
Kumar                                80      14000
Abel                                 80      14000
95 rows selected

Topics: Database Oracle