종이 한 장의 차이

 사람들이 명승부를 보면서 간혹 ‘종이 한 장의 차이였다’라는 말을 하곤 한다. 나도 자주는 아니지만 가끔 사용하는 문장이다. 그런데 이 종이 한 장의 차이는 과연 무슨 차이일까? 사람들은 무슨 일을 할 때 흔히 확률을 물어본다. 그 승패의 확률은 어떻게 돼 ? 이길 가능성은 얼마나 돼?라고 물어본다. 그러나 그것은 집단 안에 많은 사람들이 시도했을 때의 결과 값일 뿐 개개인은 그 통계와는 다른 양상을 띤다. 개개인에게 그 승패의 결과는 성공 또는 실패의 50% 확률의 게임이다.

 여기서 종이 한 장 차이가 발생한다. 그 개인이 어떠한 승부에서 이길 확률은 50%에서 51%가 되는 순간 승부가 결정되며 우리는 한순간의 51%를 위하여 매일 같이 노력하는 것이다. 그리고 그 1%는 종이 한 장의 차이이다. 비록 어떠한 승부에서 큰 차이가 나서 지게 되더라도 그것은 결과이며 시작은 항상 50% 확률로 시작하게 된다. 이러한 생각을 하게 되면 50% 확률이라는 의미를 조금 다르게도 생각해 볼 수 있게 된다. 내가 아무리 자신이 있더라도 확률이 50%라고 생각하게 되면 자만심을 줄일 수 있다. 또한, 내가 아무리 자신이 없더라도 확률이 50%라고 생각하게 되면 자신감을 가질 수 있다. 

 또 50%는 어쩐지 아리스토텔레스의 ‘중용’을 떠오르게 한다. 중학교 시절 배웠던 아리스토텔레스의 최선의 선 ‘중용’은 혈기가 왕성하던 시절에 그저 어느 한쪽에도 치우치지 못한 어중간함으로 보였다면 세월이 지나면서 느끼게 된 ‘중용’ 현명한 삶 그 자체를 느끼게 했다. 치우침은 극과 맞닿아 있다. 그리고 극과 극은 서로 종이 한 장 차이이다. 궁극의 선이라고 하여도 인간의 다양성의 대한 포용이 없이 그것을 타인에게 강요하는 순간 그것은 궁극의 선과 맞닿아 있는 어둠을 접하게 된다. 중용이라는 단순히 가운데 있는 것이 아니다. 중용을 지키기 위해서는 먼저 서로의 끝을 알 만큼 지혜가 있어야 한다. 그러한 지혜가 없다면 가운 지점을 찾을 수 없다. 또한, 중용은 치우치지 않아야 한다. 서로의 끝을 알고 가운데를 찾았다고 하더라도 균형을 잡을 수 없다면 중용을 지킬 수 없다. 또한 중용은 포용할 줄 알아야 한다. 가운데라는 의미는 양쪽 끝을 절단하는 구분점이 아닌 양쪽의 끝을 이어준다는 의미이다. 이렇듯 중용이란 지혜, 균형 포용의 3박자의 어우러짐이다. 이런 것들을 생각한 후에 나 또한 중용을 지키고자 하지만 쉽지 않은 일이다.

 사실 이 종이 한 장 차이에 대한 글은 드라마 ‘동네변호사 조들호’에서 박신양을 보고 시작되었다. 동네변호사 조들호에서 2화 마지막은 ‘자신에 말을 들어준 사람이 조변호사 밖에 없었다’는 피고인의 이야기를 들은 조들호(박신양)의 표정을 클로즈업하면서 끝이 나게 된다. 그런데 이 마지막 표정에서 나는 종이 한 장의 차이를 느꼈다. 그 표정에서 조들호의 복잡한 심경을 느끼게 하였다. 마지막 부분이며 그냥 무심코 보면 드라마의 연출상 마지막 부분을 만들기 위한 단순한 클로즈업일 수도 있다. 그리고 많은 드라마에서 배우들의 얼굴을 클로즈업하면서 드라마를 마무리한다. 그러나 그러한 마무리 표정으로 복잡한 감정을 느끼게 하는 배우는 얼마나 있을까? 세상에는 많은 배우들이 있다. 그리고 그들은 서로 다른 모습을 지니고 있다. 그리고 난 솔직히 누가 절대적으로 연기를 잘하고 못 하는지에 대한 차이를 잘 알지도 못한다. 대배우와 신인배우의 연기력은 차이가 날 수도 있겠지만, 그 차이가 얼마나 나는지 알 수도 없으며 인기에 대한 후광효과 또한 연기력에 포함된다면 더욱더 그 차이를 알기 힘들다. 그러나 열심히 하는 배우라면 미세한 차이도 신경을 쓸 것이다. 모든 사람이 느낄 수 없다고 하더라도 누군가는 미세한 차이를 위해서 계속해서 노력해온다. 그리고 그 미세한 차이는 정확히 언제 인지는 사람인지라 알 수 없지만, 언제가 드러나게 된다. 나는 여기서 박신양이라는 배우를 칭찬하고 싶은 것이 아니다. 그저 박신양이라는 배우는 연기를 위해서 매우 노력하는 사람이라는 것을 방송을 통해서 알게 되었고 그 노력에 대한 값어치를 말하고 싶은 것이다. 즉, 박신양이라는 배우는 많은 사람들이 알만한 예시이며 결국에는 누군가 미세한 차이를 위해 노력하는 사람들 전부를 의미하는 것이다.

 결국, 미세한 차이는 종이 한 장의 차이이다. 나는 이러한 종이 한 장의 차이를 위해서 오늘도 최선을 다하고 있다. 하루에 5시간을 노력하는 사람과 10시간을 노력하는 사람이 처음에는 같은 결과가 나왔다고 하자. 그렇다고 해서 그 둘은 같은 사람은 아니다. 그 둘의 차이는 언젠가 종이 한 장의 차이로 밝혀질 것이다. 이렇듯 종이 한 장의 차이는 50%의 확률을 1%로 만드는 것이지만 결국의 승패를 결정짓는 것은 그 1% 종이 한 장의 차이로 결정짓게 된다. 그 1%의 힘을 믿으며 나는 오늘도 최선을 다한다.

 

''키워드'로 생각해보기' 카테고리의 다른 글

사랑 감기  (1) 2020.03.01
시간의 상대성  (0) 2019.12.28
온천  (0) 2019.12.24
의문  (0) 2019.12.16
"고독"  (1) 2014.11.30

  7) index merge scan 
        - 두개의 인덱스를 동시에 사용해서 하나의 인덱스만 사용했을 때보다 더 큰 시너지 효과를 보겠금 하는 스캔 방법 
           (시너지 효과 : table 엑세스 횟수를 줄이는 효과가 있다.)
            (10g 부터는 사용되지 않고 있는 기능이다. )  
        -  힌트 : and_equal 

     ⑴ index merge test
        ① @ctas (초기화)
        ② create index emp_deptno on emp(depno) ; 
        ③ create index emp_job on emp(job) ;

     ⑵ index merge 실행계획 

     SELECT /*+ and_equal(emp emp_deptno emp_job) */ ename, job, deptno
      FROM EMP
     WHERE deptno = 30
       AND job = 'SALESMAN';    
 
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    14 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP        |     1 |    14 |     3   (0)| 00:00:01 |
|   2 |   AND-EQUAL                 |            |       |       |            |          |
|*  3 |    INDEX RANGE SCAN         | EMP_JOB    |     3 |       |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_DEPTNO |     5 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JOB"='SALESMAN' AND "DEPTNO"=30)
   3 - access("JOB"='SALESMAN')
   4 - access("DEPTNO"=30)
 
* 실행계획을 읽을 때 동일한 위치에 있는 것들은 위에서 부터 읽으면 된다. 

 8) index bitmap merge scan  ( 10g 버젼부터 가능한 스캔 방법 _ 책에는 9c 부터)
     - index merge scan 과 스캔방법은 똑같은데 인덱스의 크기를 줄이기 위해서 인덱스를 bitmap 으로 변환하는 작업이 추가되었다. 
     - hint : index_combine  

    SELECT /*+ index_combine(emp) */ ename, job, deptno
    FROM EMP
    WHERE deptno = 30
     AND job = 'SALESMAN';
      -- 4 
   
-----------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |     1 |    14 |     3   (0)| 00:00:01 |        -----------------------------------------> 실행순서 8
|   1 |  TABLE ACCESS BY INDEX ROWID     | EMP        |     1 |    14 |     3   (0)| 00:00:01 |  ------------------------------------------> 실행순서 7
|   2 |   BITMAP CONVERSION TO ROWIDS    |            |       |       |            |          |     ------------------------------------------> 실행순서 6
|   3 |    BITMAP AND                    |            |       |       |            |          |             ------------------------------------------> 실행순서 5
|   4 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |            |          |   ------------------------------------------> 실행순서 2
|*  5 |      INDEX RANGE SCAN            | EMP_JOB    |       |       |     1   (0)| 00:00:01 |     ------------------------------------------> 실행순서 1
|   6 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |            |          |   ------------------------------------------> 실행순서 4
|*  7 |      INDEX RANGE SCAN            | EMP_DEPTNO |       |       |     1   (0)| 00:00:01 |   ------------------------------------------> 실행순서 3
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("JOB"='SALESMAN')
   7 - access("DEPTNO"=30)

※ 선택도 확인 방법 
      ① ANALYZE TABLE EMP COMPUTE STATISTICS ;

      ② select c.column_name, t.num_rows, c.num_nulls, c.num_distinct
                    , 1/c.num_distinct selectivity
                    , num_rows/c.num_distinct cardinality
           from   user_tables t, user_tab_columns c
         where  t.table_name = 'EMP'
            and   c.table_name = t.table_name
            and   c.column_name in ('DEPTNO','JOB') ;   


- num_distinct 가 클 수록 선택도가 좋아진다. (num_distinct : 해당 컬럼을 구분하는 구분기준의 갯수)
  (selectivity, cardinality 의 값이 작을 수록 선택도가 좋아진다.) 
- 선택도가 좋아진다는 것은 optimize 가 선택할 가능성이 크다는 것을 의미한다. 

 9) index join
   - 인덱스끼리 조인해서 바로 결과를 보고 테이블 엑세스는 따로 하지 않는 스캔 방식 
   - 힌트 : index_join

   ⑴ index join 테스트
      ① create index emp_deptno on emp(deptno) ;
         create index emp_job on emp(job) ;

      ② select deptno, job
          from emp
          where deptno = 30 and job='SALESMAN'; 
        -- consistent gets 4 
        -- full table scan  

      ③ SELECT /*+ index_join(emp emp_deptno emp_job) */ deptno, job
           FROM EMP
          where deptno = 30 and job='SALESMAN';
         -- consistent gets 3
         -- 2번의 index range scan 
         -- hash join 

 10) index unique scan 
       - primary key 나 unique 제약을 걸면 unique 인덱스가 자동으로 생성이 되는데 바로 이 unique 인덱스를 이용해서 데이터를 스캔하는 방법 
         ( 생성할 수만 있다면 non unique 인덱스보다 성능이 좋다.) 
       - 힌트 : index ( 해당 컬럼에 unique 제약이 있으면 자동으로 적용된다.) 

        ⑴ index unique scan 생성 방법  
             ①  primary key 생성시 unique 인덱스 자동 생성)
               alter table emp 
               add constraint emp_empno_pk primary key(empno) ;

               select index_name, uniqueness
                 from user_indexes
               where table_name ='EMP'
               
           
             ②  unique index 직접 생성
               - create unique index emp_comm on emp(comm);
             
         ⑵ unique index 의 특징               
             - non unique index는 unique 하다고 선언되어 있지 않아서 필요한 자료를 찾은 후 또 자료 있는지 그 행 다음열 부터 다시 찾는 작업을 한 번더 실행한다.)
             - 즉, unique index는 필요한 data를 찾는 1번의 검색만 하는 반면 non unique index는 총 2번의 검색 작업이 이루어진다.)


(4) 테이블 random 엑세스 부하 
     1) 테이블 랜덤 엑세스 줄이는 방법 
         ① 결합 컬럼 인덱스를 활용한다. 
         ② index의 클러스터링 팩터를 좋게한다. (p. 68)
         ③ index의 컬럼추가 (p. 79)               
         ④ 테이블의 구조를 IOT (index organizaion table) 로 구성한다. (p. 98)
         ⑤ 테이블의 구조를 cluster table로 구성한다. (p.118)
 
     2) index 컬럼추가 
         - 기존에 사용되던 index에서 필요한 컬럼을 추가하여 table access를 줄인다

          ①   create table ts
                 as select *
                  from all_objects
                  order by object_type ;

          ② create index t2_owner
              on t2(owner) ;

          ③ alter system flush buffer_cache ; ----------------> buffer cache 내용 비우는 작업 

    ※ 차세대 시스템 구축시 주의 사항 !! 
 
         <예시>
            SQL> insert /*+ parallel(e1,4) */ into emp e1
            SQL> select /*+ parallel(e2,4) */ *
                     from emp e2; 

      - 데이터 이행시 위와 같이 병렬로 작업해서 이행하면 데이터가 무작위로 흩어져서 저장되기 때문에 클러스터링 팩터가 더 안좋아져서 성능이 느려질 수 있다. 

문제 29. 부서번호가 30번이고 직업이 SALESMAN인 사원들의 이름과 직업과 부서번호를 출력하시오! 
          (실행계획을 보고 인덱스 둘중에 어느 인덱스를 사용했는지 확인하시오!) 

SELECT ename, job, deptno
 FROM EMP
 WHERE deptno = 30
  AND job = 'SALESMAN';       

- emp(job) 에 걸려 있는 index를 사용 
- emp(job) 으로는 4건 , emp(deptno) = 6 건 
- 더 적게 table acess 하는 방법으로 optimize 가 선택하였다.

* 튜닝을 할 때는 where 조건에 걸려 있는 각각의 건수들이 몇건인지 확인해보는 것이 기본이다. 
* 옵티마이져가 인덱스의 선택도를 조사해서 선택도가 좋은 인덱스를 선택했다. 
     ( emp_job > emp_deptno) 

문제 30. 위의 SQL 의 실행계획이 부서번호의 인덱스를 엑세스 하겠금 힌트를 주시오 ! 

SELECT /*+ index(emp emp_deptno) */ ename, job, deptno
 FROM EMP
 WHERE deptno = 30
  AND job = 'SALESMAN'; 

문제 31. 아래의 SQL 의 컬럼에 각각 인덱스를 걸고 두개의 실행계획을 비교하는데 하나는 index merge scan 이 되게하고 , 
          다른 하나는 index bitmap merge scan 이 되게해서 consistent gets를 비교하시오 ! 

튜닝 전 :

select * 
 from sales500
where channel_id = 3 
  and amount_sold= 1232.16 ;

 --  consistent gets 4442
 -- (full hint 사용 시) 

튜닝 후:

1) 일반적인 인덱스 사용 

select /*+ index(sales500 sales500_amount) */ *
 from sales500
where channel_id = 3
  and amount_sold= 1232.16 ;

-- consistent gets 18
-- index range scan 

2) index merge scan 사용

SELECT /*+ and_equal(sales500 sales500_channel sales500_amount) */ *
 from sales500
where channel_id = 3
  and amount_sold= 1232.16 ;

-- consistent gets 31
-- index range scan 2번

3) index bitmap merge scan 

SELECT /*+ index_combine(sales500) */ *
 from sales500
where channel_id = 3
  and amount_sold= 1232.16 ;     

-- consistent gets 412
-- index range scan
-- bitmap conversion 

문제 32. 아래의 SQL 을 튜닝하시오 ! 

튜닝 전:

select * 
 from t2
 where owner='SCOTT' and object_name='EMP';

-- consistent gets 232

튜닝 후:

1) index column 1개만 사용 

 select /*+ index(t2 t2_owner)*/ *
 from t2
 where owner='SCOTT' and object_name='EMP';

  -- consistent gets 30
  -- index range scan

 2) index column 을 2개 사용하여 복합 컬럼 인덱스 구성  

 select /*+ index(t2 t2_owner_name)*/ *
 from t2
 where owner='SCOTT' and object_name='EMP';
  -- consistent gets 4
  -- index range scan 

문제 33. 짝궁의 SCOTT 의 SALES500 테이블의 SALES500_CUST_ID 인덱스의 클러스터링 팩터를 비교하시오! 

SQL> select a.index_name, a.clustering_factor, b.index_name, b.clustering_factor
          from user_indexes a, user_indexes@shs_link2 b
      WHERE a.table_name = 'SALES500';

* 점심시간 문제 : 아래의 SQL 을 튜닝하시오 ! 
                    (deptno , sal 2개의 컬럼으로 구성 된 index가 있을 시 )   

create index emp_deptno_sal on emp(deptno,sal);


튜닝 전 :

select deptno, max(sal)
  from emp
where deptno = 20
 group by deptno ; 

-- 46
-- full (full 힌트를 주어서 작동 시켰을 때 값)  


튜닝 후 : 
1) 일반적인 복합 컬럼 인덱스를 사용하여 튜닝

SELECT /*+ index_desc(emp emp_deptno_sal) */ deptno, sal
  FROM EMP
 WHERE deptno = 20
  AND ROWNUM = 1;

-- consistent gets 1
-- index range scan 

2) 인라인 뷰 안에서 먼저 복합 컬럼 인덱스를 사용하여 튜닝 후 첫번째 로우만 뽑아 내기 
 
 SELECT *
 FROM (SELECT /*+ index_desc(emp emp_deptno_sal) */ deptno, sal
         FROM EMP
        WHERE deptno = 20 )
 where rownum = 1 ;

-- consistent gets 1
-- index range scan 

3) 인라인 뷰 안에서 스킵 스캔을 사용하여 튜닝 후 첫번째 로우만 뽑아 내기 

SELECT *
 FROM (SELECT /*+ index_ss_desc(emp emp_deptno_sal) */ deptno, sal
         FROM EMP
        WHERE deptno = 20 )
 where rownum = 1 ;

-- consistent gets 1
-- index skip scan 


'ORACLE > SQL 튜닝' 카테고리의 다른 글

SQL튜닝(4) - IOT (index organizaion table) 구성, 클러스터 테이블 구성, 다중 클러스터 테이블 구성  (0) 2016.04.11
SQL튜닝(2)(1.10) - index ( index range scan, index full scan, index fast full scan, index skip scan )  (0) 2016.04.01
SQL튜닝(1) - index (index 구조, index 사용 시 주의 사항, index 스캔 방식의 종류 )  (0) 2016.03.15

- 함수를 왜 배워야하는가? 

ex)
우리반 테이블에서 전공, 전공별 인원수를 출력하시오!

우리반 테이블에세 가장 나이가 어린 학생의 이름을 출력하시오!

메일 도메인, 메일 도메인별 건수를 출력하시오! 

- 함수란 무엇인가?



- 함수의 종류 2가지? 


1. 단일행 함수
   - 문자
   - 숫자
   - 날짜
   - 변환
   - 일반 

1) 문자 함수 
   (1) 대소문자 변환 함수 
       - lower : 소문자로 출력하는 함수
       - upper : 대문자로 출력하는 함수
       - initcap: 첫번째 철자는 대문자 나머지는 소문자로 출력하는 함수 

   (2) 문자 조작 함수 
       - concat : 두개의 컬럼의 값을 연결하는 함수
       - substr : 특정 철자를 잘라내는 함수
       - length : 철자의 길이를 출력하는 함수 
       - instr : 특정 철자의 위치를 출력하는 함수
       - lpad : 왼쪽에 특정값을 채워넣는 함수 
       - rpad : 오른쪽에 특정값을 채워넣는 함수 
       - trim : 특정 철자나 값을 잘라내는 함수 
       - replace : 특정 철자로 변경하는 함수 

2) 숫자 함수 
      - round : 반올림하는 함수
      - trunc : 잘라내는 함수
      - mod : 나눈 나머지값을 출력  

3) 날짜 함수 
     - between month : 해당 기간 동안의 몇달이 있었는지 출력 
     - add-months: 기준 날짜에서 입력한 달 수 만큼 지난 날짜를 출력
     - next-day: 기준 날짜에서 해당 요일이 오늘 날짜를 출력 
     - last-day: 기준 날짜에서 해당 달의 끝까지 몇일이 남았는지 출력

4)변환 함수 / data type 을 변환
    
   (1) data type 
         - 숫자
         - 문자
         - 날짜 
   (2) 형변환 2가지 (하나의 데이터가 아니라 형식을 바꾼다고 생각해라)
        1. 암시적 형변환 : 오라클이 알아서 형변환 수행 
           예시) select ename, sal 
                     from emp
                     where sal = '3000'
                               숫자   문자 
                     - 이는 원래 실행 되어서는 안된지만 오라클 내부적으로 data type을 자동 변환하여 실행된다.
                     - 오라클은 숫자를 문자보다 더 높은 우선 순위에 두기 때문에 문자를 숫자로 변환한다.  (숫자>문자)
                     - BUT. 이런 식의 코드는 실행은 되지만 효율이 떨어져 수정해야 한다. 

        2. 명시적 형변환:
           - to_char :문자로 형변환하는 함수 
                            날짜 ---> 문자 
                            숫자 ---> 문자 

           - to_number : 숫자로 형변환하는 함수 
           - to_date :날짜로 형변환하는 함수 
  
  5) 일반 함수 
     - NVL 함수: null 값이 숫자를 변화하여 출력하라 
     - NVL2
     - decode 함수
     - case 함수 

2. 복수행 함수
   - max
   - min
   - count
   - avg
   - sum

문제 92. 나이대가 20대인 학생들의 이름과 나이와 주소를 출력하는데, 나이가 높은 학생부터 출력하시오!

SQL> SELECT ename, age, address
           FROM EMP2
           WHERE age BETWEEN 20 AND 29
           ORDER BY age DESC;

문제 93. 서울에서 살지 않는 학생들의 이름과 학원까지의 시간을 출력하는데 시간이 가장 오래 걸리는 학생부터 출력하시오! 

SQL> SELECT ename, distance
           FROM EMP2
           WHERE address NOT LIKE '%서울%'
           ORDER BY distance DESC;

문제 94. 이름에 EN 또는 IN이 포함되어져 있는 사원의 이름과 월급을 출력하시오!(EMP 테이블) 
   
  SQL> SELECT ename, sal
             FROM EMP
             WHERE ename LIKE '%EN%' OR
                        ename LIKE '%IN%';

*Regexp_substr 활용하여 더 간편하게 검색 가능

SQL> select ename, sal 
         from emp
         where regexp_like(ename,'(EN|IN)');
  - 이 쿼리에서 '|'는 또는을 의미하며 계속 추가할 수 있다. 
  - regexp_like 는 '%'(와일드 카드)를 쓸 필요가 없다.  

* MS_SQL에서는 regexp를 사용할 수 없다.


문제 95. 이름, 월급을 출력하는데 월급을 암호화해서 출력하시오! 월급의 숫자 234를 각각 *로 출력하시오 

SQL> SELECT ename,regexp_REPLACE(sal,'[2-4]','*') 
           FROM EMP;

* regexp/정규 표현식 
  
* 이 경우 이런 코드도 가능하다. 
  SELECT ename,regexp_REPLACE(sal,'(2|3|4)','*') 
  FROM EMP;

만약 연속 된 숫자가 아니였다면, 이런 식으로 입력가능 할듯 하다.

* MS_SQL 에서는 replace에 정규표현식을 사용할 수 없어서 위와 같은 결과를 내려면 아래와 같이 중첩으로 사용해야 한다. 

select ename,replace(replace(replace(sal,'2','*'),'3','*'),'4','*')
 from emp ;

문제 96. 이름, 월급, 보너스(sal,*02) 를 출력하시오! 
             컬럼별칭을 사용해서 월급*0.2가 보너스로 컬럼명이 출력되게하시오! 
             (보너스가 일의 자리에서 반올림되게 하시오!)

SELECT ename, sal, ROUND (sal*0.2,-1) AS 보너스
  FROM EMP;

*ROUND / 반올림 하는 keyword
  TRUNC / 버림하는 ketword

예시) select round(756.57,1), trunc (756.57,1)
         여기서 '.'은 기준으로 '0'이 된다. 
         뒤로 가면서 1,2, ... 
         앞으로 가면서 -1, -2, -3, 으로 된다. 

Tip. dual 이란?
  - dumy table 인데 select절의 결과 값을 보기 위한 가상의 테이블 
   (실제 테이블을 일력하면 같은 숫자가 대체 되어 나온다.)
 

주의! 별칭을 정해주기 전에 ROUND로 묶어준다. 

* MS_SQL도 반올림과 버림을 할 수 있지만 Oracle과 차이가 있다. 
  - round는 소수점 몇 번째까지 지정해 줄 수 있지만 버림음 floor 함수를 사용해서 정수만 나타낸다.  
   예시 ) select round(756.57,1) ;
            - 이 문장은 756.5 으로 결과가 나타남 

     
          select FLOOR(756.57 ,1) ;
            - 이 문장은 뒤에 1 때문에 문자 오류가 발생하고, 1을 없애면 756 만 출력된다. 


문제 97. 우리반 테이블에서 이름과 나이를 출력하는데 이름과 나이컬럼 옆에 나이가 짝수인지 홀수인지를 출력되게 하시오!

SQL> SELECT ename, age, MOD(age,2) AS 짝수홀수여부
           FROM EMP2;

*MOD/ 해당 숫자로 나누어서 나머지를 구하는 keyword

예시) select mod(10,3)
          from dual;
     - 10을 3으로 나눈 나머지 값 
      (실무에서는 해당 숫자가 짝수인지 홀수인지 판별할때 주로 사용한다.)

* MS_SQL 은 Oracle의 Mod 함수를 지원하지 않는다. 
   - 그래서 위의 Oracle 쿼리처럼 구현하기 위해서는 %를 사용해야 한다. 
    예시 ) SQL> SELECT ename, age, age%2 AS 짝수홀수여부
                     FROM EMP2;


문제 98.  오늘날짜를 출력하시오! 

SQL> select sysdate
           from daul;

*날짜 - 숫자 =   날짜 (date)
*날짜 + 숫자 =  날짜 (date)
*날짜  - 날짜 =   숫자 (for)


* MS_SQL에서 오늘의 날짜 출력 법 
  SQL> select SYSDATETIME() ;


문제 99 우리반 테이블에서 이름, 내가 태어날 날부터 오늘까지 총 몇일 살았는지 출력하시오!

SQL> SELECT ename, ROUND((sysdate - birth),0)
            FROM EMP2;

* '0'을 쓰지 않아도 default 값이 0으로 자동 출력된다. 

* MS_SQL에서는 이런식의 날짜 계산은 사용할 수 없다. 
  - 날짜 변환 시 문자 형식으로 변환되는데 문자끼리는 연산이 되질 않는다. 
  - 그래서 dateadd, datediff 같은 날짜 계산 함수를 써서만 날짜 계산이 가능하다. 


문제 100. 우리반 테이블에서 이름, 내가 태어난 날부터 오늘까지 총 몇주를 살았는지 출력하시오! 

SQL> SELECT ename, ROUND((sysdate - birth)/7,0)
           FROM EMP2;


문제 101. 우리반 테이블에서 이름, 내가 태어난 날부터 오늘까지 총 몇달을 살았는지 출력하시오!

SQL> SELECT ename, MONTHS_BETWEEN(SYSDATE, birth)
            FROM EMP2;

* 날짜 함수 
      1. months_between / 주의 사항은 앞에 변수 값에 최신 날짜를 입력해야 한다. 

* MS_SQL에서 날짜 계산 함수 형식
  - datediff( 시간단위구분자, 시작시간, 종료시간 )

select DATEDIFF ( mi , getdate() , getdate()+1 ) ==> 분
select DATEDIFF ( s , getdate() , getdate()+1 ) ==> 초 
select DATEDIFF ( hour , getdate() , getdate()+1 ) ==> 시간 (1일)
select DATEDIFF ( day , getdate() , getdate()+1 ) ==>  일 (1일)
select DATEDIFF ( month , getdate() , getdate()+31 ) ==> 개월 (31일)
select DATEDIFF ( year , getdate() , getdate()+730 ) ==>  년 (730일)

예시) (위와 같이 출력하고자 할때) 
     SQL> select datediff(month, hirth, getdate())
               from emp2 ;


문제 102. 사원 테이블에서 이름,  입사후 부터 그동안 받았던 총 급여를 출력하시오! (sal이 월급이라고 할 때)


SQL> SELECT ename, sal*ROUND(MONTHS_BETWEEN(SYSDATE,hiredate))
           FROM EMP;

* MS_SQL에서 출력 시 

  SQL> select ename, sal*datediff(month, HIREDATE, getdate())
           from emp ;


문제 103. 오늘부터 100달 뒤에 돌아오는 날짜를 출력하시오! 
 
SQL> select add_months(sysdate, 100)
           from dual;

* add_months/ 앞으로 해당 달 수 이후의 날짜 

* MS_SQL에서 앞으로 해당 달 수 이후의 날짜 구하는 법
  - MS_SQL에서는 Oracle 의 add_month 와 같은 dateadd 함수가 있다. 
    예시 )  select ename,dateadd(month,100,hiredate)
               from emp ;


문제 104. 오늘부터 앞으로 돌아올 금요일에 날짜를 출력하시오! 

SQL> select next_day(sysdate,'friday')
           from dual;

주의! 지정 요일에는 ' ' 표시를 꼭 해야 한다. 

* MS_SQL에서는 next_day와 같은 기능이 없다. 
   - 그래서 특정 날짜의 요일을 찾고 추론해야 한다. 
     예시 )   select datename(dw,getdate());
              
               OR 

               select datename(weekday,getdate());
             
             위의 쿼리를 돌리면 해당 요일이 나온다 거기서 원하는 요일까지 추론해야 한다. 



문제 105. 오늘부터 200달 뒤에 돌아오는 목요일의 날짜를 출력하시오 

SQL>  select next_day(add_months(sysdate, 200),'thursday')
           from dual;

* MS_SQL 에서 몇 달 후 돌아오는 날짜 출력하기 
   - 이번에도 위와 같이 특정 날짜를 찾고 추론해야 한다. 

SQL > select datename(dw,dateadd(month,200,getdate()))

문제 106. 오늘부터 요번달 말일까지 총 몇일 남았는지 출력하시오! 

SQL> SELECT LAST_DAY(SYSDATE)-SYSDATE
            FROM dual;


* MS_SQL은 LAST_DAY를 지원하지 않는다. 
   - 그래서 날짜 비교함수를 이용해서 해당 말일까지 몇일이 남았는지 출력해야 한다.
     
     예시 ) select datediff(day, getdate(), dateadd(month,1,getdate())-day(getdate()));
                   ( dateadd(month,1,getdate())-day(getdate()) 는 해당달의 마지막 날을 표시해준다.) 

문제 107. 이름과 월급을 출력하는데 월급을 출력할때에 천단위를 표시하시오! 

 SQL> select ename, to_char(sal), to_char(sal,'999,999')
   from emp;

* to_char(sal) / sal 의 type 이 문자로 바뀜
    따라서.
   select ename, to_char(sal) AS aaa
     from emp 
     ORDER BY aaa ASC;
    이 코드는 숫자의 크기로 정렬되지 않는다. 

* to_char(sal, '999,999') 
   - 이는 천단위로 끊어주게 해준다. 
   - 여기서 '9'는 0~9까지 무슨 수가 와도 관계 없다는 의미 

* MS_SQL은 천단위 표시를 하기 위해서는 2번의 형변환이 필요하다 
   - 첫번째 money로 형변화, 두번째 문자로 형변환이다. 
     예시 ) select ename, replace (convert(varchar,cast(sal as money),1) , '.00','')
              from emp ;
      
           위의 예시를 설명하자면 
              1) 먼저 숫자를 money 형으로 바꾸어 주어야 한다. 
              2) 두번째는 money형을 문자로 바꾸어 주어야 한다. 
                 (이때 convert에 3번째 인자 값은 money 형의 형식을 구분한다. '1' 의 형식은 천의자리에 콤마이다. )
              3) 필요 없는 소수점을 없애주기 위해서 replace를 사용한다. 
 
* money의 문자로 변환하기 위한 style 값의 차이 비교 

값

출력

0(기본값)

소수점 왼쪽의 세 자릿수마다 쉼표를 사용하지 않으며, 소수점 오른쪽에 두자리가 나타난다
(예 : 1234.56)

1

소수점 왼쪽의 세 자릿수마다 쉼표를 사용하며, 소수점 오른쪽에 두자리가 나타납니다.
(예:1,234.56)

2

소수점 왼쪽의 세 자릿수마다 쉼표를 사용하지 않으며, 소수점 오른쪽에 네 자리가 나타납니다.
(예:1234,5678)


문제 108. 이름과 총급여 (SAL*25000000) 을 출력하는데, 총급여를 출력할때에 천단위와 백만단위를 출력되게하시오!

SQL> SELECT ename, TO_CHAR((sal*25000000),'999,999,999,999')
           FROM EMP; 

주의! 숫자를 끊어줄 때 범위를 실제 값보다 적게 잡아주면 모든 숫자가 '#'로 표시 된다.

* 숫자에 to_char 함수 사용 

문제 109. 이름, 입사일, 입사한 요일을 출력하시오! 

  SQL> SELECT ename, hiredate, TO_CHAR(hiredate,'day')
              FROM EMP;

* 지난 10년 동안 살인 사간이 가장 많이 일어난 요일이 무엇인가?
* 날짜 형식 
  - YYYY , RRRR, YY, RR : 숫자 년도 
  - YEAR : 영문으로 년도 출력
  - MM : 달
  - MONTH : 달을 영문으로 출력
  - DD : 일
  - DAY, DY : 요일 (DY는 요일이 요약해서 출력 됨)

문제 110. 현재 세션의 날짜 형식을 확인하시오! 

SQL>SELECT *
           FROM nls_session_parameters;

문제 111. 81년 12월 11일에 입사한 사원의 이름과 입사일을 출력하시오! 

SQL> SELECT ename, hiredate
            FROM EMP
             WHERE hiredate = '11/dec/81';

*현재 세션의 날짜 형식을 RR/MM/DD 로 변경
   ALTER SESSION SET nls_date_format='RR/MM/DD';

문제 112. 현재 세션의 날짜 형식을 아래와 같이 변경하고, 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하시오

SQL> SELECT ename, hiredate
            FROM EMP
            WHERE hiredate = '81/12/11';


 * RR  과 YY 의 차이
                 RR                                   YY
                 81                                    81
         1981       2081                 1981          2081
                2015                               2015
        현재 연도에서 가장           현재 세기의 연도
        가까운 연도 선택

*주로 RR 을 사용한다. 
문제 113. 현재 세션의 날짜 형식이 어떻게 되는 상관없이 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하시오 !


1. to_char
         
        select ename, hiredate
            from emp
            where to_char(hiredate, 'RR/MM/DD')='81/12/11';

* 그러나 이럴 경우 모든 데이타를 변화해야 하기 때문에 성능이 떨어지게 된다. 

2. to_date
         
         select ename, hiredate
            from emp
            where hiredate = to_date('81/12/11','RR/MM/DD');

* 이럴 경우 성능에 영향을 주지 않으면서 정확하게 검색할 수 있다.

문제 114. 87년 5월 6일 부터 93년 3월 4일 사이에 태어난 학생들의 이름과 생일을 출력하시오!

SQL> SELECT ename, birth
           FROM EMP2
           WHERE birth BETWEEN TO_DATE('87/05/06','RR/MM/DD')
                                      AND TO_DATE('93/03/04','RR/MM/DD');


* MS_SQL은 to_data 함수를 사용할 수 없다. 
  - 그래서 실제 날짜 데이터 형식과 다른 형식으로 검색해서 결과를 보고 싶다면 convert 함수를 이용해야 한다. 

    예시 )  select ename, hirth 
               from emp2 
            where hiredate between convert(varchar,'1987/05/06',111) and convert(varchar,'1993/03/04',111) ;

  
* convert 시 날짜 형식 변화 시 style 식

세기 포함 안함(yy)

세기 포함(yyyy)

표준

입력/출력**

-

0 또는 100 (*)

기본값

mon dd yyyy hh:miAM(또는 PM)

1

101

USA

mm/dd/yy

2

102

ANSI

yy.mm.dd

3

103

영국/프랑스

dd/mm/yy

4

104

독일

dd.mm.yy

5

105

이탈리아

dd-mm-yy

6

106

-

dd mon yy

7

107

-

Mon dd, yy

8

108

-

hh:mm:ss

-

9 또는 109 (*)

기본값 + 밀리초

mon dd yyyy hh:mi:ss:mmmAM(또는 PM)

10

110

USA

mm-dd-yy

11

111

일본

yy/mm/dd

12

112

ISO

yymmdd

-

13 또는 113 (*)

유럽 기본값 + 밀리초

dd mon yyyy hh:mm:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 또는 120 (*)

ODBC 표준

yyyy-mm-dd hh:mi:ss(24h)

-

21 또는 121 (*)

ODBC 표준(밀리초)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126(***)

ISO8601

yyyy-mm-dd Thh:mm:ss.mmm(스페이스 없음)

-

130*

회교식****

dd mon yyyy hh:mi:ss:mmmAM

-

131*

회교식****

dd/mm/yy hh:mi:ss:mmmAM
             

문제 115. 오늘 부터 100달 뒤에 돌아오는 날짜가 어떻게 되는가?

SQL> SELECT ADD_MONTHS(SYSDATE,100)
            FROM dual;


문제 116. 오늘 부터 100달뒤에 돌아오는 날짜의 요일을 출력하시오! 

SQL> SELECT to_char(ADD_MONTHS(SYSDATE,100),'day')
            FROM dual;


문제 117. 이름과 커미션을 출력하는데 커미션이 NULL 인 사원들은 0으로 출력되게 하시오! 

SQL> SELECT ename,comm, NVL(comm,'0')
           FROM EMP;


문제 118. 이름, 커미션을 출력하는데 커미션이 null인 사원들은 no comm이란 글씨로 출력되게 하시오! 

SQL>  select ename, comm, nvl(TO_CHAR(comm), 'no comm')
             from emp;

주의 ! select ename, nvl(comm, 'no comm') 
           from emp;
         - 이 경우  comm은 number이고  치환하는 값이 string 이기 때문에 오류가 난다. 

Tip.
   nvl 은 data가 없는 상태, data를 문자 type을 바꾸어도 null 값은 data가 없다.
   그래서 변경, 치환된다.
   즉, 자신이 변환하고 싶은 모양으로 미리 데이터를 변환 시켜놓아야 한다. 

* MS_SQL은 Oracle 의 nvl 함수에 대응하는 isnull 이 있다. 
   - 그래서 Oracle의 nvl 함수와 동일한 효과를 MS_SQL에서 얻고 싶다면 isnull 함수를 사용해야 한다. 

   예시 ) SQL> select ename, isnull(cast(comm as varchar), 'no comm') 
                   from emp ;



문제 119. 이름, mgr을 출력하는데 mgr이 null인 사원들은 no manager란 글씨로 출력되게하시오!

 SQL> SELECT ename, NVL(TO_CHAR(mgr),'no manager')
             FROM EMP;  


문제 120. 이름, 월급, 커미션, 월급+커미션을 출력하시오! 

SQL> SELECT ename, sal, comm, sal+NVL(comm,0)
            FROM EMP;

SQL> SELECT ename, sal, comm, sal+NVL(comm,0),NVL2(comm,sal+comm,sal)
          FROM EMP;
 
* nvl2 (comm, sal+comm,sal) 
   1. 만약 comm 이 null 이 아니면 sal+comm 값을 출력하고 
   2. 만약 comm 이 null 이면 sal 값을 출력해라 
   (자바스크립트의 if/else 와 비슷한듯 하다.) 


주의!
 - nvl 에서 '0'이 숫자 이기 때문에 ' '이 필요 없다. 


문제 121. 이름, 직업, 보너스를 출력하는데 보너스가
             직업이 SALESMAN이면 3000으로 출력되게하고
             직업이 SALESMAN이 아니면 0으로 출력되게하시오 !

SQL> select ename, job, decode(job,'SALESMAN',3000,0) bonus
           from emp;


*decode/ null 값이 아닌 data가 있는 값 중 특정 data를 선택해야 할 때 

* MS_SQL은 decode를 지원하지 않는다. 
  - 그래서 Oracle의 decode 식을 MS_SQL에서 구현하기 위해서는 case 문을 사용해야 한다. (But. Oracle에서도 case 문을 지원한다.) 

   예시) select ename, job, (case job when 'SALESMAN' then 3000
                                                  else 0 end ) as bonus
      from emp ;


문제 122. 이름 직업, 보너스를 출력하는데 보너스가 직업이 SALESMAN이면 3000이 출력되게하고
               직업이 ANALYST면 2500이 출력되게하고
               나머지 직업은 0으로 출력되게하시오!

  SQL>  select ename, job, decode(job,'SALESMAN',3000,
                                               'ANALYST',2500,0) bonus
               from emp; 


문제 123. 이름, 나이, 등급을 출력하는데 
              나이가 30살 이상이면 A 등급
              나이가 27살 이상이면서 30미만이면서 B등급 
              나이가 24살 이상이면서 27살 미만이면 C등급
              나머지 나이는 D 등급으로 출력하시오! 

SQL> SELECT ename, age, case when age >=30 then 'A'
                                      when age >=27 then 'B'
                                      when age >=24 then 'C'
                                             else 'D' end as 등급 
           from emp2;

* decode는 등호비교만 가능하다. 부등호 비교를 하려면 case 를 사용해야 한다.
* case는 등호 비교, 부등호 비교 모두 가능하다.
* 부등호 사용시 자동으로 그 이전까지의 범위로 제한되는 듯 하다. 
* 범위는 between and를 가지고도 설정 가능하다. 


문제 124. 이름, 월급, 부서번호, 보너스를 출력하는데
               부서번호 보너스가 부서번호가 10번이면 자신의 월급의 40%를 보너스로 출력하고
               부서번호가 20번이면 자신의 월급의 20%를 보너스로 출력하고
               나머지 부서번호는 그냥 0으로 출력하시오!

 SQL> select ename, sal, empno, case when empno = 10 then sal*0.4
                                             when empno = 20 then sal*0.2
                                                         else 0 end as bonus
   from emp; 
         
주의! case는 한 번만 쓰자! 

* 점심시간 문제: ( 라인 검사) 

내가 태어난 생일의 요일을 출력하시오! 

SQL> SELECT ename,TO_CHAR(birth,'Day')
           FROM EMP2
           WHERE ename = '송수신';

* to_char 앞 부분에는 해당 날짜만 지정해주면 자동으로 요일을 찾아서 변환해준다. 



* 마지막 문제 :
 
   점심시간 면담순서 (연장자 부터) 일주일 목, 금
   
   월, 화,  수 

   이름, 나이, 분류 (A,B,C,D)
   A는 성별이 남자이고 서울에 거주하는 학생 
   B는 성별이 여자이고 서울에 거주 하는 학생 
   C는 성별이 남자이고 서울이 아닌 학생 
   D는 성별이 여자이고 서울이 아닌 학생 

    나이가 높은 순서대로 출력하시오! 

SQL> SELECT ename, age, CASE WHEN (gender='남자' AND address LIKE '%서울%') THEN 'A'
                                        WHEN (gender='여자' AND address LIKE '%서울%') THEN 'B'
                                        WHEN (gender='남자' AND address NOT LIKE '%서울%') THEN 'C'
                                        WHEN (gender='여자' AND address NOT LIKE '%서울%') Then 'D'
                                          END AS 분류
           FROM EMP2
           ORDER BY age DESC;

SQL> SELECT ename, age, CASE WHEN (gender='남자' AND address LIKE '%서울%') THEN 'A'
                                        WHEN (gender='여자' AND address LIKE '%서울%') THEN 'B'
                                        WHEN (gender='남자' AND address NOT LIKE '%서울%') THEN 'C'
                                          else 'D' END AS 분류
           FROM EMP2
           ORDER BY age DESC;

* 괄호는 안 넣어주어도 괜찮다. 




























'ORACLE > SQL' 카테고리의 다른 글

SQL(3)- order by 복습, 단일 함수 中 문자 함수  (0) 2016.03.29
SQL(2)- as, SQL 작성 규칙, " ", distinct, 대체 인용 연산자(q'[ ]'), 날짜 출력, 날짜 세션 변경, order by, 오름차순(asc), 내림차순 (decsend), 연산자 우선순위 규칙, 테이블 생성, 데이터 입력  (0) 2016.03.23
SQL(1)- select,from, where 연산자(산술, 비교, 논리, 기타논리 (between, in, is null, like(%,_,escape)))  (0) 2016.03.15

+ Recent posts

티스토리툴바