19일차 : 함수

2022/12/22

 

[ 1. 단일함수 ] =========================================================

--[ 문자열관련 함수 ] 
-- LENGTH(주어진문자의 길이), LENGTHB(주어진문자의 BYTE)
SELECT EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME),
EMAIL, LENGTH(EMAIL), LENGTHB(EMAIL)
FROM EMPLOYEE;
-- 오라클XE는 한글을 3BYTE로 처리하므로 이름은 3글자 - 9BYTE


-- INSTR : 찾는 문자열이 지정한 위치부터 지정한 횟수만큼 나타난 위치
-- INSTR(STRING, STR, NUM1, NUM2)
-- STRING에서 STR이라는 문자열을 찾음, 찾는 위치는 NUM1부터 NUM2번째 나타나는 위치
-- STRING:문자열 STR:찾는문자열 

SELECT INSTR('Hello-World Hi High','H',1,1) FROM DUAL;
-- DUAL : 임의의 테이블
-- 문자열에서 H를 첫번째글자부터 만나는 첫번째 위치의 H가 몇번째에 있는지 찾아라
SELECT INSTR('Hello-World Hi High','H',1,2) FROM DUAL;
-- 13, 2번째 H가 13번째에 있음
SELECT INSTR('Hello-World Hi High','H',1,3) FROM DUAL;
SELECT INSTR('Hello-World Hi High','H',2,1) FROM DUAL;
SELECT INSTR('Hello-World Hi High','H',-1,1) FROM DUAL;
SELECT EMAIL, INSTR(EMAIL,'@',1,1)-1 FROM EMPLOYEE;
--EMAIL중에 @를 찾아서 -1 : 아이디만 찾을 수 있음

-- LPAD, RPAD
-- LPAD(STRING, NUM, STR), RPAD(STRING, NUM, STR)
-- STRING 문자열을 NUM길이만큼 늘림 남은 공간을 STR로 채움
-- STRING문자열의 길이가 NUM보다 긴경우는 문자열을 자름 
-- LPAD는 남은공간을 왼쪽에 채우고, RPAD는 오른쪽에 채운다
SELECT EMAIL, LENGTH(EMAIL), LPAD(EMAIL, 20, '#'), RPAD(EMAIL, 20, '#'),
LPAD(EMAIL, INSTR(EMAIL, '@', 1,1)-1)
FROM EMPLOYEE;
-- EMAIL을 20글자로 늘리고 왼쪽을 #으로 채움
-- 주의점 : 남는공간이 없는걸 넘어 모자라게되면 잘리고 출력된다.
-- LPAD(EMAIL, INSTR(EMAIL, '@', 1,1)-1) : 이메일 아이디 길이만큼만
-- 잘라내려고 쓸때는(공간이남지않을거니까) 채워줄문자를 적지않아도 된다.

-- LTRIM, RTRIM
-- LTRIM(STRING, STR) RTRIM(STRING, STR)
-- 주어진 STRING에서 STR에 포함된 문자를 왼쪽(오른쪽)에서 전부 제거
SELECT LTRIM('00000KH','0') FROM DUAL;
-- 왼쪽부터 0에 해당하는걸 모두 지움, 일치하지않는걸 찾을때까지 지움
SELECT LTRIM('12312111233KH', '123') FROM DUAL;
-- 결과 : KH, '123'을 지우는게아니라 '123'을 한글자단위로 잘라서 1 2 3을 지우는것
-- 하나의 덩어리가X 포함되어있는 문자를 지우는것
SELECT RTRIM('123KH12312111233', '123') FROM DUAL;
SELECT LTRIM(RTRIM('123KH12312111233', '123'), '123') FROM DUAL;
-- RTRIM한걸 LTRIM해서 전체를 지울 수 있음(함수를 여러개같이쓸 수 있다)

-- TRIM
-- 주어진문자열의 앞/뒤/양쪽에 지정된 문자를 제거한 나머지 반환
SELECT '1111111KH1111111' FROM DUAL;
SELECT TRIM('1' FROM '1111111KH1111111') FROM DUAL; -- 아무 옵션이 없으면 양쪽제거
SELECT TRIM(LEADING '1' FROM '1111111KH1111111') FROM DUAL; --왼쪽제거
SELECT TRIM(TRAILING '1' FROM '1111111KH1111111') FROM DUAL; -- 오른쪽제거
SELECT TRIM(BOTH '1' FROM '1111111KH1111111') FROM DUAL; -- 양쪽제거(옵션없을때 DEFAULT)
-- 한글자로 할거면 TRIM / 여러글자를 제거할거면 RTRIM LTRIM

-- SUBSTR(문자열을 지정한 위치부터 지정한 갯수만큼 잘라서 리턴)
-- SUBSTR(STRING, NUM, [LENGTH])
-- STRING의 NUM위치부턴 LENGTH개 문자열을 잘라서 리턴
-- 문법에서의 [대괄호]는 생략가능이라는 의미
-- 만약 LENGTH를 생략하면 NUM위치부터 끝까지 다 자른다
SELECT SUBSTR('Hello World Hi High', 7, 5) FROM DUAL;
-- 문자열 7번위치부터 5개를 잘라와라 : World
SELECT SUBSTR('Hello World Hi High', 7) FROM DUAL;
-- 문자열 7번위치부터 끝까지 다 잘라와라 : World Hi High
SELECT SUBSTR('Hello World Hi High', 13, 2) FROM DUAL; -- Hi
SELECT SUBSTR('Hello World Hi High', 16) FROM DUAL; -- High
SELECT SUBSTR('Hello World Hi High', -7,2) FROM DUAL; -- Hi(-는 뒤에서부터)

-- 문자열 대소문자 변환
-- LOWER(전부소문자)/UPPER(전부대문자)/INITCAP(각단어의 첫글자만 대문자)
SELECT LOWER('Hello World Hi High') FROM DUAL;
SELECT UPPER('Hello World Hi High') FROM DUAL;
SELECT INITCAP('hello world hi high') FROM DUAL;
SELECT EMAIL, UPPER(EMAIL) FROM EMPLOYEE;

-- CONCAT 
-- CONCAT(STR1, STR2) : 두 문자열을 합쳐서 리턴
SELECT CONCAT('ABCD', '가나다라') FROM DUAL;
SELECT 'ABCD'||'가나다라' FROM DUAL; -- 이걸로도 결과가 똑같음
-- CONCAT은 문자열 2개를 합쳐주기때문에 더 합차기위해선 한번 더 CONCAT을 해야한다
-- (CONCAT('ABCD', '가나다라'),'abcd')
-- 연결연산자(||)는 여러개 합치기가 가능해서 더 편하다(더자주사용)

-- REPLACE : 대체함수
-- REPLACE(STRING, STR1, STR2) : STRING중에 STR1을 STR2로 변경
SELECT REPLACE('지금 수업중인 과목은 JAVA입니다.', 'JAVA', 'ORACLE') FROM DUAL;
-- 원본 문자열에서 'JAVA'를 찾아서 'ORACLE'로 변경해줌
-- 직원메일 : 계정@kh.or.kr -> 계정@iei.or.kr
SELECT EMP_NAME, EMAIL FROM EMPLOYEE;
SELECT EMP_NAME, REPLACE(EMAIL, 'kh', 'iei') FROM EMPLOYEE;
-- 주의점 : 원본에 변화를 주는게아님! 출력할때만 변화해서 보이는 것

 

--[수학]----------------------------------------------------------------

-- ABS : 절대값
SELECT ABS(10), ABS(-10) FROM DUAL;

-- MOD : 나머지연산
SELECT MOD(10, 3) FROM DUAL;
-- 10/3 -> 3...1

-- ROUND : 반올림
-- ROUNT(NUM1, [POSITION]) : NUM1숫자를 POSITION자리에서 반올림 
SELECT ROUND(123.556) FROM DUAL; --생략 시 소수첫번째자리에서 반올림 124
SELECT ROUND(123.456, 1) FROM DUAL; --.기준 첫째자리까지 남음123.5
SELECT ROUND(123.456, 2) FROM DUAL; --.기준 둘째자리까지 남음 123.46
SELECT ROUND(123.456, -1) FROM DUAL; --.기준 반대첫째자리까지 남음 120

-- FLOOR : 소수점아래 버림
SELECT FLOOR(126.999) FROM DUAL; --126
-- CEIL : 소수점 올림
SELECT CEIL(126.111) FROM DUAL; --127

--[날짜]---------------------------------------------------------

--SYSDATE
SELECT SYSDATE FROM DUAL; --22/12/22(현재날짜)

-- 현재시간을 처리하는 함수
SELECT SYSDATE, CURRENT_DATE, LOCALTIMESTAMP, CURRENT_TIMESTAMP
FROM DUAL;
-- LOCALTIMESTAMP : 시간까지 출력 
-- CURRENT_TIMESTAMP : 시간, 지역까지 출력

-- MONTHS_BETWEEN(DATE1, DATE2) : 두 날짜사이의 월차이를 계산(일수차이는 그냥 빼면된다)
SELECT EMP_NAME, HIRE_DATE, FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) 
FROM EMPLOYEE;

-- ADD_MONTHS(DATE, NUMBER) : DATE날짜에서 NUMBER개월뒤를 계산
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM DUAL;
-- 오늘날짜(SYSDATE)에서 3(NUMBER)개월뒤 출력

-- NEXT_DAY(DATE, NUMBER) : 해당날짜에서 숫자로받은 가장 가까운 다음요일이 몇일인지
-- NUMBER : 1=일요일, 2=월요일,.....7=토요일
SELECT SYSDATE, NEXT_DAY(SYSDATE, 2) FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '월') FROM DUAL;
-- 오늘(SYSDATE)기준으로 가장가까운 월요일 출력(다음주월요일)
-- 버전에 따라 한글지원이 안될 수도있다(영어버전은 영어로), 언어가 맞아야함

-- LAST_DAY(DATE) : 인자로 전달받은 날짜가 속한 달의 마지막날
SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL; -- 22/12/31

-- EXTRACT : DATE타입데이터에서 년,월,일 정보를 추출
SELECT SYSDATE FROM DUAL; --기본형식으로추출 22/12/22
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; --년도만 추출 2022
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; -- 월만 추출 12
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; -- 일만 추출 22

SELECT EXTRACT(YEAR FROM SYSDATE)||'년'
     ||EXTRACT(MONTH FROM SYSDATE)||'월'
     ||EXTRACT(DAY FROM SYSDATE)||'일' AS 오늘날짜
FROM DUAL; -- 2022년 12월 22일

--[형변환]----------------------------------------------------------------
-- TO_CHAR : 날짜형데이터를 문자로변경하거나, 숫자형데이터를 문자로 바꾸는 함수
-- TO_CHAR(DATE, [FORMAT])
-- TO_CHAR(NUMBER, [FORMAT])
SELECT TO_CHAR(SYSDATE), SYSDATE FROM DUAL;
--원래 SYSDATE는 시분초가 포함되어있지만 TO_CHAR를 이용해서 문자로 변환하면 시분초가 날아감
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL; --형식을 지정해줄 수 있다
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD/DAY') FROM DUAL; -- 2022/12/22/목요일
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD/DY') FROM DUAL; -- 2022/12/22/목
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD/ HH:MI:SS') FROM DUAL; -- 날짜 시분초
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD/ HH"시"MI"분"SS"초"') FROM DUAL; -- 날짜 시분초
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD/ HH24"시"MI"분"SS"초"') FROM DUAL; --24시표기법
SELECT TO_CHAR(SYSDATE,'FMYYYY/MM/DD/ HH24"시"MI"분"SS"초"') FROM DUAL; 
-- FM : 00시00분00초를 0시0분0초로변경

SELECT TO_CHAR(500000), 500000 FROM DUAL;
SELECT TO_CHAR(500000, '000,000,000'), 500000 FROM DUAL; --000,500,000
--주의점 : 자릿수를 충분히줘야한다, 자릿수가 모자라면 처리가안됨
-- 0사용시 남는부분을 0으로 채워줌, 9사용시 없애줌
SELECT TO_CHAR(500000, '999,999,999'), 500000 FROM DUAL; --500,000
SELECT TO_CHAR(500000, '999,999,999.999'), 500000 FROM DUAL; -- 소수점
SELECT TO_CHAR(500000, 'L999,999,999'), 500000 FROM DUAL; --L:원화표시 \500,000
SELECT EMP_NAME, TO_CHAR(SALARY, 'L999,999,999') FROM EMPLOYEE;


-- TO_DATE : 숫자/문자를 날짜로 변경
SELECT TO_DATE(20000101,'YYYYMMDD') FROM DUAL;

SELECT TO_DATE('20000101','YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD'),'YYYY/MM/DD/ HH24"시"MI"분"SS"초"') 
FROM DUAL; --시분초정보를 안줬으므로 0시0분0초로 세팅이된다

-- TO_NUMBER
SELECT TO_NUMBER('1,000,000', '9,999,999')FROM DUAL;
-- 문자열'1,000,000'을 숫자로 변경
SELECT TO_NUMBER('100')FROM DUAL;
-- 주의점 : 숫자로 변경이 불가능하면 에러발생(정상적인 숫자를 바꿔야함)
SELECT '1000'+'100' FROM DUAL; --1100
-- 오라클의 +는 무조건 산술연산만 하기때문에 
-- 문자를 더해도 자동으로 TO_NUMBER가 돌아가서 형변환을 해서 연산을한다
-- 숫자형태의 문자면 자동연산가능(단, 숫자가아닌 형태의 문자 100A면 에러발생)

-- NVL(COLUMN, VALUE2) : NULL을 처리해주는 함수
-- COLUMN값이 있으면 그 값을 사용하고, NULL이면 VALUE2로 대체해서 사용
SELECT EMP_NAME, SALARY, NVL(BONUS, 0),(SALARY+SALARY*NVL(BONUS, 0))*12 연봉
FROM EMPLOYEE;

SELECT EMP_NAME, NVL(DEPT_CODE, '부서없음')
FROM EMPLOYEE;

-- DECODE : 선택함수1(JAVA의 SWICH~CASE와 유사)
SELECT EMP_NAME, EMP_NO
FROM EMPLOYEE;

SELECT EMP_NAME, EMP_NO,
       DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여','3','남','4','여','성별을알수없음')성별
FROM EMPLOYEE;
/*
JAVA로 따지면
switch(SUBSTR(EMP_NO,8,1)){
    case '1' : '남'; break;
    case '2' : '여'; break;
    case '3' : '남'; break;
    case '4' : '여'; break;
    default : '성별을알수없음'
}
*/

-- CASE : 선택함수2(JAVA의 ELSE IF와 유사)
SELECT EMP_NAME,
    CASE
        WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남'
        WHEN SUBSTR(EMP_NO, 8, 1) = '2' THEN '여'
        WHEN SUBSTR(EMP_NO, 8, 1) = '3' THEN '남'
        WHEN SUBSTR(EMP_NO, 8, 1) = '4' THEN '여'
        ELSE '성별을 알수없음'
    END 성별
FROM EMPLOYEE;
/*
사용법
CASE
    WHEN 조건1 THEN 조건1TRUE면 실행
    WHEN 조건2 THEN 조건2TRUE면 실행
    WHEN 조건3 THEN 조건3TRUE면 실행
    ELSE 조건이 맞는게 없으면 실행
END
*/

 

[ 2. 그룹함수 ] =====================================================================

-- 그룹함수 : 결과가 무조간 1개 FILED로 나옴
-- SUM : 누적합계를 리턴
SELECT SUM(SALARY) FROM EMPLOYEE; --급여(SALARY)의 총합 23명직원급여의 총합이 1개로 출력
-- 그룹함수는 일반컬럼과 같이 사용X, 그룹함수는 그룸함수 끼리만 사용해야함
-- SELECT EMP_NAME, SUM(SALARY) FROM EMPLOYEE; LOW갯수가 다르기때문에 이름:23 총급여:1

-- AVG : 그룹의 평균을 리턴
SELECT AVG(SALARY) FROM EMPLOYEE;
SELECT SUM(SALARY), AVG(SALARY) FROM EMPLOYEE;-- 둘다 1LOW만 나오기때문에 함께사용가능

SELECT SUM(SALARY), AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE = 'D9';
-- 조건을 걸면 조건에 해당하는 총합/평균이 나온다(WHERE절이 먼저실행되므로)
-- DEPT_CODE가 D9인 사람들의 급여총합/급여평균

-- COUNT : 조회된 ROW의 수를 리턴
SELECT COUNT(*) FROM EMPLOYEE; -- 전체LOW(23)
SELECT COUNT(BONUS) FROM EMPLOYEE; -- NULL은 셀수없음(9)
SELECT COUNT(NVL(BONUS, 0)) FROM EMPLOYEE; -- NULL값을 변경해주고 실행한다(23)

-- MAX, MIN : 그룹의 최대값 최소값
SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEE;

 

[ 3. 문제실습 ] =====================================================================

-- [문제]
-- 1. 직원명, 이메일, 이메일 길이 출력
SELECT EMP_NAME 직원명, EMAIL 이메일, LENGTH(EMAIL) 이메일길이
FROM EMPLOYEE;

-- 2. 직원명, 이메일아이디 출력
--방법1.LPAD / INSTR 사용
SELECT EMP_NAME, LPAD(EMAIL, INSTR(EMAIL, '@', 1,1)-1) EMAIL
FROM EMPLOYEE;
--방법2. SUBSTR
SELECT EMP_NAME, SUBSTR(EMAIL,1,INSTR(EMAIL, '@', 1,1)-1) EMAIL
FROM EMPLOYEE;
--방법3. REPLACE
SELECT EMP_NAME, REPLACE(EMAIL, '@kh.or.kr','') EMAIL
FROM EMPLOYEE;

-- 3. 60년생의 직원명과 년생, 보너스값을 출력하시오(보너스 NULL의경우 0으로 출력)
-- 행의갯수가 줄었다 : 무조건 WHERE절이 있음
-- 방법1.와일드카드
SELECT EMP_NAME 직원명, SUBSTR(EMP_NO, 1,2) 년생, NVL(BONUS, 0) 보너스
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 1,2) LIKE '6_';
-- 방법2.BETWEEN AND
SELECT EMP_NAME 직원명, RPAD(EMP_NO, 2) 년생, NVL(BONUS, 0) 보너스
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 1,2) BETWEEN 60 AND 69; -- 값을 포함하기때문에 70(X)
-- RPAD/LPAD는 처음부터 자르는것만가능, SUBSTR은 중간을 자르는것도 가능

-- 4. '010' 핸드폰 번호를 쓰지않는 사람의 수를 출력하시오(뒤에 단위 '명')
-- 방법1. 와일드카드
SELECT COUNT(PHONE)||'명' 인원
FROM EMPLOYEE
WHERE PHONE NOT LIKE '010%';
-- 방법2. SUBSTR
SELECT COUNT(PHONE)||'명' 인원
FROM EMPLOYEE
WHERE SUBSTR(PHONE, 1, 3) != '010';

-- 5. 직원명과 입사년월을 출력하시오
SELECT EMP_NAME 직원명, TO_CHAR(HIRE_DATE, 'YYYY"년"MM"월"') 입사일,
       EXTRACT(YEAR FROM HIRE_DATE)||'년'||
       EXTRACT(MONTH FROM HIRE_DATE)||'월' 입사일
FROM EMPLOYEE;


-- 6. 직원명과 주민번호를 조회(단 주민번호 9부터 끝자리는 '*')
-- 방법1.RPAD
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*') 주민등록번호
FROM EMPLOYEE;
-- 방법2.연결연산자
SELECT EMP_NAME, SUBSTR(EMP_NO, 1, 8)||'******' 주민등록번호
FROM EMPLOYEE;

-- 7. 직원명, 직급코드, 연봉(원) 조회(연봉은 \00,000,000으로 표시되게함, 보너스가적용된 1년치급여)
SELECT EMP_NAME, JOB_CODE,
       TO_CHAR((SALARY+SALARY*NVL(BONUS, 0))*12, 'L999,999,999') 연봉
FROM EMPLOYEE;

-- 8. 부서코드가 D5, D9인 직원들 중에서 2004년도에 입사한 직원의 사번 사원명 부서코드 입사일 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE IN('D5', 'D9') AND
      HIRE_DATE LIKE '04%';

-- 9. 직원명, 입사일, 오늘까지 근무일수 조회(주말포함, 소수점아래버림)
-- 날짜-날짜 : 두 날짜사이의 일수
SELECT EMP_NAME 직원명, HIRE_DATE 입사일, FLOOR(SYSDATE-HIRE_DATE) 근무일수
FROM EMPLOYEE;

-- 10. 모든직원의 나이중 가장 많은나이와 가장 적은나이 출력
-- 나이 : 올해년도 - 태어난년도 + 1
--       오늘날짜 - 주민등록번호 + 1
-- 1900+SUBSTR(EMP_NO,1,2))로 하면 2000년생을 계산할때 오류발생
SELECT  MAX(EXTRACT(YEAR FROM SYSDATE)-(1900+SUBSTR(EMP_NO,1,2))+1) 최대나이,
        MIN(EXTRACT(YEAR FROM SYSDATE)-(1900+SUBSTR(EMP_NO,1,2))+1) 최소나이
FROM EMPLOYEE;

SELECT MAX(
       EXTRACT(YEAR FROM SYSDATE) -
       EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,2), 'RR'))+1) 최대나이,
       MIN(
       EXTRACT(YEAR FROM SYSDATE) -
       EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,2), 'RR'))+1) 최소나이
FROM EMPLOYEE;
-- RR : 년도표현 2자리 - 50이하면 20XX / 50이상이면 19XX

-- 11. 부서코드가 D5,D6,D9 야근 / 그외는 야근없음 / 이름,부서코드 야근유무(부서코드기준 오름차순)
-- 방법1. DECODE사용
SELECT EMP_NAME, DEPT_CODE,
    DECODE(DEPT_CODE, 'D5', '야근', 'D6', '야근', 'D7', '야근', '야근없음') 야근유무
FROM EMPLOYEE
ORDER BY DEPT_CODE ASC;
-- 방법2. CASE사용 : CASE가 더 좋음 - 여러조건을 검사해야할 때는 더 좋다
SELECT EMP_NAME, DEPT_CODE,
    CASE
        WHEN 
            DEPT_CODE IN('D5', 'D6', 'D9') 
        THEN 
            '야근'
        ELSE 
            '야근없음'
    END 야근유무
FROM EMPLOYEE
ORDER BY DEPT_CODE;

-- 12. 부서코드 D5:총무부 D6:기획부 D9:영업부 부서코드기준 오름차순 
-- 방법1. DECODE : 이런경우 DECODE가 더 편하다 - 값이명확하고 값에대한 THEN이 바뀜
SELECT EMP_NAME, DEPT_CODE,
    DECODE(DEPT_CODE, 'D5', '총무부', 'D6', '기획부', 'D9', '영업부') 부서명
FROM EMPLOYEE
WHERE DEPT_CODE IN('D5', 'D6', 'D9')
ORDER BY DEPT_CODE;
-- 방법2. CASE
SELECT EMP_NAME, DEPT_CODE,
    CASE
        WHEN DEPT_CODE = 'D5' THEN '총무부'
        WHEN DEPT_CODE = 'D6' THEN '기획부'
        WHEN DEPT_CODE = 'D9' THEN '영업부'
    END 부서명
FROM EMPLOYEE
WHERE DEPT_CODE IN('D5', 'D6', 'D9')
ORDER BY DEPT_CODE ASC;

-- 13. 직원명, 부서코드, 생년월일, 나이조회(생년월일은 주민번호에서 추출해서 년월일)
--     나이는 주민번호에서 추출해서 날짜데이터로 변환한다음 계산(주민번호가 이상한 사람들은 제외 200 201 214)
SELECT EMP_NAME, DEPT_CODE,
       TO_CHAR(TO_DATE(SUBSTR(EMP_NO,1,6), 'RRMMDD'),'YY"년"MM"월"DD"일"') 생년월일,
      EXTRACT(YEAR FROM SYSDATE) -
      EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,2), 'RR'))+1 나이
FROM EMPLOYEE
WHERE EMP_ID NOT IN(200, 201, 214);
-- 200 201 214 직원의 생년월일이 이상함 - 정상적인 년/월/일만 TO_DATE로 뽑을 수 있음

-- 14. 직원들의 입사일로부터 년도만 가지고 각 년도별 입사인원수를 구하시오
-- 방법1. CASE - COUNT
SELECT 
    COUNT(CASE WHEN HIRE_DATE LIKE '98%' THEN 1 END) "1998년", 
    COUNT(CASE WHEN HIRE_DATE LIKE '99%' THEN 1 END) "1999년",
    COUNT(CASE WHEN HIRE_DATE LIKE '00%' THEN 1 END) "2000년",
    COUNT(CASE WHEN HIRE_DATE LIKE '01%' THEN 1 END) "2001년",
    COUNT(CASE WHEN HIRE_DATE LIKE '02%' THEN 1 END) "2002년",
    COUNT(CASE WHEN HIRE_DATE LIKE '03%' THEN 1 END) "2003년",
    COUNT(CASE WHEN HIRE_DATE LIKE '04%' THEN 1 END) "2004년",
    COUNT(*) 전체직원수
FROM EMPLOYEE;
-- 방법 2. DECODE - SUM
SELECT 
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),1998,1,0)) "1998년",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),1999,1,0)) "1999년",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2000,1,0)) "2000년",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2001,1,0)) "2001년",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2002,1,0)) "2002년",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2003,1,0)) "2003년",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2004,1,0)) "2004년",
    COUNT(*) "전체직원"
FROM EMPLOYEE;
--방법 3.DECODE - COUNT(NULL이용)
SELECT 
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),1998,1)) "1998년",
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),1999,1)) "1999년",
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2000,1)) "2000년",
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2001,1)) "2001년",
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2002,1)) "2002년",
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2003,1)) "2003년",
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2004,1)) "2004년",
    COUNT(*) "전체직원"
FROM EMPLOYEE;

 

[ 후기 ] -------------------------------------------------------------------------------------------------------------------------------------------------------

오늘 너무많은 함수를배워서 문제풀때마다 너무너무 헷갈렸다ㅎ 

그래도 실습을 해보니까 사용법을 거의 익힐수 있엇는데, 형변환은 너무너무 어려웠다.....

특히 나이계산...나이계산은 몇번 더 해봐야겠다

 

오늘실수한점

1. 나이계산할때 1900+태어난년도로 계산 / 코딩을 하다보면 자꾸 생각을 너무 짧게함