014 dev log

집합 연산자 (SET OPERATOR)

여러개의 쿼리문을 하나의 쿼리문으로 만드는 연산자이다

두 쿼리문의 결과를 합하여 한 개의 테이블로 나타낸다

즉, 두 쿼리문의 SELECT절 부분은 동일 해야한다 (조회할 컬럼이 동일)

집합별 예제 코드

UNION(합집합)

--합집합 적용 전
--부서코드가 D5인 사원 조회
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE='D5'; --6명
--급여가 300만원 초과인 사원 조회
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE SALARY>3000000; --8명
--중복 사원 2명

--합집합 적용 후
--부서코드가 D5 이거나 급여가 300만원 초과인 사원들 조회(사번,사원명,부서코드,급여) -> OR
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE='D5'
UNION
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE SALARY>3000000;

--하지만 OR연산자로 하는게 더 간단
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE='D5' OR SALARY>3000000;

UNION ALL(합집합 + 교집합)

SELECT EMP_ID,EMP_NAME,DEPT_CODE,JOB_CODE
FROM EMPLOYEE
WHERE JOB_CODE='J6'
UNION ALL
SELECT EMP_ID,EMP_NAME,DEPT_CODE,JOB_CODE
FROM EMPLOYEE
WHERE DEPT_CODE='D1'; --9명 / UNION때는 7명

INTERSECT(교집합)

--직급코드가 J6이고 부서코드가 D1인 사원들을 조회(사번,사원명,부서코드,직급코드)
SELECT EMP_ID,EMP_NAME,DEPT_CODE,JOB_CODE
FROM EMPLOYEE
WHERE JOB_CODE='J6'
INTERSECT
SELECT EMP_ID,EMP_NAME,DEPT_CODE,JOB_CODE
FROM EMPLOYEE
WHERE DEPT_CODE='D1'; --중복자는 2명
--AND 연산자로 가능
SELECT EMP_ID,EMP_NAME,DEPT_CODE,JOB_CODE
FROM EMPLOYEE
WHERE JOB_CODE='J6' AND DEPT_CODE='D1';

MINUS(차집합)

--직급코드가 J6이고 부서코드가 D1인 사원들을 조회(사번,사원명,부서코드,직급코드)
SELECT EMP_ID,EMP_NAME,DEPT_CODE,JOB_CODE
FROM EMPLOYEE
WHERE JOB_CODE='J6'
MINUS
SELECT EMP_ID,EMP_NAME,DEPT_CODE,JOB_CODE
FROM EMPLOYEE
WHERE DEPT_CODE='D1'; --4명, 부서코드 J6 인원 6명 중 중복2명 제거한 값

GROUP BY

그룹을 묶어줄 기준을 제시하는 구문으로 그룹 함수와 함께 사용한다

여러개의 값들을 하나의 그룹으로 묶어서 처리 시 사용한다

   ※ GROUP함수는 WHERE절에서 조건 사용 불가함 ⇒ HAVING절에서 처리

GROUP BY 그룹할컬럼

--각 부서별 사원수
SELECT DEPT_CODE 부서명, COUNT(*)||'명' 인원
FROM EMPLOYEE
GROUP BY DEPT_CODE;

--각 직급별 직급코드, 총급여의 합, 사원수, 보너스를 받는 사원수, 평균급여, 최고급여, 최소급여
SELECT JOB_CODE
       ,SUM(SALARY) "총 급여 합"
       ,COUNT(*) "직원수"
       ,COUNT(BONUS) "보너스 받는 직원수"
       ,ROUND(AVG(SALARY)) "평균 급여"
       ,MAX(SALARY) "최고 급여"
       ,MIN(SALARY) "최소 급여"
FROM EMPLOYEE
GROUP BY JOB_CODE
ORDER BY JOB_CODE; --오름차순

--성별별 사원수
SELECT DECODE(SUBSTR(EMP_NO,8,1),'1','남자','2','여자') "성별"
        ,COUNT(*) "사원수"
FROM EMPLOYEE
GROUP BY SUBSTR(EMP_NO,8,1);

HAVING

그룹에 대한 조건 제시 시 사용되는 구문이며 GROUP BY 절 다음에 작성한다

HAVING 조건식;

--각 부서별 평균 급여가 300만원 이상인 부서들만 조회
SELECT DEPT_CODE, FLOOR(AVG(SALARY)) "평균급여"
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING AVG(SALARY)>=3000000;

--각 부서별 보너스를 받는 사원이 없는 부서만 조회
SELECT DEPT_CODE
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING COUNT(BONUS)=0;

함수(FUNCTION)의 정의

JAVA의 메소드 역할, 매개변수로 전달된 값을 읽고 계산값 반환한다

단일행 함수

N개의 값을 읽어서 N개의 결과를 리턴한다

(매 행마다 함수 실행 후 결과 반환)

그룹 함수

N개의 값을 읽어서 1개의 결과를 리턴한다

(하나의 그룹별로 함수 실행 후 결과 반환)

SUM

컬럼 값 총 합계 반환

SUM(숫자타입컬럼)

--부서코드가 'D5'인 사원들의 총 급여 합계
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE='D5';

--남자 사원들의 총 급여 합계
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1)=1;

AVG

컬럼 값 평균 반환

AVG(숫자타입컬럼)

--전체 사원들의 평균 급여
SELECT FLOOR(AVG(SALARY)) --버림
FROM EMPLOYEE;

MIN

컬럼 값 중 가장 작은 값 반환

MIN(모든타입)

--전체사원들중 최저급여,이름,이메일,입사날짜 //문자열은 영문, 한글순 기준
SELECT MIN(SALARY), MIN(EMP_NAME), MIN(EMAIL), MIN(HIRE_DATE)
FROM EMPLOYEE;
--각 컬럼의 최소값이 출력됨

MAX

컬럼 값 중 가장 큰 값 반환

MAX(모든타입)

--전체사원들중 최대급여,이름,이메일,입사날짜 //문자열은 영문, 한글순 기준
SELECT MAX(SALARY), MAX(EMP_NAME), MAX(EMAIL), MAX(HIRE_DATE)
FROM EMPLOYEE;
--각 컬럼의 최대값이 출력됨

COUNT

//조회 된 행의 개수를 세어 반환
COUNT(컬럼이름/DISTINCT 컬럼이름)

//조회 결과에 해당하는 모든 행의 개수를 모두 세어 반환
COUNT(*)

//제시한 해당 컬럼 값 중 NULL이 아닌 행의 개수를 세어 반환
COUNT(컬럼이름)

//제시한 해당 컬럼 값 중 중복 값이 있을 경우 하나로만 세어 반환, NULL도 제외
COUNT(DISTINCT 컬럼이름)

CF. DISTINCT : 중복값은 한번만 출력

--여자 직원수 조회
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1)=2;
 
--부서배치가 된 직원수
SELECT COUNT(*)
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL;

--현재 사원들이 속해있는 부서의 개수
SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;

--EMPLOYEE 테이블에서 직원명, 부서코드, 생년월일, 나이(만) 조회
--단 생년월일은 주민번호에서 추출 후 00년 00월 00일로 출력하고
--나이는 주민번호에서 출력해서 날짜 데이터로 변환한 다음 계산
SELECT EMP_NAME 직원명
        ,DEPT_CODE 부서코드
        ,CONCAT(SUBSTR(EMP_NO,1,2),'년 ')
         ||CONCAT(SUBSTR(EMP_NO,3,2),'월 ')
         ||CONCAT(SUBSTR(EMP_NO,5,2),'일') 생년월일
        ,EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,2),'RR')) 나이 
         --YY로하면 1965년인 경우 2065로 처리됨(50년기준으로 년도 앞자리가 19, 20 처리)
FROM EMPLOYEE;

CASE WHEN THEN END

CASE WHEN 조건식1 THEN 결과값1
        WHEN 조건식2 THEN 결과값2
        WHEN 조건식3 THEN 결과값3 ...
        ELSE 결과값 END

--직원들의 급여 인상하여 조회
--직급코드 'J7' 10% 인상, 'J6' 15% 인상, 'J5' 20% 인상, 그외 5% 인상
--사원명, 직급코드, 변경전 급여, 변경후 급여
SELECT EMP_NAME 사원명
      ,JOB_CODE 직급코드
      ,SALARY "변경전 급여"
      ,CASE WHEN JOB_CODE='J7' THEN (SALARY*1.1)
       WHEN JOB_CODE='J6' THEN (SALARY*1.15)
       WHEN JOB_CODE='J5' THEN (SALARY*1.2)
       ELSE (SALARY*1.05) 
       END "인상 후 급여"
FROM EMPLOYEE;

DECODE

비교대상을 조건값에 확인하여 결과값 매칭해주는 함수

비교대상에는 컬럼명, 산술연산(결과는 NUMBER), 함수가 들어갈 수 있다

DECODE(비교대상, 조건값1, 결과값1, 조건값2, 결과값2, ..., ..., 조건값N, 결과값N, 결과값)

--사번, 사원명, 주민번호, 주민번호로 성별자리 추출
SELECT EMP_ID, EMP_NAME, EMP_NO, DECODE(SUBSTR(EMP_NO,8,1), 1, '남자', 2, '여자') "성별"
FROM EMPLOYEE;

--직원들의 급여 인상하여 조회
--직급코드 'J7' 10% 인상, 'J6' 15% 인상, 'J5' 20% 인상, 그외 5% 인상
--사원명, 직급코드, 변경전 급여, 변경후 급여
SELECT EMP_NAME, JOB_CODE, SALARY "인상전급여"
       , DECODE(JOB_CODE, 'J7', (SALARY+(SALARY*1.1))
        ,'J6', (SALARY*1.15)
        ,'J5', (SALARY*1.2)
        ,(SALARY*1.05)) "인상 후 급여"
FROM EMPLOYEE;

NVL

해당 컬럼값이 NULL일 떄 : 제시한 특정값 반환

해당 컬럼값이 NULL이 아닐 때 : 기존 컬럼값 반환

NVL(컬럼명, 해당컬럼명이NULL일경우반환할값)

--사원명, 보너스 조회 시 보너스가 없으면 0으로 출력
SELECT EMP_NAME, BONUS, NVL(BONUS,0) AS "NULL 처리후"
FROM EMPLOYEE;

SELECT EMP_NAME, DEPT_CODE, NVL(DEPT_CODE, '없음') AS "NULL 처리후"
FROM EMPLOYEE;

NVL2

NVL2(컬럼명, NULL이아닐때출력값, NULL일때출력값)

--보너스가 있는 사원은 보너스 있음, 보너스가 없는 사람은 보너스가 없음으로 조회
SELECT EMP_NAME,BONUS,NVL2(BONUS,'있음','없음') AS "보너스 유무"
FROM EMPLOYEE;

NULLIF

두 값이 동일할 경우 : NULL 반환

두 값이 상이할 경우 : 비교대상1 반환

NULLIF(비교대상1, 비교대상2)

SELECT NULLIF('123','123')
FROM DUAL; --NULL반환

SELECT NULLIF('123','456')
FROM DUAL; --'123'반환