3. MAX(expr); MIN(expr);
MAX(expr), MAX(ALL expr), and MAX(DISTINCT expr) examine the values for expr
in a group of rows and return the largest value. Null values are ignored. MIN(expr),
MIN(ALL expr), and MIN(DISTINCT expr) examine the values for expr in a group of
rows and return the smallest value. Consider these queries:
Query 1: select min(commission_pct), max(commission_pct) from
employees
Query 2: select min(start_date),max(end_date) from job_history
Query 3: select min(job_id),max(job_id) from employees
Query 1 returns 0.1 and 0.4 for the minimum and maximum COMMISSION_PCT
values in the EMPLOYEES table. Notice that null values for COMMISSION_PCT are
ignored. Query 2 evaluates a DATE column and indicates that the earliest START_DATE
in the JOB_HISTORY table is 17-SEP-1987 and the latest END_DATE is 31-DEC-1999.
Query 3 returns AC_ACCOUNT and ST_MAN as the JOB_ID values appearing first and
last alphabetically in the EMPLOYEES table.
EXAM TIP There are two fundamental rules to remember when studying
group functions. First, they always operate on a single group of rows at a time.
The group may be one of many groups a dataset has been segmented into, or
it may be an entire table. The group function executes once per group. Second,
rows with nulls occurring in group columns or expressions are ignored by all
group functions, except the COUNT(*) form of the COUNT function.
Bạn đang xem 3. - OCA OCP ORACLE DATABASE 11G A LL IN ONE EXAM GUIDE P51 PPS