공부 기록

▶ 그룹 함수와 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. 학생 중 기말고사 성적이 가장 낮은 학생의 정보를 검색한다.

▶ 그룹 함수

* 그룹 함수를 사용하는 경우 고려 사항
- NULL값은 무시된다.
- 반드시 단 하나의 값만을 반환한다.
- GROUP BY 설정 없이 일반 컬럼과 기술될 수 없다.

예제 1. 사원의 급여 평균을 검색한다.

SELECT AVG(sal) 평균급여, ROUND(AVG(sal)) 평균급여
FROM emp;

  평균급여   평균급여
---------- ----------
3167.57143       3168

예제 2. 사원들에게 지급된 보너스 총합과 보너스 평균을 검색한다.

SELECT SUM(comm) 총액,
ROUND(AVG(comm)) 평균, COUNT(comm) 수령인원,
ROUND(AVG(NVL(comm,0))) 환산평균, COUNT(*) 전체인원
FROM emp;

      총액       평균   수령인원   환산평균   전체인원
---------- ---------- ---------- ---------- ----------
     14780        869         17        704         21
     
     
SELECT COUNT(*) FROM emp
WHERE comm IS NOT NULL;

  COUNT(*)
----------
        17

null 값은 무시가 되기 때문에 위 방식은 문제가 발생한다. 

예제 3. 10번 부서원들보다 급여가 높은 사원을 검색한다.

SELECT eno 사번, ename 이름, dno 부서번호
FROM emp
WHERE sal > (SELECT MAX(sal) // 추천!!!
FROM emp
WHERE dno = '10');

SELECT eno 사번, ename 이름, dno 부서번호
FROM emp
WHERE sal > ALL(SELECT sal
FROM emp
WHERE dno = '10');

사번    이름       부서번호
------- ---------- ---------
0001    안영희     01

보통은 위의 두 방식 중 첫번째 방식처럼 사용한다. 단일행 서브쿼리로 사용한다.

▶ 그룹 함수와 GROUP BY절

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

- 통계정보를 출력할 경우 같은 정보를 가진 사람끼리 통계정보를 출력하기 위해 GROUP BY 절을 사용한다.

- GROUP BY 절에 있는 컬럼 명을 SELECT절 일반컬럼에 꼭 넣어준다.

- 카디널리티: 값의 종류 (ex. 성별은 카디널리티 낮음, 이름은 카디널리티 높음)

예제 4. 업무별 평균 급여, 평균 연봉과 부서별 평균 연봉을 검색한다.

SELECT job 업무, ROUND(AVG(sal)) 평균_급여,
ROUND(AVG(sal*12+NVL(comm,0))) 평균_연봉
FROM emp
GROUP BY job;

업무     평균_급여  평균_연봉
------- ---------- ----------
분석          2900      35500
지원          3803      47467
개발          2782      34282
모델링        4300      51883
경영          4800      57600
회계          2380      28790

SELECT d.dno 부서번호, dname 부서명,
ROUND(AVG(sal*12+NVL(comm,0))) 평균_연봉
FROM dept d, emp e
WHERE d.dno = e.dno
GROUP BY d.dno, dname
ORDER BY d.dno;

부서번호  부서명   평균_연봉
--------- ------- ----------
01        총무         49833
02        회계         27990
10        ERP          42664
20        ISP          38768
30        ITEA         43323
40        CRM          25200

dno만 그룹바이 해도 되지만 dname를 한 이유는 select절의 일반컬럼에 dno, dname 둘다 있기 때문에 dname도 그룹바이절에 적어준다.

예제 5. 부서별로 급여 평균의 최대 값과 최소 값을 검색한다.

SELECT dno 부서번호,
MAX(AVG(sal)) 최대평균, MIN(AVG(sal)) 최소평균
FROM emp
GROUP BY dno;
SELECT dno 부서번호,
       *
1행에 오류:
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

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

MAX(AVG(SAL)) MIN(AVG(SAL))
------------- -------------
         4070          2100

dno로 그룹바이해도 select 절에 컬럼이 하나씩밖에 안나오는 그룹함수를 적었기 때문에 select 절에 dno를 뺀다.

예제 6. 그룹 대상 컬럼과 그룹 함수를 이용한 검색 결과 확인

SELECT dno 부서번호, COUNT(*) 인원수
FROM emp
WHERE job != '개발'
GROUP BY dno, job;

부서번호      인원수
--------- ----------
10                 1
02                 1
20                 1
20                 1
01                 1
01                 2
02                 4

부서번호      인원수
--------- ----------
10                 1
30                 1
10                 2
20                 1

예제 7. 각 부서별 최소 급여를 받는 사원의 정보를 검색한다.

SELECT d.dno, dname, eno, ename, sal
FROM emp e, dept d
WHERE d.dno=e.dno
AND (d.dno, sal) IN (SELECT dno, MIN(sal)
FROM emp
GROUP BY dno)
ORDER BY d.dno;

DNO     DNAME   ENO     ENAME             SAL
------- ------- ------- ---------- ----------
01      총무    0202    손하늘           3510
02      회계    0309    김선유            900
10      ERP     0269    권나현           2600
20      ISP     2002    제갈민           1520
30      ITEA    2007    이초록           1989
40      CRM     2008    윤고은           2100

실습
1. 각 학과별 학생 수를 검색한다.

SELECT major 학과, COUNT(sno)
FROM student
GROUP BY major;

학과    COUNT(SNO)
------- ----------
화학            17
유공            18
생물            17
식영            19
물리            16

2. 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색한다.

SELECT major 학과, TO_CHAR(AVG(avr*4.5/4), '90.99') "4.5 환산평균"
FROM student
WHERE major IN ('화학', '생물')
GROUP BY major;

학과    4.5 환산평균
------- ------------
화학      2.45
생물      2.76

3. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색한다. ♠♠♠

SELECT orders 직위, COUNT(orders) "부임일이 10년 이상 교수 수"
FROM professor
WHERE (TO_CHAR(sysdate, 'YYYY')-TO_CHAR(hiredate, 'YYYY')+1)>=10
GROUP BY orders;

직위    부임일이 10년 이상 교수 수
------- --------------------------
조교수                           7
정교수                           8
부교수                           9

4. 과목명에 화학이 포함된 과목의 학점수 총합을 검색한다.

SELECT cname 과목이름, SUM(st_num) 학점수총합
FROM course
WHERE cname LIKE '%화학%'
GROUP BY cname;

과목이름     학점수총합
------------ ----------
무기화학              2
환경화학              2
일반화학실험          2
일반화학              3
핵화학                3
식품화학              3
유기화학              3

5. 화학과 학생들의 기말고사 성적을 성적순으로 검색한다. //같은 사람이 여러번 나오는데... 어떻게 하징... 평균내야하나...

SELECT major 학과, s.sno 학번, sname 이름, TO_CHAR(AVG(result), 90.99) 기말고사평균성적
FROM student s, score r, course c
WHERE major = '화학'
AND s.sno=r.sno AND r.cno=c.cno
GROUP BY major, s.sno, sname
ORDER BY TO_CHAR(AVG(result), 90.99) DESC;

학과    학번    이름       기말고사평균
------- ------- ---------- ------------
화학    915301  정동상      73.30
화학    925306  김재백      73.15
화학    905302  정욱상      72.85
화학    925305  황현정      72.63
화학    945303  남궁경아    70.86
화학    945314  이철윤      70.59
화학    915304  권보수      69.50

6. 학과별 기말고사 평균을 성적순으로 검색한다.

SELECT major 학과, TO_CHAR(AVG(result), 90.99) 기말고사평균
FROM student s, score r, course c
WHERE s.sno=r.sno AND r.cno=c.cno
GROUP BY major
ORDER BY TO_CHAR(AVG(result), 90.99) DESC;

학과    기말고사평균
------- ------------
식영     69.99
유공     69.56
화학     69.44
물리     68.92
생물     67.93

7. 30번 부서의 업무별 연봉의 평균을 검색한다.
(단 출력 양식은 소수이하 두 자리까지 통일된 형식으로 출력한다.)

SELECT dno 부서번호, job 업무,  TO_CHAR(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM emp
WHERE dno = 30
GROUP BY dno, job;

부서번호  업무    평균연봉
--------- ------- -----------
30        모델링  51600
30        개발    39184

8. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색한다.

SELECT major 학과, syear 학년, TO_CHAR(MAX(avr), 90.99) 성적
FROM student
WHERE major = '물리'
GROUP BY major, syear
ORDER BY syear;

학과          학년 성적
------- ---------- ------------
물리             1   3.20
물리             2   3.22
물리             3   4.00
물리             4   3.33

9. 학년별로 환산 평점의 평균값을 검색한다.

(단 출력 양식은 소수이하 두 자리까지 통일된 양식으로 출력한다.)

SELECT syear 학년, TO_CHAR(AVG(avr*4.5/4), 90.99) 성적
FROM student
GROUP BY syear
ORDER BY syear;

      학년 성적
---------- ------------
         1   3.13
         2   2.97
         3   3.08
         4   2.85

10. 화학과 1학년 학생 중 평점이 평균 이하인 학생을 검색한다.

SELECT major 학과, syear 학년, sno 학번, sname 이름, TO_CHAR(avr, 90.99) 성적
FROM student
WHERE major='화학' AND syear=1
AND avr <= (SELECT AVG(avr) FROM student
WHERE major='화학' AND syear=1);

학과          학년 학번    이름       성적
------- ---------- ------- ---------- ------------
화학             1 945303  남궁경아     2.36
화학             1 945314  이철윤       2.22