[Oracle] 오라클 - Test 1 (SQL함수_2회차)

[Oracle]
Test 1 

---

-- 남자이면서 개발부 or 여자이면서 영업부 출력

SELECT name, rrn, city, dept, pos, DECODE(MOD(SUBSTR(rrn,8,1),2),1,'남자', '여자') 성별
FROM emp
WHERE (MOD(SUBSTR(rrn,8,1),2)='1'AND dept='개발부') OR (MOD(SUBSTR(rrn,8,1),2)='0'AND dept='영업부');

 

 

 

-- hireDate는 "2020-03-11 수요일" 형식으로 출력하고 컬럼명은 입사일로 변경

SELECT name, dept, TO_CHAR(hireDate, 'YYYY-MM-DD DAY') 입사일
FROM emp;

 

 

 

-- emp 테이블 : name, city, sal, bonus, pay, tax, 실수령액 출력
   -- 단, pay=sal+bonus, tax=pay*2%, 실수령액=pay-tax
   -- 세금과 실수령액은 소수점 첫째자리에서 반올림하며 실수령액이 200만원 이상인 자료만 출력
   -- sal, bonus, pay, tax, 실수령액은 원화기호와 세자리마다 컴마를 출력

 

1) WITH 블록 사용

   WITH tb AS (
    SELECT name, city, sal, bonus, (sal+bonus) pay, ROUND((sal+bonus)*0.02, 0) tax,
        ROUND((sal+bonus)-((sal+bonus)*0.02), 0) 실수령액
        FROM emp
   ) SELECT name, city, 
   TO_CHAR(sal, 'L9,999,999')sal, TO_CHAR(bonus, 'L9,999,999')bonus, TO_CHAR(pay, 'L9,999,999')pay, TO_CHAR(tax, 'L9,999,999')tax, TO_CHAR(실수령액, 'L9,999,999')실수령액 
   FROM tb
   WHERE 실수령액 >= 2000000;

 

2) WITH 블록 없이 짠 코드

 SELECT name, city, TO_CHAR(sal, 'L9,999,999') sal , TO_CHAR(bonus, 'L9,999,999') bonus,
		TO_CHAR((sal+bonus), 'L9,999,999') pay, TO_CHAR(ROUND((sal+bonus)*0.02, 0), 'L9,999,999') tax, TO_CHAR(ROUND((sal+bonus) - ((sal+bonus)*0.02), 0), 'L9,999,999') 실수령액
FROM emp
WHERE ROUND((sal+bonus) - ((sal+bonus)*0.02), 0) >= 2000000;

 

 

 

-- emp 테이블 : name, birth, city 출력
-- 단 birth는 rrn을 이용하며, "2000년 10월 10일" 형식으로 출력

SELECT name, TO_CHAR(TO_DATE(SUBSTR(rrn,1,6)), 'YYYY"년" MM"월" DD"일"') birth, city FROM emp;

💡 주민등록번호 rrn에서 앞에 SUBSTR(6글자 추출). date 형식이 아니기 때문에 TO_DATE 함수로 날짜형식으로 변경하고, TO_CHAR 로 문제에서 요구하는 형식으로 변환해줌. 'YYYY"년" MM"월" DD"일"'

 

 

 

--emp 테이블 : 근속년수가 10년이상 사람 출력
   -- name, 생년월일, 나이, 입사일, 근속년수 출력
   -- 생년월일과 나이는 rrn을 이용하고 근속년수는 hireDate 이용하여 계산
   -- 생년월일은 "2000-10-10" 형식으로 출력

   SELECT name, TO_CHAR(TO_DATE(SUBSTR(rrn, 1,6)), 'YYYY-MM-DD') 생년월일, TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE(SUBSTR(rrn,1,6)))/12) 나이
   , hireDate 입사일, TRUNC((MONTHS_BETWEEN(SYSDATE, hireDate)/12)) 근속년수
   FROM emp
   WHERE hireDate + (INTERVAL '10' YEAR) <= SYSDATE;

💡 나이와 근속년수 구하기
둘 다 단위가 'YEAR'인 점을 기억하기! SYSDATE - hireDate 하면 '일수'로 계산된다.
더 큰 단위로 계산할 수 있도록 MONTHS_BETWEEN 함수 사용하는 것이 더 좋음!!
** 소숫점은 모두 절삭해야한다. (TRUNC)

EX)  TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE(SUBSTR(rrn,1,6)))/12)

 

 

 

-- emp 테이블: INSTR를 이용하여 이씨가 아닌 자료만 출력
-- name, ciry 컬럼 출력

SELECT name, city 
FROM emp
WHERE INSTR(SUBSTR(name, 1, 1), '이')=0;

💡 INSTR : 해당되는 문자의 위치를 알려주는 함수로, 문자열을 검색해서 위치를 반환한다.
없으면 0!! (자바는 -1임)
INSTR(문자열, 찾을문자값, 찾기 시작할 위치, 찾은 결과의 순번)

SELECT INSTR('korea seoul' , 'e', 1, 2) FROM dual; --처음부터 검색하여 두번째 e가 나오는 위치
SELECT INSTR('korea seoul' , 'e') FROM dual; --처음부터 검색하여 가장 처음 만나는 위치

💡 문제풀이
INSTR 가 서치할 범위를 미리 SUBSTR 로 잘라낸다. '성'만 '이' 가 되는 사람의 정보를 찾을 것이니, SUBSTR 로 성만 짤라내고, 성만 짤라낸 범위(맨 첫 글자) 에 INSTR 를 붙여서 결과를 만든다.

 

 

 

-- emp 테이블: name, dept, sal, bonus, pay, tax, 실수령액 출력
   -- 단, pay=sal+bonus,tax 실수령액=pay-tax
   -- tax는 pay가 300만원이상이면 pay의 3%, pay가 250만원이상이면 pay의 2%, 그렇지 않으면 0
   -- tax는 일의자리에서 절삭

WITH tb AS (
    SELECT name, dept, sal, bonus, (sal+bonus) pay,
       CASE WHEN (sal+bonus) >= 3000000 THEN TRUNC((sal+bonus)*0.03, -1)
            WHEN (sal+bonus) >= 2500000 THEN TRUNC((sal+bonus)*0.02, -1)
            ELSE 0
            END tax FROM emp
) SELECT name, dept, sal, bonus, pay, tax, (pay-tax) 실수령액 FROM tb;

💡 절삭(일의 자리) : TRUNC(값, -1) : -1은 1의 자리. 0은 소수 첫번째 자리 절삭. 1은 소수 두번째 자리 절삭

 

 

 

-- emp 테이블: name, hireDate 출력
    -- 단, 입사일자가 월요일인 사람만 출력

SELECT name, hireDate FROM emp WHERE TO_CHAR(hireDate, 'd')=2;

💡 'd' 를 사용하면 요일값을 추출할 수 있고, 1(일요일) 부터 시작한다.

 

 

 

-- emp 테이블: name, hireDate 출력
    -- 단, 입사일자의 일자가 1일~5일인 사람만 출력

 SELECT name, hireDate FROM emp WHERE TO_CHAR(hireDate, 'DD') IN (1,2,3,4,5);

💡 'DD' 는 일자를 나타날 때 사용한다.

 

 

 

-- 현재시간에서 '2020-03-12 09:00:00' 까지의 차이를 분으로 환산하여 출력 : dual 테이블 이용

SELECT (TO_DATE('2020-03-12 09:00:00', 'YYYY-MM-DD HH24:MI:SS') 
	- TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')) *24 *60 FROM dual;

💡 분으로 출력하기 위해서 *24, *60 해주기

 

 

 

-- 다음달 시작날자 출력(다음달 1일 0시 0분 0초) : dual 테이블 이용

아래와 같이 코드 짜면, 딱 1달 뒤 결과가 나옴. TRUNC 에서 'MONTH' 기준 넣어야 완전히 절삭됨.

SELECT TRUNC(TO_DATE(SYSDATE+(INTERVAL '1' MONTH))) FROM dual;

SELECT TRUNC(TO_DATE(SYSDATE+(INTERVAL '1' MONTH)), 'MONTH') FROM dual;

 

 

 

 -- emp 테이블 : 회사의 정년이 만 60세이다. 만약 나이가 60을 초과하면 "정년초과"를 출력하고,
     나이가 60이면 "올해정년"을 출력하며, 그렇지 않으면 앞으로 정년까지 남은 기간(년수)을 출력한다.
     나이와 생년월일은 rrn을 이용하여 계산한다.
     출력 형식 : name, birth(YYYY-MM-DD), age, 정년까지남은년수

WITH tbs AS (
   SELECT name, 
  TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(rrn, 1,6), 'RRMMDD'))/12) age,
   CASE WHEN SUBSTR(rrn, 8,1) IN (1,2) THEN TO_DATE('19' || SUBSTR(rrn, 1,2) || SUBSTR(rrn,3,4))
        WHEN SUBSTR(rrn, 8,1) IN (3,4) THEN TO_DATE('20' || SUBSTR(rrn, 1,2) || SUBSTR(rrn,3,4))
   END birth
   FROM emp
)
SELECT name, TO_CHAR(birth, 'YYYY-MM-DD') birth, age, 
    CASE WHEN age > 60 THEN '정년초과' 
     	  WHEN age = 60 THEN '올해정년'
           ELSE TO_CHAR(60 - age)
    END "정년까지남은년수"
FROM tbs;