Oracle_ Multi-line function

Oracle_ Multi-line function

Multiline function min (), Max (), count (), SUM (), AVG ()

--1.1 Statistics of the company's maximum wage, minimum wage and total number --for null values to skip directly, do not operate--max,min,count can operate on any typeSelect max(sal ), Min(sal),Count(empno) From emp; select  max (sal ), Span style= "Color:windowtext" >  min ( sal ), count ( comm )   From emp Select max(sal ), Min(sal),count(Mgr) from emp;  Select sum(comm), avg(comm) from emp; Select max(ename), min(hiredate) from EMP ;

--2. Count the number of records in the EMP tableSelect Count(*) from emp; Total number of department employees with--2.1 statistics Department Number 10 Select Count(*) from emp where deptno=ten;

--3. Group BY, the order by is always put in the last--to count the department number and number of employees in each department and sort them by department numberSelect deptno, Count(*) from the EMP GROUP BY Deptno order by deptno;  --3.1 for each department number, number, total wage, average salary, maximum wage, minimum wageSelectdeptno, Count(*), Sum(sal), Avg(sal), Max(sal), Min(sal) FromEMPGroup ByDeptnoOrder By Avg (sal); Select Count(*), Sum(sal), Avg(sal), Max(sal), Min(sal) FromEMPGroup ByDeptnoOrder By Avg (sal);   --3.2 count the number of each department, excluding the department numbered 30 --Write the WHERE clause first, then write group bySelect deptno, Count(*) from emp where deptno<>30 Group by deptno;

-4. Statistics of average wage per department, excluding departments with average wage less than 2000multi-line function not allowed in--WHERE clause--having After grouping is complete, filter (cannot use alias to judge), having grammar and where basically consistentSelect deptno, avg(sal) from the EMP group by Deptno has avg(sal) >=; Select deptno, avg(sal) from the EMP group by Deptno having avg(sal) >= and deptno< >;

Practice

--In the EMP table, list jobs with a minimum wage of less than 2000Select Job,min(sal) from emp Group by Job having min(sal) <;  --a combination of departments and work pairings with average wage greater than 1200Select deptno,job,avg(sal) from emp Group by Deptno, Job has avg(sal) >; -average wage of a department with a statistical population of less than 4Select deptno,avg(sal),Count(*) from emp Group by Deptno have count(*) <4;  -Statistics of the highest wages in each department, excluding the department with the highest wage of less than 3000Select from emp Group by deptno have Max(sal) > ;

Oracle_ Multi-line function