SQL공부 #4

1.
시스템 날짜 포맷 변경
alter session set nls_date_format = 'YYYYMMDDHH24MISS';
--2010년 5월 19일 오후7시 53분 : 20100519195300 이렇게 표현됨
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
--2010년 5월 19일 오후7시 53분 : 2010-05-19 19:53:00 이렇게 표현됨

2.
select empno, ename, sysdate, hiredate,
round(sysdate - hiredate) as days,
--오늘날짜와 입사일을 빼고 반올림함. 입사후 오늘까지의 날짜
round((sysdate - hiredate)/7) as weeks,
--입사 후 오늘까지의 날짜를 7로 나눔. 몇주가 지났는지
ceil(months_between(sysdate, hiredate)) as months
--오늘날짜와 입사일 사이에 몇개월이 지났는지, 함수사용
from emp;

3.
select substr(ename, '1', '3') from emp;
select substr(ename, 1, 3) from emp;
--같은 결과

4.
select to_char(hiredate, 'Year Month DD')
from emp;

select to_char(hiredate, 'fmYear Month DD')                      --> fm(fill

mode) element는 "중간에 포함된 스페이스" 및 "선행 제로"를 제거한다.
from emp;

select to_char(hiredate, 'YEAR Year year')
from emp;

select to_char(hiredate, 'Month Mon Day Dy')
from emp;

select to_char(hiredate, 'YYYY Q MM RM Rm rm WW W DDD DD D day')
from emp
order by hiredate;

select sysdate, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
from dual;

select sysdate, to_char(sysdate, 'sssss')
from dual;

select sysdate, to_char(sysdate, 'Ddsp Ddth Ddspth Ddthsp DD ')
from dual;

select to_char(sysdate, 'YYYYsp')
from dual;

select to_char(sysdate, 'fm "Today is" Year Month Day ') as day
from dual;

# SQL*Plus에서 중복값 출력을 억제하는 명령 : break 명령
break on Quarter skip 1
select to_char(hiredate, 'Q') as Quarter, e.*             --> 분기를 기준으로

정렬하는 예제
from emp e                                            
order by to_char(hiredate, 'Q');

clear break
--앞서 설정한 break를 지움

5.
# 문제 : 아래 쿼리의 결과는 일요일이 먼저 출력된다. 요구사항이 변경될

경우 어떻게 할 것인가?

    select to_char(hiredate, 'D Day') as Day, e.*
 from emp e
 order by to_char(hiredate, 'D');

-- monday가 가장 먼저 출력되도록

 select to_char(hiredate, 'D Day') as col, translate(to_char(hiredate,

'D'), 1, 8) as col
 from emp e
 order by translate(to_char(hiredate, 'D'), 1, 8);

 select hiredate, to_char(hiredate, 'D Day'), to_char(hiredate - 1, 'D

Day'), to_char(hiredate - 2, 'D Day')
 from emp;

 select hiredate, to_char(hiredate, 'Day')                          

                        --> 교재의 답
 from emp
 order by to_char(hiredate - 1, 'D');

        -- 수요일부터 출력이 되도록 하려면...

 select to_char(hiredate, 'D Day') as col,  translate(to_char

(hiredate, 'D'), 1, 8)  as col
 from emp e
 order by translate(to_char(hiredate, 'D'), '1234567', '5671234');

 select hiredate, to_char(hiredate, 'Day')
 from emp
 order by to_char(hiredate - 3, 'D');

6.
select sal, to_char(sal, '999,999.99'), to_char(sal, '000,000.00')
from emp;
-- 아래의 결과가 나옴
       SAL TO_CHAR(SAL TO_CHAR(SAL
---------- ----------- -----------
       800      800.00  000,800.00

7.
  select sal, to_char(sal, '$999,999.99'), to_char(sal, 'L000,000.00',

'nls_currency=\')
  from emp;
--9를 사용하면 자리수에 맞춰서 0이 없어지지만, 0을 사용하면 자리수에 맞춰서 빈

칸에 0을 채운다.
--nls_currency = \의 \를 바꾸면 L이 바뀐다.

8.
<<<문제발견>>>
alter session set nls_date_format = 'DD-MON-YY';
--날짜포맷 변경1, 'DD-MON-YY'
select * from emp where hiredate < '01-jan-82';
--01-jan-82보다 작은, 즉 이전의 사원을 select
--14개가 출력됨, 83년입사자도 나옴
--82년 1월 1일 이전의 입사자는, 2082년 1월 1일 이전의 입사자가 검색된다. 따라서 모두 검색된다.
select * from emp where hiredate < '01-jan-1982';
--01-jan-1982보다 작은, 즉 이전의 사원을 select
--11개 출력됨, 82년 1월 1일 이전의 사원만 select
--연도가 YY포맷, 즉, 20**포맷이지만 1982년 이라고 명시해줬으므로 정확한 검색결과가 나옴
alter session set nls_date_format = 'DD-MON-RR';
--날짜포맷 변경2, 'DD-MON-RR'
select * from emp where hiredate < '01-jan-82';
select * from emp where hiredate < '01-jan-1982';
--연도포맷을 RR로 했기때문에 82년 이라고 하면 1982년으로 인식한다.
--따라서 82년이든 1982년이든 모두 1982년으로 인식하여,
--둘다 1982년 1월 1일 이전 입사자만 select

<<<확인해보자>>>
drop table t1 purge;
-- t1테이블을 새로 만들기 위해 지움
create table t1 (col1 number, col2 date);
-- t1테이블을 만듬
alter session set nls_date_format = 'DD-MON-YY';
--날짜포맷 변경1, 'DD-MON-YY'
insert into t1 values (1, '01-jan-82');
--t1에 첫번째 데이터 입력
alter session set nls_date_format = 'DD-MON-RR';
--날짜포맷 변경2, 'DD-MON-RR'
insert into t1 values (2, '01-jan-82');
--t1에 두번째 데이터 입력, 첫번째 데이터와 같은 것을 입력
select col1, col2, to_char(col2, 'YYYY/MM/DD')
from t1;
--t1을 select해보면,
날짜포맷의 형태가 'DD-MON-YY' 일때 '01-jan-82'의 형태로 입력된 데이터는 2082/01/01 가 입력되고,
날짜포맷의 형태가 'DD-MON-RR' 일때 '01-jan-82'의 형태로 입력된 데이터는 1982/01/01 가 입력된다.

<<<결론>>>
YY일 때는 01년은 2001년을 나타낸다.
RR일 때는 01년은 1901년을 나타낸다.