Oracle SQL
03 숫자함수, 날짜함수, 형변환함수
ryunyryuny
2023. 10. 10. 18:21
[문제] employees 테이블에서 employee_id, last_name과 first_name은 연결해서 표시하고
(공백으로 구분) 열 별칭은 화면예 처럼 보고서 작성해 주세요.
<화면결과>
Emp# Employee Name
---------- ------------------------------
100 King Steven
101 Kochhar Neena
102 De Haan Lex
103 Hunold Alexander
104 Ernst Bruce
...
SELECT employee_id "Emp#", last_name || ' ' || first_name "Employee Name"
FROM hr.employees;
[문제] employees 테이블에서 컬럼중에 last_name, job_id를 연결해서 표시하고
(쉼표와 공백으로 구분) 열 별칭은 화면예 처럼 보고서 작성하세요.
<화면결과>
Employee and Title
-------------------------
Abel, SA_REP
Ande, SA_REP
...
SELECT last_name || ', ' || job_id "Employee and Title"
FROM hr.employees;
[문제] departments 테이블에 있는 데이터에서 department_name , manager_id 칼럼을 가지고
화면 결과처럼 출력하는 쿼리 문장을 만드세요.
<화면결과>
Department and Manager
--------------------------------------------------
Administration Department's Manager Id: 200
Marketing Department's Manager Id: 201
...
SELECT department_name || ' Department''s Manager ID: '|| manager_id
as "Department and Manager"
FROM hr.departments;
or
SELECT department_name || q'[ Department's Manager ID: ]'|| manager_id
as "Department and Manager"
FROM hr.departments;q 연산자 q'[ ]'
[문제] 2006년도 입사한 사원의 employee_id, last_name, hire_date를 출력해주세요
단 last_name을 기준으로 오름차순정렬 해주세요.
SELECT employee_id, last_name, hire_date
FROM hr.employees
WHERE hire_date BETWEEN '2006/01/01' AND '2006/12/31'
ORDER BY last_name;
[문제] 80번 department_id 사원중에 commission_pct 값이 0.2 이고
job_id는 SA_MAN인 사원의 employee_id, last_name, salary를 출력해주세요.
단 last_name을 기준으로 오름차순정렬해 주세요.
SELECT employee_id, last_name, salary
FROM hr.employees
where department_id = 80
AND commission_pct = 0.2
AND job_id = 'SA_MAN'
ORDER BY last_name; -- ORDER BY 2;
[문제] salary가 5000 ~ 12000의 범위에 속하지 않는 모든 사원의 last_name 및 salary를
출력해주세요. 단 salary을 기준으로 내림차순 정렬하세요.
SELECT last_name, salary
FROM hr.employees
WHERE salary NOT BETWEEN 5000 AND 12000
ORDER BY salary desc;
[문제] employees 테이블에 last_name 컬럼의 값 중에 "J" 또는 "A" 또는 "M"으로 시작하는
사원들의 last_name, last_name의 길이를 표시하는 query(select문) 를 작성합니다.
사원들의 last_name 기준으로 내림차순 정렬해 주세요.
SELECT last_name, length(last_name)
FROM hr.employees
WHERE last_name LIKE 'J%'
OR last_name LIKE 'A%'
OR last_name LIKE 'M%'
ORDER BY last_name desc;
---------------------------------------
WHERE last_name LIKE 'J%'
= substr(last_name, 1, 1) = 'J'
= instr(last_name,'J',1,1) = 1
[문제] employees테이블에서 department_id(부서코드)가 50번 사원들 중에
last_name에 두번째 위치에 "a"글자가 있는 사원들을 조회하세요.
SELECT *
FROM hr.employees
WHERE department_id = 50
AND last_name LIKE '_a%'; -- AND substr(last_name,2,1) = 'a'
-- AND instr(last_name,'a',2,1) = 2
[문제] 주민번호 뒷6자리부분을 아래화면과 같이 출력해주세요.
주민번호_1 주민번호_2
------------------ ------------------
210101-1234567 210101-1******
SELECT '210101-1234567' 주민번호_1,
rpad(substr('210101-1234567', 1, 8), 14, '*') 주민번호_2
FROM dual;
또는
rpad(substr('210101-1234567',1,instr('210101-1234567', '-', 1, 1) +1) ), 14, '*')
RPAD : 왼쪽부터 자리수 고정 14자리, 공란 * 채우기
SUBSTR : 기준문자에서 1번째자리에서 8개 추출
INSTR : 기준문자에서 - 단어를 1번째자리에서 1번째로 위치한 자리값 찾기 = 7 + 1
■ 숫자함수 (PLSQL로 개발된 함수)
- round : 지정된 소숫점 자릿수 값을 반올림하는 함수
1 : 소수1자리 표시, 소수2자리에서 반올림
0 : 기본값, 소수1자리에서 반올림, 소수점없애기
-1 : 10단위 표시, 1단위에서 반올림
round(숫자형, 소수점자리수)
- trunc : 지정된 소숫점 자릿수값을 버리는 함수
1 : 소수1자리 표시
0 : 기본값, 소수1자리에서 버림, 소수점없애기
-1 : 10단위 표시, 1단위에서 버림
trunc(숫자형, 소수점자리수)
- ceil : 숫자값을 가장 큰 정수로 반환하는 함수
즉, 소수점이 있는 경우 무조건 올려서 다음 정수값을 반환한다.
ceil(숫자형)
- floor : 숫자값을 가장 작은 정수로 반환하는 함수
즉, 소수점이 있는 경우 무조건 값을 내려서 정수값을 반환한다.
floor(숫자형)
- mod : 어떤 값을 나눈 나머지를 반환하는 함수
mod(숫자형, 숫자형) = mod(12, 5) = 12 / 5 = 2
- power : 거듭제곱
power(숫자형, 제곱숫자) = power(2, 3) = 2 * 2 * 2 = 8
- sqrt : 루트
sqrt(숫자형) = sqrt(9) = 3
■ 날짜함수
- sysdate : 현재 서버의 날짜를 리턴하는 함수
- systimestamp : 현재 서버의 날짜, 시간, 타임존을 리턴하는 함수
- current_date : 현재 클라이언트의 날짜를 리턴하는 함수
- current_timestamp : 현재 클라이언트의 날짜, 시간, 타임존을 리턴하는 함수
- localtimestamp : 현재 클라이언트의 날짜, 시간을 리턴하는 함수
SELECT
sysdate, -- 23/10/10
systimestamp, -- 23/10/10 12:13:17.751000000 +09:00
current_date, -- 23/10/10
current_timestamp, -- 23/10/10 12:13:17.000000000 ASIA/SEOUL
localtimestamp -- 23/10/10 12:13:17.000000000
-- 클라이언트 접속지역에 따라 다름, 가변
FROM dual;
ALTER SESSION SET TIME_ZONE = '-08:00'; -- 타임존 변경
SELECT
sysdate, -- 23/10/10
systimestamp, -- 23/10/10 13:47:15.923000000 +09:00
current_date, -- 23/10/09
current_timestamp, -- 23/10/09 20:47:15.000000000 -08:00
localtimestamp -- 23/10/09 20:47:15.000000000
FROM dual;
ALTER SESSION SET TIME_ZONE = '+09:00'; -- 한국시간으로 복구
- 날짜계산
| 날짜 + 숫자(일수) | = 날짜 |
| 날짜 - 숫자(일수) | = 날짜 |
| 날짜 - 날짜 | = 숫자(일수) |
| 날짜 + 날짜 | = 오류 |
| 날짜 + 시간 | = 날짜 시간 |
| 날짜 - 시간 | = 날짜 시간 |
| - 시/24 | |
| - 분/(24*60) , 분/1440 | |
| - 초/(24*60*60), 초/86400 | |
SELECT
systimestamp,
systimestamp + 10/24,
to_char(systimestamp + 10/24, 'yyyy-mm-dd hh24:mi:ss'),
to_char(systimestamp + 10/(24*60), 'yyyy-mm-dd hh24:mi:ss'),
to_char(systimestamp + 10/(24*60*60), 'yyyy-mm-dd hh24:mi:ss')
FROM dual;
- months_between : 두 날짜간의 개월수를 리턴하는 함수
months_between(최근날짜,과거날짜) = 양수
months_between (과거날짜,최근날짜) = 음수
SELECT employee_id,
hire_date "입사일",
trunc(sysdate - hire_date) "근무일수",
trunc(months_between(sysdate,hire_date)) "근무달수_1",
trunc(months_between(hire_date,sysdate)) "근무달수_2",
round(months_between(sysdate,hire_date)/12) "근무년수"
FROM hr.employees;
- add_months : 개월수를 더하거나 빼는 함수
add_months(날짜,개월수)
SELECT
sysdate,
add_months(sysdate,6), -- 6개월 후
add_months(sysdate,-1) -- 1개월 전
FROM dual;
- next_day : 입력한 날짜를 기준으로 찾고자하는 요일의 첫번째 날짜를 반환하는 함수
next_day(날짜, 요일) -- 요일 : 클라이언트 지역언어 기준으로 작성
SELECT sysdate, next_day(sysdate, '토요일')
FROM dual;
- last_day : 기준날짜달의 마지막 날짜를 리턴하는 함수
last_day(날짜)
SELECT sysdate,
last_day(sysdate),
last_day(add_months(sysdate,2))
FROM dual;
[문제] 20년 이상 근무한 사원들의 사원번호(employee_id), 입사날짜(hire_date), 근무개월수를 출력.
SELECT employee_id, hire_date,
trunc(months_between(sysdate,hire_date)) "근무개월수"
FROM hr.employees
WHERE trunc(months_between(sysdate,hire_date)/12) >= 20;
[문제] 사원의 last_name, hire_date 및 근무 6개월 후 월요일에 해당하는 날짜를 출력.
SELECT last_name, hire_date,
next_day(add_months(hire_date,6),'월요일') "근무 6개월 후 월요일"
FROM hr.employees;
■ 형변환 함수
- to_char : date(날짜형)을 char(문자형)으로 변환하는 함수
to_char(날짜,'날짜모델요소')
SELECT
sysdate, -- 23/10/10 , 서버기준
- 연도
to_char(sysdate, 'yyyy yy rr rrrr year'), -- 2023 23 23 2023 twenty twenty-three
to_char(sysdate, 'yyyy') || '년', -- 2023년
to_char(sysdate, 'yyyy"년"'), -- 2023년
- ' 날짜형식 "문자" '
- 월
to_char(sysdate, 'month mon mm fmmm'), -- 10월 10월 10 10
to_char(add_months(sysdate,-1), 'month mon mm fmmm'), -- 9월 9월 09 9
- fm : 선행되는 제로를 제거하는 옵션
- 일
to_char(sysdate,'ddd dd d'), -- 283 10 3
- ddd : 1/1부터 일수
- d : 일요일 1 ~ 토요일 7
to_char(sysdate, 'day dy'), -- 화요일 화
to_char(sysdate, 'dd ddth ddsp ddthsp'), -- 10 10th ten tenth
- 주
to_char(sysdate, 'ww w'), -- 41 2
- ww : 해당 연도의 주번호
- w : 해당 월의 주번호
- 분기
to_char(sysdate, 'q"분기"'), -- 4분기
to_char(sysdate, 'am pm hh hh12 hh24') -- 오후 오후 05 05 17
- am, pm 하나만 사용
to_char(sysdate, 'yyyymmdd hh24:mi.ss.sssss'), -- 20231010 17:38.01.63481
- ss.sssss : 2자리.5자리까지
FROM dual;
[문제] 사원들의 employee_id, hire_date, 입사요일을 출력. 단, 요일을 기준으로 오름차순 정렬.
SELECT employee_id, hire_date, to_char(hire_date,'day')
FROM hr.employees
ORDER BY to_char(hire_date,'d'); -- 기본값: 1일 2월 3화 4수 5목 6금 7토
SELECT employee_id, hire_date, to_char(hire_date,'day')
FROM hr.employees
ORDER BY to_char(hire_date-1,'d'); -- 월요일부터 시작[문제] 오늘 날짜를 '2023년 10월 10일 화요일' 출력.
SELECT to_char(sysdate,'yyyy"년" month dd"일" day')
FROM dual;
- to_char : number(숫자형)을 char(문자형)으로 변환하는 함수
to_char(숫자, '숫자모델형식')
SELECT
salary, -- 24000
to_char(salary,'$999,999'), -- $24,000
to_char(salary,'$000,999'), -- $024,000
to_char(salary,'$000,999.00'), -- $024,000.00
to_char(salary,'L000,999.00'), -- ￦024,000.00
to_char(salary,'L999g999d00') -- ￦24,000.00
FROM hr.employees;
to_char(salary,'L999g999d00') L : 언어 지역의 통화부호를 출력
g : 언어 지역의 금액 단위를 출력
d : 언어 지역의 소수점을 출력
SELECT
to_char(-1000,'999'), -- #### 오류 : 자리수 잘못지정
to_char(-1000,'9999'), -- -1000 음수 표현
to_char(-1000,'9999pr'), -- <1000> 음수일경우 <> 로 묶는다
to_char(-1000,'9999mi'), -- 1000- 음수 부호를 뒤에 표현
to_char(1000,'s9999') -- +1000 s 요소를 이용하여 부호 표현
FROM dual;
- to_number : (숫자로된)문자형을 숫자형으로 변환하는 함수
to_number(숫자로된문자, '숫자모델요소(생략가능)')
SELECT
1 + '2', -- 암시적으로 형변환이 되어 계산은 됨
1 + to_number('2')
FROM dual;
- to_date : (날짜로된)문자형을 날짜형으로 변환하는 함수
to_date(날짜로된문자, '날짜모델요소')
ALTER SESSION SET NLS_LANGUAGE = american; -- 미국형식으로 변환
ALTER SESSION SET NLS_TERRITORY = america;
SELECT * FROM nls_session_parameters;
NLS_LANGUAGE : AMERICAN
NLS_TERRITORY : AMERICA
NLS_DATE_FORMAT : DD-MON-RR
SELECT hire_date
FROM hr.employees
WHERE hire_date BETWEEN '06/01/01' AND '06/12/31'; -- 형식오류
↓
SELECT hire_date
FROM hr.employees
WHERE hire_date BETWEEN to_date('2006/01/01','yyyy/mm/dd') -- to_date 형변환 사용
AND to_date('2006/12/31','yyyy/mm/dd');
> 어디서나 사용가능하게 to_date 형변환 항상 사용하기
ALTER SESSION SET NLS_LANGUAGE = korean; -- 언어 지역(날짜형식) 수정
ALTER SESSION SET NLS_TERRITORY = korea; -- 통화 지역 수정
SELECT * FROM NLS_SESSION_PARAMETERS; -- 클라이언트 정보
NLS_LANGUAGE : KOREAN
NLS_TERRITORY : KOREA
NLS_DATE_FORMAT : RR/MM/DD