[Oracle] 17. 그룹 함수와 having 정리

그룹 함수와 having절

select [distinct/all] 컬럼 or 그룹함수….

from 테이블

where 조건

group by group 대상

having < 그룹 함수 포함 조건 >

order by 정렬대상 [asc/desc];

예제 1. 부서별 급여 평균이 3천 달러 미만인 부서의 부서번호와 평균 급여를 검색한다.

select dno 부서번호, round(avg(sal)) 평균급여

from emp

group by dno

having avg(sal) < 3000;

예제 2. having절의 다양한 사용법

select dno 부서번호, count(*) 인원수

from emp

group by dno

having job != '개발';

select dno 부서번호, count(*) 인원수

from emp

group by dno

having dno != '10';

예제 3. 부서 중 가장 급여를 많이 받는 부서를 검색한다.

select dno, avg(sal)

from emp

group by dno

having avg(sal) = (select max(avg(sal)) from emp group by dno);

실습

 1. 화학과를 제외하고 학과별로 학생들의 평점 평균을 검색한다.

select major, avg(avr)

from student

where major!='화학'

group by major;

# 평균을 avg로 구함

 2. 화학과를 제외한 각 학과별 평균 평점 중에 평점이 2.0 이상인 정보를 검색한다.

select major, avg(avr)

from student

where major != '화학'

and 2.0 <= any(select avg(avr) from student group by major)

group by major;

# 각 학과 평균 평점을 구하기 위한 서브쿼리와 다중 값에 대응한 any

# 학과로 나누기 위한 group by

 3. 기말고사 평균이 60점 이상인 학생의 정보를 검색한다. (학번과 기말고사 평균)

select t.sno, avg(s.result)

from student t, score s

where t.sno=s.sno

and 60 <= any(select avg(result) from score group by sno)

group by t.sno;

# any 서브쿼리를 이용해 학생들의 점수 평균을 구함

# group by 학번으로 사람을 구분할 수 있으므로 그룹을 묶는다.

 4. 강의 학점수가 3학점 이상인 교수의 정보를 검색한다.(교수번호, 이름과 담당 학점수)

select distinct p.pno, p.pname, c.st_num

from professor p, course c

where p.pno = c.pno

and c.st_num >= 3

# distinct 중복값 제거

# 테이블 연동을 위한 조인

 5. 기말고사 성적이 핵화학과목보다 우수한 과목의 과목명과 담당 교수명 검색한다. -( 평균을 말하는것인가? )

select distinct c.cname, p.pname

from professor p, score s, course c

where p.pno=c.pno

and c.cno = s.cno

group by c.cname, p.pname, s.result

having avg(s.result) >

(select avg(s.result)

from course c, score s

where c.cno = s.cno

and c.cname ='핵화학'

group by c.cname);

# 서브쿼리를 이용해서 핵화학의 평균 점수를 알아낸다.

# having를 이용하여 그룹의 조건을 준다.

 6. 근무 중인 직원이 4명 이상인 부서를 검색한다.

select d.dname, count(*)

from emp e, dept d

where e.dno=d.dno

group by d.dname

having count(*) >= 4;

# count를 이용하여 부서원의 명수를 세고

# having 을 이용하여 그룹에 조건을 단다

 7. 업무별 평균 년봉이 2만불 이상인 업무를 검색한다.

select job, avg(sal*12+nvl(comm,0))

from emp

group by job

having  avg(sal*12+nvl(comm,0)) >= 20000;

# group by 를 이용하여 업무별로 그룹화

# having 절을 이용하여 연봉비교

 8. 각 학과의 학년별 인원중 인원이 5명 이상인 학년을 검색한다.

select major, syear, count(*)

from student

group by major, syear

having count(*) >= 5;

# 전공과 학년을 그룹으로 묶는다

# 총갯수를 having로 비교 한다.

 9. 인원수가 가장 많은 학과를 검색한다.

select major, count(sno)

from student

group by major

having count(sno) =

(select max(count(sno)) from student group by major);

#

 10. 학생 중 기말고사 성적이 가장 낮은 학생의 정보를 검색한다.

select t.sname, avg(s.result)

from student t, score s

where s.sno=t.sno

group by t.sname

having avg(s.result)=

(select min(avg(s.result))

from student t, score s

where s.sno=t.sno

group by t.sname)

# 서브쿼리와 group by를 이용하여 구함