Database 5일차

[ 숫자함수 ]

▶ ABS(n) : n의 절대값 반환
예) ABS(3) ➔ 3, ABS(-3) ➔ 3

 

-- 숫자 함수
SELECT ABS(10), ABS(-10), ABS(-10.123)
    FROM DUAL;
    
SELECT CEIL(10.123),CEIL(10.541), CEIL(11.001)
    FROM DUAL;

SELECT FLOOR(10.123), FLOOR(10.541), FLOOR(11.001)
    FROM DUAL;
    
SELECT ROUND(10.154),ROUND(10.541), ROUND(11.001)
    FROM DUAL;

SELECT ROUND(10.154, 1),ROUND(10.154, 2), ROUND(10.154, 3)
    FROM DUAL;
    
SELECT ROUND(0, 3),ROUND(115.155, -1), ROUND(115.1555, -2)
    FROM DUAL;

▶ CEIL(n) : n과 같거나 가장 큰 정수 반환
예) CEIL(10.123) ➔ 11, CEIL(10.541) ➔ 11

 

▶ FLOOR(n) : n보다 작거나 가장 큰 정수 반환
예) FLOOR(10.123) ➔ 10, FLOOR(10.541) ➔ 10

 

▶ ROUND(n, i) : n을 소수점 기준 (i+1) 번째에서 반올림한 결과 반환
예) ROUND(10.154) ➔ 10, ROUND(10.154, 2) ➔ 10.15

• VARCHAR2(1)로 DUMM컬럼으로 만들어진 임시 테이블이다
• SYS소유이고 모든 사용자가 액세스 할 수 있게 되어 있다
• 오라클 설치와 동시에 만들어진다
• 이 테이블을 이용해 원하는 select를 테이블 없이 실행 가능하다

---

 

▶ TRUNC(n1, n2) : n1을 n2 소수점 자리에서 버림한 결과 반환
예) TRUNC(115.155) ➔ 115, TRUNC(115.155, 1) ➔ 115.1

SELECT TRUNC(115.155), TRUNC(115.155, 1), TRUNC(115.155, 2), TRUNC(115.155, -2)
    FROM DUAL;
    
SELECT POWER(3, 2), POWER(3, 3), POWER(3, 3.0001)
    FROM DUAL;
    
SELECT POWER(-3, 2)
    FROM DUAL;
    
SELECT SQRT(2), SQRT(5)
    FROM DUAL;
    
SELECT MOD(19, 4), MOD(19.123, 4.2)
    FROM DUAL;

▶ POWER(n2, n1) : n2의 n1 제곱값 반환 (n2가 음수일 경우 n1은 정수여야 함)
예) POWER(3, 2) ➔ 9, POWER(3, 3) ➔ 27

2번째 값이 실수로 들어올 수 없다  ↓

▶ SQRT(n) : n의 제곱근 반환
예) SQRT(2) ➔ 1.41421356, SQRT(5) ➔ 2.23606798

 

▶ MOD(n2, n1) : n2를 n1로 나눈 나머지 반환
예) MOD(19, 4) ➔ 3, MOD(19.123, 4.2) ➔ 2.323

---

▶ REMAINDER(n2, n1) : MOD와 같으나 연산 수식이 다름
• MOD ➔ n2 - n1 * FLOOR(n2/n1)

• REMAINDER ➔ n2 - n1 * ROUND(n2/n1)
예) REMAINDER(19, 4) ➔ -1, REMAINDER(19.123, 4.2) ➔ -1.877

SELECT REMAINDER(19,4), REMAINDER(19.123, 4.2)
    FROM DUAL;

▶ EXP(n) : 지수 함수로 e의 n제곱 값을 반환
예) EXP(2) ➔ 7.3890561

 

▶ LN(n) : 자연로그 함수로 밑수가 e인 로그 함수
예) LN(2.713) ➔ 0.998055034

 

▶ LOG(n2, n1) : n2를 밑수로 하는 n1의 로그값을 반환
예) LOG(10, 100) ➔ 2

 

---

 [ 문자함수 ]

▶ INITCAP(char) : char의 첫 문자는 대문자로, 나머지는 소문자로 반환
첫 문자 인식 기준은 공백 그리고 알파벳과 숫자를 제외한 문자
예) INITCAP('never say goodbye') ➔ Never Say Goodbye

SELECT INITCAP('never say goodbye'), INITCAP('never6say*good가bye')
    FROM DUAL;

SELECT LOWER('NEVER SAY GOODBYE'), UPPER('never say goodbye')
    FROM DUAL;
    
SELECT CONCAT('I Have', ' A Dream'), 'I Have' || ' A Dream'
    FROM DUAL;

▶ LOWER(char) : 소문자 변환 후 반환
예) LOWER('NEVER SAY GOODBYE') ➔ never say goodbye

▶ UPPER(char) : 대문자 변환 후 반환
예) UPPER('never say goodbye') ➔ NEVER SAY GOODBYE

▶ CONCAT(char1, char2) : 두 문자를 붙여 반환 “”
예) CONCAT('I Have', ' A Dream') ➔ I Have A Dream

---

[ 문자함수 ] 

▶ SUBSTR(char, pos, len) : char의 pos번째 문자부터 len 길이만큼 잘라낸 결과를 반환
예) SUBSTR('ABCDEFG', 1, 4) ➔ ABCD

SUBSTR('ABCDEFG', -1, 4) ➔ G

SELECT SUBSTR('ABCDEFG', 1, 4), SUBSTR('ABCDEFG', -3, 2)
    FROM DUAL;
    
SELECT SUBSTRB('ABCDEFG', 1, 4), SUBSTRB('가나다라마바사', 1, 4)
    FROM DUAL;
    
SELECT LTRIM('ABCDEFGABC', 'ABC'),
    LTRIM('가나다라', '가'),
    RTRIM('ABCDEFGABC', 'ABC'),
    RTRIM('가나다라', '라')
FROM DUAL;
    
SELECT LTRIM ('가나다라', '나'), RTRIM('가나다라', '다')
FROM DUAL;

 

▶ SUBSTRB(char, pos, len) : SUBSTR과 같으나 문자 개수가 아닌 바이트 수 단위
예) SUBSTRB('ABCDEFG', 1, 4) ➔ ABCD
SUBSTRB('가나다라마바사', 1, 4) ➔ 가 (한글 3byte)

▶ LTRIM(char, set) : char에서 set으로 지정된 문자열을 왼쪽 끝에서 제거 후 나머지 문자열 반환
예) LTRIM('ABCDEFGABC', 'ABC') ➔ DEFGABC

▶ RTRIM(char, set) : LTRIM과 반대로 오른쪽 끝에서 제거한 뒤 나머지 문자열을 반환
예) RTRIM('ABCDEFGABC', 'ABC') ➔ ABCDEFG

LTRIM과 RTRIM은 왼쪽 끝, 오른쪽 끝만 찾기 때문에 해당사항이 없다면 아무일도 일어나지 않는다

---

[ 문자함수 ]

▶ LPAD(expr1, n, expr2) : expr2 문자열을 n자리만큼 왼쪽부터 채워 expr1을 반환
예) LPAD('111-1111', 12, '(02)') ➔ (02)111-1111

▶ RPAD(expr1, n, expr2) : LPAD와는 반대로 오른쪽에 해당 문자열을 채워 반환
예) RPAD('111-1111', 12, '(02)') ➔ 111-1111(02)

▶ REPLACE(char, search_str, replace_str) : char에서 search_str을 찾아 이를 replace_str로 대체한 결과를 반환
예) REPLACE('나는 너를 모르는데', '나', '너') ➔ 너는 너를 모르는데

간단한 암호화를 시킬 수 있다.

▶ TRANSLATE(expr, from_str, to_str) : expr에서 from_str에 해당하는 문자를 찾아 to_str로 한 글자씩 바꾼 결과 반환
예) TRANSLATE('나는 너를 모르는데', '나는', '너를') ➔ 너를 너를 모르는데

 

---

--chap04_ex1

--1. 사원테이블(employees)에는 phone_number라는 컬럼에 사원의 전화번호가 ###.###.#### 형태로
--저장되어 있다. 여기서 처음 3자리 숫자 대신 서울 지역번호인 (02)를 붙여 전화번호를 출력하도록 쿼리를 작성해 보자

SELECT  employee_id,
--            REPLACE(phone_number, SUBSTR(phone_number, 1, 3),'(02)') --아래 LPAD와 같은 결과
            LPAD(SUBSTR(phone_number, 4), 13, '(02)') -- 4번째 글자부터 가져와서 13글자 완성
FROM employees;

 

---

--2. 현재일자 기준으로 사원테이블의 입사일자(hire_date)를 참조해서 근속년수가 10년 이상인 사원을 
--다음과 같은 형태의 결과를 출력하도록 쿼리를 작성해보자.(근속년수가 많은 사원순서대로 결과를 나오도록 하자)
----------------------------------------
--사원번호 사원명 입사일자 근속년수
----------------------------------------

SELECT EMPLOYEE_ID AS 사원번호,
       EMP_NAME AS 사원명,
       HIRE_DATE AS 입사일자,
       ROUND((SYSDATE - HIRE_DATE) / 365) AS 근속년수
FROM employees
WHERE ROUND((SYSDATE - HIRE_DATE) / 365) >= 10
ORDER BY 근속년수 DESC; -- 입사일자 기준으로 내림차순 정렬
--ORDER BY 3; --여기서 3은 3번째 컬럼인 HIRE_DATE를 말한다. 
--따라서 입사일자 기준으로 오름차순 정렬이 맞다. 윗줄과 같은 결과가 나옴

---

--3. 고객 테이블(CUSTOMERS)에는 고객 전화번호(cust_main_phone_number) 컬럼이 있다. 이 컬럼 값은
--###-###-#### 형태인데, '-' 대신 '/'로 바꿔 출력하는 쿼리를 작성해 보자

 

SELECT cust_name, cust_main_phone_number,  
    REPLACE(cust_main_phone_number, '-', '/')  new_phone_number
FROM CUSTOMERS;