01_OracleDB_SQL 함수 연습 문제

---

함수 연습 문제

 

---

1. 직원명과 주민번호를 조회하세요.
   단, 주민번호 9번째 자리부터 끝까지는 '*'문자로 채운다.
   예 : 홍길동 771120-1****** 

 

SELECT 
       EMP_NAME 직원명
     , SUBSTR(EMP_NO, 1, 8) || '******' 주민번호
  FROM EMPLOYEE;

 

 

2. 직원명, 직급코드, 연봉(원) 조회하세요.
    단, 연봉은 ￦57,000,000 으로 표시되게 한다.
    연봉은 보너스 포인트가 적용된 1년치 급여이다.

 

SELECT 
       EMP_NAME 직원명
     , JOB_CODE 직급코드
     , TO_CHAR((SALARY + (SALARY * NVL(BONUS, 0)))*12, 'L999,999,999') "연봉(원)"
  FROM EMPLOYEE;

 

 

3.  부서코드가 D5, D9인 직원들 중에서 2004년도에 입사한 직원의 
     사번 사원명 부서코드 입사일 조회하세요.

SELECT 
       EMP_ID 사번
     , EMP_NAME 사원명
     , DEPT_CODE 부서코드
     , HIRE_DATE 입사일
  FROM EMPLOYEE
 WHERE DEPT_CODE IN('D5', 'D9')
   AND SUBSTR(HIRE_DATE, 1, 2) = '04';

 

 

 

4. 직원명, 부서코드, 생년월일, 나이(만) 조회하세요.
    단, 생년월일은 주민번호에서 추출해서, ㅇㅇ년 ㅇㅇ월 ㅇㅇ일로 출력되게 한다.
    나이는 주민번호에서 추출해서 날짜 데이터로 변환한 다음 계산한다.

 

SELECT 
       EMP_NAME 직원명
     , DEPT_CODE 부서코드
     , SUBSTR(EMP_NO, 1, 2) || '년 ' || SUBSTR(EMP_NO, 3, 2) || '월 ' || SUBSTR(EMP_NO, 5, 2) ||'일 ' 생년월일
     , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM(TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD'))) + 1 "나이(한국식)"
     , FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12) "나이(만)"
  FROM EMPLOYEE
-- (월, 일이 범위 값이 아닌 경우 날짜 데이터화 할 때 오류 발생하므로 해당 데이터는 제외)
 WHERE EMP_ID NOT IN ('200', '201', '214');

 

 

5. 부서코드가 D5이면 총무부, D6이면 기획부, D9이면 영업부로 처리하세요.
    단, 부서코드가 D5, D6, D9 인 직원의 정보만 조회한다.
     => HINT) 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 2;

 

 

 

6. 직원들의 입사일로 부터 년도만 가지고, 각 년도별 입사인원수를 구하세요.
    아래의 년도에 입사한 인원수를 조회하세요.
     => HINT) TO_CHAR, DECODE, COUNT

----------------------------------------------------------------
전체직원수   2001년   2002년   2003년   2004년
----------------------------------------------------------------

SELECT 
       COUNT(*) 전체직원수
     , COUNT(DECODE(TO_CHAR(HIRE_DATE, 'RRRR'), '2001', 1)) "2001년"
     , COUNT(DECODE(TO_CHAR(HIRE_DATE, 'RRRR'), '2002', 1)) "2002년"
     , COUNT(DECODE(TO_CHAR(HIRE_DATE, 'RRRR'), '2003', 1)) "2003년"
     , COUNT(DECODE(TO_CHAR(HIRE_DATE, 'RRRR'), '2004', 1)) "2004년"
  FROM EMPLOYEE;

 

---

사용한 함수 정리

 

---

 

• SUBSTR : 컬럼이나 문자열에서 지정한 위치로 부터 지정한 갯수의 문자열을 잘라서 리턴하는 함수

SELECT SUBSTR('SHOWMETHEMONEY', 5, 2) FROM DUAL;
SELECT SUBSTR('SHOWMETHEMONEY', 7) FROM DUAL;
SELECT SUBSTR('SHOWMETHEMONEY', -8, 3) FROM DUAL;
SELECT SUBSTR('쇼우 미 더 머니', 2, 5) FROM DUAL;

결과
1. ME
2. THEMONEY
3. THE
4. 우 미 더

 

• SYSDATE : 시스템에 저장 되어 있는 날짜를 반환하는 함수

SELECT SYSDATE FROM DUAL;

결과
시스템에 저장되어 있는 날짜

 

 

• TO_CHAR (날짜, [포맷])  날짜형 데이터를 문자형 데이터로 변경
• TO_CHAR (숫자, [포맷])  숫자형 데이터를 문자형 데이터로 변경

(1) TO_CHAR(숫자, [포맷])

SELECT TO_CHAR(1234) FROM DUAL;
SELECT TO_CHAR(1234, '99999') FROM DUAL;
SELECT TO_CHAR(1234, '00000') FROM DUAL;
SELECT TO_CHAR(1234, 'L99999') FROM DUAL;
SELECT TO_CHAR(1234, '$99999') FROM DUAL;
SELECT TO_CHAR(1234, '00,000') FROM DUAL;
SELECT TO_CHAR(1234, '999') FROM DUAL;

결과
1234
 1234 공백생김
01234
￦1234
 $1234
01,234
####

(2) TO_CHAR(날짜, [포맷])

SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MON, DY, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-fmMM-DD DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YEAR, Q') || '분기' FROM DUAL;

결과
오후 19:10:46
오후 07:11:02
7월, 화, 2022
2022-7-18 화요일
2022-07-18 화요일
TWENTY TWENTY-TWO, 3분기

 

 

• EXTRACT : 년, 월, 일 정보를 추출하여 리턴하는 함수

(1) EXTRACT(YEAR FROM 날짜) : 년도만 추출
SELECT EXTRACT(YEAR FROM SYSDATE) 년도 FROM DUAL;
결과
2022

(2) EXTRACT(MONTH FROM 날짜) : 월만 추출
SELECT EXTRACT(MONTH FROM SYSDATE) 월 FROM DUAL;
결과
7월

(3)  EXTRACT(DAY FROM 날짜) : 날짜만 추출
SELECT EXTRACT(DAY FROM SYSDATE) 일 FROM DUAL;
결과
18

(4) 묶어서 사용 가능
SELECT
       EXTRACT(YEAR FROM SYSDATE) 년도
     , EXTRACT(MONTH FROM SYSDATE) 월
     , EXTRACT(DAY FROM SYSDATE) 일
  FROM DUAL;

결과
2022 7 18 
 
 
EX) EMPLOYEE 테이블에서 사원 이름, 입사년, 입사월, 입사일 조회
SELECT
       EMP_NAME 사원이름
     , EXTRACT(YEAR FROM HIRE_DATE) 입사년
     , EXTRACT(MONTH FROM HIRE_DATE) 입사월
     , EXTRACT(DAY FROM HIRE_DATE) 입사일
  FROM EMPLOYEE

 

• TO_DATE : 문자 혹은 숫자형 데이터를 날짜 형 데이터로 변환하여 리턴

(1) TO_DATE(문자형데이터, [포맷])


(2) TO_DATE(숫자형데이터, [포맷])

 

• FLOOR (숫자 | 숫자로 된 컬럼명) : 내림처리하는 함수

SELECT FLOOR(123.456) FROM DUAL;
SELECT FLOOR(123.678) FROM DUAL;

결과
123
123

 

 

• WHEN 조건식 THEN 결과값

표현법
 CASE
WHEN 조건식 THEN 결과값
WHEN 조건식 THEN 결과값
ELSE 결과값
END

SELECT 
       EMP_ID
     , EMP_NAME
     , SALARY
     , CASE 
         WHEN SALARY > 5000000 THEN '고급'
         WHEN SALARY BETWEEN 3000000 AND 5000000 THEN '중급'
         ELSE '초급'
       END 구분
  FROM EMPLOYEE;