[SQL] 함수(NVL, 그룹함수, 조건함수)

BORAM KIM·2022년 8월 15일

DB

목록 보기

4/6

NVL(), NVL2()

null을 0또는 다른 값으로 변환하기 위해 사용하는 함수
** 오라클에만 있다.

SELECT ENAME, SAL, COMM, SAL+COMM
  FROM EMP;
-- * COMM에 있는 null때문에 산술연산 불가
-- 1. NVL(컬럼, 보여줄값) : 컬럼내의 null이 있다면 보여줄 값으로 변환하여 출력,
--                         null이 아니라면 컬럼 내용 출력
-- 2. NVL2(컬럼, 보여줄값1, 보여줄값2) : 컬럼내의 null이 있다면 보여줄값 2으로 변환하여 출력,
--                                     null이라면 보여줄값1로 출력
SELECT ENAME, SAL, COMM
    ,  SAL+COMM
    ,  SAL+NVL(COMM,0) AS "NVL()"
    ,  NVL2(COMM,SAL+COMM,SAL) AS "NVL2()"
  FROM EMP;

조건 함수

DECODE(조건식, 1번결과, 1번출력, 2번결과, 2번출력, 3번결과, 3번출력...,[기본값])

SELECT * FROM JUMIN;
SELECT JUMIN
    ,  SUBSTR(JUMIN,8,1) AS GENDER
    ,  DECODE(SUBSTR(JUMIN,8,1),'1','남자','3','남자','여자') AS GENDER2
  FROM JUMIN;

CASE WHEN THEN END

CASE 조건식 WHEN 조건에대한결과 THEN 결과에대한출력1
WHEN 조건에대한결과2 THEN 결과에대한출력2
ELSE 그외 END AS 별칭

SELECT JUMIN
    ,  CASE SUBSTR(JUMIN,8,1) WHEN '1' THEN '남자'
                              WHEN '3' THEN '남자'
                              ELSE '여자'
                              END AS GENDER
  FROM JUMIN;


- 부서번호가 10이면영업부, 20이면 관리부, 30이면 IT부 그 외는 기술부로 출력
SELECT ENAME, DEPTNO
    ,  CASE DEPTNO WHEN 10 THEN '영업부'
                   WHEN 20 THEN '관리부'
                   WHEN 30 THEN 'IT부'
                   ELSE '기술부'
                   END AS 부서
  FROM EMP;
  
SELECT ENAME
    ,  DEPTNO
    ,  DECODE(DEPTNO,10,'영업부',20,'관리부',30,'IT부','기술부') AS 부서
  FROM EMP;


- 업무(job)이 analyst이면 급여 증가가 10%이고 clerk이면 15%, manager이면 20%인 경우
  사원번호, 사원명, 업무, 급여, 증가한급여를 출력
SELECT * FROM EMP;
SELECT EMPNO, ENAME, JOB, SAL
    ,  CASE JOB WHEN 'ANALYST' THEN SAL*1.1
                WHEN 'CLERK' THEN SAL*1.15
                WHEN 'MANAGER' THEN SAL*1.2
                ELSE SAL
                END AS 증가급여
  FROM EMP;
 
SELECT EMPNO, ENAME, JOB, SAL
    ,  DECODE(JOB,'ANALYST',SAL*1.1,'CLERK',SAL*1.15,'MANAGER',SAL*1.2,SAL) AS 증가급여
  FROM EMP;

예제

0.이름, 업무, 급여(SAL), 보너스(COMM), 급여+보너스를 출력
SELECT ENAME, JOB, SAL, COMM, SAL+NVL(COMM,0)
  FROM EMP; 

1.현재 급여에 15% 증가된 급여를 사원번호, 이름, 업무, 급여,
  증가된 급여(New Salary), 증가액(Increase)를 출력
SELECT EMPNO, ENAME, JOB, SAL, SAL*1.5 AS NEW_SAL, SAL*0.5 AS INCREASE
  FROM EMP;

2.이름, 입사일, 입사일로부터 6개월 후에 돌아오는 월요일을 출력
SELECT ENAME, HIREDATE, NEXT_DAY(ADD_MONTHS(HIREDATE,6),'월')
  FROM EMP;

3.이름, 입사일, 입사일로부터 현재까지의 월수, 급여, 입사일로부터 현재까지의 급여의 총계를 출력
SELECT ENAME, HIREDATE, TRUNC(SYSDATE-HIREDATE), SAL, SAL*TRUNC(SYSDATE-HIREDATE) AS ALL_SAL
  FROM EMP;

4.이름, 업무, 입사일, 입사한 요일을 출력
SELECT ENAME, JOB, HIREDATE, TO_CHAR(HIREDATE,'DAY')
  FROM EMP;

5. 모든 사원의 이름과 급여를 출력
 ( 급여는 15자리로 좌측의 빈곳에 '*'로 대치 );
SELECT ENAME, LPAD(SAL,15,'*')
  FROM EMP;

6. 다음의 결과처럼 출력 
SELECT ENAME||' earns'||TO_CHAR(SAL,'$999,999.99')||
' monthly but wants'||TO_CHAR(SAL*3,'$999,999.99') AS SAL
  FROM EMP;

그룹함수

하나이상의 행을 "그룹으로 묶어서" 연산 후 "하나의 결과"로 나타내는 함수

SUM AVG MIN MAX COUNT

부서별 월급의 합 / 최대급여를 받는 사원의 정보

SELECT ENAME FROM EMP; -- 단순컬럼
SELECT MAX(SAL) FROM EMP; -- 14개의 행중에서 가장 큰 것 "하나"를 표현
SELECT ENAME, MAX(SAL) FROM EMP; -- ENAME: 14행 / MAX(SAL): 1행

표현하고자 하는 컬럼들의 행의 수가 달라서 문제 발생
14개의 행을 표현하는 ENAME 단순컬럼도 그룹화를 하자!
GROUP BY : 컬럼들을 그룹화 시켜주는 키워드

SELECT DEPTNO FROM EMP
GROUP BY DEPTNO; -- 특정 컬럼값을 기준으로 그룹화

SELECT JOB FROM EMP
GROUP BY JOB;
-- GROUP BY + 그룹함수를 값이 사용 : 그룹화 된 컬럼으로 그룹함수 진행

SELECT DEPTNO, TRUNC(AVG(SAL)) AS AVG_SAL
  FROM EMP
GROUP BY DEPTNO;
-- 그룹함수를 사용할때는 그룹함스를 적용하지 않은 단순 컬럼은 사용불가능
-- GROUP BY 사용하면 단순컬럼이 그룹화되기때문에 그룹함수 사용가능!
-- * 그룹함수와 단순컬럼을 같이 쓰려면 GROUP BY 필수!

SELECT DEPTNO, SUM(SAL), TRUNC(AVG(SAL)), COUNT(SAL) FROM EMP GROUP BY DEPTNO;

SELECT DEPTNO, SAL
  FROM EMP
GROUP BY DEPTNO; -- GROUP BY 표현식이 아니다
-- GROUP BY 특정컬럼을 기준으로 그룹화 해주는 명령어
-- GROUP BY 뒤에 작성되지 않은 컬럼을 SELECT문에 작성하지 못한다
SELECT DEPTNO, JOB, COUNT(*)
  FROM EMP
GROUP BY DEPTNO,JOB;

SELECT JOB,SUM(SAL),TRUNC(AVG(SAL),-1), MAX(SAL), MIN(SAL)
  FROM EMP
 WHERE JOB = 'SALESMAN'
 GROUP BY JOB;
-- WHERE + GROUP BY
-- WHERE 에 의해서 나온 결과를 대상으로 그룹화

-- HAVING : GROUP BY 절을 통해 생성된 결과에서 "조건"에 부합하는 결과 출력
SELECT JOB,SUM(SAL),TRUNC(AVG(SAL),-1), MAX(SAL), MIN(SAL)
  FROM EMP
GROUP BY JOB
HAVING AVG(SAL) >=2000;

WHERE

조건을 사용하여 결과를 제한
단순 컬럼
테이블에서 특정 조건에 부합된 자료만 검색하는데 사용

HAVING

그룹의 결과를 제한
그룹함수
그룹함수를 적용해서 나온 결과 중 조건에 부합되는 자료만 산출할때 사용

작성 순서

SELECT > WHERE > GROUP BY > HAVING > ORDER BY

예제

-- 부서의 최대값, 최소값 출력 + 최대급여가 2900 이상인 부서만 출력
SELECT DEPTNO, MAX(SAL), MIN(SAL)
  FROM EMP
GROUP BY DEPTNO
HAVING  MAX(SAL)>= 2900
ORDER BY MAX(SAL);

-- 부서별로 인원수, 평균급여, 최저급여, 최고급여, 급여의 합을 구하기
-- 부서별로 인원수, 평균급여, 최저급여, 최고급여, 급여의 합을 구하기 ( 부서별 급여의 합이 높은 순으로           
SELECT DEPTNO, COUNT(*), MAX(SAL), MIN(SAL), SUM(SAL)
  FROM EMP
GROUP BY DEPTNO
ORDER BY SUM(SAL) DESC;

-- 부서별 업무별 그룹하여 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하기
SELECT DEPTNO,JOB, COUNT(*),AVG(SAL), SUM(SAL)
  FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO;

-- 최대 급여가 2900 이상인 부서에 대해 부서번호, 평균 급여, 급여의 합을 출력
SELECT DEPTNO, AVG(SAL), SUM(SAL)
  FROM EMP
GROUP BY DEPTNO
HAVING  MAX(SAL)>= 2900;

-- 업무별 급여의 평균이 3000이상인  업무에 대해 업무명, 평균 급여, 급여의 합을 출력
SELECT JOB, AVG(SAL), SUM(SAL)
  FROM EMP
GROUP BY JOB
HAVING  AVG(SAL)>= 3000;

-- 전체 합계 급여가 5000를 초과하는 각 업무에 대해서 업무와 급여 합계를 출력
-- 단, SALESMAN은 제외하고 급여 합계가 높은 순으로 정렬
SELECT JOB,SUM(SAL)
  FROM EMP
 WHERE NOT JOB = 'SALESMAN'
GROUP BY JOB
HAVING  SUM(SAL)> 5000
ORDER BY SUM(SAL)DESC;

--  업무별 최고 급여와 최소 급여의 차이를 구하라
SELECT JOB, MAX(SAL)-MIN(SAL)
  FROM EMP
GROUP BY JOB;

-- 부서 인원이 4명 보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력
SELECT DEPTNO, COUNT(*), SUM(SAL)
  FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>4;