Oracle Learning Notes (vii)

Nine, advanced query (group, sub-query)
To inquire about an upgraded version:
Need to use three sheets
Employee table:
DESC EMP
EMPNO Employee Number
ENAME Employee Name
Job Employee Positions
MGR Boss Employee Number
HireDate Employee Entry Date
SAL Employee Monthly Salary
COMM Employee Bonuses
DEPTNO Employee Department Department number

View Current User:
Show Users;
SELECT * from EMP;
Department Table:
DESC Dept
DEPTNO Department Number
Dname Department Name
LoC Department Locations
SELECT * FROM dept;
Salary scale:
Desc Salgrade
GRADE level
Losal level minimum Wage
Hisal Level Highest Salary
SELECT * from Salgrade;

Ten, group query
1. Concept of grouping function
A grouping function acts on a set of data and returns a value to a set of data

2. Use of grouping functions
Commonly used grouping functions: AVG, SUM, MIN, MAX, COUNT, Wm_concat (Row to column)
(1) To find out the average salary and payroll of employees
Select AVG (SAL) as average wage, sum (SAL) as total wages from EMP;
(2) Find out the maximum and minimum value of employees ' wages
The maximum value of the Select Max (SAL) as salary, Min (sal) as the minimum value from EMP;
(3) To find out the total number of employees
Select COUNT (*) as total number from EMP;
(4) Query department number (remove duplicates)
Select COUNT (Distinct DEPTNO) as sector number from EMP;
(5) Row to column
Set line width
Set Linesize 200
Name of employee in Col Department for A60
Select Deptno as department number, the name of the employee in the Wm_concat (ename) as department from the EMP Group by DEPTNO;
(6) Grouping function and null value
A, the average salary of the staff (three methods of statistical pay)
Select sum (SAL)/count (*) One, sum (SAL)/count (SAL) two, avg (SAL) three from EMP;
B, the average employee bonus (three values are different)
Select SUM (COMM)/count (*) One, sum (comm)/count (comm) two, AVG (COMM) three from EMP;

Attention:
The grouping function automatically ignores null values
(7) using the NVL function in a grouping function
Note: The NVL function does not allow the grouping function to ignore null values
Rewrite b
All employees are counted
Select SUM (COMM)/count (*) One, sum (comm)/count (NVL (comm,0)) two, Avg (NVL (comm,0)) three from EMP;
Statistics for non-empty employees
Select SUM (comm)/count (comm) two, AVG (COMM) three from EMP;

3. Use GROUP BY clause data grouping
(1) The average salary of each department in the employee table is calculated and grouped according to department number
Select Deptno department number, AVG (SAL) average salary from EMP Group by DEPTNO;
Attention:
All columns in the select list that are not included in the group function should be included in the groups by clause
Is the Select column name one, column name two from the EMP Group by column name one;
Because column name one is not the contents of column name two, all to be added after group by
(2) The average salary of each department is asked to show: the average wage of the department
Select AVG (SAL) average salary from EMP Group by DEPTNO;

Attention:
Columns included in the GROUP BY clause do not have to be included in the select list
(3) Grouping with multiple columns
Check the employee's total salary
Select Deptno employee number, job work, SUM (SAL) total salary from EMP Group by deptno,job ORDER by Deptno;
To modify a statement:
Edit
Save after editing
Execute statement:
/
(4) Illegal use of group functions
Columns that are included with the select list, and not included in the group function, must be included in the groups by clause
Classic Error
Select Deptno,count (ename) from EMP;
Correct form:
Select Deptno,count (ename) from the EMP group by DEPTNO;

4. Use the HAVING clause to filter the grouped result set
(1) Departments with average wage greater than 2000
Select Deptno department number, AVG (SAL) average salary from EMP GROUP by DEPTNO have avg (SAL) >2000;

The difference between where and having
A, you cannot use group functions in the WHERE clause
b, you can use group functions in the HAVING clause

(2) To inquire about the average salary of department 10th (where and having can be used in general)
Select Deptno department number, AVG (SAL) average salary from EMP GROUP by DEPTNO have deptno=10;
Select Deptno department number, AVG (SAL) average salary from EMP where deptno=10 Group by DEPTNO;

Attention:
From the perspective of SQL optimization, using Where,where as much as possible can greatly reduce the number of grouped records, thus improving efficiency.

5. Use the ORDER BY clause in a grouped query
(1) The average salary of each department is requested to show: Department number, department average salary, and in ascending order of wages (can be sorted by: column, alias, expression, ordinal)
Select Deptno department number, AVG (SAL) Average salary from the EMP Group by Deptno ORDER by AVG (SAL);
Two:
Select Deptno department number, AVG (SAL) average salary from EMP GROUP by Deptno ORDER by 2;
Append Descending command:
A (followed by two or above)
A desc
/
Select Deptno department number, AVG (SAL) average salary from EMP GROUP by Deptno ORDER by 2 desc;

(2) Nesting of grouping functions
Find the maximum of departmental average wages
Idea: (a) The average salary per department is calculated through the AVG function
(b) Nesting Max functions to find the maximum of the department's average salary
Select Max Avg (SAL) average salary from EMP Group by DEPTNO;

6, GROUP BY statement enhancement
(1) According to departments, different positions, statistics of payroll,
Statistics by department, payroll
Statistical payroll
Select Deptno employee number, job work, SUM (SAL) total salary from EMP Group by deptno,job ORDER by Deptno;
Select Deptno Employee number sum (SAL) total salary from EMP GROUP by Deptno
Select SUM (SAL) total salary from EMP
The last three sentences add up to the bottom sentence
Break on Deptno Skip 2
Set PageSize 30
Select Deptno,job,sum (SAL) from the EMP Group by Rollup (Deptno,job);(not set aliases)

7, Sql*plus report function
Reports include: Title/page number/alias, etc.
Ttitle Col 15 ' My Reports ' col sql.pno empty 15 columns Show my report this title, empty 35 columns
Col deptno Heading Department number header set to no number
Col Job heading Jobs
Col sum (SAL) heading payroll
Break on Deptno Skip 1
Select Deptno,job,sum (SAL) from the EMP Group by Rollup (Deptno,job);
Save a directory, get file path
@ Path Execution

8. Multi-Table Query
(1) What is a multi-table query?
Fetching data from multiple tables is a multi-table query
(2) Cartesian set
Example Demo:
CREATE TABLE Dept_disk (
Deptno number (5,0) primary key,
Dname VARCHAR2 (20)
);
INSERT into Dept_disk values (1, ' development Department ');
INSERT into Dept_disk values (2, ' administration Department ');
CREATE TABLE Emp_disk (
Emptno number (5,0),
ename VARCHAR2 (20),
Sal Number (5,0),
Deptno Number (5,0)
);
INSERT into Emp_disk values (1, ' development Department ');
INSERT into Emp_disk values (2, ' administration Department ');
The two-pack table is merged into a table according to the following rules, called Cartesian set table
(1) The number of columns in the Cartesian set table equals the number of columns in each table, and the number of rows in the Cartesian set table, equal to the number of rows per table. Get the complete Descartes
(2) The core of multi-table query is the connection condition. The number of join conditions, the number of tables minus one, two tables have at least one connection condition.
Attention:
(a) In order to avoid the Cartesian set, a valid connection condition can be added to the where.
(b) in the actual operating environment, the use of the Descartes complete works should be avoided.
Connection type:
(3) Equivalent connection
The connection condition is an equal sign, which is the equivalent connection
Example: Query employee information, requirements display: Employee number, name, salary, department name
Select E.empno as employee number, e.ename as name, e.sal as monthly, D.dname as department name from EMP E, dept D where E.deptno=d.deptno;
(4) No equivalent connection
Example: Query employee information, requirements display: Employee number, name, salary, salary level (with Between...and need small value in front, large value in the rear)
Select E.empno as employee number, e.ename as name, e.sal as monthly salary, s.grade as salary level from EMP E,salgrade s where e.sal between S.losal and S.h Isal
(5) External connection
Example: Number of employees by department, requirements display: Department number, department name, number of people
Select D.deptno as department number, d.dname as department name, Count (E.empno) as number from Dept D, EMP E where E.deptno (+) =d.deptno GROUP by D.DEPTN O,d.dname;

Attention:
(a) Core: The record that is not established for the connection condition is still included in the final result through an outer connection
(b) Left outer connection: When the condition is not true, the table to the left of the equal sign is still included (plus (+) to the right of the equals sign)
(c) Right outer connection: When the connection condition is not true, the table to the right of the equal sign is still included (plus (+) to the left of the equals sign)

(6) Self-connected
Example: Query employee name and employee's boss name
Connection conditions the employee's boss number equals the boss's employee number.
Select E.ename as employee name, B.ename as boss name from EMP E,emp b where e.mgr=b.empno;

Attention:
(a) Core: use aliases to treat the same table as multiple tables

Problems with self-connection:
(1) Since the connection does not do any operation, the obtained is the Cartesian product
Select COUNT (*) from EMP e,emp B;
(a) Question one: self-linking is not and operation of large tables (because squared will have very large data)
Workaround: Hierarchical Query
Description: In some cases, you can override the self-connection, which is essentially a single-table query
(7) Hierarchical query
Principle: The relationship becomes a tree-like image
Select level as hierarchy, empno as employee number, ename as employee name, Sal as monthly salary, Mgr as boss from EMP Connect by prior empno=mgr start with Mgr N ull order by 1;
Advantages: Single-table query
Cons: No self-connected intuitive

Oracle Learning Notes (vii)