Oracle Series: (12) Multi-line functions

Function: Oracle server first write a certain function of the program fragment, built into the Oracle server, for users to call

Single-line function: Enter a parameter to output a result, for example: Upper (' baidu.com ')->baidu.com

Multiline function: Enter multiple parameters, or an internal scan multiple times, to output a result, for example: count (*)->14

Count the total number of employees in the EMP table

Select COUNT (*) from EMP;

* number is suitable for small table fields, if the field is large, scanning time is many, inefficient, the project advocates the use of a non-null unique field, usually the primary key

How many non-repeating departments does the statistics company have?

Select COUNT (Distinct deptno) from EMP;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/86/F7/wKioL1fP5-jzPdcnAAAIxCfhpn4028.png "title=" 012. PNG "alt=" Wkiol1fp5-jzpdcnaaaixcfhpn4028.png "/>

Number of employees counting commissions

Select COUNT (comm) from EMP;

Note: These are the multiple line functions that are spoken today, and do not count null values

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/86/F7/wKioL1fP6EvyJrbXAAAHHXCHrHE425.png "title=" 013. PNG "alt=" Wkiol1fp6evyjrbxaaahhxchrhe425.png "/>

Total employee salary, average wage, rounded, reserved 0 digits after decimal point

Select sum (SAL) "total wage", round (avg (SAL), 0) "average wage" from EMP;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/86/F8/wKiom1fP6IqSteiMAAANtFT0fec471.png "title=" 014. PNG "alt=" Wkiom1fp6iqsteimaaantft0fec471.png "/>

Check the employee table for maximum wage, minimum wage

Select Max (SAL) "Maximum wage", min (sal) "minimum wage" from EMP;

The earliest entry, the latest entry staff

Select Max (hiredate) "Latest Entry Time", Min (hiredate) "Earliest entry time" from EMP;

Multi-line function: Count/sum/avg/max/min

The average wage of the department is calculated by department, and the average wage is taken as an integer, using truncated

Select Deptno "department number", Trunc (avg (SAL), 0) "departmental average wage" from empgroup by Deptno;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/86/F7/wKioL1fP6SzzdNLmAAATKAmEMqA240.png "title=" 015. PNG "alt=" Wkiol1fp6szzdnlmaaatkamemqa240.png "/>

(continued) To inquire departments with average salary greater than 2000 yuan

Select Deptno "department number", Trunc (avg (SAL), 0) "departmental average wage" from Empgroup by Deptnohaving trunc (avg (SAL), 0) > 2000;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/86/F8/wKiom1fP6XSCDDQxAAAREdLPxhI958.png "title=" 016. PNG "alt=" Wkiom1fp6xscddqxaaaredlpxhi958.png "/>

(continued) Descending by department average wage

Select Deptno "department number", Trunc (avg (SAL), 0) "departmental average wage" from Empgroup to Deptnohaving trunc (avg (SAL), 0) > 2000order by 2 desc;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/86/F7/wKioL1fP6b-THbLUAAAV-mGM6ho064.png "title=" 017. PNG "alt=" Wkiol1fp6b-thbluaaav-mgm6ho064.png "/>

In addition to department 10th, the Department of Inquiry department with average salary greater than 2000 yuan, the way a "have deptno<>10"

Select Deptno,avg (SAL) from Empgroup by Deptnohaving deptno<>10;

In addition to department 10th, the department with the average salary is more than 2000 yuan, the way two "where deptno<>10" "Recommended"

Select Deptno,avg (SAL) from Empwhere Deptno<>10group by Deptno;

Show the maximum of departmental average wages

Select Max (SAL) "Department maximum average wage value" from Empgroup by Deptno;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/86/F8/wKiom1fP6lDgOt1ZAAAPHLpPLeY622.png "title=" 018. PNG "alt=" Wkiom1fp6ldgot1zaaaphlppley622.png "/>

think : Show the maximum of the department's average salary and the department number?

Select Max (SAL) "Department maximum average wage", deptno "department number"

From EMP

Group BY Deptno;

Error

Details of the GROUP BY clause: 1) All columns of non-multiline functions that appear in the SELECT clause, "must" appear in the GROUP BY clause 2) All columns appearing in the GROUP BY clause, "can appear, are not present" in the SELECT clause The difference between where and having: where 1) Line Filter 2) for the original record 3) follow from behind 4) where can save 5) Execute First Having 1) Group Filter 2) for post-grouping records 3) following group by 4) Having can save 5) After execution Integrated syntax in Oracle: 1) The SELECT clause-----must be 2) FROM clause-------must, do not know what table, write dual 3) WHERE clause------optional 4) GROUP BY clause---optional 5) HAVING clause-----optional 6) ORDER BY clause--optional, if column name, alias, Expression, field

Oracle Series: (12) Multi-line functions