2024.11.29 3일차 수업

어제 복습 문제) employees 테이블에 last_name 컬럼의 값 중에 J, A, M으로 시작하는 사원들의 last_name, last_name의 길이를 출력해 주세요. 사원들의 last_name 기준으로 내림차순 정렬할 것

 

1) like 사용 (like에는 in을 쓰지 못한다. 같은 연산자끼리는 합칠 수 없다.)

select last_name,length(last_name)
from hr.employees
where last_name like 'J%'
or last_name like 'A%'
or last_name like 'M%'
order by 1 desc;

2) substr 사용

select last_name,
       length(last_name)
from hr.employees
where substr(last_name,1,1) = 'J'
or substr(last_name,1,1) = 'A'
or substr(last_name,1,1) = 'M'
order by 1 desc;

select last_name,
       length(last_name)
from hr.employees
where substr(last_name,1,1) in ('J','A','M')
order by 1 desc;

3) instr 사용

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;

---

1. replace 함수 : 문자를 다른 문자로 치환하는 함수

      - replace(컬럼(문자열), 이전문자, 새로운 문자)

      - 공백이 많으면 replace 함수로 한 번에 제거할 수 있다.

select 
replace('100-001','-','%'),
replace('100-001','-',''),
replace(' 100  001 ',' ','')
from dual;

출력

 

2. lpad, rpad : 첫 번째에 빈칸으로 두면 null이 나오기 때문에 공백이라도 두어야 값이 나옴

 

   - lpad : 문자의 자리를 고정시킨 후 문자값을 오른쪽 기준으로 채우고 빈 왼쪽 공백을 다른 값으로 채우는 함수

 

   - rpad : 문자의 자리를 고정시킨 후 문자값을 왼쪽 기준으로 채우고 빈 오른쪽 공백을 다른 값으로 채우는 함수

 

ex1) 

select 
     last_name,
     lpad(last_name,10,'*')
from hr.employees;

출력

ex2)

select 
     salary,
     lpad(salary,10,'*')
     rpad(salary,10,'*')
from hr.employees;

출력

 

문제) salary 값을 1000당 * 출력해 주세요.

 

예시 - 5000이면 ***** , 3000이면 *** 출력

 

답 :

select 
     salary,
     lpad('*',salary/1000,'*'),
     lpad(' ',salary/1000+1,'*')
from hr.employees;

출력

- 먼저 자릿수를 고정시킨다. salary / 1000을 나누면 값의 자릿수가 나오고 *오른쪽에 채우고 그 나머지만큼 *을 채우면 된다.

 

3. 숫자함수

  

  - round : 지정된 소수점 자릿수 값을 반올림하는 함수

select 
     45.926,
     round(45.926, 2),
     round(45.926, 1),
     round(45.926, 0),
     round(45.926),
     round(45.926, -1),
     round(45.926, -2,
     round(55.926, -2)   
from dual;

출력

 

 - trunc : 지정된 소수점 자릿수 값을 버림하는 함수

select 
     45.926,
     trunc(45.926, 2),
     trunc(45.926, 1),
     trunc(45.926, 0),
     trunc(45.926),
     trunc(45.926, -1),
     trunc(45.926, -2),
     trunc(155.926, -2) 
from dual;

출력

 

 - ceil : 숫자값을 가장 큰 정수로 반환하는 함수

select
round(10.1),
ceil(10.1),
ceil(10),
ceil(10.0000001)
from dual;

출력

 - floor : 숫자값을 가장 작은 정수로 반환하는 함수

select
trunc(10.1),
floor(10.1),
floor(10),
floor(10.0000001),
floor(-10.000001)
from dual;

출력

 

 - mod : 어떤 값을 나눈 나머지를 반환하는 함수

select 
12/5,
mod(12,5)
from dual;

출력

 

 - power : 거듭제곱

select
2*2*2,
power(2,3),
power(2,5)
from dual;

출력

 

 - abs : 절댓값

select
-15,
abs(-15)
from dual;

출력

 

 - sqrt : 루트

select
sqrt(9)
from dual;

출력

 

---

4. 날짜함수

   - sysdate : 현재 서버 날짜를 리턴하는 함수

   - systimestamp : 현재 서버 날짜 시간 타임존을 리턴하는 함수

   - current_date : 현재 클라이언트의 날짜를 리턴하는 함수

   - current_timestamp : 현재 클라이언트의 날짜 시간 타임존을 리턴하는 함수

   - localtimestamp : 현재 클라이언트의 날짜 시간을 리턴하는 함수

   - 날짜 계산(hire_date는 날짜 타입이기 때문에 날짜 계산할 때만 연산 가능)

 

  # 타임존을 변경하는 방법 (session(화면이 켜질동안만))

alter session set time_zone = '08:00';

- 시간은 맘대로 변경 가능

 

날짜	+	숫자(일수)	=	날짜
날짜	-	숫자(일수)	=	날짜
날짜	-	날짜	=	숫자(일수)
날짜	+	시간	=	날짜 시간

 

• 시간을 표현할 때 : 시간/24
• 분을 표현할 때 : 분/(24*60), 분/1440
• 초를 표현할 때 : 초/(24*60*60), 초/86400

ex) to_char(~~~,'yyyy-mm-dd hh24:mi:ss')를 적지 않으면 날짜만 출력이 된다.

select
   systimestamp,
   to_char(systimestamp + 4/24,'yyyy-mm-dd hh24:mi:ss'),
   to_char(localtimestamp + 600/1440,'yyyy-mm-dd hh24:mi:ss'),
   to_char(current_timestamp + 3600/86400,'yyyy-mm-dd hh24:mi:ss')
from dual;

출력

 

- months_between : 두 날짜 간의 달 수(개월 수)를 리턴하는 함수

select
    employee_id 사번,
    sysdate - hire_date 근무일수,
    months_between(sysdate, hire_date) 근무달수
from hr.employees;

출력

 

- add_months : 달 수를 더하거나 빼는 함수

select
    add_months(sysdate,5),
    add_months(sysdate,-5)
from hr.employees;

출력

 

- next_day : 입력한 날짜를 기준으로 찾고자 하는 요일의 날짜를 반환하는 함수, 언어에 종속받는다. (현재 접속한 지역에 따라)

select
     sysdate,
     next_day(sysdate,'월요일')
from dual;

출력

 

- last_day : 기준 날짜의 달에 마지막 날짜를 리턴하는 함수

select
     sysdate,
     last_day(sysdate)
from dual;

출력

---

문제 1) 20년 이상 근무한 사원들의 employee_id, hire_date, 근무 개월수를 출력해라

select
    employee_id,
    hire_date,
    months_between(sysdate, hire_date)
from hr.employees
where months_between(sysdate,hire_date) >= 240;

select 
    employee_id,
    hire_date,
    months_between(sysdate, hire_date)
from  hr.employees
where months_between(sysdate, hire_date)/12 >= 12;

해설 : ① select절에 months_between 함수를 통해 현 시간과 입사한 날짜의 개월 수를 나타낸다.

          ② 조건절인 where절을 통해 위에 months_between 함수를 똑같이 적고 그 결과 값이 20년인 240개월보다 크거나 같은 조건을 만족한 사원들이 20년 이상 근무한 사원들이다.

 

문제 2) 사원들의 last_name, hire_date(입사일), 근무 6개월 후 월요일에 해당하는 날짜 정보를 출력해라

select
    last_name,
    hire_date,
    next_day(add_months(hire_date,6),'월요일')
from hr.employees;

해설 : ① 먼저 add_months를 통해 입사한 날짜부터 6개월 후 날짜를 구한다.

          ② 그 값을 next_day를 통해 그 날짜부터 가장 가까운 월요일을 구하면 된다.

---

5. 형변환 함수

  - to_char : date를 char형으로 변환하는 함수, 날짜가 숫자로 출력되가지고 숫자 타입이 아니라 숫자로 된 문자를 리턴한 것이다.

  - to_char(날짜, '날짜 모델 요소')

 

ex1)

select
     sysdate,
     to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss.sssss'),
     to_char(sysdate, 'yyyy-mm-dd hh:mi:ss.sssss'),
     to_char(sysdate, 'yyyymmddhhmisssssss')
from dual;

출력

▶ hh24에서 24를 빼면 오전인지 오후인지 구분이 안 가서 표현해주는 게 좋다.

 

ex2)

select
     sysdate,
     to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss.sssss'),
     to_char(sysdate, 'yyyy-mm-dd hh:mi:ss.sssss'),
     to_char(sysdate, 'yyyymmddhhmisssssss'), 
     to_char(sysdate, 'yy rr yyyy rrrr year yyyy"년"'),
     to_char(sysdate, 'month mon mm'),
     to_char(sysdate, 'ddd dd d'), -- d 숫자요일 일요일 1 ~ 토요일 7
     to_char(sysdate, 'day dy d'),
     to_char(sysdate, 'ww w'),
     to_char(sysdate,'q"분기"'),   -- 분기를 나타낸다 (현재 4분기)
     to_char(sysdate, 'dd ddth ddsp ddthsp')
from dual;

출력 1

출력 2

해설 : 

• d : 숫자요일 ex) 일요일 1 ~ 토요일 7
• dy : 현재 요일 ex) 목
• day : 현재 요일 ex) 목요일
• ddd : 365일 기준 현재 며칠 지났는지 반환
• ww : 1년 기준 몇 주 지났는지 반환
• q : 현재 몇 분기인지 반환
• mon : 현재 몇 월인지 반환
• w : 현재 날짜가 현재 달의 몇 주차인지 반환
• hh24 : 하루를 24시간으로 표시
• hh : 하루를 12시간으로 표시
• ddth : 몇 번째 날인지 표시
• mm : 숫자 두 자리로 표현 ex) 07
• month : 월을 뜻하는 이름 전체 표시 

ex) 입사한 날별로 요일을 나타내고 sort 정렬을 하고 싶은 경우 

select employee_id, hire_date, to_char(hire_date, 'day')
from hr.employees
order by 3;

출력

▶ 금요일부터 나오는 이유는 한글은 가-나-다 순으로 정렬이 되기 때문이다.

 

ex) 저 위에 값에서 요일별로 정렬하고 싶을 경우

select employee_id, hire_date, to_char(hire_date, 'day')
from hr.employees
order by to_char(hire_date, 'd');

- d를 써줌으로써 숫자요일 순서대로 나타내기 때문에 요일정렬 할 때는 d를 쓴다.

출력

▶일요일이 1번이기 때문에 일요일부터 순서대로 출력이 되는 것이다.

 

ex) 보기 편하게 월요일부터 일요일까지 정렬하고 싶은 경우

select employee_id, hire_date, to_char(hire_date, 'day')
from hr.employees
order by to_char(hire_date - 1, 'd');

출력

▶ order by절에 -1 해주면 월요일부터 시작되기 때문이다.

 

ex) 일요일에 입사한 사원을 출력하고 싶은 경우

 

1번째 방법)

select *
from hr.employees
where to_char(hire_date,'d') = 1;

▶ to_char는 문자형이고 1은 숫자 타입이라 오류가 되지만 오라클에선 문자가 숫자보다 힘이 쎄기때문에 내부적으로 문자형으로 반환된다.

 

2번째 방법)

select *
from hr.employees
where to_char(hire_date,'dy') = '일';

 

3번째 방법)

select *
from hr.employees
where to_char(hire_date,'day') = '일요일';

 

- to_number : char를 number형으로 변환하는 함수

select salary, to_char(hire_date,'mm'), to_number(to_char(hire_date, 'mm'))
from hr.employees
where to_char(hire_date,'d') = 1;

출력

▶ 기존에 문자형으로 변환한 것을 to_number를 통해 한 번 더 묶어서 출력한다. 결과를 보면 숫자형이기 때문에 오른쪽에 결과값이 나오는 걸 볼 수 있다.

▶하지만 가운데 문자형 컬럼에 앞에 0을 제거하고 싶을 경우가 있다. 그럴 땐 fm을 사용한다.

 

- fm : 선행되는 제로를 제거하는 기능

select salary, to_char(hire_date,'fmmm'), to_number(to_char(hire_date, 'mm'))
from hr.employees
where to_char(hire_date,'d') = 1;

▶ mm 앞에 fm만 붙여주면 된다. 

출력