oracleocp笔记脚本齐全-第七天les分组函数

Reporting

Aggregated

DataUsing

the

Group

Functions分组函数Objectives课程目标5

-

2After

completing

this

lesson,

you

should

be

able

to

do

thefollowing:完成本课你应该学到Identify

the

available

group

functions识别组函数Describe

the

use

of

group

functions了解组函数作用Group

data

by

usingthe

GROUP

BY

clause运用groupby子句分组数据Include

or

exclude

grouped

rows

by

using

the

HAVINGclause限制分组数据用having子句Lesson

Agenda课程进度Group

functions:Types

and

syntax类型与语法–

Use

AVG,

SUM,

MIN,

MAX,

COUNT使用AVG,SUM,MIN,MAX,COUNTUse

the

DISTINCT

keyword

within

group

functions分组函数中使用DISTINCT关键字NULL

values

in

a

group

function分组函数空值问题Grou

rows:GROUP

BY

clauseHAVING

clauseNesting

group

functions5

-

3What

Are

Group

Functions?分组函数Group

functionsoperate

on

sets

of

rows

to

give

one

result

pergroup.分组函数操作一个分组的结果集.将行分组,按照组产生一个结果集EMPLOYEESum

salary

inEMPLOYEES

table…5

-

4Types

of

Group

Functions常用分组函数AVGCOUNTMAXMINSTDDEVSUMVARIANCEGroupfunctions5

-

5SELECTgroup_function(column),

...FROM[WHEREtablecondition][ORDERBYcolumn];5

-

6Group

Functions:

Syntax分组函数语法SELECTAVG(salary),

MAX(salary),MIN(salary),

SUM(salary)FROMWHEREemployeesjob_id

LIKE

'%REP%';Using

the

AVG

and

SUM

Functions使用AVG,SUM函数You

can

use

AVG

and

SUM

for

numericdata.对数字使用AVG,SUM5

-

7MIN(hire_date),

MAX(hire_date)SELECTFROMemployees;Using

the

MIN

and

MAX

Functions使用MIN,MAX函数You

can

use

MIN

andMAX

for

numeric,

character,

and

datedata

types.对数字，字符，日期类型的可使用MIN,MAX5

-

8Using

the

COUNT

FunctionCOUNT函数COUNT(expr)

returns

the

number

of

rows

with

non-null

valuesfor

expr:返回非空的总行数COUNT(commission_pct)SELECTFROM

employeesWHERE department_id

=

80;SELECTCOUNT(*)FROM

employeesWHERE department_id

=

50;COUNT(*)

returns

the

number

of

rows

in

a

table返回总行数:125

-

9COUNT(DISTINCT

department_id)SELECTFROM

employees;Using

the

DISTINCT

KeywordDISTINCTCOUNT(DISTINCT

expr)

returns

the

number

of

distinctnon-null

values

ofexpr.返回非空唯一总行数To

display

the

number

of

distinct

department

values

in

theEMPLOYEES

table:显示唯一部门数量5

-

10Group

Functions

and

Null

Values空值Group

functions

ignore

null

values

in

the

column:默认分组忽略空值The

NVL

function

forces

group

functions

to

includenullvalues:nvl可以强制分组函数处理空值AVG(commission_pct)SELECTFROM

employees;AVG(NVL(commission_pct,

0))SELECTFROM

employees;125

-

11Lesson

Agenda课程进度Group

functions:Types

and

syntaxUse

AVG,

SUM,

MIN,

MAX,

COUNTUse

DISTINCT

keyword

within

group

functionsNULL

values

in

a

group

functionGrou

rows:GROUP

BY

clauseHAVING

clauseNesting

group

functions5

-

12Creating

Groups

of

Data创建分组数据EMPLOYEES…440095003500640010033Average

salary

in

theEMPLOYEES

table

foreach

department5

-

13Creating

Groups

of

Data:

GROUP

BY

Clause

Syntax创建分组数据group

by语法You

can

divide

rows

in

a

table

into

smaller

groups

by

using

theGROUP

BY

clause.使用group

by

可以把表的行分割成多个小组SELECTFROM[WHERE5

-

14column,

group_function(column)tablecondition][GROUP

BY

group_by_expression][ORDER

BY

column];department_id,SELECT

AVG(salary)FROMemployeesGROUP

BY

department_id

;Using

the

GROUP

BY

ClauseGROUP

BY例子All

the

columns

in

the

SELECT

list

that

are

not

ingroupfunctions

must

be

in

the

GROUP

BY

clause.组列出现在select列表必须用group

by5

-

15Using

the

GROUP

BY

ClauseGROUP

BY例子The

GROUP

BY

column

does

not

have

to

be

in

the

SELECT

list.分组列可以不出现在select列表中SELECT

AVG(salary)FROM

employeesGROUP

BY

department_id;5

-

16GrouEMPLOYEESby

More

Than

One

Column多列分组Add

the

salaries

in

the

EMPLOYEEStable

for

each

job,

grouped

bydepartment.…5

-

17SELECTFROMWHEREdepartment_id,

job_id,

SUM(salary)employeesdepartment_id

>

40GROUP

BY

department_id,

job_idORDER

BY

department_id;Using

the

GROUP

BY

Clause

on

Multiple

Columns多列分组例子5

-

18Illegal

Queries

Using

Group

Functions容易犯错的地方elect列表出现的组函数列或表达式必须用group

byAny

column

or

expression

in

the

SELECT

list

that

is

not

anaggregate

function

must

be

in

the

GROUP

BY

clause:SSELECT

department_id,

COUNT(last_name)FROM

employees;A

GROUP

BY

clause

must

be

added

tocount

the

last

names

for

eachdepartment_id.SELECT

department_id,

job_id,

COUNT(last_name)FROM

employeesGROUP

BY

department_id;Either

addjob_id

in

the

GROUP

BY

orremove

the

job_id

column

from

theSELECT

list.5

-

19Illegal

Queries

Using

Group

Functions容易犯的错误You

cannot

use

the

WHERE

clause

to

restrict

groups.You

use

the

HAVING

clause

to

restrict

groups.限制分组函数结果不用where，而用havingYou

cannot

use

group

functions

in

the

WHERE

clause.where子句中不能使用分组函数SELECTFROMWHEREdepartment_id,

AVG(salary)employeesAVG(salary)

>

8000GROUP

BY

department_id;Cannot

use

theWHERE

clause

torestrict

groups5

-

20Restricting

Group

Results限制分组结果EMPLOYEES…The um

salary

perdepartment

when

it

isgreater

than

$10,0005

-

21SELECTFROM[WHEREcolumn,

group_functiontablecondition][GROUP

BY

group_by_expression][HAVING

group

condition][ORDER

BY

column];Restricting

Group

Results

with

the

HAVING

Clausehaving语法When

you

use

the

HAVING

clause,

the

Oracle

serverrestrictsgroups

as

follows:使用HAVING限制分组Rows

are

grouped.行被分组The

group

function

is

applied.分组函数已使用Groups

matching

the

HAVING

clause

are

displayed.匹配having的结果被显示5

-

22SELECTFROMdepartment_id,

MAX(salary)employeesGROUP

BY

department_idHAVING

MAX(salary)>10000

;Using

the

HAVING

Clausehaving例子5

-

23SELECTFROMWHEREjob_id,

SUM(salary)

PAYROLLemployeesjob_id

NOT

LIKE

'%REP%'GROUP

BY

job_idHAVING SUM(salary)

>

13000ORDER

BY

SUM(salary);Using

the

HAVING

Clausehaving例子5

-

24Lesson

Agenda课程进度Group

functions:Types

and

syntaxUse

AVG,

SUM,

MIN,

MAX,

COUNTUse

DISTINCT

keyword

within

group

functionsNULL

values

in

a

group

functionGrou

rows:GROUP

BY

clauseHAVING

clauseNesting

group

functions分组函数嵌套5

-

25MAX(AVG(salary))SELECTFROM

employeesGROUP

BY

department_id;Nesting

Group

Functions嵌套Display

theum

average

salary:显示最大的平均薪水5

-

265

-

27Quiz课堂测试Identify

the

guidelines

for

group

functions

and

the

GROUP

BYclause.You

cannot

use

a

column

alias

in

the

GROUP

BY

clause.The

GROUP

BY

column

must

be

in

the

SELECT

clause.By

using

a

WHERE

clause,

you

can

exclude

rows

beforedividing

them

into

groups.The

GROUP

BY

clause

groups

rows

and

ensures

order

ofthe

result

set.If

you

include

a

group

function

in

a

SELECT

clause,

youcannot

select

individual

results

as

well.SELECTFROM[WHEREcolumn,

group_functiontablecondition][GROUP

BY

group_by_expression][HAVING

group_condition][ORDER

BY

column];Summary小结In

this

lesson,

you

should

have

learned

howto:Use

the

group

functions

COUNT,

MAX,

MIN,

SUM,

and

AVGWrite

queries

that

use

the

GROUP

BY

clauseWrite

queries

thatuse

the

HAVING

clause5

-

28Practice

5:

OverviewThis

practice

covers

the

following

topics:Writing

queries

that

use

the

group

functionsGrou by

rows

to

achievemore

than

one

resultRestricting

groups

by

using

theHAVING

clause5

-

29Find

the

highest,

lowest,

sum,

and

average

salary

of

allemployees.

Label

the

columns um,

Minimum,

Sum,

andAverage,

respectively.

Round

your

results

to

the

nearest

wholenumber.

Save

your

SQL

statement

as

lab_05_01.sql.

Run

thequery.Modify

the

query

in

lab_05_01.sql

to

display

the

minimum,um,

sum,and

average

salary

for

each

job

type.

Savelab_05_02.sql

as

lab_05_02.sql

again.

Run

the

statement

inlab_05_02.sql.Write

a

query

to

display

the

number

of

people

withthe

same

job.Generalizethe

query

sothat

the

user

inthe

HR

department

isprompted

for

a

job

title.Save

the

script

to

a

file

named

lab_05_03.sql.Run

the

query.

Enter

IT_PROG

when

prompted

and

click

OK.5

-

305

-

31Determine

the

number

of

managers

without

listing

them.

Label

thecolumn

Number

ofManagers.Find

the

difference

between

the

highest

and

lowestsalaries.

Labelthe

column

DIFFERENCECreate

a

report

to

display

the

manager

number

and

the

salary

ofthe

lowest-paid

employee

for

that

manager.

Exclude

anyone

whosemanager

is

not

known.

Exclude

any

groups

where

the

minimumsalary

is

$6,000

or

less.

Sort

the

output

in

descending

order

of

salaryCreate

a

query

that

will

display

the

total

number

of

employees

and,of

that

total,

the

number

of

employees

hired

in

2001,

2002,

2003,and

2004.

Create

appropriate

column

headingsCreate

a

matrix

query

to

display

the

job,

the

salary

for

that

jobbased

on

the

department

number,

and

the

total

salary

for

that

job,

fordepartments

20,

50,

80,

and

90,

giving

each

column

an

appropriateheading5

-

325

-

331.

Find

the

highest,

lowest,

sum,

and

average

salary

of

allemployees.

Label

the

columns um,

Minimum,

Sum,

andAverage,

respectively.

Round

your

results

to

the

nearest

wholenumber.

Save

your

SQL

statement

as

lab_05_04.sql.

Run

thequery.um",SQL>SELECT

ROUND(MAX(salary),0)

"ROUND(MIN(salary),0)

"Minimum",ROUND(SUM(salary),0)

"Sum",ROUND(AVG(salary),0)

"Average"FROMemployees;5

-

342.

Modify

the

query

in

lab_05_04.sql

to

display

the

minimum,um,

sum,and

average

salary

for

each

job

type.Savelab_05_04.sql

as

lab_05_05.sql

again.

Run

the

statement

inlab_05_05.sql.SQL>SELECT

job_id,ROUND(MAX(salary),0)

"

um",ROUND(MIN(salary),0)

"Minimum",ROUND(SUM(salary),0)

"Sum",ROUND(AVG(salary),0)

"Average"FROMemployeesGROUP

BY

job_id;5

-

355

-

363.Write

a

query

to

display

the

number

of

people

with

the

same

job.Generalize

the

query

so

that

the

user

in

the

HR

department

isprompted

for

a

job

title.Save

the

script

to

a

file

named

lab_05_06.sql.Run

the

query.

Enter

IT_PROG

when

prompted

and

click

OK.SQL>SELECT

job_id,

COUNT(*)FROM

employeesGROUP

BY

job_id;SQL>SELECT

job_id,

COUNT(*)FROM

employeesWHERE

job_id

=

'&job_title'GROUP

BY

job_id;5

-

374.

Determine

the

number

of

managers

withoutlisting

them.

Label

thecolumn

Number

ofManagers.SQL>SELECT

COUNT(DISTINCT

manager_id)

"Number

of

Managers"FROM

employees;5

-

385.

Find

the

difference

between

the

highest

and

lowest

salaries.

Labelthe

column

DIFFERENCESQL>SELECT

MAX(salary)

-

MIN(salary)

DIFFERENCEFROMemployees;5

-

396.

Create

a

report

to

display

the

manager

number

and

the

salary

ofthe

lowest-paid

employee

for

that

manager.

Exclude

anyone

whosemanager

is

not

known.

Exclude

any

groups

where

the

minimumsalary

is

$6,000

or

less.

Sor