공부 기록

▶ 그룹 함수와 HAVING절

SELECT [DISTINCT/ALL] 컬럼 or 그룹함수, ...
FROM 테이블
WHERE 조건
GROUP BY Group대상
HAVING <그룹 함수 포함 조건>
ORDER BY 정렬대상 [ASC/DESC];

조건인데 그룹 함수가 포함되어있으면 having절에 기술한다.

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

SELECT dno 부서번호, ROUND(AVG(sal)) 평균급여
FROM emp
GROUP BY dno
HAVING AVG(sal) < 3000;

부서   평균급여
---- ----------
02         2320
40         2100

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

SELECT dno 부서번호, COUNT(*) 인원수
FROM emp
GROUP BY dno
HAVING job != '개발';
HAVING job != '개발'
              *
4행에 오류:
ORA-00979: GROUP BY 표현식이 아닙니다.


SELECT dno 부서번호, COUNT(*) 인원수
FROM emp
GROUP BY dno
HAVING dno != '10'; //일반칼럼 조건인데 having절에 쓰면 안된다. 실행은 되지만 쓰지말자!

부서번호      인원수
--------- ----------
20                 4
01                 3
30                 3
02                 5
40                 1

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

SELECT dno, AVG(sal)
FROM emp
GROUP BY dno
HAVING AVG(sal) = (SELECT MAX(AVG(sal))
FROM emp
GROUP BY dno);

DNO       AVG(SAL)
------- ----------
01            4070

실습

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

SELECT major, TO_CHAR(AVG(avr), 90.99) 평점평균
FROM student
GROUP BY major
HAVING major!='화학';

MAJOR   평점평균
------- ------------
유공      2.95
생물      2.45
식영      3.25
물리      2.42

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

SELECT major, TO_CHAR(AVG(avr), 90.99) 평점평균
FROM student
GROUP BY major
HAVING major!='화학' AND AVG(avr)>2.0;

MAJOR   평점평균
------- ------------
유공      2.95
생물      2.45
식영      3.25
물리      2.42

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

SELECT s.sno 학번, TO_CHAR(AVG(result), 90.99) 기말고사평균
FROM student s, score r
WHERE s.sno=r.sno
GROUP BY s.sno
HAVING AVG(result)>=60;

학번    기말고사평균
------- ------------
948204   72.19
905603   68.56
913904   65.81
913908   77.23
915305   69.47
918202   63.76
924501   68.32

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

SELECT p.pno 교수번호, pname 이름, SUM(st_num) 학점수
FROM professor p, course c
WHERE p.pno=c.pno
GROUP BY p.pno, pname, st_num
HAVING SUM(st_num)>=3;

교수번호  이름           학점수
--------- ---------- ----------
1029      주동평              3
1013      하영진              3
1018      김응전              3
1035      장관용              3
1010      이규진              3
1017      최무송              6
1004      시진영              6
1006      장청아              3
1022      이준                6
1027      임충원              3
1008      문규식              3
1036      성현수              3

5. 기말고사 성적이 핵 화학과목보다 우수한 과목의 과목명과 담당 교수명 검색한다.

SELECT c.cno 과목번호, cname 과목이름, p.pno 교수번호, pname 교수이름
FROM professor p, course c, score r
WHERE p.pno = c.pno
AND c.cno = r.cno
AND result > ALL(SELECT result
FROM score r, course c
WHERE r.cno=c.cno
AND cname='핵화학')
GROUP BY c.cno, cname, p.pno, pname;

과목번호  과목이름     교수번호  교수이름
--------- ------------ --------- ----------
1712      분자생물학   1030      김동평
1226      양자물리학   1016      호연작

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

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

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

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

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