3. AVG(expr)
When AVG(DISTINCT expr) is invoked, the distinct values of expr are summed and
divided by the number of unique occurrences of expr. AVG(ALL expr) and AVG(expr)
add the non-null values of expr for each row and divide the sum by the number of
non-null rows in the group. The expr parameter must be a numeric value. Consider
the queries:
Query 1: select avg(2) from employees;
Query 2: select avg(salary) from employees;
Query 3: select avg(distinct salary) from employees;
Query 4: select avg(commission_pct) from employees;
There are 107 rows in the EMPLOYEES table. Query 1 adds the number 2 across 107
rows and divides the total by the number of rows to return the number 2. Numeric
literals submitted to the AVG function are returned unchanged. Query 2 adds the
SALARY value for each row to obtain the total salary amount of 691400, which is
divided by the rows with non-null SALARY values (107) to return the average 6461.68224.
There are 57 unique salary values, which when added, yield a total of 397900. Dividing
397900 by 57 returns 6980.70175 as the average of the distinct salary values, which is
OCA/OCP Oracle Database 11g All-in-One Exam Guide464
returned by the third query. Adding the non-null COMMISSION_PCT values produces
a total of 7.8. Dividing this by the employee records with non-null COMMISSION_PCT
values (35) yields 0.222857143, which is returned by query 4.
The MAX and MIN Functions
The MAX and MIN functions return the maximum (largest) and minimum (smallest)
expr value in a group. The MAX and MIN functions operate on NUMBER, DATE, CHAR,
and VARCHAR2 data types. They return a value of the same data type as their input
arguments, which are either the largest or smallest items in the group. When applied
to DATE items, MAX returns the latest date and MIN returns the earliest one. Character
strings are converted to numeric representations of their constituent characters based
on the NLS settings in the database. When the MIN function is applied to a group of
character strings, the word that appears first alphabetically is returned, while MAX
returns the word that would appear last. The MAX and MIN functions have this syntax:
MAX([DISTINCT|ALL] expr); MIN([DISTINCT|ALL] expr)This syntax may be deconstructed into the following forms:
Bạn đang xem 3. - OCA OCP ORACLE DATABASE 11G A LL IN ONE EXAM GUIDE P51 PPS