Oracle PL/SQL functions) (4)

62. Min (distinct | all)
Minimum value. "All" indicates minimum value for all values, and "distinct" indicates minimum value for different values. The same value is used only once.
SQL> select Min (all Sal) from Gao. table3;

Min (allsal)
-----------
1111.11

63. stddev (distinct | all)
Evaluate standard deviation. "All" indicates evaluate standard deviation for all values, and "distinct" indicates evaluate standard deviation for different values only.
SQL> select stddev (SAL) from Scott. EMP;

Stddev (SAL)
-----------
1182.5032

SQL> select stddev (distinct Sal) from Scott. EMP;

Stddev (distinctsal)
-------------------
1229.951

64. Variance (distinct | all)
Covariance

SQL> select variance (SAL) from Scott. EMP;

Variance (SAL)
-------------
1398313.9

65. Group
It is mainly used to calculate the number of a group.
SQL> select deptno, count (*), sum (SAL) from Scott. EMP group by deptno;

Deptno count (*) sum (SAL)
---------------------------
10 3 8750
20 5 10875
30 6 9400

 

66. Having
Grouping statistics plus restrictions
SQL> select deptno, count (*), sum (SAL) from Scott. EMP group by deptno having NT (*)> = 5;

Deptno count (*) sum (SAL)
---------------------------
20 5 10875
30 6 9400
SQL> select deptno, count (*), sum (SAL) from Scott. EMP having count (*)> = 5 group by TNO;

Deptno count (*) sum (SAL)
---------------------------
20 5 10875
30 6 9400

67. Order
Used to sort and output the queried results.
SQL> select deptno, ename, Sal from Scott. EMP order by deptno, Sal DESC;

Deptno ename Sal
----------------------------
10 King 5000
10 Clark 2450
10 Miller 1300
20 Scott 3000
20 Ford 3000
20 Jones 2975
20 Adams 1100
20 Smith 800
30 Blake 2850
30 Allen 1600
30 Turner 1500
30 ward 1250
30 Martin 1250
30 James 950