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