[Day6] Oracle 6 - ALL, correlated subquery, 문자함수, 날짜함수, COALESCE, DECODE

[Day6] Oracle 6 [10/4]

--1. EMP 테이블의 사원수를 조회하는 쿼리 작성.
   집계함수 == 그룹함수 == 복수행함수
   COUNT(*)  + NULL 포함
   COUNT(컬럼명) + NULL 제외
   
   STDDEV( 표준 편차 )
   VARIANCE( 분산 )
   
SELECT    COUNT(comm), COUNT(*) , SUM( sal ) , MAX(sal )
           -- , empno    *** ORA-00937: not a single-group group function
FROM emp;

   복습) 총급여합, 평균급여, 최고급여액, 최저 급여액 조회
       조건) pay = sal + comm

-- 인라인뷰 

SELECT SUM( t.pay ) , AVG( t.pay ), MAX( t.pay ) , MIN( t.pay )
FROM (
        SELECT sal  + NVL( comm, 0 ) pay
        FROM emp
) t;

--  WITH절 

WITH
   temp as (
         SELECT sal  + NVL( comm, 0 ) pay
        FROM emp
   )
SELECT SUM( t.pay ) , AVG( t.pay ), MAX( t.pay ) , MIN( t.pay )
FROM  temp t;

 -- 테이블 별칭을 사용. (FROM temp t)


--2. 현재 시스템의   날짜를 출력하는 쿼리 작성
오라클 날짜 자료형 : DATE              , TIMESTAMP
                  날짜+ h,m,s                         + ms + 타임존

SELECT SYSDATE               -- 함수  22/10/04
       , CURRENT_TIMESTAMP   -- 22/10/04 10:15:08.296000000 ASIA/SEOUL
FROM dual;  -- PUBLIC 시노님

--3. SQL 집합 연산자의 종류와 설명을 하세요
--     UNION, UNION ALL, INTERSECT, MINUS
--   주의사항 ) 컬럼수 동일, 자료형 동일

--4. 함수 설명
--  ㄱ. 반올림 함수를 선언형식을 적고 설명하세요 
         ROUND( 반올림값[, 반올림 위치] )
                             3+1
                             -3
--  ㄴ. 절삭(내림) 함수를 선언형식을 적고 설명하세요.    
        FLOOR()  무조건 소수점 첫 번째 자리에서 절삭
        TRUNC(  a[, b] )
        
--  ㄷ. 절상(올림) 함수를 선언형식을 적고 설명하세요.   
       CEIL()  
       
--5. 게시판에서 총 게시글 수가 : 65 개 이고  한 페이지에 : 15개의 게시글 출력할 때
--    총 페이지 수를 계산하는 쿼리 작성. ( 절상 )
   
    SELECT 65 / 15  --4.33333333333333333333333333333333333333
       , CEIL( 65/ 15 ) 총페이지수
    FROM dual;
    
--6. emp 테이블에서 사원들의 평균 급여보다 많은 급여를 받으면 1
--                                     적은 급여를 받으면 -1
--                                     같으면           0 
--  을 출력하는 쿼리 작성.  (  SIGN 함수 )

SELECT t.ename, t.pay
--        PL + SQL == PL/SQL
--       , if( t.pay > t.avg_pay)
--         else if( t.pay < t.avg_pagy)
--         else
        , SIGN( t.pay - t.avg_pay )
FROM (
    SELECT  ename, sal  + NVL( comm, 0 ) pay
            --, AVG(  sal  + NVL( comm, 0 ) ) avg_pay  -- ORA-00937: not a single-group group function
            , ( SELECT AVG(  sal  + NVL( comm, 0 ) ) FROM emp ) avg_pay
    FROM emp
    ) t;

--7. insa테이블에서 80년대( 80년~89년생 )에 출생한 사원들만 조회하는 쿼리를 작성
--  ㄱ. LIKE 사용
--  ㄴ. REGEXP_LIKE 사용
--  ㄷ. BETWEEN ~ AND 사용   

SELECT name, ssn
FROM insa
WHERE REGEXP_LIKE( ssn, '^8');
WHERE ssn LIKE '8%';
WHERE SUBSTR( ssn, 0, 2 ) BETWEEN 80 AND 89;  -- 1889

--  
--8. insa 테이블에서 주민등록번호를 123456-1******  형식으로 출력하세요 . ( LPAD, RPAD 함수 사용  )
--[실행결과]
--홍길동   770423-1022432   770423-1******
--이순신   800423-1544236   800423-1******
--이순애   770922-2312547   770922-2******

         RPAD()/ LPAD()

SELECT name, ssn
       , SUBSTR( ssn, 0, 8 ) || '******'
       , CONCAT( SUBSTR( ssn, 0, 8 ), '******' )
       , RPAD( SUBSTR( ssn, 0, 8 ), 14 , '*' )
       , LENGTH( ssn )
       , RPAD( SUBSTR( ssn, 0, 8 ), LENGTH( ssn ) , '*' )
FROM insa;

--
--8-2. emp 테이블에서 30번 부서만 PAY를 계산 후 막대그래프를 아래와 같이 그리는 쿼리 작성
--   ( 필요한 부분은 결과 분석하세요~    PAY가 100 단위당 # 한개 , 반올림처리 )
--[실행결과]
--DEPTNO     ENAME        PAY        BAR_LENGTH      
------------ ---------- ----------     ----------
--30              BLAKE          2850               29             #############################
--30              MARTIN       2650                27             ###########################
--30              ALLEN          1900                19             ###################
--30              WARD           1750                18             ##################
--30              TURNER       1500                 15             ###############
--30              JAMES          950                  10             ##########

SELECT deptno, ename, sal + NVL(comm, 0) pay
     , ROUND( (sal + NVL(comm, 0))/ 100 )     bar_length
     , RPAD( ' ', ROUND( (sal + NVL(comm, 0))/ 100 )+ 1, '#' )
FROM emp
WHERE deptno = 30
ORDER BY pay DESC;

--8-3. insa 테이블에서  주민번호를 아래와 같이 '-' 문자를 제거해서 출력
--[실행결과]
--NAME    SSN                         SSN_2
--홍길동   770423-1022432   7704231022432
--이순신   800423-1544236   8004231544236
--이순애   770922-2312547   7709222312547

SELECT  name, ssn
        , INSTR( ssn, '-' )
        , SUBSTR( ssn, 0, 6 ) || SUBSTR( ssn, -7 )
        , REPLACE( ssn, '-', '') -- *** JSP
FROM insa;

--9. 쿼리의 결과를 적으세요 . 

--  TRUNC( 숫자, [날짜] , 문자) 절삭함수

SELECT TRUNC( SYSDATE, 'YEAR' )      2022/01/01
       , TRUNC( SYSDATE, 'MONTH' )   2022/10/01
       , TRUNC( SYSDATE  )           2022/10/04  00:00:00.0000        ***
FROM dual;

--10. emp  에서  comm 이 null 이 아닌 사원수 출력.

SELECT comm
FROM emp
WHERE comm IS NULL;
WHERE comm IS NOT NULL;
WHERE comm = null;

--
--11. emp 에서 평균PAY 보다 같거나 큰 사원들만의 급여합을 출력.

SELECT t.*
FROM ( 
        SELECT ename, sal, comm,  sal + NVL(comm, 0) pay
               , ( SELECT AVG( sal + NVL(comm, 0) ) FROM emp ) avg_pay
        FROM emp
) t
WHERE t.pay >= t.avg_pay ;

--12. emp 테이블에서 급여를 가장 많이 받는 사원의 empno, ename, pay 를 출력.

SELECT MAX(  sal + NVL(comm, 0) )  max_pay
FROM emp;

SELECT deptno, ename, job,  sal + NVL(comm, 0) pay
FROM emp
WHERE  sal + NVL(comm, 0) = ( SELECT MAX(  sal + NVL(comm, 0) )  max_pay FROM emp   );
-- WHERE pay= 5000;  -- ORA-00904: "PAY": invalid identifier

-- (문제) max_pay, min_pay 최고,최저 급여자 확인

SELECT deptno, ename, job,  sal + NVL(comm, 0) pay , '최저급여자'
FROM emp
WHERE  sal + NVL(comm, 0) = ( SELECT MIN(  sal + NVL(comm, 0) )  max_pay FROM emp   )
UNION 
SELECT deptno, ename, job,  sal + NVL(comm, 0) pay , '최고급여자'
FROM emp
WHERE  sal + NVL(comm, 0) = ( SELECT MAX(  sal + NVL(comm, 0) )  max_pay FROM emp   );

-- 2

SELECT deptno, ename, job,  sal + NVL(comm, 0) pay 
FROM emp
WHERE  sal + NVL(comm, 0) = ( SELECT MIN(  sal + NVL(comm, 0) )  max_pay FROM emp   )
       OR
       sal + NVL(comm, 0) = ( SELECT MAX(  sal + NVL(comm, 0) )  max_pay FROM emp   );

-- 3.  IN                 SQL연산자 

SELECT deptno, ename, job,  sal + NVL(comm, 0) pay 
FROM emp
WHERE  sal + NVL(comm, 0) IN ( 
                                 ( SELECT MIN(  sal + NVL(comm, 0) )  max_pay FROM emp   )
                             ,   ( SELECT MAX(  sal + NVL(comm, 0) )  max_pay FROM emp   )
                          );

   
   SQL연산자 : ANY, ALL, SOME, EXISTS 

 

-- 최고급여자 

SELECT deptno, ename, job,  sal + NVL(comm, 0) pay
FROM emp
WHERE sal + NVL(comm, 0) <=  ALL( SELECT sal + NVL(comm, 0) FROM emp );
WHERE sal + NVL(comm, 0) >=  ALL( SELECT sal + NVL(comm, 0) FROM emp );

WHERE  sal + NVL(comm, 0) = ( SELECT MAX(  sal + NVL(comm, 0) )  max_pay FROM emp   );

--12-2. emp 테이블에서 각 부서별 급여를 가장 많이 받는 사원의 pay를 출력
-- java  for( int i=10; i<=40; i+=10 )        PL/SQL

SELECT *
FROM emp
WHERE deptno = 10 AND sal + NVL(comm, 0) = (
                                                SELECT MAX( sal + NVL(comm, 0) ) max_pay_10
                                                FROM emp
                                                WHERE deptno = 10 
                                            ) 
UNION 
SELECT *
FROM emp
WHERE deptno = 20 AND sal + NVL(comm, 0) = (
                                                SELECT MAX( sal + NVL(comm, 0) ) max_pay_10
                                                FROM emp
                                                WHERE deptno = 20 
                                            )
UNION                                             
SELECT *
FROM emp
WHERE deptno = 30 AND sal + NVL(comm, 0) = (
                                                SELECT MAX( sal + NVL(comm, 0) ) max_pay_10
                                                FROM emp
                                                WHERE deptno = 30 
                                            )                                            
UNION 
SELECT *
FROM emp
WHERE deptno = 40 AND sal + NVL(comm, 0) = (
                                                SELECT MAX( sal + NVL(comm, 0) ) max_pay_10
                                                FROM emp
                                                WHERE deptno = 40 
                                            );

-- 2.

SELECT deptno
         --, sal + NVL(comm, 0)   -- 257행, 16열에서 오류 발생, ORA-00979: not a GROUP BY expression
        , MAX( sal + NVL(comm, 0) )  -- 각 부서별 최고 급여액
        , MIN( sal + NVL(comm, 0) )  -- 각 부서별 최저 급여액
        , AVG( sal + NVL(comm, 0) )  -- 각 부서별 평균 급여액
        , SUM(  sal + NVL(comm, 0) ) -- 각 부서별 총 급여액
FROM emp
GROUP BY deptno  -- 집계 함수
ORDER BY deptno ASC;

-- 3. 상관된( correlated ) 서브쿼리( subquery )  - [시험]
--    ㄴ sub에서 리턴한 값을 main에서 사용하고 main에서 리턴한 값을 다시 sub에 사용함

SELECT *
FROM emp a
WHERE a.sal + NVL(a.comm, 0) = (
                                SELECT MAX( b.sal + NVL(b.comm, 0) ) 
                                FROM emp  b
                                WHERE b.deptno = a.deptno
                            )  
ORDER BY a.deptno ASC                            ;

 

-- 4. 순위(RANK) 함수
-- 5. TOP - N 방식

---

-- [ 문자 함수 ] 

ASCII() <-> CHR() 
문자    <-> ASCII

SELECT ename
    , SUBSTR( ename, 0, 1)
    , ASCII( SUBSTR( ename, 0, 1)  )
FROM emp;

 

SELECT ASCII(1) -- 49
,  CHR(  49 )
FROM dual; 
--  GREATEST(최대의 ), LEAST( 최소한)  나열된 문자 중에 가장 큰 값, 작은값을 리턴하는 함수
SELECT GREATEST( 10, 20 )
       , GREATEST( 10, 20, 30, 40, 50 )
       , LEAST( 10, 20, 30, 40, 50 )
       , GREATEST( 'KBS', 'MBC', 'SBS', 'JTBC' )
       , LEAST( 'KBS', 'MBC', 'SBS', 'JTBC' )
FROM dual;

-- VSIZE(  문자 )   지정된 문자열의 크기를 숫자로 반환.

SELECT VSIZE('a')   -- 알파벳 한 문자 1바이트
     , VSIZE('한')  -- 한글은 한 문자 3바이트
     , ASCII('가')   -- 15380608
FROM dual;

-- [ 날짜 함수 ] --
SYSDATE
ROUND( 날짜 )
TRUNC( 날짜 )


--날짜 + 숫자 = 숫자만큼 일수가 더해진 날짜  
--날짜 - 숫자 = 숫자만큼 일수가 빼진 날짜
--날짜 + 숫자/24 = 날짜에 숫자만큼의 시간이 더해진 날짜.
--날짜 - 날짜 = 두 날짜의 차이 일 수

-- (문제) 오늘부터 10일 후에 만나자.. 약속 
-- (문제) 지금부터 2시간 후...

SELECT SYSDATE
       ,SYSDATE + 10
       , SYSDATE - 3   -- 3일전
       , SYSDATE +  2/24  -- 2시간 후 날짜 객체 (시간표시는 안됨)
       -- , TO_CHAR( 숫자, 날짜, 문자 , '형식' )
       , TO_CHAR( SYSDATE +  2/24, 'YYYY.MM.DD HH24:MI:SS' )
FROM dual;

-- (문제) emp 테이블에서 각 사원의 근무 일수를 조회.

SELECT ename, hiredate
      , SYSDATE
      , CEIL( ABS(SYSDATE - hiredate) ) -- 1시간이라도 넘으면 CEIL
FROM emp;

-- [문제] 수료일까지 몇일이 남았는지 ?  날짜 - 날짜 = 일수
-- 수료일   2023.01.18

SELECT SYSDATE
     , '2023.01.18'
     -- , '2023.01.18' - SYSDATE  -- ORA-00932: inconsistent datatypes: expected CHAR got DATE
     --                                      일치하지 않는 데이터유형 :          문자     날짜 
     -- '2023.01.18'  날짜 자료형이 아니고 문자로 인식한다.
     -- 오라클 형 변환 : 문자 -> 날짜 [검색]
     , CEIL( TO_DATE('2023.01.18') - SYSDATE  ) "남은 일 수 "
FROM dual;

 

SELECT 2 + TO_NUMBER( '2' )
  , 2 + '2' -- 자동형변환되기에 굳이 TO_NUMBER() 로 변환  X
  , TO_DATE('2023.01.18')
FROm dual;

【형식】
TO_DATE( char [,'fmt' [,'nlsparam']])


-- MONTHS_BETWEEN  두 개의 날짜 간의 달의 차이 ( 개월수 )
-- ADD_MONTHS      특정 수를 달에 더한 날짜
-- LAST_DAY        특정 날짜가 속한 달의 가장 마지막 날짜를 리턴
-- NEXT_DAY        명시된 요일이 돌아오는 가장 최근의 날짜  "

SELECT ename, hiredate
      , CEIL(  ABS( hiredate - SYSDATE ) ) 근무일수
      , ROUND( MONTHS_BETWEEN(  SYSDATE, hiredate  ) , 2 ) 근무개월수
      , ROUND( MONTHS_BETWEEN(  SYSDATE, hiredate  ) / 12 , 2 )  근무년수
FROM emp;

-- ADD_MONTHS 함수
【형식】
    ADD_MONTHS(date, month)
   

SELECT ename, hiredate
     , hiredate + 100  -- 100일 후
     , hiredate + 10/24 -- 10시간 후
     , ADD_MONTHS( hiredate, 3) -- 3개월 후
     , ADD_MONTHS( hiredate, -3) -- 3개월 전
FROM emp;

-- 확인

SELECT  TO_DATE( '2022-02-01' )
   ,  ADD_MONTHS( TO_DATE( '02-01-2022', 'MM-DD-YYYY') , 1 ) a -- 문자를 날짜로 형변환 TO_DATE
  -- ORA-01830: [date format] picture ends before converting entire input string
  -- 전체 입력 문자열이 변환되기 전에  날짜 형식 사진이 종료되었다.
   ,  ADD_MONTHS( TO_DATE( '02-28-2022', 'MM-DD-YYYY') , 1 ) b --  2/28 + 한달
   ,  ADD_MONTHS( TO_DATE( '02-27-2022', 'MM-DD-YYYY') , 1 ) c --  2/27 + 한달
   ,  ADD_MONTHS( TO_DATE( '03-30-2022', 'MM-DD-YYYY') , -1 ) d --  3/31 + 한달
     -- 3/31~3/28       - 1 달 = 2/28
FROM dual;

-- 달력  : 년,월   마지막날짜.

SELECT TO_DATE( '2022-02-01' )-1        -- 22/01/31
FROM dual;

-- LAST_DAY 함수 : 특정날짜가 속한 달(월)의 가장 마지막 날짜를 반환

SELECT SYSDATE
  , LAST_DAY( SYSDATE )
  , TO_CHAR( LAST_DAY( SYSDATE ), 'DD' ) -- 문제) 이번 달이 몇일 까지 있나요? 
FROm dual;

-- TO_NUMBER()
-- TO_CHAR()
-- TO_DATE()

-- NEXT_DAY 함수 : 명시된 요일이 돌아오는( 다가오는 ) 가장 최근의 날짜를 리턴하는 함수
 --                   다음 주 X

【형식】
      NEXT_DAY(date,char)

-- 문제) 돌아오는( 다가오는 )   월요일은 휴강이다. 몇 일 ? 

SELECT SYSDATE
  , NEXT_DAY( SYSDATE, '월요일' )  -- 대체 공휴일  X
  , NEXT_DAY( SYSDATE, '화요일' ) 
  , NEXT_DAY( SYSDATE, '금요일' ) -- 이번 주 금요일
FROM dual;

-- datetime(= date 함수의 확장 형태) 종류

SELECT SYSDATE
,   CURRENT_DATE
,   CURRENT_TIMESTAMP
FROM dual;

-- EXTRACT 함수 :
【형식】
      EXTRACT (
         {YEAR ¦ 
          MONTH ¦ 
          DAY ¦ 
          HOUR ¦ 
          MINUTE ¦ 
          SECOND ¦
          TIMEZONE_HOUR ¦ 
          TIMEZONE_MINUTE ¦ 
          TIMEZONE_REGION ¦ 
          TIMEZONE_ABBR} 
      FROM {datetime_value_expr ¦ 
            interval_value_expr}
        ) 
--

SELECT SYSDATE
     , TO_CHAR( SYSDATE, 'YYYY' )    year  -- 왼쪽정렬( 문자 )
     , EXTRACT( YEAR FROM SYSDATE ) year   -- 오른쪽정렬( 숫자 )
FROM dual;

-- [ 변환 함수의 종류 ]
-- TO_NUMBER    :   문자 -> 숫자 변환   X

SELECT  TO_NUMBER(  '1234'  ) 
FROM dual;

-- TO_CHAR      :   [숫자],날짜,문자 -> 문자 변환
【형식】
      TO_CHAR( n [,'fmt' [,'nlsparam']]);
--  홍길동   통화기호2,810,000

SELECT name, basicpay + sudang pay
     , TO_CHAR(   basicpay + sudang,  '9G999G999'   ) -- 콤마 G
     , TO_CHAR(   basicpay + sudang,  '9,999,999'   ) -- 콤마 ,
     , TO_CHAR(   basicpay + sudang,  'L9,999,999'   ) -- 원화 L
FROM insa;

--  2260.416666666666666666666666666666666667
--  소수점 2자리 출력   :   ROUND() X  자동 반올림  확인

SELECT AVG( sal + NVL( comm, 0) )  avg_pay
        ,  TO_CHAR(   AVG( sal + NVL( comm, 0) ),  'L9,999.00' ) avg_pay
FROM emp;

-- TO_CHAR( 날짜형 )  날짜형 -> 내가 원하는 문자열 형변환함수
【형식】
    TO_CHAR( date [,'fmt' [,'nlsparam']])

-- YY와 RR 의 차이점(기억) --

SELECT SYSDATE
  , TO_CHAR( SYSDATE, 'CC' ) ㄱ -- 21세기
  , TO_CHAR( SYSDATE, 'BC' ) ㄴ -- 서기
   --  서기 (西紀)
   -- 기원 원년 이후. 주로 예수가 태어난 해를 원년으로 하여 이른다.
   , TO_CHAR( SYSDATE, 'Q' ) ㄷ -- 10월 == 4분기
--   , TO_CHAR( SYSDATE, 'IYYY' )-- 년
   , TO_CHAR( SYSDATE, 'YYYY' )-- 년   RRRR
   , TO_CHAR( SYSDATE, 'YEAR' )-- 영문 년도 TWENTY TWENTY-TWO
   , TO_CHAR( SYSDATE, 'MM' ) -- 월  MM, MONTH, MON
   , TO_CHAR( SYSDATE, 'DD' ) -- 일  D,  DD,  DDD 
   , TO_CHAR( SYSDATE, 'HH24' )-- 시간  HH, HH12, HH24
   , TO_CHAR( SYSDATE, 'MI' )-- 분
   , TO_CHAR( SYSDATE, 'SS' )-- 초   SSSSS 자정에서 부터 초
   , TO_CHAR( SYSDATE, 'DAY' )-- 요일  DY(화), DAY(화요일) 
FROM dual;

--  WW, IW, W  ( 기억 )

SELECT 
  TO_CHAR( SYSDATE, 'WW' ) ㄱ    -- 년 중 몇 째 주
  , TO_CHAR( SYSDATE, 'IW' ) ㄴ  -- 년 중 몇 째주
  , TO_CHAR( SYSDATE, 'W'  ) ㄷ   -- 월 중 몇 째 주
  , TO_CHAR( SYSDATE, 'TS' ) ㄹ  -- 시간 간략 (  오후 3:15:51 )
FROM dual;

-- [문제] SYSDATE 를 사용해서
--       TO_CHAR() 함수를 사용해서
--      '2022년 10월 04일 오후 15:21:12 (화)' 형식으로 출력

-- ORA-01821: date format not recognized
--            날짜 형식이 인식되지 않는다. 
SELECT TO_CHAR( SYSDATE, 'YYYY"년" MM"월" DD"일" AM HH24:MI:SS (DY)' )
      ,  TO_CHAR(SYSDATE, 'YYYY') || '년 ' || TO_CHAR(SYSDATE, 'MM') || '월 ' || TO_CHAR(SYSDATE, 'DD') || '일 ' || TO_CHAR(SYSDATE, 'TS') 날짜시간
FROM dual;
-- String.format("%04d", 9)  -- 0009
-- DecimalFormat 형식화 클래스
SELECT TO_CHAR(  9 , '0999' )  -- '0009'
FROM dual;

-- [문제]  문자열 -> 날짜  형변환  TO_CHAR(),   [ TO_DATE() ]
   --   '2022년 12월 31일'

SELECT TO_DATE('2022년 12월 31일', 'YYYY"년" MM"월" DD"일"')
FROM dual;

-- [문제] emp 테이블에서 각 사원들의 입사일자를 기준으로 10년 5개월 20일째 되는 날 ? 

날짜 + 일수 = 날짜
날짜 - 일수 = 날짜
날짜 - 날짜 = 일수
날짜 + /24 = 날짜

MONTHS_BETWEEN()  개월수
ADD_MONTHS()      개월  +/-
NEXT_DAY(), LAST_DAY()
--

SELECT   hiredate
       , ADD_MONTHS( hiredate + 20 ,  12 * 10 + 5 ) 
FROM emp;

-- [문제] insa 테이블에서 ssn 주민등록번호를 통해서 생일을 얻어오고
--       오늘을 기준으로 생일 지났다. 지나지 않았다라고 출력.
--                       1/ 0/  -1 SIGN()

SELECT *
FROM insa;
--
UPDATE insa
SET ssn = '801004-1544236'
WHERE num = 1002;
COMMIT;

-- (복습)  '지났다', '안지났다', '오늘 ' 처리

SELECT ssn
--      , SUBSTR(ssn, 0, 6) X  태어난 년도 X
      , TO_DATE(  SUBSTR( ssn, 3, 4 ) ,   'MMDD' )  -- MMDD  2022년      
      , SYSDATE 
      -- 양수( 생일이 지나지 않음 )
      -- 정답.  시간:분:초   00:00:00
      -- , SIGN(   TO_DATE(  SUBSTR( ssn, 3, 4 ) ,   'MMDD' ) - TRUNC( SYSDATE ) ) 
      , SIGN(   TO_DATE(  SUBSTR( ssn, 3, 4 ) ,   'MMDD' ) - SYSDATE  ) 
FROm insa;

SELECT TO_CHAR(  TO_DATE(  SUBSTR( ssn, 3, 4 ) ,   'MMDD' ) , 'TS')
   , TO_CHAR(  SYSDATE  , 'TS')
   , TO_CHAR(   TRUNC( SYSDATE ) , 'TS')
FROM insa;

-- TO_DATE 기본값 (시험)

SELECT   TO_DATE(  '2021' , 'YYYY' )  -- 21     /10/01
, TO_DATE(  '2021-02' , 'YYYY-MM' )   -- 21/02/01
, TO_DATE(  '23' , 'DD' )   -- 22/10/23
FROM dual;

-- [일반 함수]
--  NVL
--  NVL2
--  NULLIF

-- COALESCE 함수 ( 코우얼레스 ) : 합치다. 연합
--  ㄴ 나열해 놓은 값을 순차적으로 체크하여 NULL 이 아닌 값을 리턴하는 함수
【형식】
        COALESCE(expr[,expr,...])

SELECT ename
   , sal + NVL(comm, 0) pay
   , sal + NVL2( comm, comm, 0) pay
   , COALESCE( sal + comm, sal , 0 ) pay
   --  sal+comm이 null이면 (더하기 중 하나라도 null이면 null임) sal 출력, sal이 null이면 0 출력
   , sal + COALESCE( comm, 0 ) pay
FROM emp;
--  '' == NULL
select coalesce('','','arirang','kunsan') 
from dual;

-- DECODE 함수 ( 시험 ) *****
  ㄴ [여러 개의 조건]을 주어 조건에 맞을 경우 해당 값을 리턴하는 함수 
  ㄴ PL/SQL 안에서 사용할 오라클 함수
  ㄴ (기억) 비교연산은 '=' 만 가능하다  + IF ~ ELSE IF ~ ELSE  문 처럼 사용 가능

-- 자바 -
int x = 10;
if( x == 11 ){
   return C;
}
-- 오라클 DECODE -
DECODE( x, 11, C)



-- 자바 -
int x = 10;
if( x == 11 ){
   return C;
}else{
    return D;
}
-- 오라클 DECODE -
DECODE( x, 11, C, D)



-- 자바 -
int x = 10;
if( x == 11 ){
   return C;
}else if( x == 12 ){
    return D;
}else if( x == 13 ){
    return E;
}else {
     return F;
}
-- 오라클 DECODE -
DECODE( x, 11, C, 12,  D, 13, E, F)

-- [문제] insa테이블에서  ssn 주민번호를 가지고
--       생일 지남 여부   SIGN 1,-1,0 출력.
--  조건) DECODE 함수를 사용해서   '생일 전', '생일 후' , '오늘 생일' 문자열 출력

SELECT ssn     
     , DECODE(   SIGN(  TO_DATE(  SUBSTR( ssn, 3, 4) ,  'MMDD' ) - TRUNC( SYSDATE ) ) ,  0 , '오늘 생일', 1 , '생일 전', -1, '생일 후' )
FROM insa;

-- 1. 문제) insa 테이블에서 ssn을 가지고 '남자'/'여자'    라고 성별을 출력              
--       (   DECODE ()  사용  )

SELECT ssn 
       ,  DECODE(   MOD(  SUBSTR( ssn, -7, 1) , 2 ), 0 , '여자', '남자') gender
FROM insa;

-- 문제) insa테이블에서 남자 사원수, 여자 사원수 몇명인지 출력(조회)
-- 문제) emp 테이블에서  총사원수, 10번, 20번, 30번, 40번 사원수를 출력(조회)


-- 2. 문제) emp 에서 각 사원들의 번호,이름,급여(pay) 출력
--        10번 부서원  15% 인상
--        20번 부서원    5% 인상
--        그외 부서원   10% 인상

SELECT deptno, empno, ename
     , sal  + NVL( comm, 0)   pay
     , DECODE(   deptno , 10,  ( sal  + NVL( comm, 0) ) * 0.15
                        , 20,  ( sal  + NVL( comm, 0) ) * 0.05
                        , ( sal  + NVL( comm, 0) ) * 0.10
              ) 인상액   
FROM emp
ORDER BY deptno ASC;