SQLD/ SQL활용 예제

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 출력