개발 관련 자격증 정보
SQLD/ SQL활용 예제
dev.mk
2020. 5. 21. 17:43
반응형
SQL 전문가 가이드 실습용 파일.zip
0.43MB
위의 SQL활용 예제를 위한 스크립트 파일을 다운 받고 DBMS에서 실행한다.
예제의 필요한 테이블 및 데이터를 생성한다.
-- 옵션+커맨드 슬러쉬 주석달기
-- RANK()
-- 우리가 일반적으로 점수의 순위는 ORDER BY DESC를 해서 볼수가 있다.
-- 하지만 똑같은 점수를 가진 사람들이 많이 나올때, 예를 들면.100, 90, 90, 80 이렇게 점수가 나오면 순위를 1,2,2,4 이렇게 부여 해야 한다.
-- 이럴 경우 RANK 함수를 사용하여 쉽게 순위를 부여 할 수 있다.
-- RANK() 함수를 사용하여 급여 순으로 순위를 부여한 예제 입니다.
-- RK의 출력값을 보면 급여가 같으면 같은 순위로 부여가 됩니다.
SELECT
empno
, ename
, sal
, RANK() OVER (ORDER BY sal DESC ) as rk
FROM emp;
--그룹별로 순위를 부여 하는 법
--아래 예제는특정한 그룹별로 순위를 부여 하는 예제이다.
SELECT
deptno
, ename
, sal
, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk
FROM emp ;
-- DENSE_RANK() 함수
-- DENSE_RANK() 함수는 중복 RANK의 수와 무관하게 numbering을 한다. 1등, 2등, 2등 이렇게 2등이 중복 되었는데 4등이 아니라 3등이 부여 된다.
SELECT
empno
, ename
, sal
, DENSE_RANK() OVER (ORDER BY sal DESC ) as rk
FROM emp;
-- ROW_NUMBER()
-- 중복 값들에 대해서도 순차적인 순위를 표시하도록 출력
SELECT
JOB
, ENAME
, SAL
, ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP
-- 정리
SELECT
JOB
, ENAME
, SAL
, RANK() OVER (ORDER BY SAL DESC) ALL_RANK -- 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력
, DENSE_RANK() OVER (ORDER BY SAL DESC) DENS_RANK --중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력
, ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER --중복 값들에 대해서도 순차적인 순위를 표시하도록 출력
, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS JOB_RANK --특정범위내에서 순위 부여
FROM EMP
-- JOIN
-- USING 은 조인 대상테이블의 조인조건을 알아서 해준다 ex) DEPT.DEPTNO = EMP.DEPTNO
SELECT
*
FROM DEPT
JOIN EMP
USING (DEPTNO);
--->(다음도 같은 코드)
SELECT
EMP.DEPTNO
, EMPNO
, ENAME
, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
--
--->(다음도 같은 코드)
SELECT
EMP.DEPTNO
, EMPNO
, ENAME
, DNAME
FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
--
--->(다음도 같은 코드, INNER JOIN을 JOIN으로 써도 상관 없다. 디폴트값이 INNER JOIN)
--
SELECT
EMP.DEPTNO
, EMPNO
, ENAME
, DNAME
FROM EMP
JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
SELECT
E.ENAME
, E.DEPTNO
, D.DEPTNO
, D.DNAME
FROM EMP E
JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
WHERE E.DEPTNO = 30;
SELECT
REGION_NAME 연고지명
, TEAM_NAME 팀명
, E_TEAM_NAME 영문팀명
FROM TEAM
WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정현수')
ORDER BY TEAM_NAME;
--Natural join
--두테이블간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=)JOIN을 수행
--JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 한다
--NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.
--SQL Server에서는 지원하지 않는 기능두테이블간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=)JOIN을 수행
--JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 한다
--NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.
--SQL Server에서는 지원하지 않는 기능
SELECT
DEPTNO, EMPNO, ENAME, DNAME
FROM EMP
NATURAL JOIN DEPT;
-- ROLLUP(요약)
-- GROUP BY를 사용해서 직업별로 급여 합계를 구하는 예제이다.
SELECT
job
, SUM(sal)
FROM emp
GROUP BY job;
-- ROLLUP을 사용해서 직업별로 급여 합계와 총계를 구하는 예제이다.
SELECT
job
, SUM(sal)
FROM emp
GROUP BY ROLLUP(job);
--부서별로 인원수와 급여 합계를 구하는 예제
SELECT
b.dname
, a.job
, SUM(a.sal) sal
, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname, a.job;
-- 부서별로 인원과, 급여합계가 한 눈에 보이지 않는다.
-- 일일이 부서에 해당하는 직업별 급여와 사원수를 일일이 더해야 한다.
-- 이런 경우 ROLLUP을 사용하여 쉽게 조회 할 수 있다.
SELECT
b.dname
, a.job
, SUM(a.sal) sal
, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job)
--1. ROLLUP 함수(상세_
--그룹핑된 컬럼의 소계를 생성하기 위해 사용.
--그룹핑된 컬럼의 수가 n 이면 소계는 N+1 레벨이 생성
SELECT
DNAME
, JOB
, COUNT(*) "Total Empl"
, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);
--//결과는 (부서명,직업)으로 그룹핑하고 해당 그룹의 총 인원과 총 급여를 출력한다.
--// 그리고 그 부서별로 소계가 밑에 나오고 마지막으로 총계가 나온다.
-- * GROUPING 함수?
--// GROUPING 함수가 컬럼에 추가되었는데
--// GROUPING 함수는 ROLLUP이나 CUBE에 의해 소계가 생성되었을때 1이 표시되고 이외에는 0을 표시한다.
--// 따라서 소계 필드에 원하는 문자열을 넣는등 보고서쓸때 적절하다.
SELECT
DNAME
, GROUPING(DNAME)
, JOB
, GROUPING(JOB)
, COUNT(*) "Total Empl"
, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);
--=> ROLLUP 함수 일부 사용
SELECT
CASE GROUPING(DNAME) WHEN 1
THEN '>>>>All Departments'
ELSE DNAME END AS DNAME
, CASE GROUPING(JOB) WHEN 1
THEN '>>>>All Jobs'
ELSE JOB END AS JOB
, COUNT(*) "Total Empl"
, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, ROLLUP(JOB)
--// 이렇게 ROLLUP을 JOB에만 사용했을 때 결과는 어떨까?
--// 결과를 보면 기존과 같이 소계는 나오지만 부서별로 소계를 낼 필요가 없기 때문에
--// 총 합계는 나오지 않는다.
-- 2. CUBE 함수
-- ROLLUP함수에서 단순히 소계만 생성했지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를함
-- * 연산이 많아 시스템에 부담을 줌.
SELECT
CASE GROUPING(DNAME) WHEN 1 THEN '>>>>All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN '>>>>All Jobs' ELSE JOB END AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE(DNAME,JOB);
--// ROLLUP은 부서별 소계와 총합계까지만 나타내줬으나
--// CUBE는 직업(JOB)별로도 소계가 다 구해진다.
--3. GROUPING SETS 함수
--표시된 인수들에 대한 개별 집계를 구할 수 있으며,
--ROLLUP인수의 계층구조와 달리 평등한 관계라 순서에 상관없이 동일한결과가 나옴.
-- GROUPING SETS은 ROLLUP이나 CUBE처럼 GROUP BY 절에 명시해서 그룹 쿼리에 사용되는 절이다.
-- 이 장 맨 마지막에 소개하는 이유는 GROUPING SETS 절이 그룹 쿼리이긴 하나 UNION ALL 개념이 섞여 있기 때문이다.
-- 예를 들어, GROUPING SETS (expr1, expr2, expr3)를 GROUP BY 절에 명시했을 때,
-- 괄호 안에 있는 세 표현식별로 각각 집계가 이루어진다.
-- 즉 쿼리 결과는 ((GROUP BY expr1) UNION ALL (GROUP BY expr2) UNION ALL (GROUP BY expr3)) 형태가 된다.
SELECT
DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME
, DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB
, COUNT(*) "Total Empl"
, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (JOB, DNAME);
--// 부서별 모든 직업의 소계
--// 직업별 모든 부서의 소계
--ROLLUP : 소그룹간 소계 출력
--CUBE : GROUP BY 항목들과 다차원 소계 출력
--GROUPING SETS : 특정항목에 대한 소계 출력
--* DECODE함수는 CASE문이랑 동일.
-- 계층구조 쿼리란?
-- 오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있으며, mgr 컬럼 데이터는 해당 사원의 관리자의 empno를 의미 한다.
-- 예를 들어서 아래의 데이터를 보면
/*
EMPNO ENAME SAL MGR
------ ------- ------ ------
7369 SMITH 800 7902
7902 FORD 3000 7566
*/
-- empno 7369사원의 관리자는 7902의 empno를 가진 사원이며
-- empno 7902사원의 관리자는 7566의 empno를 가진 사원이다.
-- 이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서 쉽게 조회 할 수 있다.
--계층구조 쿼리 Synctax
--START WITH
--- 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
--- 서브쿼리를 사용할 수도 있다.
--CONNECT BY
--- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
--- PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
--- CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
--- CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
--- CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
--- 서브쿼리를 사용할 수 없다.
--LEVEL Pseudocolumn
--- LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.
--ORDER SIBLINGS BY
--- ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.
--CONNECT BY의 실행순서는 다음과 같다.
--- 첫째 START WITH 절
--- 둘째 CONNECT BY 절
--- 세째 WHERE 절 순서로 풀리게 되어있다.
--계층구조 쿼리 예제
--간단예제
--아래는 직업이 PRESIDENT을 기준으로 계층 구조로 조회하는 예이다.
-- LEVEL컬럼으로 depth를 알수 있다.
-- JONES의 관리자는 KING 이며, SCOTT의 관리자는 JONES 이다.
-- 상/하의 계층 구조를 쉽게 조회 할 수 있다.
SELECT LEVEL, empno, ename, mgr
FROM emp
START WITH job = 'PRESIDENT'
CONNECT BY PRIOR empno = mgr;
-- * PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서 상하위간의 관계를 기술할때 사용.
--LEVEL의 활용
--LEVEL Pseudocolumn을 이용하면 계층구조 쿼리를 좀 더 다양하게 활용 할 수 있다.
--아래는 LEVEL의 배율만큼 공백을 왼쪽에 추가하여 계층구조를 한눈에 볼 수 있게 표현한 예이다.LEVEL의 활용
-- SQL*Plus에서만 깔끔하게 보기위해서
-- COL ename FORMAT A20;
-- 왼쪽에 LEVEL만큼 공백을 추가하여 계층구조로 조회하는 예제
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
--아래는 LEVEL별로 급여 합계와 사원수를 조회하는 예제이다.
-- LEVEL별로 급여 합계와 사원수를 조회하는 예제
SELECT LEVEL, AVG(sal) total, COUNT(empno) cnt
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr
GROUP BY LEVEL
ORDER BY LEVEL;
-- PRIOR의 활용
--PRIOR연산자를 SELECT 절에서 사용해보자.
--아래는 사원의 관리자를 PRIOR연산자를 이용해서 조회하는 예제이다.
-- SQL*Plus에서만 깔끔하게 보기위해서
-- COL mgrname FORMAT A10;
-- SELECT절에 "PRIOR ename mgrname"을 확인해 보자
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename,
PRIOR ename mgrname,
empno, mgr, job
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
--PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서만 사용할 수 있는 것은 아니다.
--Bottom Up 조회 예제
--위 간단 예제를 역순(자식에서 부모로 트리 구성, Bottom Up)으로 조회 해 보자
-- SQL*Plus에서만 깔끔하게 보기위해서
-- COL ename FORMAT A20;
-- ename을 기준으로 Bottom Up으로 조회하는 예제이다.
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
FROM emp
START WITH ename='SMITH' -- 최 하위 노드 값이 와야 한다.
CONNECT BY PRIOR mgr = empno; -- ename을 기준으로 Bottom Up으로 조회하는 예제이다.
--PRIOR 컬럼에 따라(상위 OR 하위) 계층전개 방향이 달라진다.
--- 순방향(Top Down-상위~하위) : PRIOR 하위 = 상위
--- 역방향(Bottom Up-하위~상위) : PRIOR 상위 = 하위
--4. 문자열함수
SELECT
LOWER('SQL Expert')
FROM DUAL;
-- => 결과 : 'sql expert' 기능 : 모든 문자를 소문자로 바꾼다.
SELECT
UPPER('SQL Expert')
FROM DUAL;
-- => 결과 : 'SQL EXPERT' 기능 : 모든 문자를 대문자로 바꾼다.
SELECT
ASCII('A')
FROM DUAL;
-- => 결과 : 65 기능 : 문자나 숫자를 아스키코드 번호로 바꾼다.
SELECT
CHAR(65)
FROM DUAL;
-- => 결과 : 'A' 기능 : 아스키코드 번호를 문자나 숫자로 바꾼다.
SELECT
CONCAT('RDBMS','SQL')
FROM DUAL;
-- => 결과 : 'RDBMS SQL' 기능 : 문자열 연결
SELECT
'RDBMS' || 'SQL'
FROM DUAL;
-- => 위와 동일함
SELECT
SUBSTR('SQL Expert',5,3)
FROM DUAL;
-- => 결과 : 'Exp' 기능 : 문자열 자르기
-- // 5번째 문자부터 3개에 해당하는 문자열 까지 리턴, 3번째 인자가 없으면 마지막 문자까지 리턴
SELECT
SUBSTRB('SQL Expert',5,3)
FROM DUAL;
-- => 결과 : 위와 동일
SELECT
LENGTH('SQL Expert')
FROM DUAL;
-- => 결과 : 10 >>기능 : 문자열의 길이 리턴
SELECT
LTRIM('xxxYYZZxYZ','x')
FROM DUAL;
-- => 결과 : 'YYZZxYZ' > 기능 : 왼쪽에서 다른문자 만날때까지 해당문자열 제거
SELECT
LTRIM(' xxxYYZZxYZ')
FROM DUAL;
-- => 결과 : 'XXYYZZXYZ' 기능 : 왼쪽에서 공백 제거
SELECT
RTRIM('XXXYYzzXYzz','z')
FROM DUAL;
-- => 결과 : 'XXXYYzzXY' > 기능 : 오른쪽에서 다른문자 만날때까지 해당문자열 제거
SELECT
TRIM('x' FROM 'xxYYZZxYZxx')
FROM DUAL;
-- => 결과 : 'YYZZxYZ' 기능 : 양쪽에서 다른 문자 만날때까지 해당문자열 제거
SELECT
TRIM(' xxYYZZxYZxx ')
FROM DUAL;
-- => 결과 : 양쪽 공백 제거
SELECT
RTRIM('XXYYZZXYZ ')
FROM DUAL;
-- => 결과 : 'XXYYZZXYZ' 기능 : 오른쪽에서 공백 제거
-- 5. 숫자형함수
SELECT
ABS(-15)
FROM DUAL
-- => 결과 : 15 기능 : 절대값 리턴
SELECT
SIGN(-20)
FROM DUAL
-- => 결과 : -1 기능 : 양수인지 음수인지 0인지 구별
SELECT
SIGN(0)
FROM DUAL
-- => 결과 : 0
SELECT
SIGN(20)
FROM DUAL
-- => 결과 : 1
SELECT
MOD(7,3) -- 7/3 = 2..1
FROM DUAL
-- => 결과 : 1 기능 : 첫번째 인자를 두번째 인자로 나눈 나머지=> 결과 : 1 기능 : 첫번째 인자를 두번째 인자로 나눈 나머지
-- 7%3
-- CEIL(n) : n보다 크거나 같은 정수 중 제일 작은 수
-- 올림 함수
SELECT
CEIL(38.123)
FROM DUAL
-- => 결과 : 39 기능 : 숫자보다 크거나 같은 최소 정수
SELECT
CEIL(-38.123)
FROM DUAL
-- => 결과 : -38
-- FLOOR(n) : n보다 작거나 같은 정수 중 제일 큰 수
-- 버림 함수
SELECT
FLOOR(38.123)
FROM DUAL
-- => 결과 : 38 기능 : 숫자보다 작거나 같은 최대 정수
SELECT
FLOOR(-38.123)
FROM DUAL
-- => 결과 : -39
-- ROUND 함수는 특정 소수점을 반올림하고 나머지를 버리는 함수 이다.
-- 소수점을 반올림하지 않고 절사만 원한다면 TRUNC 함수를 사용해야 한다.
-- - 함수 : ROUND("값", "자리수")
SELECT
ROUND(1235.543) --1236
, ROUND(1235.443) --1235
, ROUND(1235.443, 0) --1235 두번째 파라미터(0)은 생략이 가능하다.
FROM DUAL
-- =>결과 1236 / 1235 / 1235
SELECT
ROUND(1235.3456, 1) -- 1235.3
, ROUND(1235.3456, 2) --1235.35
, ROUND(1235.3456, 3) --1235.346
FROM DUAL
-- =>결과 1235.3 / 1235.35 / 1235.346
-- 1번은 소수점 첫번째 자리수까지 표시하는 것이며 두번째 소수점을 반올림 한다. 2번은 두번째 소수점까지 표시하고 세번째 소수점을 반올림한다.
SELECT
ROUND(38.5235,3)
FROM DUAL
-- => 결과 38.524 기능 : 반올림함수. 두번째 인자 소수점까지 유효 디폴트는 0
SELECT
TRUNC(38.5235,3) --3번쨰 뒤로 자름
FROM DUAL
-- => 결과 38.523 기능 : 버림함수. 두번째 인자 소수점까지 유효
SELECT
TRUNC(38.5235) -- 인자 생략시 소숫점 모두 버림
FROM DUAL
-- => 결과 38
-- 6. 날짜형함수
SELECT
SYSDATE
FROM DUAL;
-- => 결과 : 20/05/21 현재날짜와 시각 출력현재날짜와 시각 출력
SELECT
EXTRACT(YEAR FROM SYSDATE)
, EXTRACT(MONTH FROM SYSDATE)
, EXTRACT(DAY FROM SYSDATE)
FROM DUAL;
-- => 결과 : 날짜데이터에서 년/월/일 데이터를 출력할 수 있음 시분초도 가능
SELECT
ENAME
, HIREDATE
, EXTRACT(YEAR FROM HIREDATE) 입사년도
, EXTRACT(MONTH FROM HIREDATE) 입사월
, EXTRACT(DAY FROM HIREDATE) 입사일
FROM EMP;
-- 각 HIREDATE 컬럼에서 항목별로 추출
SELECT
TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))
FROM DUAL;
-- => 결과 날짜데이터에서 년/월/일 출력가능, TO_NUMBER함수 제외시 문자형으로 출력됨.**
SELECT
ENAME
, HIREDATE
, TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) 입사년도
, TO_NUMBER(TO_CHAR(HIREDATE,'MM')) 입사월
, TO_NUMBER(TO_CHAR(HIREDATE,'DD')) 입사일
FROM EMP;
-- 7. 변환형함수
-- 명시적(Explicit)변환 : 데이터 유형을 변환하도록 명시하는 경우
-- 암시적(Implicit)변환 : 명시 없이 자동으로 데이터유형을 변환하여 계산하는 경우
-- TO_NUMBER(문자열) : ALPHANUMERIC 문자열을 숫자로 변환
SELECT
TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜
, TO_CHAR(SYSDATE, 'YYYY. MON, DAY') 문자형
FROM DUAL;
-- // 결과는 날짜로는 2017/09/02 문자형으로는 2017. 9월, 토요일
SELECT
TO_CHAR(123456789/1200, '$999,999,999.99') 환율반영달러
FROM DUAL;
-- // 결과는 $102,880.66
-- 8. CASE표현
-- 오라클에서 DECODE함수와 같은 기능을 함 *****중요
SELECT
ENAME
, CASE WHEN SAL > 2000
THEN SAL
ELSE 2000
END REVISED_SALARY
FROM EMP;
-- IF-THEN-ELSE 논리와 유사한 방식으로 급여가 2000이 넘으면 그대로 출력하고 2000이하면 2000으로 변경해서 출력한다.
SELECT
LOC
, CASE LOC
WHEN 'NEW YORK' THEN 'EAST'
WHEN 'BOSTON' THEN 'EAST'
WHEN 'CHICAGO' THEN 'CENTER'
WHEN 'LA' THEN 'WEST'
ELSE 'ETC'
END as AREA
FROM DEPT;
-- // 뉴욕이면 동부, 보스턴이면 동부, 시카고면 중부, LA면 서부, 그외는 ETC로 출력하고 그 칼럼을 AREA로 하겠다.
SELECT
ENAME
, CASE WHEN SAL >= 3000 THEN 'HIGH'
WHEN SAL >= 1000 THEN 'MID'
ELSE 'LOW'
END AS SALARY_GRADE
FROM EMP;
-- CASE 이후에 칼럼이나 조건식을 따로 넣지않고 사용가능
-- 급여가 3000 이상이면 HIGH, 급여가 1000 이상이면 MID 그 이하는 LOW 출력
-- 자연스럽게 앞에 것 부터 적용하는것 같다. 4000은 3000보다 크고 1000보다 크니 HIGH, MID 조건에 둘다 맞지만 먼저 적힌 3000이상에 걸리는듯.
-- 9. NULL 관련 함수
-- NULL 은 아직 정의 되지않은 값으로 0 또는 공백과 다르다.
-- NULL 값을 포함하는 연산에서는 값도 NULL 이다.
-- NULL + 2 = NULL, 2 - NULL = NULL, NULL * 2 = NULL, 2/NULL = NULL
-- //그래서 이런 NULL값을 처리하기 위해 NVL함수가 생겼다.
-- * NVL(NULL판단대상, 'NULL일 때 대체값');
SELECT
NVL(NULL, 'NVL-OK') NVL_TEST
FROM DUAL;
--// NVL-OK 출력. NULL이니까
SELECT
NVL('Not-Null', 'NVL-OK') NVL_TEST
FROM DUAL;
-- //결과는 Not-Null 출력, 왜? NULL이 아니니까
SELECT
NVL('NULL', 'NVL-OK') NVL_TEST
FROM DUAL;
-- // 결과는 'NULL' 출력, 왜? NULL 과 'NULL'은 다르니까
-- **NVL함수를 다중행 함수의 인자로 사용하는 경우 오히려 불필요한 부하가 발생할 수 있으므로 굳이 NVL함수 사용할 필요가 없다. 나중에 다중행함수때 알게됨.
-- NULLIF (EXPR1, EXPR2) : EXPR1과 EXPR2가 같으면 NULL 출력, 다르면 EXPR1 출력
SELECT
ENAME
, EMPNO
, MGR
, NULLIF(MGR,7698) NUIF
FROM EMP;
-- // MGR은 매니저 번호인데 사원테이블에서 매니저 번호가 7698과 같으면 NULL 출력, 다르면 기존의 MGR출력
-- // 다소 헷갈릴수 있다.
--- COALESCE(a1, a2, a3, a4,... aN)
-- a1 부터 aN까지 첫음으로 null 이 아닌 값을 리턴
-- 모든 값이 null 이면 null 리턴
-- 비교하는 데이터 형은 일치 시켜야함
-- ex) sale1/2/3 중에 순서대로 비교해서 null 이 아닌 첫번째 값을 가져오고 모두 null이면 0을 리턴
-- SELECT COALESCE(sale1,sale2,sale3,0)
-- ex) 'a1' 이 숫자형일 경우
-- COALESCE(a1, 'none') --> 'error'
-- COALESCE(TO_CHAR(a1), 'none') --> 'OK'
-- ex) 'b1' 이 문자형일 경우
-- COALESCE(b1, 0) --> 'error'
-- COALESCE(b1, '0') --> 'OK'
SELECT
ENAME
, EMPNO
, MGR
, COALESCE(MGR,1) COLESCE
FROM EMP;
-- // 인자는 한정되어 있지않고 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 출력한다. 모든 EXPR이 NULL 이면 NULL 출력
반응형