오라클(Oracle) - (DQL) : 함수(다중행 함수)

• 오라클에서는 (1)내장함수와 사용자가 필요에 의해서 직접 정의한 (2)사용자 정의 함수로 나누어짐.
• 단일행 함수와 다중행 함수(집계 함수)로 나누어짐.

SELECT ENAME, SUM(SAL)
    FROM EMP;

⚠️ 해당 커맨드는 오류가 난다. 왜?

📌 SUM(SAL)은 결과가 하나만 나오는데(SAL 항목에 대한 합계이므로) ENAME은 각 행이 다 나오기 때문 

      이것을 해결하기 위해선 GROUP BY를 이용해 그룹 단위로 묶어야 함!

SELECT DEPTNO, SUM(SAL)
    FROM EMP
    GROUP BY DEPTNO; -- 급여에 대한 합계를 부서별로 묶어줌.

🐸 응용 예제. 직업별로 급여 합계, 급여 평균, 해당 직업군의 직원 숫자를 출력

SELECT JOB, SUM(SAL), TRUNC(AVG(SAL)), COUNT(*)
    FROM EMP
    GROUP BY JOB
    ORDER BY AVG(SAL) DESC;

SUM 함수 (합계)

• DISTINCT : 중복 제거
• ALL : 사용하지 않아도 기본적으로 ALL 특성을 가집니다.

SELECT SUM(DISTINCT SAL) AS 중복제거, -- SAL 항목 중 중복인게 있으면 제거된 후 더해짐.
    SUM(ALL SAL) AS 전부,
    SUM(SAL) AS 기본
    FROM EMP;

기타 구문

SELECT COUNT(*) FROM EMP; -- 전체 몇 명인지( == 행의 수)

SELECT COUNT(COMM) FROM EMP; -- COMM에서 NULL이 아닌 사원의 수

🐸 부서 번호가 20인 사원 중 입사일이 가장 최근인 사원 출력

SELECT MAX(HIREDATE)
    FROM EMP
    WHERE DEPTNO = 20;

🐸 절 작성 순서

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

GROUP BY

• 하나의 결과를 특정 열을 묶어서 출력하는 것을 그룹화 한다고 합니다.
• 이 때, 출력해야 할 대상 열 지정을 GROUP BY로 수행 합니다.

SELECT TRUNC(AVG(SAL)) 평균급여, DEPTNO
    FROM EMP
    GROUP BY DEPTNO;

🐸 부서 번호 및 직책별 평균 급여로 정렬하기

SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
    GROUP BY DEPTNO, JOB
    ORDER BY DEPTNO, JOB;

🐸 GROUP BY 절을 사용하여 부서 번호별 평균 추가 수당 출력

SELECT DEPTNO, NVL(AVG(COMM),0) AS 부서별평균수당
    FROM EMP
    GROUP BY DEPTNO;

🐸 부서 코드, 급여 합계, 부서 평균, 부서 코드 순 정렬

SELECT DEPTNO, SUM(SAL),
    TRUNC(AVG(SAL)) AS 평균급여,
    COUNT(*) 인원수
    FROM EMP
    GROUP BY DEPTNO
    ORDER BY DEPTNO;

HAVING 절

• SELECT문에 GROUP BY가 존재할 때만 사용 가능
• 그룹화된 값의 범위를 제한하는 용도로 사용
• WHERE절을 못 쓰는 이유는 GROUP BY와 WHERE가 수행되는 시점이 다르기 때문!

SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
    GROUP BY DEPTNO, JOB
        HAVING AVG(SAL) >= 2000
    ORDER BY DEPTNO, JOB;

🐸 WHERE 절과 HAVING 절을 모두 사용한 경우

SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
    WHERE SAL <= 2500
    GROUP BY DEPTNO, JOB
        HAVING AVG(SAL) >= 2000
    ORDER BY DEPTNO, JOB;

---

🐸연습문제🐶

1. HAVING절을 사용하여 EMP 테이블의 부서별 직책의 평균 급여가 500 이상인 사원들의 부서 번호, 직책, 부서별 직책의 평균 급여가 출력

SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
    GROUP BY DEPTNO, JOB
        HAVING AVG(SAL) >= 500
    ORDER BY DEPTNO, JOB;

2. EMP 테이블을 이용하여 부서번호, 평균급여, 최고급여, 최저급여, 사원수를 출력, 단, 평균 급여를 출력 할 때는 소수점 제외하고 부서 번호별로 출력

SELECT DEPTNO,
    TRUNC(AVG(SAL)) AS "평균 급여",
    MAX(SAL) AS "최고 급여",
    MIN(SAL) AS "최저 급여",
    COUNT(*) AS "사원 수"
    FROM EMP
    GROUP BY DEPTNO
    ORDER BY DEPTNO DESC;

3. 같은 직책에 종사하는 사원이 3명 이상인 직책과 인원을 출력

SELECT JOB, COUNT(JOB) AS 사원수
    FROM EMP
    GROUP BY JOB
        HAVING COUNT(JOB) >= 3;

4. 사원들의 입사 연도를 기준으로 부서별로 몇 명이 입사했는지 출력

SELECT EXTRACT(YEAR FROM HIREDATE) AS 입사일,
    DEPTNO,
    COUNT(*) 사원수
    FROM EMP
    GROUP BY EXTRACT(YEAR FROM HIREDATE), DEPTNO
    ORDER BY 사원수;

5. 추가 수당을 받는 사원 수와 받지 않는 사원수를 출력 (O, X로 표기 필요)

SELECT NVL2(COMM,'O','X') AS 추가수당,
    COUNT(NVL2(COMM,'O','X')) AS 사원수
    FROM EMP
    GROUP BY NVL2(COMM,'O','X');

6. 각 부서의 입사 연도별 사원 수, 최고 급여, 급여 합, 평균 급여를 출력

SELECT DEPTNO,
    EXTRACT(YEAR FROM HIREDATE) AS 입사년도,
    COUNT(*) 사원수,
    MAX(SAL) 최고급여,
    TRUNC(AVG(SAL)) 평균급여,
    SUM(SAL) 합계
    FROM EMP
    GROUP BY EXTRACT(YEAR FROM HIREDATE),DEPTNO
    ORDER BY DEPTNO;