SQL 4일차

20230317 SQL

■ 형 변환 함수
to_char(두 가지), to_number, to_date
■ NULL 함수
nvl, nvl2, coalesce, nullif
■ 조건제어문
case 표현식, decode 함수

■ 형 변환 함수
● to_char
(1) date(날짜형)을 char(문자형)으로 변환하는 함수

• to char(날짜, ‘날짜모델요소’)

SELECT * FROM nls_session_parameters;

SELECT sysdate,
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss.sssss') day1,
to_char(sysdate, + 100, 'yyyy-mm-dd hh24:mi:ss.sssss') day2,
to_char(sysdate, - 100, 'yyyy-mm-dd hh24:mi:ss.sssss') day3,
to_char(sysdate, + 10/24, 'yyyy-mm-dd hh24:mi:ss.sssss') day4,
to_char(sysdate, + 10/(24*60), 'yyyy-mm-dd hh24:mi:ss.sssss') day5,
to_char(sysdate, + 10/1440, 'yyyy-mm-dd hh24:mi:ss.sssss') day6,
to_char(sysdate, + 10/(246060), 'yyyy-mm-dd hh24:mi:ss.sssss') day7,
to_char(sysdate, + 10/86400, 'yyyy-mm-dd hh24:mi:ss.sssss') day8
FROM dual;

=> 초 이하 단위 표기는 반드시 ‘sssss’

SELECT to_char(sysdate, 'yyyy yy rr rrrr year')
FROM dual;

=> 2023 23 23 2023 twenty twenty-three

SELECT to_char(sysdate, 'yyyy')||'년'
FROM dual;

SELECT to_char(sysdate, 'yyyy"년"') => 큰 따옴표 써야함
FROM dual;

=> 2023년

SELECT to_char(sysdate, 'month mon mm')
FROM dual;

=> 3월 3월 03

SELECT to_char(sysdate, 'month mon mm fmmm')
FROM dual;

=> 3월 3월 03 3 (fm : 선행되는 ‘0’ 제거하는 옵션)

SELECT to_char(sysdate, 'ddd dd d')
FROM dual;

=> 076 17 6
=> 그 해의 일, 그 달의 일, 그 주의 일(d: 일요일 1 ~ 토요일 7)

SELECT to_char(sysdate, 'day dy')
FROM dual;

=> 금요일 금

SELECT to_char(sysdate, 'ww iw w')
FROM dual;

=> 11 11 3
=> ww: 그 해의 주, iw: ios 기준, w: 그 달의 주

SELECT to_char(sysdate, 'q"분기"')
FROM dual;

SELECT to_char(sysdate, 'q')||‘분기’
FROM dual;

=> 1분기

SELECT to_char(sysdate, 'dd ddth ddsp ddthsp')
FROM dual;

=> 17 17th seventeen seventeenth

SELECT to_char(sysdate, 'hh hh12 hh24') => hh12의 경우 오전 오후 모름
FROM dual;

SELECT to_char(sysdate + 10/24, 'hh hh12 hh24 am pm') => 오전/오후 구분
FROM dual;

[문제21] 사원들의 사원번호, 입사한 요일을 출력하세요. 단, 요일 오름차순 정렬

SELECT employee_id "사원번호", to_char(hire_date, 'dy') "입사한 요일"
FROM employees
ORDER BY 2;

=> 이 경우 정렬 안됨

SELECT employee_id, hire_date, to_char(hire_date, 'dy')
FROM employees
ORDER BY to_char(hire_date, 'd');

=> 일요일부터 정렬 시작

SELECT employee_id, hire_date, to_char(hire_date, 'dy')
FROM employees
ORDER BY to_char(hire_date-1, 'd');

=> 월요일부터 정렬 시작

[문제22] employees(사원) 테이블에서 일요일에 입사한 사원의 정보를 조회하세요.

SELECT *
FROM employees
WHERE to_char(hire_date, 'd') = 1;

SELECT *
FROM employees
WHERE to_char(hire_date, 'day') = ‘일요일’;

SELECT *
FROM employees
WHERE to_char(hire_date, 'dy') = ‘일’;

[문제23] 오늘 날짜를 “2023년 3월 17일 금요일” 출력해주세요.

SELECT to_char(sysdate, 'yyyy"년" fmmonth dd"일" day')
FROM dual;

SELECT to_char(sysdate, 'yyyy"년" fmmm“월” dd"일" day')
FROM dual;
(memo)

SELECT to_char(sysdate+3/24, 'fmyyyy"년" mm"월" dd"일" day PM hh:mi:ss')
FROM dual;

=> ‘fm’작성한 부분 이후 모두 0 제거 적용됨
=> 2023년 3월 17일 금요일 오후 2:3:49

[문제24] 사원의 employees(사원) 테이블에 있는 last_name, hire_date 및 근무 6개월 후 첫 번째 월요일에 해당하는 급여 협상 날짜를 표시합니다. 열 레이블을 REVIEW로 지정합니다.
날짜는 “월요일, the Second of 4, 2001”과 유사한 형식으로 나타나도록 지정합니다.

SELECT last_name,
hire_date,
to_char(next_day(add_months(hire_date,6), '월요일'), 'day", the" Ddthsp "of" fmmm, yyyy') as REVIEW
FROM employees;

=> 대소문자 구분한 요소 표현시 출력 결과 동일 형태

● to_char
(2) number(숫자형)을 char(문자형)으로 변환하는 함수

• to char(숫자, ‘숫자모델요소’)

SELECT salary
FROM employees;)

● 천 단위 구분자

SELECT to_char(salary, '999,999') => 9 자리에 값 없어도 9 안 채워짐
FROM employees;

SELECT to_char(salary, '000,999') => 0 자리에 값 없으면 0 채워짐
FROM employees;

SELECT to_char(salary, '999,999.00')
FROM employees;

(memo) SELECT*FROM nls_session_parameters;

• NLS_NUMERIC_CHARACTERS . , => 소수점 표기(.) 천 단위 구분자(,)

● 통화가치 부호

1) L요소는 지역통화부호를 출력 – 우리나라 경우 원화 표시

SELECT to_char(salary, 'L999,999')
FROM employees;

2) 달러 표시는 키보드 내 달러 표시 사용

SELECT to_char(salary, '$999,999')
FROM employees;

3) 언어, 지역 설정값 변환

ALTER SESSION SET nls_language = French;
ALTER SESSION SET nls_territory = France;

ALTER SESSION SET nls_language = 'simplified chinese';
ALTER SESSION SET nls_territory = China;

ALTER SESSION SET nls_language = american;
ALTER SESSION SET nls_territory = america;

ALTER SESSION SET nls_language = english;
ALTER SESSION SET nls_territory = ‘united kingdom’; => 두 단어 이상 따옴표 필요

ALTER SESSION SET nls_language = Korean;
ALTER SESSION SET nls_territory = Korea;

SELECT to_char(hire_date, 'month mon day dy'),
to_char(salary, 'l999g999d00') => g : 콤마, d :점
FROM employees;

● 음수, 양수 부호 표현

SELECT to_char(-1000, '9999pr') => 음수일 경우 <>로 묶음
FROM dual;

SELECT to_char(-1000, '9999mi') => 음수 부호를 뒤에 표현
FROM dual;

SELECT to_char(1000, 's9999') => 부호를 표현
FROM dual;

● to_number : 문자형(숫자)을 숫자형으로 형 변환하는 함수

• to_number(문자(숫자), ‘숫자모델요소’(생략가능))

SELECT 1 + '2' => 임시적으로 형 변환되어 계산됨
FROM dual;

SELECT 1 + to_number('2','9') => 3
FROM dual;

SELECT 1 + to_number('two') => invalid number
FROM dual;

[문제25] 짝수달에 입사한 사원들의 정보를 출력해주세요.

SELECT *
FROM employees
WHERE mod(to_number(to_char(hire_date,'mm')),2)=0;

[문제26] 2006년도 홀수달에 입사한 사원들의 정보를 출력해주세요.

SELECT *
FROM employees
WHERE to_char(hire_date,'yyyy')='2006'
AND mod(to_number(to_char(hire_date, 'mm'))+1,2)=0;

=> 악성코드가 됨!!!
=> hire_date : 날짜형, ‘yyyy’ : 문자형, 형 변환시 추후 색인 불가

★오라클 임의/혹은 직접적 형 변환 주의(오라클은 데이터가 서버, R과 파이썬은 PC이므로)
SELECT *
FROM employees
WHERE hire_date BETWEEN '2006/01/01' AND '2006/12/31'
AND mod(to_number(to_char(hire_date, 'mm')),2)<>0;

◆ to_date : 문자형(날짜)를 날짜형으로 형 변환하는 함수

• to_date(문자(날짜), ‘날짜모델요소’)

SELECT to_date('2006/01/01', 'rr/mm/dd')
FROM dual;

SELECT *
FROM employees
WHERE hire_date >= to_date('2006/01/01', 'rr/mm/dd')
AND hire_date <= to_date('2006/12/31', 'rr/mm/dd')
AND mod(to_number(to_char(hire_date, 'mm')),2)<>0;

=> 미국 설정도 오류 안 남

SELECT*FROM nls_session_parameters;
=> DATE_FORMAT : RR/MM/DD

=> 이렇게 작성하면 오라클이 임의로 형 변환하여 결과값 나오도록 함
=> 어느 지역에나 통용되는 DATE 형식을 사용하는 것을 권장함

SELECT *
FROM employees
WHERE hire_date >= to_date('2006/01/01', 'yyyy/mm/dd')
AND hire_date <= to_date('2006/12/31', 'yyyy/mm/dd')
AND mod(to_number(to_char(hire_date, 'mm')),2)<>0;

=> 연(yyyy)은 네 글자로 표기하는 것을 권장함

SELECT *
FROM employees
WHERE hire_date BETWEEN '2006-01-01' AND '2006-12-31'
AND mod(to_number(to_char(hire_date, 'mm')),2)<>0;

=> 슬래시(/) 하이픈(-) 혼용 무방

(주의!)
SELECT *
FROM employees
WHERE hire_date >= to_date('2006/01/01', 'yyyy/mm/dd')
AND hire_date <= to_date('2006/12/31', 'yyyy/mm/dd')

=> F10 실행시, 2006/12/31 00:00:00 으로 입력되어, 마지막 날짜 이후 시간 데이터 누락
=> 시, 분, 초 표기 아래와 같이 명확하게 해야 함

SELECT *
FROM employees
WHERE hire_date >= to_date('2006/01/01', 'yyyy/mm/dd')
AND hire_date <= to_date('2006/12/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss')

혹은

SELECT *
FROM employees
WHERE hire_date >= to_date('2006/01/01', 'yyyy/mm/dd')
AND hire_date < to_date('2007/01/01', 'yyyy/mm/dd')

■ NULL

• 사용할 수 없거나, 할당되지 않았거나, 알 수 없는, 적용할 수 없는, 계산할 수 없는 결측값
• NULL은 0, 공백 아님

◆ nvl : NULL 값을 실제값으로 리턴하는 함수

• nvl(컬럼, 실제값)
• nvl 함수 사용시 두 인수의 타입이 일치돼야 함

SELECT employee_id,
salary,
commission_pct,
(salary*12)+(salary*12*commission_pct) annual_salary 1
(salary*12)+(salary*12*nvl(commission_pct,0)) annual_salary 2
FROM employees;

SELECT employee_id, nvl(to_char(commission_pct), 'no comm')
FROM employees;

◆ nvl2(exp1, exp2, exp3)

• 첫 번째 exp1이 NULL이 아니면 exp2를 수행, exp1이 NULL이면 exp3을 수행함

SELECT nvl2(commission_pct, (salary*12)+(salary*12*commission_pct), salary*12) annual_salary3
FROM employees;
▽
if commission_pct is not null then
(salary*12)+(salary*12*commission_pct)
else
salary*12
end if;

• exp2와 exp3는 타입이 일치돼야 함

SELECT nvl2(commission_pct, to_char((salary*12)+(salary*12*commission_pct)), 'no comm')
FROM employees;

◆ coalesce(exp1, exp2, exp3, ...., exp0)

• 첫 번째 exp1이 NULL이면 exp2를 수행, exp2도 NULL이면 exp3을 수행, exp3도 NULL이면 다음 exp을 수행

SELECT coalesce((salary*12)+(salary*12*commission_pct), salary\12, 0) annual_salary_4
FROM employees;

◆ nullif(exp1, exp2)

• exp1과 exp2가 일치하면 NULL, 일치하지 않으면 exp1을 리턴함

SELECT length(last_name),
length(first_name), nullif(length(last_name), length(first_name))
FROM employees;

■ 조건제어문
■ SQL문에서 IF문을 사용하여 조건제어문을 사용할 수 없음

• CASE 표현식, DECODE 함수

PL/SQL IF문
IF 기준값 = 비교값1 THEN 참값1
ELSE IF 기준값 = 비교값2 THEN 참값2
ELSE IF 기준값 = 비교값3 THEN 참값3
...
ELSE 기본값
END IF;

■ decode 함수 : 기준값과 비교값을 내부적으로 같다(=)라는 비교연산자를 사용함

• DECODE(기준값,
  비교값1, 참값1,
  비교값2, 참값2,
  비교값3, 참값3,
  ...
  기준값)

SELECT employee_id, salary, job_id,
decode(job_id,
'IT_PROG', salary*1.1,
'ST_CLERK', salary*1.2,
'SA_REP', salary*1.3,
salary) revised_salary
FROM employees;

■ case 표현식 : 기준값과 비교값에 대해서 모든 비교연산자를 사용할 수 있음

• CASE 기준값
  WHEN 비교값1 THEN 참값1
  WHEN 비교값2 THEN 참값2
  WHEN 비교값3 THEN 참값3
  ....
  ELSE 기본값
  END

기준값 = 비교값

• CASE 기준값
  WHEN 기준값 = 비교값1 THEN 참값1
  WHEN 기준값 = 비교값2 THEN 참값2
  WHEN 기준값 = 비교값3 THEN 참값3
  ....
  ELSE 기본값
  END

● 비교연산(=, >, >=, <, <=, <>, !=, ^=)
● 기타비교연산자(IN, BETWEEN AND, LIKE, IS NULL, IS NOT NULL)
● 논리연산(NOT, AND, OR)

• CASE 기준값
  WHEN 기준값 >= 비교값1 THEN 참값1
  WHEN 기준값 <= 비교값2 THEN 참값2
  WHEN 기준값 <> 비교값3 THEN 참값3
  ....
  ELSE 기본값
  END

SELECT employee_id, salary, job_id,
CASE job_id
WHEN 'IT_PROG' THEN salary*1.1
WHEN 'ST_CLERK' THEN salary*1.2
WHEN 'SA_REP' THEN salary*1.3
ELSE salary
END as revised_salary
FROM employees;

[문제27] 사원들의 급여를 기준으로 출력해주세요.

~ 4999 : low

5000 ~ 9999 : medium
10000 ~19999 : good
20000 ~ : excellent

SELECT employee_id, salary,
CASE
WHEN salary < 5000 THEN 'low'
WHEN salary >= 5000 AND salary < 10000 THEN 'medium'
WHEN salary >= 10000 AND salary < 20000 THEN 'good'
WHEN salary >= 20000 THEN 'excellent'
END
FROM employees;

SELECT salary,
CASE
WHEN salary BETWEEN 0 AND 4999 THEN 'low'
WHEN salary >= 5000 AND salary < 10000 THEN 'medium'
WHEN salary >= 10000 AND salary < 20000 THEN 'good'
ELSE 'excellent'
END
FROM employees;

SELECT salary,
CASE
WHEN salary < 5000 THEN 'low'
WHEN salary < 10000 THEN 'medium'
WHEN salary < 20000 THEN 'good'
ELSE 'excellent'
END
FROM employees;

[문제28] 사원들의 급여를 1000당 특수문자 하나를 출력해주세요.
급여가 10000 이상이면 ‘*’ 사용하고 10000 미만이면 ^로 표현해주세요.

SELECT salary,
CASE
WHEN salary >= 10000 THEN lpad('*', salary/1000, '*')
WHEN salary < 10000 THEN lpad('^', salary/1000, '^')
END as STAR
FROM employees;

=> 콤마(,) 잘 넣어줄 것

SELECT salary,
lpad(' ', salary/1000+1, CASE
WHEN salary >= 10000 THEN '*'
ELSE '^'
END) as STAR
FROM employees;

● DECODE 함수, CASE 표현식에서 null check 방법

• DECODE 함수에서 null 체크는 null 키워드를 사용함
• CASE 표현식에서 null 체크는 is null, is not null 연산자를 사용함

SELECT
employee_id,
salary,
commission_pct,
decode(commission_pct, null, salary*12, (salary*12)+(salary*12*commission_pct)) annual_salary_1,
case
when commission_pct is null then salary*12
else (salary*12)+(salary*12*commission_pct)
END as annual_salary_2
FROM employees;

■ yy와 rr 차이점

• yy : 현재 년도의 세기를 반영 -> 1995-10-27
• rr : 2000년도 부터는 표기법을 자동화로 변경해 줌

SELECT
to_date('95-10-27','yy-mm-dd'), => 95/10/27
to_date('95-10-27', 'rr-mm-dd') => 95/10/27
FROM dual;

SELECT
to_char(to_date('95-10-27','yy-mm-dd'), 'yyyy-mm-dd'), => 2095/10/27
to_char(to_date('95-10-27','rr-mm-dd'), 'yyyy-mm-dd') => 1995/10/27
FROM dual;

(RR 타입 기준 표)

현재연도/데이터 입력 연도	0-49	50-99
0-49	반환 날짜는 현재 세기를 반영	반환 날짜는 이전 세기를 반영
50-99	반환 날짜는 이후 세기를 반영	반환 날짜는 현재 세기를 반영

(예시)

현재연도	데이터입력날짜	yy	rr
1994	95-10-27	1995	1995
1994	17-10-27	1917	2017
2001	17-10-27	2017	2017
2048	57-10-27	2057	1957
2051	47-10-27	2047	2147

=> 연도 yy 두 자리 말고 yyyy 네 자리로 쓰자!