Statistical function Statistical functions include: count (): Sum of Statistics (): Calculate sum min (): Compute Minimum AVG (): Averaging Max (): Calculating the total number of people in the EMP table, average wage, total wage, minimum wage With maximum wage select COUNT (*) Number of AVG (SAL) average salary, sum (SAL) total monthly expenses, min (sal) minimum wage, max (SAL) maximum wage from EMP; Example: Statistics The average employment life of the company SELECT avg ( Months_between (sysdate,hiredate)/12) from EMP; ---Statistical functions allow nesting with other functionsNote: Only the count () function returns the result when there is no data in the table, and the others are null. Group statistics If you want to group by using a group by sentence, then the SQL syntax structure becomes the following form: "4 Select the data column you want" selection [DISTINCT] * | grouping columns [aliases], grouping columns [aliases] ... "1 determining the data source" from table name "2 Filter Data Rows" [WHERE qualification (s)]; [Alias] "3 Grouping of filtered rows" [Group by Group field, Group Field ...] "5 Data Sort" Order by sort field [asc| DESC], sort field [asc| DESC], ... example: According to the department number group, the number of each department, the number of people, the average wage select Deptno, COUNT (*), AVG (SAL) from the Empgroup by deptno; There are three conditions to note when using a group: 1, if the query does not use a GROUP BY clause, only statistical functions are allowed in the SELECT clause, and no other fields are allowed.  2, if a GROUP BY clause is used in a query, only grouped fields, statistical functions, and other fields are allowed to appear in the SELECT clause. 3. Statistical functions allow nesting, but only nested functions are allowed inside the SELECT clause after nesting, and no fields, including grouped fields, are allowed. HAVING clause having clause: there must be a group by when having a having. otherwise not necessarily. "5 Selecting the Required data column" SELECT [DISTINCT] * | grouping columns [aliases], grouping columns [aliases] ... "1 determining the data source" from table name [alias] "2 Filter data Rows" [WHERE qualification (s)]; "3 Grouping of filtered rows" [Group by Group field, Group Field ...] "4-pin grouping for filtered rows" [Having group filter] "6 data Sort" Order by sort field [asc| DESC], sort field [asc| DESC], ... example: Ask for the name of each position, the average salary of the position, but the average salary required to display is higher than 2000. SELECT job, AVG (SAL) from Empgroup by jobhaving avg (SAL) > 2000; Description: What is the difference between where and having? The WHERE clause is filtered before the group by group, which refers to the selection of data that can participate in grouping, and the use of statistical functions is not allowed in the WHERE clause; having childAfter the group by group is executed, you can use statistical functions four, subqueries nested subqueries are query nesting. All need to use (). And a subquery can appear at random anywhere in the query clause. But there are the most sub-queries in the location: where, from,having. Use the recommended scheme as follows.
WHERE clause: Subquery returns single-row, single-column, multi-row single -line;
HAVING clause: The subquery returns a single row and is filtered using statistical functions;
FROM clause: The subquery returns multiple rows and columns;
1. Where clause: The WHERE clause mainly returns a single-row, single-column, multiple-row, single-line, and so on: Example: Ask for the lowest wages and wages of the Guyuan information. select * from emp where sal= (SELECT MIN (SAL) from EMP); When a multiple row column is returned for a WHERE clause query, It is essentially the equivalent of telling the user the scope of a data operation. And the scope of the judgment, there are three operators: in, any, all in Operations: Example: Find the salary in the manager staff in the scope of employee information Select *from Empwhere Sal in (SELECT sal from emp WHERE job = ' MANAGER '), not in operation: Example: Find employee information that is not in the MANAGER's staff SELECT *from Empwhere Sal in (SELECT sal from emp WHERE job = ' MANAGER '); Note that the operation here must ensure that the result of the query in the clause is not NULL. any operation: 1, =any: functionally and in completely without any difference select *from empwhere sal = Any (select Sal from emp WHERE job = ' MANAGER '); 2, > Any: Larger than the smallest content returned by the subquery select *from empwhere sal > any (select sal from emp WHERE job = ' MANAGER '), 3, <any: the most Large value of content to small select *from Empwhere sal < any (select Sal FROM emp WHERE job = ' MANAGER '); all operation: 1, >all: greater than the subquery returns the largest value select *from empwhere sal > All (select Sal FRO M emp WHERE job = ' MANAGER '); 2, <all: Smaller than the smallest value returned by the subquery select *from empwhere sal < All (select Sal from emp WHERE Job = ' MANAGER '); exists () determines whether the condition satisfies the condition if the subquery has data returned (regardless of any data). Then the data is displayed, otherwise it is not displayed. Using exists () only cares if there are rows returned within the subquery, as to what line it does not care about the select *from EMP
where EXISTS (SELECT * from emp WHERE empno=7839); 2. The HAVING clause uses a subquery to have the premise that the group by is used and group by is used. Example: A requirement to count all department numbers, average wages and number of departments above the company's average salary. Select Deptno, COUNT (*), AVG (SAL) from Empgroup to deptnohaving avg (SAL) > (SELECT avg (SAL) from EMP); 3. The FROM clause uses a subquery example: The table that queries this statement is the most temporary table: SELECT deptno, COUNT (empno) count, avg (SAL) AVG from EMP GROUP BY DEPTNO---grouped by department Inquire about the number of departments, average salary and other information. The temp table is then queried with the Dept table for more than one table. Select D.deptno, D.dname, D.loc, Temp.count, Temp.avgfrom dept D, (select Deptno, Count (empno) count, AVG (SAL) avgfrom em P GROUP by Deptno) temp WHERE D.deptno = Temp.deptno (+);
Operations for Oracle data collection
