숫자함수
1) 수학적 함수
- ABS(n) : n의 절대값 반환
- SIGN(n) : n의 부호에 다라 0인 경우 0, 양수이면 1, 음수이면 -1을 반환
- SQRT(n) : n의 평방근(ROOT)
- POWER(n1, n2) : n1의 m2승 값을 반환
예) 상품테이블에서 상품의 매입단가와 할인판매단가를 비교하여 이익정도를 나타낼 수 있도록 조회하시오. (이익여부는 이익이 발생되면 '정상', 이익이 없으면 '원가판매상품', 손해가 발생되면 '재고처분상품' 이라고 출력하시오.)
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_COST AS 매입단가,
PROD_SALE AS 할인판매가,
CASE WHEN SIGN(PROD_SALE - PROD_COST) = 1 THEN '정상제품'
WHEN SIGN(PROD_SALE - PROD_COST) = 0 THEN '원가판매상품'
ELSE '재고처분상품' END AS 비고
FROM PROD;
표현식(CASE WHEN THEN ~ END)
- 조건을 판단하여 처리할 명령을 다르게 선택할 때 사용( IF문과 비슷한 기능)
- SELECT 절에서 사용
예) 회원테이블에서 주민번호를 이용하여 성별을 구분하시오. 단, 대전지역에 거주하는 회원정보만 조회하시오.
Alias 회원번호, 회원명, 주소, 성별
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_ADD1||' '||MEM_ADD2 AS 주소,
CASE WHEN SUBSTR(MEM_REGNO2,1,1)='2' OR
SUBSTR(MEM_REGNO2,1,1)='4' THEN
'여성회원'
WHEN SUBSTR(MEM_REGNO2,1,1)='1' OR
SUBSTR(MEM_REGNO2,1,1)='3' THEN
'남성회원'
ELSE
'데이터 오류' END AS 성별
FROM MEMBER
WHERE MEM_ADD1 LIKE '대전%';2) GREATEST(n1, n2 [ , n3,...]), LEAST(n1, n2 [ , n3,...])
- GREATEST : 주어진 수 n1, n2 [ , n3, ...] 중 제일 큰 수를 반환
- LEAST : 주어진 수 n1, n2 [ , n3,...] 중 제일 작은 수를 반환
예)
SELECT GREATEST(20, -15, 70), LEAST('오성님', '오성순', '정은실')
FROM DUAL; --DUAL : 시스템이 제공해주는 가상의 테이블
--가나다순이 빠른 '오성님'이 제일 작다.
- 예) 회원테이블에서 마일리지가 1000미만인 회원들의 마일리지를 1000으로 부여하려 한다. 이를 구현하시오. (GREATEST 사용)
Alias 회원번호, 회원명, 마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
GREATEST(MEM_MILEAGE,1000) AS 마일리지
FROM MEMBER;
3) ROUND(n [ , 1] ) , TRUNC(n [ , 1])
- ROUND : 주어진 자료 n을 1+1 번째 자리에서 반올림하여 1자리까지 표현
- TRUNC : 주어진 자려 nDMF 1+1 번째 자리에서 자리버림하여 1자리까지 표현
- 1이 음수이면 정수부분 1자리에서 반올림(ROUND), 자리버림(TRUNC)
- 1이 생략되면 0으로 간주
예) 사원테이블에서 각 부서별 평균임금을 조회하시오. 평균임금은 소수 2자리까지 출력하시오.
Alias 부서코드, 부서명, 평균임금
SELECT A.DEPARTMENT_ID AS 부서코드,
DEPARTMENTS.DEPARTMENT_NAME AS 부서명,
ROUND(AVG(A.SALARY),2) AS 평균임금
FROM EMPLOYEES A, DEPARTMENTS
WHERE A.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_NAME
ORDER BY 1;예제) 사원테이블을 이용하여 사원들의 이번달 급여를 지급하려한다. 지급액은 보너스+급여-세금이고 보너스는 영업실적*급여이다. 또, 세금은 보너스+급여의 3%이다.
Alias 사원번호, 사원명, 부서코드, 급여, 보너스, 세금, 지급액 (소수 첫자리까지 나타내시오)
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여,
ROUND(NVL(COMMISSION_PCT*SALARY,0),1) AS 보너스,
TRUNC((SALARY+NVL(COMMISSION_PCT*SALARY,0)) * 0.03,1) AS 세금,
SALARY+ROUND(NVL(COMMISSION_PCT*SALARY,0),1) -TRUNC((SALARY+NVL(COMMISSION_PCT*SALARY,0)) * 0.03,1) AS 지급액
FROM EMPLOYEES;
4) MOD(n, c)
- n을 c로 나눈 나머지
- 내부에서 서로 다른 함수를 사용하여 나머지를 구함
MOD : n - c * FLOOR(n / c)
REMAINDER : n - c * ROUND(n / c)
FLOOR(n) : n과 같거나 작은쪽에서 제일 큰 정수(n을 초과하지 않는 최대 정수)
SELECT MOD(10,3),REMAINDER(10,3) FROM DUAL; -- 1 , 1
SELECT MOD(11,3),REMAINDER(11,3) FROM DUAL; -- 1, -1
//내부 연산
MOD(10,3) : 10 - 3*FLOOR(10/3)
10 - 3*FLOOR(3.33333)
10 - 3*3 => 10 - 9 => 1
REMAINDER(10,3) : 10 - 3*ROUND(10/3)
10 - 3*ROUND(3.3333)
10 - 3*3 => 10 - 9 => 1
MOD(11,3) : 11 - 3*FLOOR(11/3)
11 - 3*FLOOR(3.6666)
11 - 3*3 => 11 - 9 => 2
REMAINDER(11,3) : 11 - 3*ROUND(11/3)
11 - 3*ROUND(3.6666)
11 - 3*4 => 11 - 12 => -15) FLOOR(n), CEIL(n)
-
FLOOR : n을 초과하지 않는 최대 정수
-
CEIL : n과 같거나 n보다 큰 제일 작은 정수 (n이 정수일 경우 같고, 실수일 경우 제일 작은 정수)
→ 소숫점이 허용되지 않는 컬럼에 정수 데이터가 입력 된 경우 소숫점을 무조건 반올림하여 정수자료만 저장하는 경우 주로 사용 ex) 급여, 세금 등의 계산 항목에 주로 사용
예)
SELECT FLOOR(-10), FLOOR(-10.234), CEIL(-10), CEIL(-10.001) FROM DUAL;
SELECT FLOOR(10), FLOOR(10.234), CEIL(10), CEIL(10.001) FROM DUA
6) WIDTH_BUCKET(n, min, max, b)
- min과 max 사이를 b개의 구간으로 나누었을 경우 주어진 수 n이 어느 구간에 속하는지를 반환
예) 사원테이블에서 급여 2000~3000 사이의 값을 3개의 구간으로 나눌때 각 사원의 급여가 속한 구간값을 구하고, 그 값이 1인 경우 '낮은 급여', 2인 경우 '중간 급여', 3인 경우 '고 임금' 이라는 메시지를 비고난에 출력하시오.
Alias 사원번호, 사원명, 부서코드, 급여, 구간값, 비고
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여,
WIDTH_BUCKET(SALARY,2000,30000,3) AS 구간값,
CASE WHEN WIDTH_BUCKET(SALARY,2000,30000,3)='1' THEN
'낮은 급여'
WHEN WIDTH_BUCKET(SALARY,2000,30000,3)='2' THEN
'중간 급여'
ELSE '고 임금' END AS 비고
FROM EMPLOYEES;
