19.09.25. (NVL/NVL2/NLUUIF/CASE/DECODE/그룹함수/GROUP BY/HAVING)

(1)

-NVL (NULL 값 부분에 문자 입력 가능)

SELECT ENAME, NVL(MGR,1111) FROM EMP;

SELECT ENAME, NVL(COMM,0) FROM EMP;

​

-NVL2 (COMM 확인 후 NULL이 아니면=값이 있으면 2번째, NULL이면=값이 없으면 3번째만 출력)

(COMM 확인 후 NULL이 아니면 급여와 커미션을 더한 값, NULL이면 급여만 출력)

SELECT ENAME, NVL2(COMM,SAL+COMM, SAL) AS RESULT FROM EMP;

​

문1 학생들의 키가 측정되어 있지 않을 경우, '재검'이라고 출력하고, 키 데이터가 있으면 '필요없음'이라고 검사사항을 출력하시오.

출력항목은 학생이름, 키, 검사사항 입니다.

​

SELECT STU_NAME, STU_HEIGHT, NVL2(STU_HEIGHT, '필요없음', '재검') AS 검사사항 FROM STUDENT;

​

**문2 모든 사원의 커미션을 200씩 추가하여 출력하시오. NVL, NVL2 모두 이용할 것.

​

SELECT ENAME, NVL(COMM,0) + 200 FROM EMP;

=SELECT ENAME, NVL2(COMM,COMM+200,200) AS COMM FROM EMP;

​

문3 MGR이 없는 사원의 경우 '사장님'으로 나타나도록 출력하시오. 단, 사원 이름, 상급자로 출력할 것

​

SELECT ENAME AS 사원이름, MGR AS 상급자, NVL(TO_CHAR(MGR), '사장님') FROM EMP;

=SELECT ENAME AS 사원이름, MGR AS 상급자, NVL2(TO_CHAR(MGR),TO_CHAR(MGR), '사장님') FROM EMP;

​

(2) NULLIF(인수1, 인수2) : 값이 같으면 NULL 이 나옴

SELECT NVL(NULLIF('A','A'), '널값') FROM DUAL;

= 널값

SELECT STU_NAME, NVL(NULLIF(TO_CHAR(STU_WEIGHT),'90'), '몸무게 90KG') AS 몸무게 FROM STUDENT;

​

(3) CASE(DECODE와 차이점은 비교연산이 가능하다는 점~)

JOB을 비교해서 SALESMAN일 경우, 점원일경우, 매니저일 경우~ 마지막에 END 꼭 붙이기!!, ( ) 쓰면 오류 발생함)

SELECT EMPNO, ENAME, SAL, CASE JOB

WHEN 'SALESMAN' THEN SAL*1.1

WHEN 'CLERK' THEN SAL*1.15

WHEN 'MANAGER' THEN SAL * 1.2

END AS 급여인상 FROM EMP;

​

문4 학생들 중에 기계과 학생의 등록금 1000만원 전기전자 학생의 등록금 500만원 컴퓨터정보 학생의 등록금 300만원이라고 하자.

학번, 이름, 과, 등록금을 출력하시오. 단, 2013학번만 출력하시오.

​

SELECT STU_NO, STU_NAME, STU_DEPT, CASE STU_DEPT

WHEN '기계' THEN '1000만원'

WHEN '전기전자' THEN '500만원'

WHEN '컴퓨터정보' THEN '300만원'

END AS 등록금 FROM STUDENT WHERE STU_NO LIKE '2013%';

​

(4) DECODE (CASE랑 같지만, 함수형태니깐 ,로 구분)

DECODE(컬럼명,조건1,결과1,조건2,결과2,기본결과)

​

ex) 직무에 해당되는 급여를 구해보자. 직급이 'ANALYST'인 사원은 5%, 'SALESMAN'인 사원은 10%, 'CLERK'인 사원은 15%, 'MANAGER'인 사원은 20%를 인상한다.

​

SELECT ENAME, JOB, SAL,

DECODE (JOB, 'ANALYST',SAL*1.05,'SALESMAN', SAL*1.1, 'CLERK', SAL*1.15, 'MANAGER', SAL*1.2, SAL) AS 급여인상

FROM EMP;

​

문4이랑 같은 문제 BUT, DECODE로 풀기

​

SELECT STU_NO, STU_NAME, STU_DEPT, DECODE (STU_DEPT,

'기계', '1000만원',

'전기전자', '500만원',

'컴퓨터정보', '300만원')

FROM STUDENT WHERE STU_NO LIKE '2013%';

​

**문5 사장님의 급여를 2배 인상하여 출력하시오. 이름, 사원번호, 급여

​

SELECT ENAME, EMPNO, DECODE (MGR, NULL, SAL*2, SAL) AS SAL

FROM EMP;

=====

SELECT ENAME, EMPNO, DECODE (JOB,'PRESIDENT',SAL*2, SAL) AS SAL

FROM EMP;

​

(5)그룹함수 : NULL값을 제외하고 계산, NVL사용 안해도 됨 (그룹함수를 사용할 때 보통 alias를 지정한다.)

​

COUNT : 개수

SELECT COUNT(EMPNO) FROM EMP;

​

직업 종류의 개수를 구하시오. 단, 중복은 제외

​

SELECT COUNT(DISTINCT JOB)

FROM EMP;

(* SELECT DISTINCT COUNT(JOB)은 틀린 것!! DISTINCT 위치 중요!!)

​

SUM : 총합

SELECT SUM(SAL) FROM EMP;

​

AVG : 평균

SELECT AVG(SAL) FROM EMP WHERE JOB='SALESMAN';

​

STDDEV : 표준편차

​

VARIANCE : 분산

​

GROUP BY : ~별 (SELECT에 있는 컬럼으로 묶어주면 보기 편하다, SELECT문에 작성하지 않아도 결과는 나옴)

SELECT JOB, ROUND(AVG(SAL),0) AS AVG FROM EMP GROUP BY JOB ORDER BY AVG DESC;

​

ex)

SELECT ENAME, MAX(SAL)

FROM EMP;

(ORA-00937 : 단일 그룹의 함수가 아닙니다)

이유는 ENAME 값은 14개고, MAX(SAL) 값은 1개밖에 없기 때문에 오류가 발생한다.

해결법 : GROUP BY로 묶어야 됨!!

SELECT ENAME, MAX(SAL)

FROM EMP

GROUP BY ENAME;

​

문6 SALESMAN의 커미션 평균을 출력하시오.

​

SELECT JOB, AVG(COMM)

FROM EMP

WHERE JOB = 'SALESMAN'

GROUP BY JOB;

​

문7 직무별 최대 임금을 출력하시오

​

SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;

​

문8 직무별 평균 급여(SAL+COMM)를 출력하시오

​

(*SAL이랑 합치는 거니까 COMM에 NVL 적용 해줘야 함!)

SELECT JOB, ROUND(AVG(SAL+NVL(COMM,0)))

FROM EMP

GROUP BY JOB;

​

(6) 출력 순서

5 SELECT

1 FROM

2 WHERE

3 GROUP BY

4 HAVING : 그룹함수에 적용한 결과에 다시 조건 부여할 때

6 ORDER BY : 5 select 후에 출력되는 것이기 때문에 alias 사용 가능

​

(7)HAVING 조건(그룹:부서별) vs WHERE 조건(단일행:각각)

​

문9 직무별, 직무별 사원들의 인원수를 출력하시오.

단, 직무인원이 4명이상인 직무만 출력하시오.

​

SELECT JOB, COUNT(*)

FROM EMP

GROUP BY JOB

HAVING COUNT(EMPNO) >= 4;

​

​

문10 사원수가 5명 이상인 부서의 부서번호와 사원수를 출력하시오.

​

SELECT DEPTNO, COUNT(ENAME) AS 사원수

FROM EMP

GROUP BY DEPTNO

HAVING COUNT(EMPNO) >=5;

​

**문11 부서번호, 부서별 평균 임금을 출력하시오.

단, 평균 임금이 2000이하인 부서는 제외 (2000이상인 부서 찾으면 됨!!!)

​

SELECT DEPTNO, AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING AVG(SAL) >= 2000;

​

문12 직무별 사원 평균 급여, 분산, 표준편차를 검색하시오.

​

SELECT JOB, AVG(SAL), VARIANCE(SAL), STDDEV(SAL)

FROM EMP

GROUP BY JOB;

​

​

**문13 직무별, 부서별 사원의 급여 합이 많은 순으로 검색하시오.

​

SELECT JOB, DEPTNO, SUM(SAL+NVL(COMM,0)) AS 급여합

FROM EMP

GROUP BY JOB, DEPTNO

ORDER BY 3 DESC;

​

​

문14 부서별, 직무별 사원들의 급여 편차가 적은 순으로 검색하시오.

​

SELECT DEPTNO, JOB, ROUND(STDDEV(SAL+NVL(COMM,0))) AS 급여편차

FROM EMP

GROUP BY DEPTNO, JOB

ORDER BY 3;