Oracle multi-table joint query, statistical query, group function, order by, having, subquery, set operation

Multi-table joint Query
You can create a multi-Table query through a connection. The data queried from multiple tables can come from multiple tables, but appropriate connection conditions must exist between tables. To query multiple tables, you must identify the public columns that are connected to multiple tables. Generally, a comparison operator is used in the WHERE clause to specify the conditions for connection.

Forgetting to describe the table's connection conditions is a common error, in this case, the query will generate the Cartesian product of the table join (that is, the result of connecting each record in one table to each record in another table ). Generally n tables to connect, need at least N-1 connection conditions, in order to be able to correctly connect. Two table joins are the most common cases. You only need to specify one join condition.

There are four Connection Methods for the two tables:
* Equal connection.
* Unequal connection.
* External connection.
* Self-connection.
1. Equal connection
Two columns with the same meaning can be used to create equal join conditions. When two tables are queried using equal join, only rows with the same value in the two tables with the same join column will appear in the query results.

Displays the employee name and department ID and name.
Run the following query:

SQLCode  

1.
SelectEMP. ename, EMP. deptno, Dept. dnameFromEMP, Dept

1. WhereEMP. deptno = Dept. deptno;

The execution result is as follows:

SQLCode  

1.
Ename deptno dname

1. ------------------------------------------------
2. Smith 20 research
3. Allen 30 sales

Note: the format of the Equi-join statement is to list the names of two tables in sequence in the from clause. You must add the table name before each column of the table, and use ". columns are separated to indicate different tables. In the where condition, specify the columns for equal join.
In the preceding training, the column names that do not appear in both tables can be omitted. Therefore, the preceding example can be simplified as follows:
Select ename, EMP. deptno, dname from EMP, Dept
Where EMP. deptno = Dept. deptno;

2. External Connection
In the above example, there is a problem with equal connection: if an employee's department is not filled in, that is, it is left blank, then the employee will not appear in the query; or a department that has no employees does not appear in the query.
To solve this problem, you can use external connections. In addition to displaying records meeting equal connection conditions, the rows that do not meet the connection conditions are displayed. rows that do not meet the connection conditions are displayed at the end. The outer join operator is (+), which can appear on the left or right of equal join conditions. The meaning on the left or right is different. The following example is used to describe the meaning.

Use external connections to display records that do not meet equal conditions.
Step 1: display the employee name, salary, Department name, and department without any employees.
Run the following query:

SQLCode  

1.
SelectEname, Sal, dnameFromEMP, Dept

1. WhereEMP. deptno (+) = Dept. deptno;

The execution result is:

SQLCode  

1.
Ename Sal dname

1. ---------------------------------------------------------

3.
Clark 2450 Accounting

1. King 5000 accounting
2. Miller 1300 accounting
3. ...
4. Turner 1500 sales
5. ward 1250 sales
6. Operations

3. Unequal connections
You can also perform unequal connections. The following is a training instance. The structure of the salgrade table used is as follows:
Desc salgrade

SQLCode  

1.
Whether the name is of the null type

2. -------------------------------------------------------------------------------
4. Grade number
6. Losal number
8. Hisal number

Grade indicates the wage level, and losal and hisal indicate the lower limit and upper limit of the wage of a certain level, respectively.
The table content is:

SQLCode  

1.
Select*FromSalgrade;

 

SQLCode  

1.
Grade losal hisal

2. ---------------------------------------------------
4. 1 700 1200
6. 2 1201 1400
8. 3 1401 2000
10. 4 2001 3000
12. 5 3001 9999

Displays the employee name, salary, and wage grade.
Run the following query:

SQLCode  

1.
SelectE. ename, E. Sal, S. GradeFromEmp e, salgrade s

1. WhereE. Sal between S. losal and S. hisal;

The execution result is:

SQLCode  

1.
Ename Sal grade

1. --------------------------------------------------------

3.
Jones 2975 4

2. Blake 2850 4
4. Clark 2450 4
6. Scott 3000 4
8. Ford 3000 4
10. King 5000 5

Note: by comparing the employee's salary with the upper and lower limit of different wages, the employee's salary grade is obtained and displayed in the query results.

4. Self-connection
The last step is a self-join training instance. The self-join is a table that is connected to itself. For a self-join, you can imagine that two identical tables (copies of tables) exist. You can use different aliases to distinguish two identical tables.
Display the employee name and employee's manager name.
Run the following query:

SQLCode  

1.
SelectWorker. ename | 'the manager is' | manager. enameAsEmployee Manager

1. FromEMP worker, EMP Manager
2. WhereWorker. Mgr = manager. empno;

The execution result is:

SQLCode  

1.
Employee Manager

2.
-------------------------------------------

1. Smith's manager is Ford.
2. Allen's manager is Blake.
3. Ward's manager is Blake.

Note: Two aliases worker and manager are created for the EMP table. As you can imagine, the first table is the employee table, and the second table is the manager table, because the manager is also an employee. Then, use the Mgr (Manager number) field in the worker table to establish a connection with the empno (employee number) field in the Manager table, so that the employee's manager name can be displayed.
Note: The manager number MGR is one of the employee numbers empno, so the manager number can be connected with the employee number.

Statistics Query
Statistics are usually required to summarize the statistical information of the database. For example, we may want to know the total number of employees and total salaries of the company, or the number of employees and salaries of each department. This function can be completed by statistical queries.
Oracle provides some functions to complete statistics. These functions are called group functions. Group functions are different from the previous functions (single-row functions ). Group functions can sum and calculate the average value of grouped data. Group functions can only be used in select clause, having clause, or order by clause. Group functions can also be called statistical functions.

Group function:
AVG: Average Value
Count: calculates the Count value and returns non-null rows. * indicates that all rows are returned.
MAX: calculates the maximum value.
Min: Minimum value
Sum: Sum
Stddev: calculates the standard deviation based on the square root of the difference.
Variance: returns the statistical variance.

In grouping functions, sum and AVG are only used for numeric columns. Max, Min, and count can be used for character, value, and date columns. Group functions ignore column null values.
You can use the group by clause to group data. Grouping refers to dividing records into groups based on the same content of columns, and grouping functions can be applied to groups.
If grouping is not used, the group function is applied to the entire table or the records that meet the conditions.
The distinct or all keyword can be used in group functions. All indicates that all non-null values (repeatable) are operated (except count ). Distinct indicates a non-null value. If a duplicate value exists, the group function operates only once. If the preceding keyword is not specified, the default value is all.

Number of employees who require Commission.
Run the following query:

SQLCode  

1.
SelectCount (Comm)FromEMP;

The returned result is:

SQLCode  

1.
Count (Comm)

1. ---------------------
2. 4

Note: In this example, all employees are not returned, and only four employees with non-blank Commission are returned.

Calculate the number of different positions in the employee table.
Run the following query:

SQLCode  

1.
SelectCount (DistinctJob)FromEMP;

The returned result is:

SQLCode  

1.
Count (DistinctJob)

1. -------------------------------
2. 5

Note: This query returns the number of different positions in the employee table. If distinct is not added, the number of employees with non-empty positions is returned.

Group statistics
Through the following training, we will learn about grouping usage.
Calculate the total salary by position.
Step 1: perform the following query:

SQLCode  

1.
SelectJob, sum (SAL)FromEMPGroup ByJob;

The execution result is:

SQLCode  

1. Job sum (SAL)

2.
------------------------------------

3.
Analytic 6000

4.
Clerk 4150

5.
Management 8275

6.
President 5000

7.
Salesman 5600

Note: grouping query allows you to include group columns in the query list. For the above instances, the job field can be included in the query column because it is grouped by job. This makes the statistical result clear.
The total salary of an employee with the job of analyst is 6000, and the total salary of an employee with the job of clerk is 4150, and so on.
Note: you cannot use columns other than group columns in the query column. Otherwise, an error message is generated.

Incorrect syntax: Select ename, job, sum (SAL) from EMP group by job;

Multi-column grouping statistics
You can group by multiple columns. The following is an example of grouping by two columns.
Calculate the total salary by department and position group.
Run the following query:

SQLCode  

1.
SelectDeptno, job, sum (SAL)FromEMP

2.
Group ByDeptno, job;

The execution result is:
Deptno job
Sum (SAL)
--------------------------------------------------
10 clerk 1300
10
Management 2450
10 President
5000
20
Analytic 6000
20
Clerk 1900
20
Management 2975
30
Clerk 950
30
Management 2850
30
Salesman 5600
Note: This query calculates the total salary of each job in each department.
Limits on group statistics
Use having clause to filter the group query results. Having clause filters the grouping results. It can only appear after the group by clause, and the where clause must appear before the Group by clause.
Calculate the highest salary of each department, excluding the departments whose highest salary is less than 3000.
Run the following query:

SQLCode  

1.
SelectDeptno,Max(Sal)FromEMP

1. Group ByDeptno
2. HavingMax(Sal) >=3000;

The execution result is:

SQLCode  

1.
DeptnoMax(Sal)

1. ------------------------------------
2. 10 5000
3. 20 3000

Note: department 30 is excluded from the result because the total salary of Department 30 is less than 3000.
Note: Group functions must appear in the limitations of having clauses. If both the where condition is used, the where condition is executed before the group, and the having condition is executed after the group.

Group statistics result sorting
You can use the order by clause to sort the statistical results. The order by clause must appear at the end of the statement.
Calculate and sort the total salary by position.
Run the following query:

SQLCode  

1.
SelectJob title, sum (SAL) Salary sumFromEMP

2.
Group ByJob

3.
Order BySum (SAL );

The execution result is:

SQLCode  

1.
Job salary sum

2.
----------------------------------

3.
Clerk 4150

4.
President 5000

5.
Salesman 5600

6.
Analytic 6000

7.
Management 8275

Nested use of group functions
In the following training, nested group functions are used.
Calculate the highest average salary of each department.
Run the following query:

SQLCode  

1.
Select Max(AVG (SAL ))FromEMPGroup ByDeptno;

The execution result is:

SQLCode  

1.
Max(AVG (SAL ))

1. -----------------------
2. 2916.66667

Note: This query first calculates the average salary of each department and then obtains the maximum value.
Note: Although there are grouping columns in the query, grouping Columns cannot appear in the query field.

Subquery
We may ask the question of who has the highest salary or who has higher salary than Scott. You can use the results of one query as part of another query. Specifically, to query the names and salaries of employees whose salaries are higher than Scott, two steps are required. The first step is to query the salaries of employee Scott, and the second step is to query employees whose salaries are higher than Scott. The first query can appear in the condition of the second query as part of the second query, which is a subquery. The query that appears in other queries is called a subquery, and the query that contains other queries is called a primary query.

Subqueries generally appear in the WHERE clause of the SELECT statement. Oracle also supports subqueries in the from or having clause. A subquery is executed before the primary query. The results are used as the conditions for the primary query. in writing, the subquery is expanded with parentheses and placed on the right of the comparison operator. Subqueries can be nested, with the latest layer of queries first executed. Subqueries can be used in select, insert, update, delete, and other statements.
Subqueries can be divided into single-row subqueries, multi-row subqueries, and multi-column subqueries based on the type of returned data.

Single Row subquery
Query the names and salaries of employees who are higher than Scott.
Run the following query:

SQLCode  

1.
SelectEname, SalFromEMP

2.
WhereSAL> (SelectSalFromEMPWhereEmpno = 7788 );

The execution result is:

SQLCode  

1.
Ename Sal

2.
----------------------------------

3.
King 5000

Note: When querying Scott's salary in this subquery, his employee number is used. This is because the employee number is unique in the table, and the employee name may be duplicated. Scott employee number is 7788.
It can also contain two or more subqueries.

SQLCode  

1.
InFromUse subquery in Clause

Subqueries can also be used in the from clause. In principle, this is similar to using subqueries in the where clause. Sometimes we may require that employees be retrieved from the employee table based on the location where the employees appear. It is easy to think of using the rownum virtual column. For example, we want to display the employee Table 6 ~ Employees at location 9 can use the following methods.

The number of queries in the employee table is 6th ~ Employee at location 9.
Run the following query:

SQLCode  

1.
SelectEname, SalFrom(SelectRownumAsNum, ename, SalFromEMPWhereRownum <= 9)

1. WhereNum> = 6;

The execution result is:

SQLCode  

1.
Ename Sal

1. -------------- --------------------
2. Blake 2850
3. Clark 2450
4. Scott 3000
5. King 5000

Note: When a subquery appears in the from clause, search for employees whose travel number is less than or equal to 9 and generate the num number column. Search for employees whose row number is greater than or equal to 6 in the primary query.
Note: The following statements do not show query results:
Select ename, Sal from EMP
Where rownum> = 6 and rownum <= 9;

Set operation
The results of multiple query statements can be set. The field type, quantity, and order of the result set should be the same.
Oracle has four set operations

Union: Union. Merge the results of the two operations to remove duplicates.
Union all: Union, which combines the results of two operations and retains the duplicate parts.
Minus: The difference set. Remove the same part from the previous operation results as the subsequent operation results.
Intersect: intersection, take the same part of the two operation results

Use the union operation of the Set
Query all positions of Department 10 and department 20.
Run the following query:

SQLCode  

1.
SelectJobFromEMPWhereDeptno = 10

1. Union
2. SelectJobFromEMPWhereDeptno = 20;

The execution result is:

SQLCode  

1.
Job

2.
---------

3.
Analyst

4.
Clerk

5.
Manager

6.
President

Note: department 10 has the following positions: President, manager, and clerk. Department 20 has the following positions: Manager, clerk, and analyst. Therefore, there are four roles in the two Departments (one for the same position): analyst, clerk, manager, and President.
The result of changing Union to Union all is: display all the values found in the two statements without removing the duplicate values.

Use the intersection of Sets
Check whether the same job title and salary exist in department 10 and department 20.
Run the following query:

SQLCode  

1.
SelectJob, SalFromEMPWhereDeptno = 10

1. Intersect
2. SelectJob, SalFromEMPWhereDeptno = 20;

The execution result is:
Unselected row

Note: department 10 has the following positions: President, manager, and clerk. Department 20 has the following positions: Manager, clerk, and analyst. Therefore, the two departments share the same roles: Clerk and manager. However, employees with the same positions and salaries do not have any results.
Use the Difference Operation of the Set
Query the Department numbers that appear only in the department table but not in the employee table.
Run the following query:

SQLCode  

1.
SelectDeptnoFromDept

2.
Minus

3.
SelectDeptnoFromEMP;

The execution result is:

SQLCode  

1.
Deptno

2.
------------------

1. 40

Note: The Department numbers in the department table are 10, 20, 30, and 40. Department numbers in the employee table include 10, 20, and 30. The result of the difference set is 40.