SQL - DAY 3

BUMSOO·2024년 6월 12일
sql오라클 DBA

함수(function)

문자함수

substr

  • 문자를 추출하는 함수,substr(컬럼(문자열),시작점,추출갯수)
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,2)
FROM 
	hr.employees;

substrb

  • 문자를 바이트 값만큼 추출하는 함수(컬럼(문자열),시작점,바이트 값)
SELECT
	substrb('abcdef',1,2),
    substrb('가나다라마바사',1,6)
FROM 
	dual;

trim

  • 왼쪽(접두),오른쪽(접미)부분에 연속되는 문자를 제거하는 함수,(제거해야할 단어 from 문장) 
SELECT trim('a' from 'aaabbcaa') FROM dual;
SELECT trim(both 'a' from 'aaabbcaa') FROM dual;

ltrim

  • 왼쪽(접두)부분에 연속되는 문자를 제거하는 함수,(문장 , 제거해야할 단어)
SELECT ltrim('aaabbcaa','a') FROM daul;
SELECT trim(leading 'a' from 'aaabbcaa') FROM dual;

rtrim

  • 오른쪽(접미)부분에 연속되는 문자를 제거하는 함수,(문장 , 제거해야할 단어)
SELECT rtrim('aaabbcaa','a') FROM dual;
SELECT trim(trailing 'a' from 'aaabbcaa') FROM dual;

공백 제거

SELECT
    trim('   sql  '),
    ltrim('  sql  '),--왼쪽공백 제거
    rtrim('  sql  ')--오른쪽공백 제거
FROM dual;

replace

  • 문자를 다른 문자로 치환하는 함수
    (컬럼(문자열),이전문자,새로운문자)
SELECT
	replace('100-001','-','%'),
    replace('100-001','-','') -- "-"부분을 제거할때
    replace(' 1 0 0 0 01',' ','') -- 공백을 제거해줌
FROM dual;

lpad

  • 문자의 자리를 고정시킨 후 문자값을 오른쪽기준으로 채우고 빈 왼쪽 공백을 다른 값으로 채우는 함수,(컬럼,자리수,채울문자)
SELECT
    salary,
    lpad(salary,10,'*')
FROM hr.employees;

rpad

  • 문자의 자리를 고정시킨 후 문자값을 왼쪽기준으로 채우고 빈 오른쪽 공백을 다른 값으로 채우는 함수,(컬럼,자리수,채울문자)
SELECT
    salary,
    rpad(salary,10,'*')
FROM hr.employees;

[문제12]

employees 테이블에 last_name 컬럼의 값 중에 "J" 또는 "A" 또는 "M"으로 시작하는
사원들의 last_name, last_name의 길이를 표시하는 query(select문) 를 작성합니다.
사원들의 last_name 기준으로 내림차순 정렬해 주세요.

<풀이>

SELECT last_name, length(last_name)
FROM hr.employees
WHERE substr(last_name,1,1) IN ('J','A','M')
ORDER BY 1 desc;

또는

SELECT last_name, length(last_name)
FROM hr.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;

[문제13]

employees테이블에서 department_id(부서코드)가 50번 사원들 중에
last_name에 두번째 위치에 "a"글자가 있는 사원들을 조회하세요.

<풀이>

SELECT *
FROM hr.employees
WHERE department_id = 50
AND substr(last_name,2,1) = 'a';

또는

SELECT *
FROM hr.employees
WHERE department_id = 50
AND instr(last_name,'a',2,1)=2;

[문제14]

salary에 있는 값을 1000당 * 출력해주세요.

SALARYSTAR
6000******
4800****
4800****

<풀이>

SELECT 
    salary, 
    lpad('*',salary/1000,'*') as STAR
FROM hr.employees;

또는

SELECT 
    salary, 
    lpad(' ',salary/1000+1,'*') as STAR
FROM hr.employees;

숫자함수

  • 숫자를 입력값으로 받아서 숫자값으로 반환하는 함수

round

  • 지정된 소수점 자릿수 값을 반올림하는 함수
SELECT 
	round(45.926,2),--소수점 둘째짜리가 까지 반올림
    round(45.926,1),--소수점 첫째자리까지 반올림
    round(45.926,0),--일의 자리까지 반올림
    round(45.926,-1)--십의 자리까지 반올림
FROM dual;

trunc

  • 지정된 소수점 자릿수값을 버리는 함수
SELECT 
	trunc(45.926,2),--결과 45.92
    trunc(45.926,1),--결과 45.9
    trunc(45.926,0),--결과 45
    trunc(45.926,-1)--결과 40
FROM dual;

ceil

  • 숫자값을 가장 큰 정수로 반환하는 함수
SELECT 
	round(10.1),--결과 10
    ceil(10.1)-- 결과 11
FROM dual;

floor

  • 숫자값을 가장 작은 정수로 반환하는 함수
SELECT 
	trunc(10.1),--결과 10
    floor(10.1)-- 결과 10
FROM dual;

SELECT 
	trunc(-10.1),--결과 -10
    floor(-10.1)-- 결과 -11
FROM dual;

mod

  • 어떤값을 나눈 나머지를 반환하는 함수
SELECT 
	12/5,
    mod(12,5)
FROM dual;

power

  • 거듭제곱
SELECT
	2*2*2
    power(2,3)
FROM daul;

abs

  • 절대값
SELECT 
	-100,
    abs(-100)
FROM dual;

sqrt

  • 루트
SELECT sqrt(9)
FROM dual;

[문제15]

employees 테이블에 있는 employee_id, last_name, salary,
salary를 10% 인상된 급여를 계산하면서 계산된 급여는 소수점은 반올림해서 정수값으로 표현하고
열별칭은 New Salary로 표시하세요.

<풀이>

SELECT 
    employee_id,
    last_name, 
    salary, 
    round(salary*1.1) as "New Salary"
FROM hr.employees;

날짜 함수

sysdate - 현재 서버 날짜를 리턴하는 함수
systimestamp - 현재 서버 날짜,시간,타임존을 리턴하는 함수
current_date - 현재 클라이언트 날짜를 리턴하는 함수
current_timestamp - 현재 클라이언트 날짜,시간,타임존을 리턴하는 함수
localtimestamp - 현재 클라이언트 날짜,시간을 리턴하는 함수

  • 클라이언트 타임존 수정

    	alter session set time_zone '+08:00';
  • 날짜 계산
    날짜 + 숫자(일수) = 날짜
    날짜 - 숫자(일수) = 날짜
    날짜 - 날짜 = 숫자(일수)
    날짜 + 날짜 = 오류
    날짜 + 시간 = 날짜 시간
    날짜 - 시간 = 날짜 시간

시 계산

SELECT
    systimestamp,
    to_char(systimestamp + 10/24,'yyyy-mm-dd hh24:mi:ss'),--10시간 더하기
    to_char(localtimestamp  + 10/24,'yyyy-mm-dd hh24:mi:ss'),--10시간 더하기
    to_char(current_timestamp + 10/24, 'yyyy-mm-dd hh24:mi:ss')--10시간 더하기
FROM dual;

분 계산

SELECT
    systimestamp,
    to_char(systimestamp + 10/(24*60),'yyyy-mm-dd hh24:mi:ss'),--10분 더하기
    to_char(localtimestamp + 10/1440,'yyyy-mm-dd hh24:mi:ss'),--10분 더하기
    to_char(current_timestamp + 10/1440,'yyyy-mm-dd hh24:mi:ss')--10분 더하기
FROM dual;

초 계산

SELECT
    systimestamp,
    to_char(systimestamp + 10/(24*60*60),'yyyy-mm-dd hh24:mi:ss'),--10초 더하기
    to_char(localtimestamp + 10/86400,'yyyy-mm-dd hh24:mi:ss'),--10초 더하기
    to_char(current_timestamp + 10/86400,'yyyy-mm-dd hh24:mi:ss')--10초 더하기
FROM dual;

months_between

  • 두 날짜간의 달(개월)수를 리턴하는 함수
SELECT
	employee_id,
    trunc(sysdate - hire_date) 근무일수,
    trunc(months_between(sysdate,hire_date)) 근무개월수,
    trunc(months_between(sysdate,hire_date)/12) 근무연수
FROM hr.employees;

add_months

  • 달수를 더하거나 빼는 함수
SELECT
	sysdate,
    add_months(sysdate,5),
    add_months(sysdate,-5)
FROM dual;

next_day

  • 입력한 날짜를 기준으로 찾고자 하는 요일의 첫번째 날짜를 반환하는 함수
    (한국 세션이기 때문에 한글로 입력)
SELECT
    next_day(sysdate,'금요일')
FROM dual;

last_day

  • 기준 날짜달의 마지막 날짜를 리턴하는 함수
select 
	last_day(sysdate),
    last_day(sysdate+100)
from dual;

[문제16]

20년 이상 근무한 사원들의 사원번호(employee_id), 입사날짜(hire_date), 근무개월수를 조회하세요.

<풀이>

SELECT 
	employee_id, 
    hire_date, 
    trunc(months_between(sysdate,hire_date)/12) as 근무개월수
FROM 
	hr.employees
WHERE 
	trunc(months_between(sysdate,hire_date)/12) >= 20;

[문제17]

사원의 last_name,hire_date 및 근무 6 개월 후 월요일에 해당하는 날짜를 조회하세요. 열별칭은 REVIEW 로 지정합니다.

<풀이>

SELECT 
    last_name, 
    hire_date, 
    next_day(add_months(hire_date,+6),'월요일') as REVIEW
FROM 
	hr.employees;

nls 언어를 변경하는 쿼리문

alter session set nls_language = 'american'

형변환 함수

데이터 타입을 변환하는 함수

to_char

  • date(날짜형)을 char(문자형)으로 변환하는 함수
  • to_char(날짜,'날짜모델요소')
SELECT
    sysdate,
    to_char(sysdate,'yyyy.mm.dd hh:mi:ss.sssss') as day1,
    to_char(sysdate,'yyyy.mm.dd hh24:mi:ss.sssss') as day2,
    to_char(sysdate,'hh hh12 hh24 am pm') as day3,
    to_char(sysdate, 'day dy') as day4,
    to_char(sysdate,'month mon mm fmmm') as day5
FROM
    dual;

SELECT  
    to_char(sysdate,'ddd dd d') as day1,-- d 일요일 1~ 토요일 7
    to_char(sysdate,'ww w') as day2,
    to_char(sysdate, 'q"분기"') as day3,
    to_char(sysdate,'q')||'분기' as day4,
    to_char(sysdate,'yyyy"년"') as day5,
    to_char(sysdate,'dd ddsp ddth ddspth') as day6--ddsp 그달을 스펠링으로 변환
FROM 
    dual;

[문제18]

사원들의 employee_id, hire_date, 입사한 요일을 출력해주세요. 단 요일을 오름차순 정렬해주세요.

<풀이>

SELECT
    employee_id,
    hire_date,
    to_char(hire_date, 'day')
FROM
    hr.employees
ORDER BY
    to_char(hire_date, 'd');

※ 월요일 기준으로 변경 할 경우 to_char(hire_date-1, 'd')

오버로딩한 to_char

  • number(숫자형)을 char(문자형)으로 변환하는 함수
  • to_char(숫자,'숫자모델요소')
SELECT
    employee_id,
    salary,
    to_char(salary, '999,999.00'),
    --빈칸을 공백으로 표현하고 싶으면 9
    to_char(salary, '000,999.00'),
    --빈칸을 0으로 표현하고 싶으면 0
    to_char(salary, '$999,999.00'),
    --앞글자 공백에 $기호 삽입
    to_char(salary, 'l999g999d00')
    --지역통화를 넣고 싶으면 영문 L
    --g,d로 표현하면 해당 세션에 맞는 포맷 제공
FROM
    hr.employees;
    
    
SELECT
    to_char(-1000,'9999'),
    to_char(-1000,'9999pr'),
    --음수를 꺽새로 표현
    to_char(-1000,'9999mi'),
    --음수부호를 뒤에 표기
    to_char(1000,'s9999')
    --양수부호를 표기
FROM dual;

[3일차 후기]
강사님은 첫날부터 일기 쓰는 습관을 가져보라고 하셨지만, 일기 또는 오늘 하루 느낀점에 대해 이렇게 글로 쓰는건 꽤나 오랜만이라 3일차가 되어서야 써보려 한다. 사실 3일차도 빠르다고 생각한다. 교육과정 초반에는 기본 sql문법을 집중적으로 배우고 있는데 나름 회사 다닐때 독학으로 끄적끄적 했던게 나름 도움이 되고 있는 것 같다. 그럼에도 불구하고 강사님께 디테일한 부분을 배우다 보면 내가 놓쳤던게 많았구나 라는걸 깨닫게 되었다. velog 블로그도 이전에는 막연하게 해야하는데 라고 생각했었는데 요번 기회를 통해 나도 IT블로그에 첫 걸음을 땐거 같아 나름 뿌듯하다.

profile
BUMSOO
이전 포스트

SQL - DAY 2

다음 포스트

SQL - DAY 4

0개의 댓글