choice blog

 

-- hr 표준함수


-- 문제1) EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고 사원번호, 성명, 담당업무(소문자로),부서번호를 출력하라.

SELECT employee_id, LOWER(last_name), LOWER(job_id), department_id  FROM employees
WHERE LOWER(last_name) = 'king';


-- 문제2) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호, 성명, 담당업무(대문자로),부서번호를 출력하라.

SELECT employee_id, UPPER(last_name), UPPER(job_id),  department_id  FROM employees
WHERE UPPER(last_name) = 'KING';


-- 문제3) DEPARTMENTS 테이블에서 (부서번호와 부서이름), 부서이름과 위치번호를 합하여 출력하도록 하라.

SELECT department_id|| ' ' || department_name, department_name|| ' ' || location_id FROM departments;

SELECT CONCAT(department_id, department_name),
    CONCAT(CONCAT(department_name,' ' ), location_id)
FROM departments;


-- 문제4) EMPLOYEES 테이블에서 이름의 첫 글자가 ‘K’ 보다 크고 ‘Y’보다 적은 사원의 정보를 
-- 사원번호, 이름, 업무, 급여, 부서번호를 출력하라. 
-- 단 이름순으로 정렬하여라.

SELECT employee_id, first_name, job_id, salary, department_id FROM employees
WHERE SUBSTR(first_name, 1, 1) > 'K' AND  SUBSTR(first_name, 1, 1) < 'Y'
ORDER BY first_name; -- 오름차순


-- 문제5) EMPLOYEES 테이블에서 20번 부서 중 이름의 길이 및 급여의 자릿수를 
-- 사원번호, 이름, 이름의 자릿수(LENGTH), 급여, 급여의 자릿수를 출력하라.
-- LENGTHB는 BYTE 혼동하지 말자!

SELECT employee_id, first_name, LENGTH(first_name), salary,  LENGTH(salary)
FROM employees
WHERE department_id = 20;


-- 문제6) EMPLOYEES 테이블에서 이름 중 ‘e’자의 위치를 출력하라.

SELECT first_name, INSTR(first_name,'e',1,1) FROM employees;


-- 문제7) 다음의 쿼리를 실행하고 결과를 출력하고 확인하라.
SELECT  ROUND(4567.678),      -- 4568     
        ROUND(4567.678, 0),   -- 4568    
        ROUND(4567.678, 2),   -- 4567.68      
        ROUND(4567.678, -2)   -- 4600    
FROM dual;

-- ROUND(4567.678): 반올림(소수점 없음)
-- ROUND(4567.678, 0): 소수점 없음 
-- ROUND(4567.678, 2) : 소수점 2번째까지 반올림
-- ROUND(4567.678, -2) : 정수 두자릿수 짤림 00으로 그리고 반올림


-- 문제8) EMPLOYEES 테이블에서 부서번호가 80인 사람의 급여를 30으로 나눈 나머지를 구하여 출력하라.

SELECT first_name, department_id, salary, MOD(salary, 30) 
FROM employees
WHERE department_id = 80;


-- 문제9) EMPLOYEES 테이블에서 30번 부서 중 이름과 담당 업무를 연결하여 출력하여라. 

SELECT first_name || ' ' || job_id
FROM employees
WHERE department_id = 30;


-- 문제10) EMPLOYEES 테이블에서 현재까지 근무일 수가 몇주 몇일 인가를 출력하여라. 
-- 단 근무 일수가 많은 사람 순으로 출력하여라.

SELECT first_name, hire_date, 
TRUNC(SYSDATE - hire_date) AS "총근무 일수",
TRUNC((SYSDATE - hire_date) / 7) AS "총근무주 일수",
TRUNC(MOD((SYSDATE - hire_date) , 7)) AS "남은 일수"
FROM employees; 


-- 문제11) EMPLOYEES 테이블에서 부서 50에서 급여 앞에 $를 삽입하고 3자리마다 ,를 출력하라

SELECT first_name, department_id, salary, TO_CHAR(salary, '$999,999,999')
FROM employees
WHERE department_id = 50;

DUAL TABLE : 가상 테이블 (결과용 테이블)

형식

 SELECT 문자 출력값, 연산 결과값 FROM DUAL;

 

예시

SELECT 1 FROM DUAL;
SELECT 'A' FROM DUAL;
SELECT '가나' FROM DUAL;
SELECT 23*45 FROM DUAL;

 

 

 

Standard Function (표준 함수)

 

문자열 함수 (Character Function) 정리

 

1. 변환함수

 

LOWER() : 알파벳 값을 소문자로 변환

 

형식

 LOWER(문자열)

 

예시

SELECT LOWER('HI, HELLO') FROM DUAL; -- hi, hello

 

 

UPPER() : 알바펫 값을 대문자로 변환

 

형식

 UPPER(문자열)

 

예시

SELECT UPPER('hi, hello') FROM DUAL;  -- HI, HELLO

 

 

INITCAP() : 첫번째 글자만 대문자로 변환

 

형식

 INITCAP(문자열)

 

예시

SELECT INITCAP('HI, HELLO') FROM DUAL; -- Hi, Hello

 

 

2. 문자 조작 함수

 

CONCAT() : 두 문자열 연결(결합)

 

형식

 CONCAT(값1, 값2)

 

예시

SELECT CONCAT(CONCAT('나는',' '),'최고입니다') FROM DUAL; -- 나는 최고입니다

 

|| : 문자열 연결(결합)

 

형식

 값1 || 값2 || ... 값n

 

예시

SELECT '나는 ' || '최고입니다' FROM DUAL; -- 나는 최고입니다

 

 

SUBSTR() : 문자열 중 특정 문자 또는 문자열의 일부분을 선택 ★★★★★

 

형식

 SUBSTR(문자열, 시작INDEX, 취득개수) 

 

예시

SELECT SUBSTR('ABCDE', 3) FROM DUAL; -- CDE


-- 3번째부터 2글자 가져와라 CD

SELECT SUBSTR('ABCDE', 3, 2) FROM DUAL; -- CD

 

LENGTH() : 문자열의 길이를 구함

 

형식

 LENGTH(문자열) 

 

예시

SELECT LENGTH('ABCDE') FROM DUAL; -- 5

 

 

INSTR() :  문자열 내 위치를 구함

 

형식

 INSTR(문자열, 찾을문자열[시작위치[, 검색차수]) 

 

참고

 시작위치가 있으면 시작위치부터 찾고 시작위치가 없다면 처음부터 찾는다.

 검색차수가 있으면 검색차수번째 순서에 있는 찾는문자열의 위치를 구한다.

 

예시

SELECT INSTR('abcde', 'a') FROM DUAL; -- 1

SELECT INSTR('123ABC456DEFABC', 'A') FROM DUAL; -- 4


-- 7번째 이후의 A의 INDEX번호

SELECT INSTR('123ABC456DEFABC', 'A', 7) FROM DUAL; -- 13


-- 7번째 이후의 1번째 나오는 A의 INDEX번호

SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 2) FROM DUAL; -- 16

 

 

LPAD() / RPAD() : 나머지를 빈칸 또는 지정문자로 채운다. LPAD:왼쪽, RPAD:오른쪽

 

형식

 LPAD(문자열, 개수, [지정문자])

 RPAD(문자열, 개수, [지정문자])

 

예시

SELECT LPAD('BBB', 10) FROM DUAL; --        BBB


-- 지정문자 사용 예

SELECT LPAD('BBB', 10, '-') FROM DUAL; -- -------BBB

SELECT RPAD('124', 10, '0') FROM DUAL; -- 1240000000

 

 

LTRIM() / RTRIM() : 공백,  반복적인 문자를 지움 (LTRIM : 왼쪽, RTRIM : 오른쪽)

 

형식

 LTRIM(문자열)

 RTRIM(문자열)

 LTRIM(문자열, 삭제문자열)

 RTRIM(문자열, 삭제문자열)

 

예시

-- 공백 삭제

SELECT LTRIM('   BBB' )   FROM DUAL; 

SELECT RTRIM('BBB   ' )   FROM DUAL; 

--반복적인 문자 삭제

SELECT LTRIM('AABBB', 'AA')   FROM DUAL; -- BBB 

SELECT RTRIM('BBBAA', 'AA')   FROM DUAL; -- BBB

 

 

REPLACE() : 문자열 치환

 

형식

 REPLACE(문자열, 삭제문자)

 REPLACE(문자열, 대상문자, 교체문자)

 

예시

SELECT REPLACE('AAAAABCD', 'A') FROM DUAL; -- BCD

SELECT REPLACE('AAAAABCD', 'A', 'a') FROM DUAL; -- aaaaaBCD

SELECT REPLACE('AAAAABCD', 'AA', 'a') FROM DUAL; -- aaABCD

 

 

TREANSLATE() : 문자 치환

 

형식

 TREANSLATE(문자열, 대상문자, 치환문자)

 

참고

 글자를 지정해주지 않으면 에러발생

 

예시

SELECT TREANSLATE('AAAAABCD', 'A' , 'a') FROM DUAL; -- aaaaaBCD

-- 위와 동일 : 문자 치환이기 때문에

SELECT TREANSLATE('AAAAABCD', 'AA' , 'a') FROM DUAL; -- aaaaaBCD

 

 

 

숫자 함수 정리

 

CEIL() : 숫자를 올림 

 

형식

 CEIL(숫자)

 

예시

SELECT CEIL(13.1) FROM DUAL; -- 14

 

 

FLOOR() : 숫자를 내림

 

형식

 FLOOR(숫자)

 

예시

SELECT FLOOR(13.1) FROM DUAL; -- 13

 

 

ROUND() : 숫자를 반올림

 

 

형식

 ROUND(숫자)

 ROUND(숫자, 자릿수)

 

예시

SELECT ROUND(13.4) FROM DUAL; -- 13


-- 소수점 1번째자리 까지만 인정

SELECT ROUND(13.45,1) FROM DUAL; -- 13.5

 

 

TRUNC() : 숫자를 버림(절삭)

 

형식

 TRUNC (숫자)

 TRUNC (숫자, 자릿수)

 

예시

SELECT TRUNC(123.456) FROM DUAL; -- 123


-- 소수점 2번째자리 까지만 인정

SELECT TRUNC(123.456, 2) FROM DUAL; -- 123.45


-- 소수점 1번째자리 까지만 인정

SELECT TRUNC(123.456, 1) FROM DUAL; -- 123.4


-- 정수 1번째자리까지 인정

SELECT TRUNC(123.456, -1) FROM DUAL; -- 120

 

 

MOD() : 숫자를 나눈 나머지

 

형식

 MOD(숫자, 나눌수)

 

예시

SELECT MOD(3, 2)FROM DUAL; -- 1

 

 

POWER() : 거듭제곱(승수)

 

형식

 POWER (숫자, 승수)

 

예시

SELECT POWER(3, 2) FROM DUAL; -- 9

 

 

SIGN() : 양수, 음수, 0인지 확인

 

형식

 SIGN (숫자)

 

참고 

 양수일 때 반환값 : 1  

 음수일 때 반환값 : -1   

 0일 때 반환값 : 0 

 

예시

SELECT SIGN(13.4) FROM DUAL; -- 1

SELECT SIGN(-13.4) FROM DUAL; -- -1

SELECT SIGN(0) FROM DUAL; -- 0

 

 

CHR() : ASCII 값으로 변환

 

형식

 CHR(숫자)

 

예시

SELECT CHR(65) FROM DUAL; -- A

SELECT CHR(97) FROM DUAL; -- a

 

 

 

날짜 함수

 

SYSDATE : 오늘 날짜, 시간 반환

 

형식

SYSDATE 

 

예시

SELECT SYSDATE FROM DUAL;

 

 

MONTHS_BETWEEN() : 두 날짜 사이의 월수를 계산

 

형식

 MONTHS_BETWEEN(기준날짜, 비교날짜)

 

예시

SELECT MONTHS_BETWEEN(TO_DATE('20/04/23'), TO_DATE('20/01/1')) FROM DUAL; -- 3.7096..

 

 

ADD_MONTHS() : 월을 날짜에 더하기

 

형식

 ADD_MONTHS (기준날짜, 개월수)

 

예시

SELECT ADD_MONTHS(TO_DATE('20/04/23'), 6) FROM DUAL; -- 20/10/23

 

 

NEXT_DAY() : 명시된 날짜로부터 다음 요일에 대한 날짜 계산 (오늘은 포함 X)

 

형식

 NEXT_DAY (기준날짜, 요일문자열(요일번호))

 

참고

요일문자열 :  일요일, 월요일, 화요일, 수요일, 목요일, 금요일, 토요일, 또는 일, 월, 화, 수, 목, 금, 토

요일번호 : 1(일), 2(월), 3(화), 4(수), 5(목), 6(금), 7(토)

 

예시

SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일') FROM DUAL;

 

출력화면

 

 

LAST_DAY() : 명시된  날짜가 속한 달의 마지막 날를 반환

 

형식

 LAST_DAY (기준날짜)

 

예시

SELECT LAST_DAY('24/02/22') FROM DUAL; -- 24/02/29

 

 

ROUND() : 날짜를 반올림

TRUNC() : 날짜를 절삭

형식

 ROUND (기준날짜, 반올림기준)

 TRUNC (기준날짜, 절삭기준)

 

예시

SELECT TO_CHAR(sysdate, 'HH:MI:SS') , 
		ROUND(sysdate, 'YEAR'), sysdate,
		ROUND(sysdate), TRUNC(sysdate, 'MONTH')
FROM DUAL;

 

출력화면

 

 

 

형변환 함수 정리

 

TO_CHAR () :  날짜,시간 → 문자 변환    ★★★★★

 

형식

TO_CHAR(날짜,문자형식)

TO_CHAR(숫자,문자형식)

 

예시

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH-MI-SS') FROM DUAL; -- 2021-05-31 01-02-12

SELECT TO_CHAR(123456789, '$999,999,999,999') FROM DUAL; -- $123,456,789 

 

 

TO_DATE() : 문자열 → 날짜,시간변환       ★★★★★

 

형식

TO_DATE(문자,날짜형식)

TO_DATE(문자,숫자형식)

 

예시

SELECT TO_DATE('20210525') FROM DUAL; -- 2021/05/25 00:00:00

-- 위와 같다.(지정변환가능)

SELECT TO_DATE('20210525', 'YYYYMMDD') FROM DUAL; -- 2021/05/25 00:00:00

 

 

TO_NUMBER() : 문자열 → 숫자

 

형식

TO_NUMBER(문자)

 

참고

TO_NUMBER()사용시 연산도 가능하다.

 

예시

SELECT TO_NUMBER('123') + 45 FROM DUAL; -- 168

 

 

 

 

참고 : 하루 10분 SQL