Oracle 3일차

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

select * from student;
select name,
replace(replace(TEL,')',''), '-', '') as 전화번호
-- substr(TEL,1,instr(TEL,')')-1)||
-- substr(TEL,instr(TEL,')')+1, instr(TEL,'-')-instr(TEL,')')-1)||
-- substr(TEL,instr(TEL,'-')+1) as 전화번호
from student;

— 2. student 테이블에서 각 학생의 이름, 학년, 생년월일 출력(주민번호에서 추출)
— 단, 이름의 두번째 글자를 마스킹 처리하여 표현

select replace(name, substr(name, 2, 1), 'x') as 이름,
grade,
substr(jumin, 1, 6) as 생년월일
from student;

— 날짜 타입의 컬럼은 substr 추출 권장 X -> to_char(), extract() 사용

desc student;

select jumin, substr(jumin, 3, 2) as month1,
birthday, substr(birthday, 6, 2) as month2
from student;

— 3. student 테이블에서 각 학생의 이름, 지역번호와 국번 각각 추출
— 단, 055)381-2158에서 지역번호는 055, 국번은 381을 의미

select name,
substr(TEL, 1, instr(TEL,')')-1) as 지역번호,
substr(TEL, instr(TEL,')')+1, instr(TEL,'-')-instr(TEL,')')-1) as 국번
from student;

— 4. HR 계정에서 수행
— EMPLOYEES 테이블에서 EMAIL에 LAST_NAME이 포함되어 있는 직원의
— EMPLOYEE_ID, LAST_NAME, EMAIL 출력 (대소 구분x) (총 85건)
— OConnell DOCONNEL (X)
— Grant DGRANT (O)
— Whalen JWHALEN (O)
— De Haan LDEHAAN (X)

select EMPLOYEE_ID, LAST_NAME, EMAIL
from EMPLOYEES
where instr(EMAIL, upper(LAST_NAME)) > 0;
--where EMAIL like '%'||upper(LAST_NAME)||'%'; -- 이 방식이 정석

—- [문자함수]
— 1. 대소문자 치환 : upper, lower, initcap
— 2. 문자열 추출 : substr
— 3. 문자열 위치 : instr
— 4. 문자열 길이 : length, lengthb
— 5. 문자열 치환/삭제 : replace, translate

— replace : 단어의 치환/삭제
— 사용법 : replace(원본대상, 찾을단어[, 바꿀단어])
— translate : 글자의 치환/삭제
— 사용법 : translate(원본대상, 찾을 문자열, 바꿀 문자열)
— 찾을 문자열과 바꿀 문자열을 글자별로 매핑하여 치환
— 3번째 파라미터에 null 전달 불가. 전달 시 모든 글자가 null됨

select replace('abcba', 'ab', 'AB'), -- output: Abcba
translate('abcba', 'ab', 'AB') -- output: ABcBA
from dual;

select translate('abcba', 'ab', 'ABC') -- 3번째 파라미터 'ABC'에서 'C'는 무시
from dual; -- output: ABcBA

select translate('abcba', 'abc', 'AB') -- 2번째 파라미터 'abc'에서 'c'는 삭제
from dual; -- output: ABBA

— 어제 문제)
— 1. student 테이블에서 각 학생의 이름, 전화번호를 출력
— 단, 전화번호는 다음과 같은 형태로 출력
— 055)381-2158 -> 0553812158

select * from student;
select name,
translate(tel, '!)-', '!') as 전화번호 -- 세번째 파라미터에 빈문자열이 못들어가니깐 !사용
from student;

— 예제) professor 테이블의 ID 컬럼의 모든 숫자 삭제

select id, translate(id, 'a0123456789', 'a')
from professor;

— [연습문제]
— 1) professor 테이블에서 101번 학과 소속 교수의 이름, 직급, 학과번호(deptno) 출력
— 단, 직급은 교수 또는 강사로만 출력되도록 함

select name as 이름,
substr(position, -2) as 직급,
-- translate(position, '1정조전임', '1') as 직급,
deptno as 학과번호
from professor
where deptno = 101;

— 2) dept2 테이블에서 area의 값을 아래와 같이 변경
— 본사->본부, 지사->본사

select area,
replace(replace(area, '본사', '본부'), '지사', '본사') as 변경후
from dept2;

— 6. 문자열 삽입 : lpad, rpad
— 문자열을 정해진 길이만큼 채우는 함수
— 사용법 : lpad(대상, 전체크기(bytes수)[, 채울 문자])
— 채울문자 생략 시 공백 삽입됨
— 지정한 전체크기를 넘기는 대상이 들어가면 전체크기까지만 나오고 뒤는 짤림

select lpad('abcd', 10, '') -- output: *abcd
from dual;

— 활용예) 날짜의 요일
–년 월 일 -> 년월일 결합 -> 날짜파싱 -> 요일변환
–2024 12 10 ->20241210(lpad를 사용하여 한자리의 월, 일을 두자리로 변경)
–2024 1 1 ->20240101

— 예제) professor 테이블에서 ID를 모두 10자로 표현, 왼쪽에 공백 삽입

select id, lpad(id, 10) as modifing
from professor;

— 7. 문자열 또는 공백 삭제 : trim, ltrim, rtrim
— ltrim, rtrim은 각각 왼쪽 오른쪽에서부터 연속되는 원하는 문자열 또는 공백 제거
— trim은 양쪽에 있는 공백만 제거 가능
— ltrim rtrim 사용법 : ltrim(대상[, 제거할 문자열])
— 제거할 문자열 생략 시 공백 제거
— trim 사용법 : trim(대상)

select ltrim('aabacaaa', 'a'), --output: bacaaa
rtrim('aabacaaa', 'a'), --output: aabac
ltrim(' bac '), --output: bac
rtrim(' bac ') --output: bac
from dual;

select trim(' bac ') --output: bac
from dual;

— 활용예1) 불필요한 공백이 있는 문자열 비교 시
— id = ‘abc ‘ 일 때
— where trim(id) = ‘abc’;

— 활용예2) 불필요한 공백이 있는 문자열의 그룹연산
— 지역 상호명 매출 -> <지역별 매출 총합>
— 서울 a 10 지역 매출
— 서울 b 20 서울 10
— 경기 c 15 서울 20 서울 지역에 불필요한 공백 삽입으로 인해 하나의 그룹으로 묶이지 않는 현상
— 경기 d 25 경기 40

— [공백으로 인한 조회 및 그룹명 문제 테스트]

create table test1(
지역1 char(5), -- char는 뒷 공백은 무시한다
지역2 varchar2(5),
상호명 varchar2(10),
매출 number);

insert into test1 values('서울','서울','A',100);
insert into test1 values(' 서울',' 서울','B',200);
insert into test1 values('서울 ','서울 ','C',300);
insert into test1 values('경기','경기','D',150);
insert into test1 values('경기','경기','E',250);

commit;

— char 타입인 경우 문자열 비교 시 뒷공백 제거 후 비교(앞공백 무시X)

select *
from test1
where 지역1 = '서울'; --output: A, C행 출력

— varchar 타입인 경우 문자열 비교 시 공백 다 따짐

select *
from test1
where 지역2 = '서울'; --output: A행 출력

select *
from test1
where trim(지역2) = '서울'; --output: A, B, C행 출력

select 지역1, sum(매출)
from test1
group by 지역1; --output: 서울이 2개(뒷공백, 앞공백)로 나뉨

select 지역2, sum(매출)
from test1
group by 지역2; --output: 서울이 3개(뒷공백, 앞공백, 그냥)로 나뉨

— [숫자함수]
— round, trunc, mod, ceil, floor, abs, …

— [날짜함수]
— sysdate, extract, add_months, …

— [일반함수]
— 1. null 치환 함수: nvl, nvl2, isnull, nullif, coalesce
— (1)nvl 사용법 : nvl(대상, 널일 때 치환값)
— ‘대상’과 ‘치환값’의 타입은 같아야한다. ‘대상’의 데이터타입이 최종 데이터타입이 됨
— (2)nvl2 사용법 : nvl2(대상, 널 아닐 때 치환값, 널일 때 치환값)
— 두번째 세번째 인수의 데이터 타입이 같아야한다. 두번째 인수의 타입이 최종 데이터타입이 됨
— 첫번째 인수인 대상의 타입은 상관없다
— 문자타입에 숫자를 넣으면 문자로 자동형변환돼서 들어가진다
— 숫자타입에 ‘숫자로 변할 수 있는 문자’를 넣으면 자동형변환돼서 들어가진다

select ename, sal, comm,
sal+comm as 총급여1,
sal+nvl(comm, 0) as 총급여2
from emp;

— 예제) professor에서 hpage가 없으면 홈페이지 없음 출력

select hpage, nvl(HPAGE, '홈페이지 없음') as 홈페이지구분
from professor;

— 예제) professor에서 hpage가 있으면 홈페이지 있음, 없으면 홈페이지 없음 출력

select hpage, nvl2(HPAGE, '홈페이지 있음', '홈페이지 없음') as 홈페이지구분
from professor;

— 문자, 숫자 타입의 정렬규칙

create table test2(
col1 varchar2(4),
col2 number(4));

insert into test2 values(100, 100); --정상
insert into test2 values(2, 'a'); --에러
insert into test2 values(50, '13'); --정상
insert into test2 values(2, 40); --정상

select *
from test2
order by col1; --output: 문자의 기준으로 정렬

select *
from test2
order by col2; --output: 숫자의 기준으로 정렬

rollback;

— [연습문제]
— emp 테이블에서의 전체 직원의 이름, 부서번호, 총급여를 출력
— 단, 총급여는 기존급여에 보너스를 합한 급여로 보너스가 정의되지 않은 경우 500을,
— 보너스가 있는 경우 10% 인상값으로 계산한다.

select ename, deptno, sal, comm,
sal+nvl2(comm, comm*1.1, 500) as total_sal
from emp;

— (3) isnull : sql-server에서의 null치환 함수

select isnull(null, 0); -- 0 리턴

select isnull(comm, 0)
from emp;

— (4) nullif : oracle에서의 null로 치환해주는 함수
— 사용법 : nullif(대상, 비교할 값)
— ‘대상’과 ‘비교할 값’이 같으면 null로, 다르면 그대로 리턴

select comm, nullif(comm, 0)
from emp;

— (5) coalesce : oracle에서의 null 치환 함수
— 사용법 : coalesce(대상1, 대상2, …)
— null이 아닌 값이 나올 때까지 계속 검사.
— null이면 다음 대상 검사, null이 아닌 값이 나오면 그 대상 리턴.

--이거는 꼭 한 번 실행시켜 보기. 좀 헷갈린다.
select comm, coalesce(comm, 0)
from emp;

— 예제) deptno2를 검사해서 null이 아니면 deptno2 출력,
— null이면 dept1 출력

select deptno2, deptno1,
coalesce(deptno2, deptno1, 100)
from student;

— 문제(24.10.16 Wed)
–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(hpage, hpage, 'http://www.itwill.com/'||substr(email, 1, instr(email, '@')-1)) as 홈페이지주소
from professor;

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

select ename, sal, deptno,
nvl2(replace(deptno, 10, ''), sal0.1, sal0.2) as 요구사항
from emp;

–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 안 써도 되네…
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
from student;