20230316 SQL
<차례>
◈ 함수
◈ 문자함수
lower
upper
initcap
concat
length
lengthb
instr
substr
substrb
trim
ltrim
rtrim
[문제14][문제15]
replace
lpad
rpad
[문제16][문제17]
◈ 숫자함수 8
round
trunc
ceil
floor
mod
power
sprt
[문제18]
◈ 날짜 함수 10
sysdate
systimestamp
current_date
current_timestamp
localtimestamp
months_between
add_months
next_day
last_day
[문제19][문제20]
■ 함수(fuction)
◆ 기능의 프로그램
◆ 단일행 함수 : 행당 조작하는 함수, 입력값으로 한 행의 필드값이 들어가서 출력값으로 하나가 리턴함
- 문자함수, 숫자함수, 날짜함수, 형변환함수, 일반함수
◆ 함수는 중첩할 수 있음
[memo]
☆ 데이터 품질 프로젝트(전처리)
☆ index scan : 천만 개 데이터 중 찾는 게 아닌 찾는 데이터 근처로 가서 찾는 방법
☆ full table scan : 끝까지 읽는 것
■ 문자함수
◆ lower : 소문자로 변환하는 함수
◆ upper : 대문자로 변환하는 함수
◆ initcap : 첫 글자 대문자, 나머지 글자 소문자로 변환하는 함수
=> 전체를 변환한 후에 비교해!
SELECT *
FROM employees
WHERE lower(last_name) = 'king';
SELECT *
FROM employees
WHERE upper(last_name) = 'KING';
SELECT *
FROM employees
WHERE initcap(last_name) = 'King';
=> 함수쓰면 index scan 아닌 full table scan이 됨(컬럼 변환)
=> 데이터 입력 시점에 규칙적으로 작성하도록 할 때 사용하면 좋음
◆ concat : 연결연산자와 동일한 함수
SELECT last_name || first_name
concat(last_name, first_name) => 두 개 동일함
last_name || first_name || job_id
concat(concat(last_name, first_name), job_id)
FROM employees;
=> 단, 연결연산자는 쭉 쓸 수 있지만, concat은 인자가 두 개만 있어야 함.
=> 두 개 이상 연결할 경우 함수를 중첩하여 사용해야 함
=> upper, lower과 concat 함수도 중첩 사용 가능함
SELECT upper(last_name || first_name || job_id)
lower(concat(concat(last_name, first_name), job_id))
FROM employees;
SELECT last_name||‘, ’||first_name
concat(last_name||‘, ’, first_name)
FROM employees;
◆ length : 문자의 길이를 리턴하는 함수
SELECT last_name, length(last_name)
FROM employees;
◆ lengthb : 문자의 바이트 값을 리턴하는 함수
SELECT last_name, lengthb(last_name)
FROM employees;
SELECT length(‘bigdata’),lengthb(‘bigdata’),length(‘빅데이터’),lengthb(‘빅데이터’)
FROM dual;
=> 영어의 경우 length와 lengthb 동일, 한글은 다름
SELECT *
FROM nls_database_parameters;
=> AL32UTFS : 데이터베이스에 설정된 문자 characterse, 유니코드
전세계 출판되는 글자는 char, varchar2 타입의 컬럼에 입력할 수 있음
1 byte ~ 4 byte, 영어는 1 byte, 한글은 3 byte
KD1EXSMIN949: 한글, 영어, 한자, 일어 글자를 char, varchar2 타입의 컬럼에 입력할 수 있음
SELECT *
FROM nls_session_parameters;
=> 지역에 맞게 셋업됨 확인
◆ instr : 문자의 위치를 리턴하는 함수
- instr(컬럼, 찾는문자열, 시작위치(1), 몇 번째로 찾는 위치(1))
SELECT last_name, instr(last_name, ‘a’)
FROM employees;
SELECT last_name, instr(last_name,'a',1,2)
FROM employees;
SELECT last_name,instr(last_name,'a'), instr(last_name, 'a',1,2)
FROM employees;
WHERE instr(last_name,'a',1,2) > 0;
◆ substr : 문자를 추출하는 함수
- substr(컬럼(문자열), 시작점, 추출개수)
SELECT last_name, substr(last_name, 1, 2)
FROM employees;
시작점
1, 2, 3, 4, 5
-5, -4, -3, -2, -1
SELECT last_name,
substr(last_name, 1, 2),
substr(last_name, 2),
substr(last_name, length(last_name)),
substr(last_name, -1, 1)
substr(last_name, -2, 1)
FROM employees;
◆ substrb : 문자를 바이트 수만큼 추출하는 함수
SELECT substrb(‘abcdef’, 1, 2), substrb(‘가나다라마바사’, 1, 3)
FROM dual;
◆ trim : 왼쪽(접두), 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
◆ ltrim : 왼쪽(접두) 부분에 연속되는 문자를 제거하는 함수
◆ rtrim : 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
=> ltrim, rtrim에는 from 넣지 않음
SELECT trim('a' from 'aaabbcaa')
FROM dual;
SELECT trim(' ' from ' bbc ') => 공백문자 제거
FROM dual;
SELECT trim(' bbc ') => 공백문자 제거
FROM dual;
SELECT trim('a' from 'aaabbcaa')
FROM dual;
SELECT ltrim('aaabbcaa','a')
FROM dual;
SELECT rtrim('aaabbcaa','a')
FROM dual;
SELECT trim(' bbc ') => 왼쪽 공백 제거
FROM dual;
SELECT ltrim(' bbc ') => 왼쪽 공백 제거
FROM dual;
SELECT rtrim(' bbc ') => 오른쪽 공백 제거
FROM dual;
[문제14] employees 테이블에 last_name 컬럼의 값 중에 “j” 또는 “A” 또는 “M”으로 시작하는 사원들의 last_name, last_name의 길이를 표시하는 query(select문)을 작성합니다. 사원들의 last_name 기준으로 내림차순 정렬해주세요.
SELECT last_name, length(last_name)
FROM employees
WHERE instr(last_name, 'J', 1, 1) = 1
OR instr(last_name, 'A', 1, 1) = 1
OR instr(last_name, 'M', 1, 1) = 1
ORDER BY 1 desc;
SELECT last_name, length(last_name)
FROM employees
WHERE last_name LIKE 'J%'
OR last_name LIKE 'A%'
OR last_name LIKE 'M%'
ORDER BY 1 desc;
SELECT last_name, length(last_name)
FROM employees
WHERE substr(last_name, 1, 1) = 'J'
OR substr(last_name, 1, 1) = 'M'
OR substr(last_name, 1, 1) = 'A'
ORDER BY 1 desc;
SELECT last_name, length(last_name)
FROM employees
WHERE substr(last_name, 1, 1) IN ('J', 'M', 'A')
ORDER BY 1 desc;
[문제15] employees 테이블에서 department_id(부서코드)가 50번인 사원들 중에 last_name에서 두 번째 위치에 “a” 글자가 있는 사원들을 조회하세요.
SELECT *
FROM employees
WHERE department_id = 50
AND instr(last_name, 'a', 2, 1) =2;
SELECT *
FROM employees
WHERE department_id = 50
AND substr(last_name, 2, 1) ='a';
SELECT *
FROM employees
WHERE department_id = 50
AND last_name LIKE '_a%';
◆ replace : 문자를 다른 문자로 치환하는 함수
- replace(컬럼(문자열), 이전 문자, 새로운 문자)
SELECT replace(‘100-001’,‘-’,‘%’),
replace(‘100-001’,‘-’,‘’)
replace(‘ bb cc a’,‘ ’,‘ ’)
FROM dual;
◆ lpad : 문자의 자리를 고정시킨 후 문자 값을 오른쪽 기준으로 세우고 빈 왼쪽 공백을 다른 값으로 채우는 함수
◆ rpad : 문자의 자리를 고정시킨 후 문자 값을 왼쪽 기준으로 세우고 빈 오른쪽 공백을 다른 값으로 채우는 함수
SELECT salary, lpad(salary, 10, ‘*’)
FROM employees;
SELECT salary, rpad(salary, 10, '*')
FROM employees;8
[문제16] salary에 있는 값을 1000당 * 출력해주세요.
SALARY STAR
6000 ******
4000 ****
4800 ****
SELECT salary, lpad('*', salary/1000, '*') as STAR
FROM employees;
SELECT salary, lpad(' ', salary/1000+1, '*') as STAR
FROM employees;
SELECT 5000, lpad(' ', 5000/1000+1, '*') as STAR
FROM dual;
=> lpad 맨 앞 인수는 공백 문자라도 넣어야 함/공백넣은 경우 고정 자리수(두 번째 인수) +1
[문제17] 주민번호 뒷 6자리 부분을 아래 화면과 같이 출력해주세요.
| 주민번호_1 | 주민번호_2 |
|---|---|
| 210101-1234567 | 210101-1** |
SELECT '210101-1234567' 주민번호_1, rpad('210101-1', 14, '*') 주민번호_2
FROM dual;
SELECT '210101-1234567' 주민번호_1, rpad(substr('210101-1234567',1,8), 14, '*') 주민번호_2
FROM dual;
■ 숫자함수
◆ round : 지정된 소수점 자리수 반올림하는 함수
SELECT ROUND(45.926, 2) => 45.93
FROM dual;
SELECT ROUND(45.926, 0) => 46
FROM dual;
SELECT ROUND(45.926, -1) => 50
FROM dual;
SELECT ROUND(45.926, -2) => 0
FROM dual;
SELECT ROUND(152.926, -2) => 200
FROM dual;
◆ trunc : 지정된 소수점 자리수 값을 버리는 함수
SELECT trunc(45.926, 2) => 45.92
FROM dual;
SELECT trunc(45.926, 1) => 45.9
FROM dual;
SELECT trunc(45.926, -1) => 40
FROM dual;
◆ ceil : 숫자값을 가장 큰 정수로 변환하는 함수
◆ floor : 숫자값을 가장 작은 정수로 변환하는 함수(몫)
SELECT ceil(10.1) => 11
floor(10.2) => 10
FROM dual;
◆ mod : 어떤 값을 나눈 나머지를 반환하는 함수
SELECT 12/5, mod(12, 5) => 2.4, 2
FROM dual;
◆ power : 거듭제곱
SELECT 2*2*2, power(2, 3)
FROM dual;
◆ abs : 절대값
SELECT -100, abs(-100)
FROM dual;
◆ sqrt : 루트
SELECT sqrt(9)
FROM dual;
[문제18] employees 테이블에 있는 employee_id, last_name, salary, salary를 10% 인상된 급여를 계산하면서 계산된 급여는 소수점은 반올림하여 정수값으로 표현하고 열 별칭은 New Salary로 표시하세요.
SELECT employee_id,
last_name,
salary,
round(salary*1.1) "New Salary",
round(salary*1.1)-salary "Diff New Salary" => 급여 차이 컬럼
FROM employees;
■ 날짜 함수
◆ sysdate : 현재 서버 날짜를 리턴하는 함수
◆ systimestamp : 현재 서버 날짜, 시간, 타임존을 리턴하는 함수
◆ current_date : 현재 클라이언트(접속) 날짜를 리턴하는 함수
◆ current_timestamp : 현재 클라이언트(접속) 날짜, 시간, 타임존을 리턴하는 함수
◆ localtimestamp : 현재 클라이언트(접속) 날짜, 시간을 리턴하는 함수
SELECT sysdate,
systimestamp,
current_date,
current_timestamp,
localtimestamp
FROM dual;
ALTER SESSION SET TIME_ZONE = "+09:00"; => 클라이언트 타임존 수정
● 날짜 계산
날짜 + 숫자(일수) = 날짜
날짜 – 숫자(일수) = 날짜
날짜 – 날짜 = 숫자(일수)
날짜 + 날짜 = 오류
날짜 + 시간 = 날짜, 시간
날짜 – 시간 = 날짜, 시간
SELECT sysdate, sysdate + 100, sysdate – 100
FROM dual;
SELECT employee_id, hire_date, sysdate - hire_date
FROM employees;.
SELECT systimestamp, systimestamp + 10/24, => 10일 24시간 기준을 의미
localtimestamp +10/24,
current_timestamp +10/24,
to_char(current_timestamp +2/24, ‘yyyy-mm-dd
hh24:mi:ss’),
to_char(current_timestamp +200/(2460), ‘yyyy-mm-dd hh24:mi:ss’),
to_char(current_timestamp +200/(2460*60), ‘yyyy-mm-dd hh24:mi:ss’),
FROM dual;
◆ months_between : 두 날짜간의 달(개월) 수를 리턴하는 함수
SELECT employee_id, trunc(sysdate – hire_date) => 근무일수
FROM employees;
SELECT employee_id, months_between(sysdate, hire_date) => 근무달수
FROM employees;
SELECT employee_id, trunc(months_between(sysdate, hire_date))
FROM employees;
SELECT employee_id, months_between(sysdate, hire_date)/12 => 근무연수
FROM employees;
SELECT employee_id, trunc(months_between(sysdate, hire_date)/12)
FROM employees;
◆ add_months : 달수를 더하거나 빼는 함수
SELECT sysdate,
add_months(sysdate, 10),
add_months(sysdate, -10)
FROM dual;
◆ next_day : 입력한 날짜를 기준으로 찾고자 하는 요일의 첫 번째 날짜를 반환하는 함수
SELECT next_day(sysdate, '월요일') => 한글로 써야함, 미래 날짜
FROM dual;
SELECT*FROM nls_session_parameters; => LANGUAGE : KOREAN
◆ last_day : 기준 날짜가 해당하는 달의 마지막 날짜를 리턴하는 함수
SELECT last_day(sysdate)
FROM dual;
SELECT last_day(sysdate + 100) => 현재 날짜 + 100일이 해당하는 날짜의 달
FROM dual;
[문제19] 15년 이상 근무한 사원들의 사원번호, 입사날짜, 입사개월수를 조회하세요.
SELECT employee_id, hire_date, months_between(sysdate, hire_date)
FROM employees
WHERE months_between(sysdate, hire_date) >= 180;
SELECT employee_id, hire_date, months_between(sysdate, hire_date)
FROM employees
WHERE months_between(sysdate, hire_date)/12 >= 15;
[문제20] 사원의 last_name, hire_date 및 근무 6개월 후 월요일에 해당하는 날짜를 조회하세요. 열 별칭은 REVIEW로 조정합니다.
SELECT employee_id, hire_date, next_day(add_months(hire_date,6), '월요일') REVIEW
FROM employees;
● 날짜함수에 소수점 결과 나오는 이유: 시간 정보가 포함되어 있기 때문
SELECT to_date('2023-03-17') - sysdate => 0.2751736111...
FROM dual;
SELECT to_date('2023-03-17') - to_date('2023-03-16') => 1
FROM dual;
SELECT to_date('2023-09-20') - to_date('2023-03-14') => 190
FROM dual;
