Oracle 4일차

어제 문제

–1. emp 테이블의 사원이름, 매니저번호(MGR)를 출력하고, 매니저번호가
–null이면 최상위관리자로 표시하고, 매니저번호가 있으면 해당매니저 담당임을 표시
–예) SMITH의 경우 7902담당, KING의 경우 최상위관리자

select ename, MGR,
    nvl2(mgr, mgr||'담당', '최상위관리자') as 담당
from emp;

–2. professor 테이블을 사용하여 각 교수의 이름, 부서번호, 홈페이지 주소 출력
–단, 홈페이지가 있는 경우 원래 홈페이지 주소를, 홈페이지 주소가 미정인 경우 email_id를 사용하여 아래와 같이 출력
–조인형의 경우 http://www.itwill.com/captain

select name, deptno,
    -- 내가 쓴 코드 nvl2
    nvl2(hpage, hpage, 'http://www.itwill.com/'||substr(email, 1, instr(email, '@')-1)) as 홈페이지주소,
    -- 강사님이 쓴 코드 nvl
    nvl(hpage, 'http://www.itwill.com/'||substr(email,1,instr(email,'@')-1)) as new_hpage
from professor;

–3. emp에서 부서번호가 10번인 직원은 급여의 20%를, 10번이 아닌 직원은 급여의 10%를 이름과 함께 출력
–(단, 조건문(decode, case) 사용 금지)

select ename, sal, deptno,
    nvl2(replace(deptno, 10, null), sal*0.1, sal*0.2) as 요구사항
from emp;

--강사님의 코드
select deptno, sal, 
    nvl2(nullif(deptno, 10),sal*0.1,sal*0.2) as a
from emp
order by 1;

--union 써서 하는 방법인데 아직 안 배움
select deptno, sal, sal*0.2
from EMP 
where deptno = 10 
union all 
select deptno, sal, sal*0.1
from EMP
where deptno!=10;

–4. student 테이블에서 각 학생의 이름, 전화번호를 출력
–단, 전화번호는 다음과 같은 형태로 출력
–055)381-2158 => 055)XXX-2158
–055)3811-2158 => 055)XXXX-2158

–아예 다시생각해보자…
–목표: 가운데 자리를 x로 변경하기.
–쓸 수 있는 함수: replace or translate – 가운데 자릿수가 3자리 4자리 둘다 가능하므로 replace보다는 translate가 맞을듯,
— ltrim()?을 써야하나?
–써야 할 함수: substr, instr
–이 함수들 안에서 해결해야됨.
–translate(가운데를 !로 바꾼 tel, ‘!’, ‘x’)
–tel의 가운데를 특정 문자로 바꾸기 가 목표야.
–instr을 써서 ‘)’랑 ‘-‘ 위치 알아내야지.
–substr을 써서 ‘)’랑 ‘-‘ 사이의 애들을 ‘!’로 바꿔야지.
–substr(tel, instr(tel,’)’)+1, instr(tel,’-‘)-instr(tel,’)’)-1) -> 가운데문자열
–replace(tel, substr(tel, instr(tel,’)’)+1, instr(tel,’-‘)-instr(tel,’)’)-1), ‘!!!’)
–아 이거 3번째 인수가 안돼.
–replace()안에서 *로 곱하는게 가능한가?
–오 챗지피티가 rpad()를 쓰면 가능하다네
–그럼 다시 3줄위 replace()문장으로 가서
–replace(tel,
— substr(tel, instr(tel,’)’)+1, instr(tel,’-‘)-instr(tel,’)’)-1),
— rpad(”, substr(tel, instr(tel,’)’)+1, instr(tel,’-‘)-instr(tel,’)’)-1), ‘!’)

select name, tel,
    translate(replace(tel,
        substr(tel, instr(tel,')')+1, instr(tel,'-')-instr(tel,')')-1),
    rpad('!', length(substr(tel, instr(tel,')')+1, instr(tel,'-')-instr(tel,')')-1)), '!')),
    '!',
    'x') as tel
from student;

--아 translate 안 써도 되네… 굳이 !에서 x로 2번 바꾸는 짓을 할 필요가 없지
select name, tel,
    replace(tel,
        substr(tel, instr(tel,')')+1, instr(tel,'-')-instr(tel,')')-1), --가운데 문자열을
        rpad('x',length(substr(tel, instr(tel,')')+1, instr(tel,'-')-instr(tel,')')-1)), 'x')) as replace_tel --x로replace
from student;

-- 강사님의 풀이
select name, tel,
    instr(tel,')') as ")의 위치",
    instr(tel,'-') as "-의 위치",
    instr(tel,'-')-instr(tel,')')-1 as "국번개수",
    substr(tel,
        instr(tel,')')+1, --시작위치
        instr(tel,'-')-instr(tel,')')-1) as 국번, --추출개수
    replace(tel,
        substr(tel,instr(tel,')')+1,instr(tel,'-')-instr(tel,')')-1), --찾을문자열
        -- 이 아래부분이 나랑 다름, 나는 문자열을 substr으로 구하고 거기에 길이를 length로 구했는데
        -- 그럴 필요없이 instr 2개끼리 비교하는 과정에서 개수 나오는 걸로 쓰면 되네..
        lpad('x',instr(tel,'-')-instr(tel,')')-1,'x')) as 마스킹전화번호
from student;

오늘 수업

— [일반함수]
— 1. null 치환함수 :
— 1)nvl, nvl2
— 2)isnull(sql-server)
— 3)nullif
— 4)coalesce

— 2. 조건문/함수
— if문, case문과 같이 조건별 명령을 다르게 하는 구문
— sql은 if문 사용 불가(PL/SQL문에서는 가능)
— oracle에서는 case문과 decode를 사용한 조건처리

— 예) if문 형식(oracle 문법 아님)
— if comm is null
— then 0
— else comm*1.1
— fi

— 1) decode
— 조건문 축약 함수. 일치 조건에 대한 처리만 가능(대소비교 불가)
— 사용법 : decode(대상,조건1,조건1만족시[,조건2,조건2만족시,…,기타 리턴]) -> 2개씩 짝지어져서 조건, 만족 시 리턴
— decode(대상, 조건1, 만족시, else) -> 짝 안맞게 하면 else일 때 뭐 리턴할건지도 정해줄 수 있음
— else(기타 리턴)생략 시 null리턴
— 이중decode문을 쓸 수는 있지만 성능문제+가독성문제+버그문제 때문에 case문을 쓰고 현업에서 쓰진 않는다.

select deptno,
     decode(deptno,10,'A'),
     decode(deptno,10,'A','B'),
     decode(deptno,10,'A',20,'B','C')
from emp;

— 예제) student테이블을 사용하여 모든 학생의 이름, 학과번호, 주민번호, 성별 출력
— 단, 성별은 주민번호 컬럼을 사용하여 남자 또는 여자로 가공하여 출력한다.

select name, deptno1, deptno2, jumin,
     decode(substr(jumin,7,1),'1','남자','여자') as 성별
from student;

— 예제) emp에서 각 직원의 이름, 급여, 보너스(comm) 출력
— 단, 보너스가 정의되지 않은 경우 100으로 출력(decode사용)

select ename, sal,
     decode(comm, null, 100, comm) as 보너스
-- nvl(comm, 100)
-- nvl2(comm, comm, 100)
-- coalesce(comm,100)
from emp;

— 예제) student 테이블에서 각 학생의 이름, 학년, 대표여부 출력
— 단, 대표여부 컬럼에는 4학년이면서 101번 학과소속(deptno1)은 대표로 출력, 4학년이면서 101번이 아니면 부대표 출력
— 4학년이 아닌 경우에는 일반회원으로 출력

select name, grade, deptno1,
decode(grade, 4,decode(deptno1,101,'대표','부대표'),'일반회원') as 대표여부
from student;

— 2) case문 : SQL의 대표 조건문
— 다중 분기 처리 가능, 대소비교 조건 처리 가능
— 사용법 : case when 조건1 then 리턴1
— when 조건2 then 리턴2
— …
— else 그외리턴값
— end as 별칭
— 축약형 : 일치조건이고, 매 조건의 대상이 같을 경우
— case 대상 when 상수1 then 리턴1
— when 상수2 then 리턴2
— …
— else 그외리턴
— end as 별칭

— 예제) emp 테이블에서 모든 직원의 이름, 입사일, 부서명 출력
— 단, 부서명 10번은 인사부, 20번은 총무부, 30번은 재무부

select ename, hiredate, deptno,
case when deptno=10 then '인사부'
when deptno=20 then '총무부'
else '재무부'
end as 부서명
from emp;

— 축약형 : 비교대상과 상수의 데이터타입이 반드시 일치해야 함

select ename, hiredate, deptno,
case deptno when 10 then '인사부'
when 20 then '총무부'
else '재무부'
end as 부서명
from emp;

— 예제) student 테이블을 사용하여 모든 학생의 이름, 주민번호, 성별 출력(case문 기본형, 축약형 모두 사용)

select name, jumin,
case when substr(jumin,7,1)='1' then '남자'
else '여자'
end as 성별1,
case substr(jumin,7,1) when '1' then '남자' -- 1을 문자열로 안하면 오류남
else '여자'
end as 성별2
from student;

— 조건의 형태(=,<,> 등)일 때 자동형변환이 일어난다.
— 하지만 축약형에서는 조건의 형태가 아니기 때문에 자동형변환 안 일어난다.

— [숫자함수]
— 1. 반올림/버림 : round, trunc
— 사용법 : round(숫자[, 자릿수])
— ‘자릿수’는 결과값의 자릿수를 표현함. 즉 2라고 적어놓으면 셋째자리를 건드려서 두자리로 맞춘다.
— 하지만 ‘자릿수’에 음수가 들어가면 해당자리까지 0으로 변한다.
— 자릿수 생략 시 소수점 첫번째 자리에서 반올림/버림 발생

select round(17.6), -- 18
trunc(17.6), -- 17
round(17.9875, 2), -- 17.99
round(123456.98, -3),-- 123000
round(3456.87, 0) -- 3457
from dual;

— 2. 올림/버림 : ceil, floor
— 사용법 : ceil(숫자)
— 수직선을 생각하면서 오른쪽은 ceil, 왼쪽은 floor라고 생각하면 됨
— ceil : 특정값보다 큰 값 중 가장 작은 정수
— floor : 특정값보다 작은 값 중 가장 큰 정수

select ceil(2.7), -- 3
floor(2.7), -- 2
ceil(-2.7), -- -2
floor(-2.7) -- -3
from dual;

— 3. 나머지 : mod
— 사용법 : mod(숫자1, 숫자2)
— 숫자1을 숫자2로 나눈 나머지 리턴

select trunc(7/2) as 몫, -- 3
mod(7,2) as 나머지 -- 1
from dual;

— 4. 절대값 : abs
— 사용법 : abs(숫자)

select abs(7), -- 7
abs(0), -- 0
abs(-7) -- 7
from dual;

— 5. 양/음 판별함수 : sign
— 양수이면 1, 음수이면 -1, 0이면 0 리턴

select sign(10), -- 1
sign(0), -- 0
sign(-10) -- -1
from dual;

— 예제) emp 테이블에서 급여 등급 출력
— 단, 급여등급은 3000 초과이면 A, 이하이면 B

select sal, sal-3000, sign(sal-3000),
decode(sign(sal-3000), 1, 'A', 'B') as sal_grade1,
case when sal>3000 then 'A'
else 'B'
end as sal_grade2
from emp
order by sal desc;

— [날짜함수]
— 1. sysdate : 오늘의 날짜와 시간 출력 (인수없음)
— oracle에서는 날짜와 숫자의 연산이 가능하다
— 날짜 – 날짜 : 두 날짜 사이의 일 수 리턴

select sysdate+100 as "100일 이후",
sysdate-100 as "100일 이전"
from dual;

select ename, sysdate, hiredate,
floor(sysdate-hiredate) as 근무일수,
trunc((sysdate-hiredate)/365) as 근속연수
from emp;

— 2. add_months : 월 연산
— 사용법 : add_months(날짜, 정수)
— n개월 이후의 날짜가 리턴됨

select add_months(sysdate,3), -- 정확한 월 연산
sysdate + 3*30 -- 잘못된 월 연산
from dual;

— 3. months_between : 두 날짜 사이의 개월 수 리턴
— 사용법 : months_between(날짜1, 날짜2)
— 날짜1>날짜2 : 양수
— 날짜2>날짜1 : 음수

select sysdate, hiredate,
months_between(sysdate, hiredate) as 입사개월수,
months_between(hiredate, sysdate) as 입사개월수2
from emp;

— 4. next_day : 돌아오는 특정 요일 날짜 리턴
— 사용법 : next_day(날짜, 요일)
— 요일 전달 방법 : 한글, 한글축약형 또는 영문, 영문축약형, 숫자(1234567=일월화수목금토)
— 한글이랑 영어 둘 중 하나만 동작한다. 날짜언어 설정에 따라 달라짐

-- 날짜 언어가 한글일 경우)
alter session set nls_date_language = 'korean'; -- 설정 변경
select sysdate,
next_day(sysdate,'금요일'),
next_day(sysdate,'금'),
next_day(sysdate,6)
from dual;

-- 날짜 언어가 영어일 경우)
alter session set nls_date_language = 'american'; -- 설정 변경
select sysdate,
next_day(sysdate,'FRIDAY'),
next_day(sysdate,'FRI'),
next_day(sysdate,6)
from dual;

— 5. last_day : 전달된 날짜의 월의 마지막 날짜 리턴
— 사용법 : last_day(날짜)

select sysdate, last_day(sysdate)
from dual;

— 6. round/trunc : 날짜에 대한 반올림/버림
— 사용법 : round(날짜[, 자릿수])
— 자릿수에 들어갈 수 있는 건 ‘month’, ‘year’다. day는 생략하면 되니깐 아예 안 쓴다.
— 만약 day를 쓰면 판단하기 어려운 날짜가 나오니 그냥 쓰지말자.
— 자릿수 생략 시 시분초를 버리고 일단위로 맞춘다.

select sysdate,
round(sysdate),
trunc(sysdate),
round(sysdate, 'month'), -- month로 맞춤
trunc(sysdate, 'month'),
round(sysdate, 'year'), -- year로 맞춤
trunc(sysdate, 'year')
from dual;

— [연습문제]
— 1. emp테이블에서 10번 부서원의 현재까지의 근무 월수를 계산하여 출력

select ename, hiredate, deptno,
trunc(months_between(sysdate,hiredate)) as 근무월수
from emp
where deptno=10;

— 2. emp테이블에서 입사한 달의 남은 근무 일수를 계산하여 출력
— 단, 토요일과 일요일도 근무일수에 포함한다.

select ename, hiredate,
last_day(hiredate)-hiredate as 남은근무일수
from emp;

— 3. emp테이블에서 각 직원의 이름, 입사일, 급여검토일 출력
— 단, 급여검토일은 입사날짜로부터 100일 뒤 돌아오는 월요일이다.

select ename, hiredate,
next_day(hiredate+100,2) as 급여검토일
from emp;

— [변환함수]
— 데이터 타입(날짜,숫자,문자)을 변환하기 위해 사용
— 1. to_char
— 사용법 : to_char(대상[, 포맷])
— 대상에는 날짜와 숫자
— 1) 숫자->문자

-- case1) 단순히 타입일치를 위해 문자 타입 변경
select nvl(to_char(comm),'보너스없음')
from emp;
-- case2) 숫자의 형태를 변경하고 싶을 때. ex) 1,000, $1000, 1000.00
select sal,
to_char(sal,'09999'), -- '0'은 to_char()에서 부족한 자릿수를 0으로 채움
to_char(sal,'9,999') -- '9'는 to_char()에서 부족한 자릿수를 공백으로 채움
from emp;

select sal,
to_char(sal, '$9999'),
to_char(sal, '9999.99')
from emp;

select sal, sal||'\'
from emp;

select pay,
to_char(pay, '9,999'), -- 원본보다 더 작은 자리수 전달 시 #으로 출력됨
trim(to_char(pay, '9,999,999,000'))
from emp2;

— 2) 날짜->문자
— 날짜의 포맷변경 또는 일부 추출 결과는 문자타입으로 리턴됨
— 날짜포맷 :
— yyyy / yy : 연도
— rrrr / rr : 연도
— mm : 월
— dd : 일
— hh24, hh : 시간
— mi : 분
— ss : 초
— year : 년의 글자
— month / mon : 월의 글자, 축약형
— day : 요일의 글자
— ddth, ddspth : 서수식 표현

select sysdate,
to_char(sysdate, 'yyyy') as year1,
to_char(sysdate, 'yy') as year2,
to_char(sysdate, 'mm') as month,
to_char(sysdate, 'dd') as day
from dual;

alter session set nls_date_language = 'american';
select sysdate,
to_char(sysdate, 'month') as month2,
to_char(sysdate, 'mon') as month3,
to_char(sysdate, 'day') as 요일,
to_char(sysdate, 'ddth') as 서수,
to_char(sysdate, 'ddspth') as 서수
from dual;

— 예제) emp에서 각 직원의 이름, 부서번호, 입사일 출력
— 단, 입사일은 월-일-년 순서로 출력

select ename, deptno, hiredate,
to_char(hiredate, 'mm-dd-yyyy') as 입사일
from emp;

— 2. to_number

— 3. to_date

오늘 문제

— [문제] 24.10.17 THR
— 1. emp 테이블에서 10번 부서원만 선택하여 입사 일자를 ’01 MAY 1981’형태로 변경

select ename, hiredate,
        to_char(hiredate, 'dd monthyyyy') as 입사일자
from emp
where deptno=10;

— 2. professor테이블에서 입사연도가 2000년보다 이른 경우 pay의 10%를 인상,
— 2000년 이후는(2000년포함) 8%를 인상하여 인상된 연봉과 함께 이름, 입사연도 출력

select name, pay,
        trunc(hiredate,'year') as 입사연도,
        add_months(trunc(sysdate,'month'), -9-1224) as "2000년", hiredate-add_months(trunc(sysdate,'month'), -9-1224) as "입사연도-2000년",
        case when hiredate-add_months(trunc(sysdate,'month'), -9-1224)<0 then pay1.1
               else pay*1.08
         end as 인상된연봉
from professor;

— 3. emp테이블에서 각 직원의 이름, 직업(job), 급여와 함께 입사한 날짜의 분기를 출력

select ename, job, sal, hiredate,
case to_char(hiredate,'mm') when '01' then '1분기'
                                    when '02' then '1분기'
                                    when '03' then '1분기'
                                    when '04' then '2분기'
                                    when '05' then '2분기'
                                    when '06' then '2분기'
                                    when '07' then '3분기'
                                    when '08' then '3분기'
                                    when '09' then '3분기'
                                    else '4분기'
end as 입사분기
from emp;

— 4. emp테이블을 사용하여 연봉기준 등급을 아래의 기준에 맞게 표현하세요.
— 2000미만 ‘C’, 2000이상 3000이하 ‘B’, 3000초과 ‘A’
— 단, decode문, case문 각각 작성
— decode 문에서 딱 3000인 경우를 어떻게 처리하지?? decode안에 decode로 해결했다.

select ename, sal,
        decode(sign(trunc(sal/1000)-2),-1,'C',0,'B',1,decode(sal,3000,'B','A')) as "decode 등급",
        case when sal<2000 then 'C' when sal>3000 then 'A'
               else 'B'
         end as "case 등급"
from emp;

— 5. emp 테이블을 이용하여 현재까지 근무일수를 XX년 XX개월 XX일 형태로 출력
— ex) 400일->1년 1개월 4일

select hiredate,
        months_between(sysdate, hiredate) as 근무개월수,
        trunc(months_between(sysdate, hiredate)/12) as 년수,
        trunc(mod(months_between(sysdate, hiredate), 12)) as 개월수,
        case when to_char(sysdate,'dd')-to_char(hiredate,'dd')>=0 then to_char(sysdate,'dd')-to_char(hiredate,'dd')
               else 30+to_char(sysdate,'dd')-to_char(hiredate,'dd')
         end as 일수,
        trunc(months_between(sysdate, hiredate)/12)||'년'||
        trunc(mod(months_between(sysdate, hiredate), 12))||'개월'||
        case when to_char(sysdate,'dd')-to_char(hiredate,'dd')>=0 then to_char(sysdate,'dd')-to_char(hiredate,'dd')
               else 30+to_char(sysdate,'dd')-to_char(hiredate,'dd')
         end||'일' as 근무일수
from emp;