가상테이블 DUAL을 활용한 산술연산
DUAL은 오라클 SQL 디벨로퍼가 자체적으로 제공하는 가상의 테이블입니다.
DUAL을 이용하여, 우리는 간단한 산술연산 작업을 수행할 수 있습니다.
왜냐하면, Oracle SQL Developer에서 특정한 결과를 출력하려면, 명목상 SELECT FROM 구문의 형식을 따라야 하기 때문입니다.
1. 가상테이블 DUAL을 활용한 간단한 산술연산
1) 기본식
select 산술식1, 산술식2, ...
from dual;
2) 예제 : 4+4 계산
select 4+4 from dual;
4+4의 결과값인 8이 출력되었습니다.
가상테이블 dual의 컬럼 이름이 입력한 문장의 산술연산식(4+4)으로 되어 있습니다.
3) 예제 : 여러 개의 산술연산 수행
여러 개의 산술연산을 수행하여 일렬로 출력되게 할 수도 있습니다.
select 10*3, 2+9, 3+10, 4-100, 10/3 from dual;
정수, 음수, 소숫점 n째 자리까지의 결과 등 여러가지 산술연산 결과가 1개 행에 나란히 출력되었습니다.
4) 예제 : 시스템 현재시각 출력, 문자열 출력
산술연산 결과값 외에도 시스템 현재시각(SYSDATE), 문자열(''작은따옴표 내 문자)도 출력할 수 있습니다.
select 100*3, sysdate, 'JIMA' from dual;
5) 예제 : employees 테이블에서 커미션비율(commission_pct)이 null이 아닌 직원들의 직원번호(employee_id), 급여(salary), 커미션비율(commission_pct)과 함께 연봉(salary*12*(commission_pct+1))을 계산하여 출력하기
employees와 같은 테이블에서 기존의 컬럼을 이용하여 산술연산한 결과값을 새로운 컬럼으로 표시하여 출력할 수도 있습니다. 이 값은 기존 테이블 데이터를 변형시키는 것이 아니라, 저장된 값들을 이용해 연산하여 조회만 하는 것입니다.
select employee_id as 직원번호,
salary as 급여,
commission_pct as 커미션비율,
salary*12*(commission_pct+1) as 연봉
from employees
where commission_pct is not null;
커미션비율(commission_pct)이 0이 아닌 직원들에 대하여, 각자의 연봉이 계산되어 새로운 컬럼으로 표시되어 화면에 출력되고 있습니다.
6) 예제 : null은 산술연산이 불가능하므로, null을 연산하면 null이 나옵니다.
employees 테이블에서 커미션비율(commission_pct)이 null인 직원과 null이 아닌 직원 모두, 직원들의 직원번호(employee_id), 급여(salary), 커미션비율(commission_pct)과 함께 연봉(salary*12*(commission_pct+1))을 계산하여 출력하기
위 '5)'와 같은 예제입니다. null을 연산하면 null밖에 나오지 않는다는 것을 보여주기 위해, 아래와 같은 SQL 문장을 구성해 보았습니다. 'commission_pct is not null' 조건을 뺀 것입니다.
select employee_id as 직원번호,
salary as 급여,
commission_pct as 커미션비율,
salary*12*(commission_pct+1) as 연봉
from employees;위 SQL 문장을 실행하여 commission_pct가 null인 직원, null이 아닌 직원들의 연봉 연산 결과를 보면 아래와 같습니다.
커미션비율(commission_pct)이 null이면, 커미션비율을 산술연산에 사용해 만든 가상의 변수인 연봉도 null입니다. 그러나 커미션비율이 null이 아닌 레코드는 모두 각자의 연봉 값이 계산된 모습을 볼 수 있습니다.
null을 포함한 산술연산의 결과값도 null이기 때문입니다.
UPPER, LOWER, INITCAP : 대문자· 소문자·첫 글자만 대문자로 데이터를 정리하거나, 대소문자 구별 없이 데이터를 검색하는 문자변환 함수
Oracle SQL Developer에서 문자열의 경우 대문자와 소문자를 구별하므로, 'E'는 e로 검색했을 때 검색이 되지 않는 경우가 있습니다. 또는, 대소문자가 무분별하게 섞인 레코드들을 일제히 대문자/소문자로 변환하거나, 첫 글자만 대문자로 바꾸고 나머지는 모두 소문자로 바꾸는 등 정돈을 하여 조회할 필요가 있기도 합니다.
- 대/소문자에 상관없이 검색하기
- 레코드를 일정한 대소문자 규칙에 따라 정돈된 상태로 조회하기
위 목적을 위해 UPPER, LOWER, INITCAP 과 같은 문자변환 함수를 사용합니다.
1. 문자변환함수 UPPER, LOWER, INITCAP 기본
1) UPPER, LOWER, INITCAP 함수의 기능과 정의
(1) 단순 문자열을 변환하는 경우의 기본식
select 문자변환함수1('문자열A'),
문자변환함수2('문자열B'), ...
from dual;|
문자변환 함수
|
기능
|
용례
|
|
UPPER('문자열')
|
괄호 안 문자열을 대문자로 변경
|
UPPER('HEllo WOrld!')
= HELLO WORLD!
|
|
LOWER('문자열')
|
괄호 안 문자열을 소문자로 변경
|
LOWER('HEllo WOrld!')
= hello world!
|
|
INITCAP('문자열')
|
괄호 안 문자열의 각 단어를
- 첫 글자는 대문자로
- 나머지는 소문자로 변경
|
INITCAP('HEllo WOrld!')
= Hello World!
|
(2) 문자열이 담긴 컬럼의 데이터를 일괄 변환하는 경우의 기본식
select 문자변환함수1(컬럼이름A),
문자변환함수2(컬럼이름B), ...
from 테이블이름;|
문자변환 함수
|
기능
|
용례
|
|
UPPER(컬럼이름)
|
컬럼이 가진 레코드들을 대문자로 변경
|
UPPER(first_name)
|
|
LOWER(컬럼이름)
|
컬럼이 가진 레코드들을 소문자로 변경
|
LOWER(first_name)
|
|
INITCAP(컬럼이름)
|
컬럼이 가진 레코드의 각 단어를
- 첫 글자는 대문자로
- 나머지는 소문자로 변경
|
INITCAP(first_na
|
2) 예제 : 단순 문자열을 변환하는 경우
대/소문자 변환을 원하는 문자열을 문자변환함수에 넣어서 가상테이블 dual로 출력해 보도록 합니다.
예시의 비교를 위해, 대문자와 소문자가 섞인 HEllo, WOrld를 문자열 자리에 넣어 보았습니다.
select upper('HEllo, WOrld!'),
lower('HEllo, WOrld!'),
initcap('HEllo, WOrld!')
from dual;
각 결과값이 HELLO WORLD!, hello world!, Hello World! 로 출력되었습니다.
UPPER - 전체를 대문자로 변환(HELLO, WORLD!)
LOWER - 전체를 소문자로 변환(hello, world!)
INITCAP - 각 단어의 첫글자만 대문자, 나머지는 소문자로 변환(Hello, World!)
다른 문자열을 넣어도 마찬가지 결과입니다.
select upper('YaNis GrEi'),
lower('YaNis GrEi'),
initcap('YaNis GrEi')
from dual;
각 결과값이 YANIS GREI, yanis grei, Yanis Grei 로 출력되었습니다.
UPPER - 전체를 대문자로 변환(YANIS GREI)
LOWER - 전체를 소문자로 변환(yanis grei)
INITCAP - 각 단어의 첫글자만 대문자, 나머지는 소문자로 변환(Yanis Grei)
* 이하의 예제에서는 hr 연습계정의 employees 테이블을 사용합니다.
2) 예제 : 문자열이 담긴 컬럼의 데이터를 일괄 변환하는 경우
- employees 테이블의 first_name, last_name 컬럼의 데이터를 각각 대문자, 소문자, 첫 글자만 대문자인 경우로 일괄 변경해보기
first_name, last_name 컬럼을 대상으로 각각 UPPER, LOWER, INITCAP 문자변환 함수를 사용해 봅니다.
select upper(first_name),
upper(last_name),
lower(first_name),
lower(last_name),
initcap(first_name),
initcap(last_name)
from employees
employees 테이블의 first_name, last_name컬럼의 레코드가 각각
- 전부 대문자(UPPER)
- 전부 소문자(LOWER)
- 첫 글자는 대문자, 나머지는 소문자(INITCAP)
로 변환된 모습을 확인할 수 있습니다.
2. 대소문자 구별 없이 레코드를 검색하는 방법 :
WHERE 조건절에 UPPER, LOWER, INITCAP을 사용
Oracle SQL Developer 문자열은 대소문자를 구별하므로, 지금까지의 방법으로는 E로 검색하면 e가 검색되지 않는 문제가 있었습니다. 하지만 이 문제는 UPPER, LOWER, INITCAP을 WHERE 조건절에 사용하는 것으로 해결할 수 있습니다.
예를 들어 이름이 제니퍼(Jennifer)인 사원을 찾으려 한다면,
테이블에 JENNIFER, jennifer, Jennifer, JeNNiFer, jenNIfEr 어떤 식으로 대소문자가 섞여 입력이 되어 있어도 문제 없이 찾을 수 있게 됩니다.
UPPER를 사용하든, LOWER를 사용하든, INITCAP을 사용하든 결과는 같습니다.
1) 검색의 기본식
select 컬럼이름1, 컬럼이름2, ...
from 테이블이름
where UPPER(컬럼이름A)='검색하려는 문구를 전부 대문자로';
select 컬럼이름1, 컬럼이름2, ...
from 테이블이름
where LOWER(컬럼이름A)='검색하려는 문구를 전부 소문자로';
select 컬럼이름1, 컬럼이름2, ...
from 테이블이름
where INITCAP(컬럼이름A)='검색할 문구 각 단어 첫글자는 대문자, 나머지는 소문자로';
2) 예제 : employees 테이블에서 이름이 제니퍼(jennifer)인 직원의 레코드를 검색하기
(1) UPPER 함수를 사용한 검색
select *
from employees
where upper(first_name)='JENNIFER';위 SQL 문장의 뜻은, '대문자로 바꾸었을 때 이름이 JENNIFER인 row의 모든 자료를 검색'한다는 것입니다.
테이블에 문자열이 Jennifer로 입력되어 있어도, JEnNifer로 입력되어 있어도 구별 없이 검색됩니다.
JENNIFER로 검색했음에도, 이름이 Jennifer로 되어 있는 레코드가 검색되었습니다.
(2) LOWER 함수를 사용한 검색
select *
from employees
where lower(first_name)='jennifer';위 SQL 문장의 뜻은, '소문자로 바꾸었을 때 이름이 jennifer인 row의 모든 자료를 검색'한다는 것입니다.
테이블에 문자열이 Jennifer로 입력되어 있어도, JEnNifer로 입력되어 있어도 구별 없이 검색됩니다.
jennifer로 검색했음에도, 이름이 Jennifer로 되어 있는 레코드가 검색되었습니다.
(3) INITCAP 함수를 사용한 검색
select *
from employees
where initcap(first_name)='Jennifer';위 SQL 문장의 뜻은, '첫글자를 대문자, 나머지는 소문자로 바꾸었을 때 Jennifer가 되는 row의 모든 자료를 검색'한다는 것입니다.
테이블에 문자열이 JENNIFER, jennifer, JEnNifer로 입력되어 있어도 구별 없이 검색됩니다.
ROUND : 결과값을 반올림해 출력하는 반올림 함수
예를 들어 급여 평균(avg(salary))을 계산하였더니 소수점 아래 수십자리까지 출력되는 경우가 있습니다. 이 경우, 결과값을 정돈하기 위해 반올림(ROUND), 버림(TRUNC), OO보다 작은 최대정수(FLOOR), OO보다 큰 최소정수(CEIL)과 같은 함수를 사용합니다.
ROUND함수는 결과값을 지정한 자릿수까지 반올림하여 정돈해 주는 기능을 수행합니다.
* 함께 알면 좋은 함수들 :
- GROUP BY (그룹화)
https://blog.naver.com/regenesis90/222179953582
- TRUNC (버림)
https://blog.naver.com/regenesis90/222180666811
- FLOOR, CEIL (~보다 작은 최대 정수, 보다 큰 최소 정수)
https://blog.naver.com/regenesis90/222180751602
1. ROUND 함수의 기본 정의
1) 기본식
(1) 숫자를 반올림한 산술연산을 가상테이블 DUAL로 출력하려는 경우
select round(숫자, 자릿수), ...
from dual;
(2) 컬럼의 레코드 데이터 검색결과를 일괄 반올림하여 정돈된 결과를 조회하려는 경우
select round(컬럼이름1, 자릿수), ...
from 테이블이름;
(3) 그룹화된 집계함수 결과값을 반올림하여 정돈된 결과를 조회하려는 경우
select round(집계함수A(컬럼이름1), 자릿수), ...
from 테이블이름
group by 컬럼이름Z, ...
order by 컬럼이름Z;이 외에도 ROUND함수를 사용하여 다양한 환경과 상황에서 반올림된 결과를 얻을 수 있을 것입니다.
2) SQL에서 ROUND 함수의 표현
그렇다면, 위 기본식에서 '자릿수'란 어떤 의미이며, 어떤 결과를 가져오는지 숙지할 필요가 있습니다.
- 자릿수가 N : 소숫점 아래 N째 자리까지 반올림하여 표시
- 자릿수가 -N : 반올림하여 10의 N승 자리까지 표시
- 자릿수가 0 : 반올림하여 1의 자리까지 표시
(* N은 양의 정수)
|
ROUND함수
|
자릿수
|
의미
|
결과
|
|
ROUND
(12.3456, 3)
|
3
|
소숫점 아래 셋째 자리까지 표시
|
12.346
|
|
ROUND
(12.3456, 2)
|
2
|
소숫점 아래 둘째 자리까지 표시
|
12.35
|
|
ROUND
(12.3456, 1)
|
1
|
소숫점 아래 첫째 자리까지 표시
|
12.3
|
|
ROUND
(12.3456, 0)
|
0
|
1의 자리까지 표시
|
12
|
|
ROUND
(12.3456, -1)
|
-1
|
10(10¹)의 자리까지 표시
|
10
|
|
ROUND
(123.456, -2)
|
-2
|
100(10²)의 자리까지 표시
|
100
|
|
ROUND
(1234.56, -3)
|
-3
|
1000(10³)의 자리까지 표시
|
1000
|
2. 예제 : ROUND 함수를 이용한 출력 결과의 정리
1) 가상테이블 DUAL에서의 산술연산 시 ROUND 결과 비교
(1) 예제 : 1234.5678을
1의 자리까지, 소숫점 아래 1째 자리까지, 소숫점 아래 2째 자리까지 반올림하여 표시하기
select 1234.5678,
round(1234.5678),
round(1234.5678, 1),
round(1234.5678, 2)
from dual;
round(1234.5678)은 round(1234.5678, 0)과 같습니다. (* 자릿수가 0이면 생략 가능합니다)
위 SQL 문장을 Ctrl+Enter를 눌러 실행해 봅니다.
각각 반올림한 결과가 출력되었습니다. 설정된 자릿수만큼의 범위만큼(ex. 2의 경우 소숫점 아래 2째 자리까지) 결과에 표기되고 있습니다.
(2) 예제 : 1234.5678을
1의 자리까지, 10의 자리까지, 100의 자리까지 반올림하여 표시하기
select 1234.5678,
round(1234.5678),
round(1234.5678, -1),
round(1234.5678, -2)
from dual;자릿수가 음수인 경우가 됩니다. 위 문장을 Ctrl+Enter로 실행하면 Oracle SQL Developer는 아래와 같은 결과를 출력합니다.
각각 1의 자리까지, 10의 자리까지, 100의 자리까지 반올림되어 출력된 결과값이 나타납니다.
2) 집계함수 결과 출력 시 ROUND 함수의 사용 예제 :
employees 테이블에서 각 부서별 부서번호, 직원수, 급여평균을 구하되 급여평균은 소숫점 아래 둘째 자리까지 표시하기
- GROUP BY로 부서별 집계정보를 구하고(COUNT, AVG),
- AVG 집계함수의 결과값은 ROUND를 이용해 소숫점 아래 둘째자리까지 반올림하여 표기해 주어야 합니다
select department_id as 부서번호,
count(employee_id) as 직원수,
round(avg(salary), 2) as 급여평균
from employees
group by department_id
order by department_id;
급여평균(AVG(salary)) 중 소수인 레코드의 경우 소숫점 아래 둘째 자리까지 반올림되어 표기되었습니다.
TRUNC : 결과값을 '버림'해 출력하는 버림 함수 (≠ TRUNCATE)
TRUNC함수는 결과값을 지정한 자릿수까지 버림하여 정돈해 주는 기능을 수행합니다.
* 함께 알면 좋은 함수들 :
- GROUP BY (그룹화)
https://blog.naver.com/regenesis90/222179953582
- ROUND (반올림)
https://blog.naver.com/regenesis90/222180576630
- FLOOR, CEIL (~보다 작은 최대 정수, 보다 큰 최소 정수)
https://blog.naver.com/regenesis90/222180751602
1) 기본식
(1) 숫자를 소수점 아래 N째 자리까지 버림하여 정리한 산술연산 결과를 가상테이블 DUAL로 출력하려는 경우
select trunc(숫자, 자릿수N), ...
from dual;
(2) 컬럼의 레코드 데이터 검색결과를 소수점 아래 N째 자리까지 일괄 버림하여 정돈된 결과를 조회하려는 경우
select trunc(컬럼이름1, 자릿수N), ...
from 테이블이름;
(3) 그룹화된 집계함수 결과값을 버림하여 정돈된 결과를 조회하려는 경우
select trunc(집계함수A(컬럼이름1), 자릿수), ...
from 테이블이름
group by 컬럼이름Z, ...
order by 컬럼이름Z;이 외에도 TRUNC 함수를 사용하여 다양한 환경과 상황에서 버림한 결과를 얻을 수 있을 것입니다.
* ROUND 함수와 문법이 같습니다.
※ 주의 : TRUNC 함수와 TRUNCATE 함수는 다른 것입니다.
2. 예제 : TRUNC 함수를 이용한 출력 결과의 정리
1) 가상테이블 DUAL에서의 산술연산 시 TRUNC 결과 비교
(1) 예제 : 987.6543을
1의 자리까지, 소숫점 아래 1째 자리까지, 소숫점 아래 2째 자리까지 버림하여 표시하기
select 987.6543,
trunc(987.6543, 0),
trunc(987.6543, 1),
trunc(987.6543, 2)
from dual;trunc(987.6543)은 trunc(987.6543, 0)과 같습니다. (* 자릿수가 0이면 생략 가능합니다)
위 SQL 문장을 Ctrl+Enter를 눌러 실행해 봅니다.
각각 버림한 결과가 출력되었습니다. 설정된 자릿수만큼의 범위만큼(ex. 2의 경우 소숫점 아래 2째 자리까지) 결과에 표기되고 있습니다.
(2) 예제 : 987.6543을
1의 자리까지, 10의 자리까지, 100의 자리까지 버림하여 표시하기
select 987.6543,
trunc(987.6543, 0),
trunc(987.6543, -1),
trunc(987.6543, -2)
from dual;
각각 1의 자리까지, 10의 자리까지, 100의 자리까지 반올림되어 출력된 결과값이 나타납니다.
2) 집계함수 결과 출력 시 TRUNC 함수의 사용 예제 :
employees 테이블에서 각 부서별 부서번호, 직원수, 급여평균을 구하되 급여평균은 소숫점 아래 셋째 자리까지 버림하여 표시하기
- GROUP BY로 부서별 집계정보를 구하고(COUNT, AVG),
- AVG 집계함수의 결과값은 TRUNC를 이용해 소숫점 아래 셋째 자리까지 버림하여 표기해 주어야 합니다.
select department_id as 부서번호,
count(employee_id) as 직원수,
trunc(avg(salary), 3) as 급여평균
from employees
group by department_id
order by department_id;
급여평균(AVG(salary)) 중 소수인 레코드의 경우 소숫점 아래 셋째 자리까지 버림하여 표기되었습니다.
FLOOR, CEIL : ' ■ 보다 작은 최대 정수 ' (FLOOR), ' ■보다 큰 최소 정수 ' (CEIL)를 찾는 함수들
FLOOR와 CEIL 함수가 출력하는 결과값은 무조건 정수입니다.
- FLOOR : 투입한 숫자보다 작은 최대 정수 (1의 자리까지 버림한 정수)
- CEIL : 투입한 숫자보다 큰 최소 정수 (1의 자리까지 올림한 정수)
이 또한, 결과값이 복잡한 소수로 나타나서 정돈이 필요하거나, 가까운 정수값이 필요한 경우에 사용합니다
1. FLOOR, CEIL의 개념과 정의
1) FLOOR, CEIL의 정의
- FLOOR : 투입한 숫자보다 작은 최대 정수 (1의 자리까지 버림한 정수)
- CEIL : 투입한 숫자보다 큰 최소 정수 (1의 자리까지 올림한 정수)
FLOOR(X) ≤ X ≤ CEIL(X)
만약 투입한 숫자 X가 정수라면, FLOOR(X)=X=CEIL(X)가 될 것입니다.
투입한 숫자 X가 음수(-)여도 마찬가지입니다.
단, 양수일 때와 절대값 차이가 있으므로 주의를 요합니다.
2) FLOOR, CEIL을 사용할 경우 기본식
(1) 가상테이블 DUAL에서 임의의 숫자에 대한 FLOOR, CEIL 구하는 경우
select floor(숫자), ceil(숫자), ...
from dual;
(2) 특정한 컬럼의 데이터들에 대한 FLOOR, CEIL 구하는 경우
select floor(컬럼이름1), ceil(컬럼이름2), ...
from 테이블이름;
(3) GROUP BY로 집계된 정보에 대한 FLOOR, CEIL을 구하는 경우
select floor(집계함수A(컬럼이름1)), ceil(집계함수B(컬럼이름2)), ...
from 테이블이름
group by 컬럼이름Z, ...
order by 컬럼이름Z;
* FLOOR와 CEIL의 결과값은 항상 정수이므로, TRUNC나 ROUND와 달리 별도의 자릿수를 지정하지 않습니다.
2. 예제 : FLOOR, CEIL 결과값 비교
1) 예제 : 가상테이블 DUAL을 사용하여, 456.789의 FLOOR, CEIL 값 출력하기 (투입하는 숫자가 양수인 경우)
select 456.789,
floor(456.789),
ceil(456.789)
from dual;
FLOOR(456.789) = 456
CEIL(456.789) = 457
출력되었습니다.
FLOOR(X) ≤ X ≤ CEIL(X)
이 순서 관계를 유의해 주세요.
2) 예제 : 가상테이블 DUAL을 사용하여, -456.789의 FLOOR, CEIL 값 출력하기 (투입하는 숫자가 음수인 경우)
select 456.789,
floor(-456.789),
ceil(-456.789)
from dual;
FLOOR(-456.789) = -457
CEIL(-456.789) = -456
출력되었습니다.
양수의 경우와 비교하여 결과 절대값이 다르지만, 같은 원리로 나타난 결과값입니다.
FLOOR(X) ≤ X ≤ CEIL(X)
X가 음수이든 양수이든, 이 순서 관계를 유의해 주세요.
3) 예제 : employees 테이블의 commission_pct 컬럼에서, null이 아닌 레코드들에 대한 FLOOR, CEIL, ROUND, TRUNC 결과값 검색 및 비교
ROUND는 반올림, TRUNC는 버림 함수입니다. 소수점 둘째 자리까지 나타내기로 합니다.
select commission_pct,
round(commission_pct,2) as ROUND,
trunc(commission_pct,2) as TRUNC,
floor(commission_pct) as FLOOR,
ceil(commission_pct) as CEIL
from employees
where commission_pct is not null;컬럼 이름이 복잡해지므로 Alias를 사용해 별칭을 각각 ROUND, TRUNC, FLOOR, CEIL이라고 붙였습니다.
또한 commission_pct가 null인 경우에는 ROUND, TRUNC, FLOOR, CEIL이 모두 null이 되므로 WHERE 조건절을 이용해 commission_pct가 null인 row들을 제거하였습니다.
commission_pct는 0 이상 1 미만의 값을 갖는 소숫점대의 숫자들입니다.
- 0.XX보다 큰 최소의 정수(CEIL) : 1
- 0.XX보다 작은 최대 정수(FLOOR) : 0
따라서, commission_pct가 0.XX대인 모든 레코드에서 FLOOR는 컬럼의 값이 0, CEIL은 컬럼의 값이 1로 나타나고 있습니다.
MOD, POWER : 나눗셈, 나머지, 제곱 구하기
오라클 SQL 디벨로퍼에서 몫, 나머지, 제곱 값을 구할 경우, '/', 'MOD함수', 'POWER함수'를 사용합니다.
특정 변수들 간에 서로를 나누어 비중, 백분율, 비율, 퍼센트 등을 구할 때 /를 사용했었지만,
그 외에 나머지와 제곱을 구하는 MOD, POWER도 알아두는 것이 좋습니다.
1. /, MOD, POWER의 개념 및 정의
1) 정의
|
SQL 표현 또는 함수
|
의미
|
|
A / B
|
A를 B로 나눈 값
|
|
MOD(A, B)
|
A를 B로 나눈 나머지
|
|
POWER(A, B)
|
A의 B승, A의 B제곱
|
2) 기본식
(1) 가상테이블 DUAL에서의 단순 산술연산
select 숫자A/숫자B,
mod(숫자C, 숫자D),
power(숫자E, 숫자F), ...
from dual;
각각
- 숫자A를 숫자B로 나눈 몫
- 숫자C를 숫자D로 나눈 나머지
- 숫자E의 숫자F만큼 제곱한 값
이 출력될 것입니다.
(2) 컬럼 간의 연산
이 경우 컬럼의 데이터 유형은 연산이 가능한 숫자(numbers)여야 합니다.
select 컬럼이름1/컬럼이름2,
mod(컬럼이름3, 컬럼이름4),
power(컬럼이름5, 컬럼이름6), ...
from 테이블이름;- 컬럼1의 레코드를 컬럼2로 나눈 값
- 컬럼3의 레코드 값을 컬럼4로 나눈 나머지
- 컬럼5의 레코드값을 컬럼6의 레코드만큼 제곱한 값
이 계산되어 컬럼으로 나타날 것입니다.
2. 예제 : Oracle SQL Developer에서의 /, MOD, POWER 사용
1) 예제 : 가상테이블 DUAL을 사용한 산술연산으로 나눈 값, 나머지, 제곱 값 구하기
ⓐ 100을 200으로 나눈 값
ⓑ 324를 100으로 나눈 나머지
ⓒ 12의 3제곱(12³)
select 100/200,
mod(324, 100),
power(12, 3)
from dual;
2) 예제 : 가상테이블 DUAL에서 POWER함수를 이용해 제곱근 구하기
ⓐ 3의 (1/2)승 (3의 제곱근)
ⓑ 3의 (1/3)승 (3의 3제곱근)
ⓒ 4의 제곱근
POWER로 제곱근도 구할 수 있습니다. 하지만 안타깝게도 허수는 구할 수 없습니다.
(ex. power(-3, 1/2) 입력 시 '인수의 범위를 벗어난' ORA-01428 오류가 발생합니다.)
예시의 문제를 가상테이블 DUAL에서 구하는 SQL 문장을 구성하면 아래와 같습니다.
select power(3, 1/2),
power(3, 1/3),
power(4, 1/2)
from dual;
큰 문제는 없지만, 4의 제곱근이 '2'로 떨어지는 정수가 아니라, 1.99999999...로 나타났습니다.
물론 무한히 순환하는 소수 1.99999....는 2와 같은 값이기는 합니다.
3) 예제 : 특정한 컬럼 간의 계산으로 비율 구하기
emp테이블에서 수당(comm)을 받는 직원들에 대하여,
- 연봉을 계산하고(sal*12+comm)
- 연봉 대비 수당 백분율을 소수점 아래 첫째 자리까지 반올림하여 구하기
select empno as 직원번호,
sal*12+comm as 연봉,
comm as 수당,
round((comm/(sal*12+comm))*100,1) as 연봉대비수당백분율
from emp
where comm is not null;
컬럼 간의 비율, 백분율 계산이 되었습니다.
4) 예제 : 특정한 컬럼 간의 계산으로 MOD, POWER 구하기
계산을 위한 계산
계산할 수치에 의미는 없지만, 컬럼 간 MOD와 POWER를 사용해 보기 위해 다음을 계산합니다.
ⓐ sal을 comm으로 나눈 값을 소숫점 아래 1째 자리까지 표시
ⓑ sal을 comm 으로 나눈 나머지
ⓒ comm의 제곱
select round(sal/comm,1),
mod(sal,comm),
power(comm,2)
from emp
where comm is not null;comm 이 null이면 각종 함수 연산 결과도 모두 null이 되므로, WHERE 조건절을 이용해 comm이 null인 레코드는 제외하고 검색하도록 합니다.
컬럼 간에도 MOD, POWER가 계산되었습니다.
'Structured Query Language > Oracle SQL' 카테고리의 다른 글
| Oracle SQL 기본_05 (0) | 2023.04.20 |
|---|---|
| Oracle SQL 기본_04 (0) | 2023.04.20 |
| Oracle SQL 기본_02 (0) | 2023.04.19 |
| Oracle SQL 기본_01 (0) | 2023.04.18 |
| SQL 문제 - null 값 처리하기 (0) | 2023.04.17 |
