숫자 함수 및 SQL 함수 정리

ROUND 함수

ROUND 함수는 숫자를 소수점 N자리로 반올림할 때 사용됩니다.

사용 구문

SELECT ROUND(number, n) FROM DUAL;

• number: 반올림할 숫자.
• n: 반올림할 소수점 자리수 (생략되면 0).

예시

SELECT
    ROUND(45.923),          -- 46 (소수점 첫째 자리에서 반올림)
    ROUND(45.923, 0),       -- 46 (소수점 첫째 자리에서 반올림)
    ROUND(45.923, 2),       -- 45.92 (소수점 셋째 자리에서 반올림)
    ROUND(45.923, -1)       -- 50 (정수 자리에서 반올림)
FROM DUAL;

TRUNC 함수

TRUNC 함수는 소수점 이하를 절삭할 때 사용됩니다.

사용 구문

SELECT TRUNC(number, n) FROM DUAL;

• number: 절삭할 숫자.
• n: 절삭할 소수점 자리수 (생략되면 0).

예시

SELECT
    TRUNC(45.923),           -- 45 (소수점 첫째 자리에서 절삭)
    TRUNC(45.923, 0),        -- 45 (소수점 첫째 자리에서 절삭)
    TRUNC(45.923, 2),        -- 45.92 (소수점 셋째 자리에서 절삭)
    TRUNC(45.923, -1)        -- 40 (정수 자리에서 절삭)
FROM DUAL;

ABS, CEIL, FLOOR, MOD 함수

설명

• ABS: 절대값 반환.
• CEIL: 올림값 반환.
• FLOOR: 내림값 반환.
• MOD: 나머지 값 반환.

예시

SELECT
    ABS(-45),                -- 45 (절대값)
    CEIL(3.14),              -- 4 (올림값)
    FLOOR(3.14)              -- 3 (내림값)
FROM DUAL;

SELECT
    5/3,                     -- 1.6667 (몫)
    MOD(5, 3)                -- 2 (나머지)
FROM DUAL;

날짜 함수

SYSDATE와 SYSTIMESTAMP

• SYSDATE: 현재 날짜와 시간을 반환.
• SYSTIMESTAMP: 년월일 시분초를 포함한 현재 타임스탬프 반환.

예시

SELECT SYSDATE FROM DUAL;       -- 현재 년월일
SELECT SYSTIMESTAMP FROM DUAL;  -- 현재 년월일 시분초

날짜 연산

날짜는 일 단위로 연산할 수 있습니다.

SELECT
    HIRE_DATE,
    HIRE_DATE + 1,              -- 채용일 다음 날
    HIRE_DATE - 1               -- 채용일 전 날
FROM EMPLOYEES;

SELECT
    FIRST_NAME,
    SYSDATE - HIRE_DATE         -- 입사 후 일수
FROM EMPLOYEES;

SELECT
    FIRST_NAME,
    (SYSDATE - HIRE_DATE) / 7   -- 입사 후 주수
FROM EMPLOYEES;

SELECT
    FIRST_NAME,
    (SYSDATE - HIRE_DATE) / 365 -- 입사 후 년수
FROM EMPLOYEES;

날짜의 반올림 및 절삭

날짜를 반올림하거나 절삭할 수 있습니다.

-- 일자 기준 반올림 및 절삭
SELECT
    ROUND(SYSDATE),             -- 현재 날짜 반올림
    TRUNC(SYSDATE)              -- 현재 날짜 절삭
FROM DUAL;

-- 월 기준 반올림 및 절삭
SELECT
    ROUND(SYSDATE, 'MONTH'),    -- 현재 월 반올림
    TRUNC(SYSDATE, 'MONTH')     -- 현재 월 절삭
FROM DUAL;

-- 년 기준 반올림 및 절삭
SELECT
    ROUND(SYSDATE, 'YEAR'),     -- 현재 년 반올림
    TRUNC(SYSDATE, 'YEAR')      -- 현재 년 절삭
FROM DUAL;

TO_CHAR 함수

사용 구문

TO_CHAR(value, 'fmt')

• value: 변환할 값 (숫자나 날짜).
• fmt: 변환할 형식 (포맷).

날짜를 문자로 변환 예시

1. 기본 날짜 형식 변환
2. TO_CHAR(SYSDATE, 'YYYY-MM-DD')
3. fm 요소 사용
4. TO_CHAR(SYSDATE, 'fmDDth Month YYYY')
5. 다양한 날짜 형식
6. TO_CHAR(SYSDATE, 'Day, DD Month YYYY HH24:MI:SS')

숫자를 문자로 변환 예시

1. 기본 숫자 형식 변환
2. TO_CHAR(1234, '999999')
3. 달러 기호 사용
4. TO_CHAR(1234, '$999999')
5. 소수점과 콤마 사용
6. TO_CHAR(1234, '999,999.99')

NVL 함수

NVL 함수는 Null 값을 다른 값으로 대체할 때 사용됩니다.

사용 구문

NVL(expr1, expr2)

• expr1: Null 값을 포함할 수 있는 표현식.
• expr2: Null 값을 대체할 값.

예시

SELECT
    NVL(column_name, 0) AS column_name_replaced,
    NVL(another_column, 'Unknown') AS another_column_replaced
FROM table_name;

DECODE 함수

DECODE 함수는 조건에 따라 값을 변환하는 IF-THEN-ELSE 구문과 유사합니다.

사용 구문

DECODE(expression, search1, result1, [search2, result2, ...], [default])

예시

SELECT DECODE(grade, 'A', 'Excellent', 'B', 'Good', 'C', 'Average', 'F', 'Fail', 'Unknown') AS result
FROM students;

CASE 구문

CASE 절은 IF-THEN-ELSE 문과 유사하게 조건에 따른 결과를 반환합니다.

사용 구문

1. 표현식이 있는 경우:
2. CASE expression WHEN value1 THEN result1 ... ELSE default END
3. 조건식이 있는 경우:
4. CASE WHEN condition1 THEN result1 ... ELSE default END

예시

1. 표현식이 있는 경우:
2. SELECT CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Good' ELSE 'Unknown' END AS result FROM students;
3. 조건식이 있는 경우:
4. SELECT CASE WHEN salary >= 10000 THEN 'High' WHEN salary >= 5000 THEN 'Medium' ELSE 'Low' END AS salary_level FROM employees;

집합 연산자

• UNION: 두 쿼리의 합집합 (중복 제거).
• UNION ALL: 두 쿼리의 합집합 (중복 포함).
• INTERSECT: 두 쿼리의 교집합.
• MINUS: 첫 번째 쿼리에서 두 번째 쿼리의 차집합.

예시

-- UNION
SELECT name FROM employees1
UNION
SELECT name FROM employees2;

-- INTERSECT
SELECT name FROM employees1
INTERSECT
SELECT name FROM employees2;

-- MINUS
SELECT name FROM employees1
MINUS
SELECT name FROM employees2;

순위 함수

RANK

RANK 함수는 값을 내림차순 또는 오름차순으로 정렬하여 우선순위를 부여합니다 (동일 순위 가능).

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

DENSE_RANK

DENSE_RANK 함수는 중복 순위를 제외한 순위를 부여합니다.

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

ROW_NUMBER

ROW_NUMBER 함수는 중복 없는 순번을 부여합니다.

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

예제 코드

--문자열 함수
SELECT LOWER('HELLO WORLD') FROM DUAL; -- SQL을 간단하게 연습하기 위한 가상테이블

SELECT LOWER(FIRST_NAME),UPPER(FIRST_NAME),INITCAP(FIRST_NAME) FROM EMPLOYEES;

--LENGTH 문자열 길이
-- INSTR 문자열 찾기 (JAVA 의 INDEXOF와 같은 기능)
SELECT FIRST_NAME, LENGTH(FIRST_NAME) FROM EMPLOYEES;
SELECT FIRST_NAME, INSTR(FIRST_NAME, 'a') FROM EMPLOYEES; -- a가 있는 위치를 반환 , 없는 경우는 0을 반환

--SUBSTR 문자열 자르기
SELECT FIRST_NAME , SUBSTR(FIRST_NAME, 3), SUBSTR(FIRST_NAME, 3, 2) FROM EMPLOYEES; -- 3미만 절삭 , 3번째 위치에서 2개 자름

--CONCAT 문자열 합치기
SELECT FIRST_NAME || LAST_NAME , CONCAT(FIRST_NAME, LAST_NAME) FROM EMPLOYEES;

--LPAD , RPAD - 범위를 지정하고 , 특정문자로 채움
SELECT LPAD ('ABC',10,'*') FROM DUAL; --ABC를 10칸 잡고, 나머지 부분은 왼쪽에서 *로 채움
SELECT LPAD (FIRST_NAME, 10, '*') ,RPAD(FIRST_NAME , 10 , '-') FROM EMPLOYEES; -- 오른쪽에서 - 채움

--LTRIM , RTRIM, TRIM -공백삭제 또는 문자삭제
SELECT TRIM(' HELLO WORLD '), LTRIM(' HELLO WORLD '), RTRIM(' HELLO WORLD ') FROM DUAL;
SELECT LTRIM('HELLO WORLD', 'HE') FROM DUAL;

--REPLACE 문자열 변경
SELECT REPLACE('서울 대구 대전 부산 찍고', ' ', '->') FROM DUAL; -- 공백을 ->로 변경
SELECT REPLACE('서울 대구 대전 부산 찍고', ' ', '') FROM DUAL; -- 공백을 삭제

------------------------------------------------------------------------------
--문제 1.
SELECT * FROM EMPLOYEES;
--EMPLOYEES 테이블 에서 이름, 입사일자 컬럼으로 변경해서 이름순으로 오름차순 출력 합니다.

--조건 1) 이름 컬럼은 first_name, last_name을 붙여서 출력합니다.
--조건 2) 입사일자 컬럼은 xx/xx/xx로 저장되어 있습니다. xxxxxx형태로 변경해서 출력합니다.
SELECT CONCAT(FIRST_NAME ||' ', LAST_NAME)AS 이름, 
REPLACE(HIRE_DATE,'/','') AS 입사일자
FROM EMPLOYEES ORDER BY 이름;

--문제 2.
--EMPLOYEES 테이블 에서 phone_numbe컬럼은 ###.###.####형태로 저장되어 있다
--여기서 처음 세 자리 숫자 대신 서울 지역변호 (02)를 붙여 전화 번호를 출력하도록 쿼리를 작성하세요
SELECT LPAD(SUBSTR(PHONE_NUMBER, 4),11,'02') FROM EMPLOYEES;
-- CONCAT ( SUBSTR())도 가능

--문제 3. EMPLOYEES 테이블에서 JOB_ID가 it_prog인 사원의 이름(first_name)과 급여(salary)를 출력하세요.
--조건 1) 비교하기 위한 값은 소문자로 입력해야 합니다.(힌트 : lower 이용)
--조건 2) 이름은 앞 3문자까지 출력하고 나머지는 *로 출력합니다. 
--이 열의 열 별칭은 name입니다.(힌트 : rpad와 substr 또는 substr 그리고 length 이용)
--조건 3) 급여는 전체 10자리로 출력하되 나머지 자리는 *로 출력합니다. 
--이 열의 열 별칭은 salary입니다.(힌트 : lpad 이용)

SELECT RPAD(SUBSTR(FIRST_NAME, 1,3),LENGTH(FIRST_NAME),'*')AS NAME,
LPAD(SALARY , 10 , '*') 
FROM EMPLOYEES WHERE LOWER(JOB_ID)='it_prog';