Archive for

--SQL05_DML

-- 1번
-- 과목유형 테이블(TB_CLASS_TYPE)에 아래와 같은 데이터를 입력하시오.
INSERT INTO TB_CLASS_TYPE VALUES('01','전공필수');
INSERT INTO TB_CLASS_TYPE VALUES('02','전공선택');
INSERT INTO TB_CLASS_TYPE VALUES('03','교양필수');
INSERT INTO TB_CLASS_TYPE VALUES('04','교양선택');
INSERT INTO TB_CLASS_TYPE VALUES('05','논문지도');

-- 2번
-- 춘 기술대학교 학생들의 정보가 포함되어 있는 학생일반정보 테이블을 만들고자 한다.
-- 아래 내용을 참고하여 적절한 SQL 문을 작성하시오. (서브쿼리를 이용하시오)
CREATE TABLE TB_학생일반정보
AS 
SELECT 
       STUDENT_NO 학번
     , STUDENT_NAME 학생이름
     , STUDENT_ADDRESS 주소
  FROM TB_STUDENT;

-- 3번
-- 국어국문학과 학생들의 정보만이 포함되어 있는 학과정보 테이블을 만들고자 한다.
-- 아래 내용을 참고하여 적절한 SQL 문을 작성하시오. (힌트 방법은 다양)
CREATE TABLE TB_국어국문학과
AS 
SELECT STUDENT_NO 학번
     , STUDENT_NAME 학생이름
     , 19||SUBSTR(STUDENT_SSN,1,2) 출생년도
     , PROFESSOR_NAME 교수이름
  FROM TB_STUDENT S
     , TB_PROFESSOR P
     , TB_DEPARTMENT D
 WHERE S.COACH_PROFESSOR_NO = P.PROFESSOR_NO(+)
   AND S.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND DEPARTMENT_NAME = '국어국문학과';

-- 4번
-- 현 학과들의 정원을 10% 증가시키게 되었다. 이에 사용할 SQL 문을 작성하시오.
-- (단, 반올림을 사용하여 소수점 자릿수는 생기지 않도록 한다.)
UPDATE 
       TB_DEPARTMENT
   SET CAPACITY = ROUND(CAPACITY * 1.1);

-- 5번
-- 학번 A413042인 박건우 학생의 주소가 "서울시 종로구 숭인동 181-21"로 변경되었다고 한다.
-- 주소지를 정정하기 위해 사용할 SQL 문을 작성하시오.
UPDATE 
       TB_STUDENT
   SET STUDENT_ADDRESS = '서울시 종로구 숭인동 181-21'
 WHERE STUDENT_NO = 'A413042';

-- 6번
-- 주민등록번호 보호법에 따라 학생정보 테이블에서 주민번호 뒷자리를 저장하지 않기로 결정하였다.
-- 이 내용을 반영할 적절한 SQL 문장을 작성하시오.
UPDATE 
       TB_STUDENT
   SET STUDENT_SSN = SUBSTR(STUDENT_SSN, 1, 6);

-- 7번
-- 의학과 김명훈 학생은 2005년 1학기에 자신이 수강한 '피부생리학' 점수가 잘못되었다는 것을 발견하고는 정정을 요청하였다.
-- 담당 교수의 확인 받은 결과 해당 과목의 학점을 3.5로 변경키로 결정되었다. 적절한 SQL 문을 작성하시오
UPDATE 
       TB_GRADE
   SET POINT = 3.5
 WHERE TERM_NO = '200501'
   AND STUDENT_NO = (SELECT STUDENT_NO
                       FROM TB_STUDENT
                       JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
                      WHERE STUDENT_NAME = '김명훈'
                        AND DEPARTMENT_NAME = '의학과')
   AND CLASS_NO = (SELECT CLASS_NO
                     FROM TB_CLASS
                    WHERE CLASS_NAME = '피부생리학');
               
-- 8번
-- 성적 테이블에서 휴학생들의 성적항목을 제거하시오
DELETE 
  FROM TB_GRADE
 WHERE STUDENT_NO IN (SELECT STUDENT_NO
                        FROM TB_STUDENT
                       WHERE ABSENCE_YN = 'Y');

ROLLBACK;

SQL02_SELECT(FUNCTION)

-- 1번
-- 영어영문학과(학과코드 002) 학생들의 학번과 이름, 입학 년도를 입학 년도가 
-- 빠른 순으로 표시하는 SQL문장을 작성하시오.
-- (단, 헤더는 "학번", "이름", "입학년도" 가 표시되도록 한다.)
SELECT 
       STUDENT_NO AS 학번
     , STUDENT_NAME AS 이름
     , TO_CHAR(ENTRANCE_DATE, 'YYYY-MM-DD') AS 입학년도
  FROM TB_STUDENT
 WHERE DEPARTMENT_NO = '002'
 ORDER BY ENTRANCE_DATE;

-- 2번
-- 춘 기술대학교의 교수 중 이름이 세 글자가 아닌 교수가 한 명 있다고 한다. 
-- 그 교수의 이름과 주민번호를 화면에 출력하는 SQL문장을 작성해보자.
SELECT 
       PROFESSOR_NAME
     , PROFESSOR_SSN
  FROM TB_PROFESSOR
 WHERE PROFESSOR_NAME NOT LIKE '___';

/* 3. 춘 기술대학교의 남자 교수들의 이름과 나이를 출력하는 SQL 문장을 작성하시오. 단
이때 나이가 적은 사람에서 많은 사람 순서로 화면에 출력되도록 만드시오. (단, 교수 중
2000 년 이후 출생자는 없으며 출력 헤더는 "교수이름", "나이"로 한다. 나이는 ‘만’으로
계산한다.) */

SELECT 
       PROFESSOR_NAME 교수이름 
     , FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE('19'||SUBSTR(PROFESSOR_SSN, 1, 6),'YYYYMMDD'))/12) 나이
     , PROFESSOR_SSN
  FROM TB_PROFESSOR
 WHERE SUBSTR(PROFESSOR_SSN, 8, 1) = '1'
 ORDER BY 2, PROFESSOR_NAME;

-- 4번
-- 교수들의 이름 중 성을 제외한 이름만 출력하는 SQL 문장을 작성하시오. 출력 헤더는 "이름"이 찍히도록 한다.
-- (성이 2자인 경우의 교수는 없다고 가정하시오)
SELECT 
       SUBSTR(PROFESSOR_NAME, 2) AS 이름
  FROM TB_PROFESSOR;

-- 5번
-- 춘 기술대학교의 재수생 입학자 학번과 이름을 표시하시오.(이때, 19살에 입학하면 재수를 하지 않은 것으로 간주)
SELECT 
       STUDENT_NO
     , STUDENT_NAME
  FROM TB_STUDENT
 WHERE EXTRACT(YEAR FROM ENTRANCE_DATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR'))) > 19;

-- 6번
-- 2020년 크리스마스는 무슨 요일인가?
--'DAY': 금요일 'DY': 금 'D': 6
SELECT 
       TO_CHAR(TO_DATE('2020/12/25'), 'DAY') 
  FROM DUAL;

-- 7번
-- TO_DATE('99/10/11', 'YY/MM/DD'), TO_DATE('49/10/11', 'YY/MM/DD')은 각각 몇 년 몇 월 몇 일을 의미할까?
-- 또 TO_DATE('99/10/11', 'RR/MM/DD'), TO_DATE('49/10/11', 'RR/MM/DD')은 각각 몇 년 몇 월 몇 일을 의미할까?
-- YY는 모두 2000년대
-- RR은 49이하는 2000년대 50이상은 1900년대
SELECT 
       TO_CHAR(TO_DATE('99/10/11', 'YY/MM/DD'),'YYYY')
     , TO_CHAR(TO_DATE('49/10/11', 'YY/MM/DD'),'YYYY')
     , TO_CHAR(TO_DATE('99/10/11', 'RR/MM/DD'), 'RRRR')
     , TO_CHAR(TO_DATE('49/10/11', 'RR/MM/DD'), 'RRRR')
  FROM DUAL;

-- 8번
-- 춘 기술대학교의 2000년도 이후 입학자들은 학번이 A로 시작하게 되어있다. 
-- 2000년도 이전 학번을 받은 학생들의 학번과 이름을 보여주는 SQL 문장을 작성하시오.
SELECT 
       STUDENT_NO
     , STUDENT_NAME
  FROM TB_STUDENT
 WHERE STUDENT_NO NOT LIKE 'A%';

-- 9번
-- 학번이 A517178인 한아름 학생의 학점 총 평점을 구하는 SQL문을 작성하시오.
-- 단, 이때 출력 화면의 헤더는 "평점"이라고 찍히게 하고, 점수는 반올림하여 소수점 이하 한자리까지만 표시한다.
SELECT 
       ROUND(AVG(POINT),1) AS 평점
  FROM TB_GRADE
 WHERE STUDENT_NO = 'A517178';

-- 10번
-- 학과별 학생 수를 구하여 "학과번호", "학생수(명)"의 형태로 헤더를 만들어 결과값이 출력되도록 하시오.
SELECT 
       DEPARTMENT_NO 학과번호
     , COUNT(*) 학생수
  FROM TB_STUDENT
 GROUP BY DEPARTMENT_NO
 ORDER BY 1;

-- 11번
-- 지도 교수를 배정받지 못한 학생의 수는 몇 명 정도 되는지 알아내는 SQL문을 작성하시오
SELECT 
       COUNT(*)
  FROM TB_STUDENT
 WHERE COACH_PROFESSOR_NO IS NULL;

-- 12번
-- 학번이 A112113인 김고운 학생의 년도 별 평점을 구하는 SQL문을 작성하시오.
-- 단, 이때 출력화면의 헤더는 "년도", "년도 별 평점"이라고 찍히게 하고, 점수는 반올림하여 소수점 이하 한자리까지만 표시한다.
SELECT 
       SUBSTR(TERM_NO,1,4) AS 년도
     , ROUND(AVG(POINT),1) AS "년도 별 평점"
  FROM TB_GRADE
 WHERE STUDENT_NO = 'A112113'
 GROUP BY SUBSTR(TERM_NO,1,4)
 ORDER BY 1;

-- 13번
-- 학과 별 휴학생 수를 파악하고자 한다. 학과 번호와 휴학생 수를 표시하는 SQL문장을 작성하시오.
SELECT 
       DEPARTMENT_NO 학과코드명
     , COUNT(DECODE(ABSENCE_YN, 'Y', 1)) "휴학생 수"
  FROM TB_STUDENT
 GROUP BY DEPARTMENT_NO
 ORDER BY 1;

-- 14번
-- 춘 대학교에 다니는 동명이인 학생들의 이름을 찾고자 한다.
-- 어떤 SQL 문장을 사용하면 가능하겠는가?
SELECT 
       STUDENT_NAME AS 동일이름
     , COUNT(*) AS "동명인 수"
  FROM TB_STUDENT
 GROUP BY STUDENT_NAME
 HAVING COUNT(*) > 1
 ORDER BY 1;

-- 15번
-- 학번이 A112113인 김고운 학생의 년도, 학기 별 평점과 년도 별 누적 평점, 총 평점을 구하는 SQL문을 작성하시오.
-- (단, 평점은 소수점 1자리까지만 반올림하여 표시한다.)
SELECT 
       SUBSTR(TERM_NO,1,4) AS 년도
     , SUBSTR(TERM_NO,5,2) AS 학기
     , ROUND(AVG(POINT),1) AS 평점
  FROM TB_GRADE
 WHERE STUDENT_NO = 'A112113'
 GROUP BY ROLLUP(SUBSTR(TERM_NO,1,4), SUBSTR(TERM_NO,5,2))
 ORDER BY SUBSTR(TERM_NO,1,4);

-- JOIN 연습문제


-- 1. 이름에 '형'자가 들어가는 직원들의
-- 사번, 사원명, 직급명을 조회하세요.
-- ANSI 표준
SELECT 
       E.EMP_ID
     , E.EMP_NAME
     , J.JOB_NAME
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE E.EMP_NAME LIKE '%형%';



-- 오라클 전용
SELECT 
       E.EMP_ID
     , E.EMP_NAME
     , J.JOB_NAME
  FROM EMPLOYEE E
     , JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
   AND E.EMP_NAME LIKE '%형%';





-- 2. 주민번호가 70년대 생이면서 성별이 여자이고, 
--    성이 전씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하세요.

-- ANSI 표준
SELECT 
       E.EMP_NAME
     , E.EMP_NO
     , D.DEPT_TITLE
     , J.JOB_NAME
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE SUBSTR(E.EMP_NO, 1, 2) >= 70 
   AND SUBSTR(E.EMP_NO, 1, 2) < 80
   AND SUBSTR(E.EMP_NO, 8, 1) = 2
   AND E.EMP_NAME LIKE '전%';


-- 오라클 전용

SELECT 
       E.EMP_NAME
     , E.EMP_NO
     , D.DEPT_TITLE
     , J.JOB_NAME
  FROM EMPLOYEE E
     , DEPARTMENT D
     , JOB J
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND J.JOB_CODE = E.JOB_CODE
   AND SUBSTR(E.EMP_NO, 1, 2) >= 70 
   AND SUBSTR(E.EMP_NO, 1, 2) < 80
   AND SUBSTR(E.EMP_NO, 8, 1) = 2
   AND E.EMP_NAME LIKE '전%';






-- 3. 가장 나이가 적은 직원의 사번, 사원명, 
--    나이, 부서명, 직급명을 조회하세요.
-- ANSI 표준
SELECT 
       E.EMP_ID
     , E.EMP_NAME
     , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM(TO_DATE(SUBSTR(E.EMP_NO,1,2), 'RR'))) + 1 AS 나이
     , D.DEPT_TITLE
     , J.JOB_NAME
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE EXTRACT(YEAR FROM SYSDATE)- EXTRACT(YEAR FROM(TO_DATE(SUBSTR(E.EMP_NO,1,2), 'RR'))) + 1 = (SELECT MIN(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM(TO_DATE(SUBSTR(EMP_NO,1,2), 'RR'))) + 1) 
                                                                                                    FROM EMPLOYEE
                                                                                                 );

-- ORACLE 전용
SELECT 
       E.EMP_ID
     , E.EMP_NAME
     , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM(TO_DATE(SUBSTR(E.EMP_NO,1,2), 'RR'))) + 1 AS 나이
     , D.DEPT_TITLE
     , J.JOB_NAME
  FROM EMPLOYEE E
     , DEPARTMENT D
     , JOB J
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND E.JOB_CODE = J.JOB_CODE
   AND EXTRACT(YEAR FROM SYSDATE)
   - EXTRACT(YEAR FROM(TO_DATE(SUBSTR(EMP_NO,1,2), 'RR'))) + 1
   = (SELECT MIN(EXTRACT(YEAR FROM SYSDATE) 
   - EXTRACT(YEAR FROM(TO_DATE(SUBSTR(EMP_NO,1,2), 'RR'))) + 1) 
                                                                                                   FROM EMPLOYEE
                                                                                                );
                
                
                
                
                
                
-- 4. 부서코드가 D2인 직원들의 사원명, 
--    직급명, 부서명, 근무지역명을 조회하세요.
-- ANSI 표준
SELECT 
       E.EMP_NAME
     , J.JOB_NAME
     , D.DEPT_TITLE
     , L.LOCAL_NAME
FROM EMPLOYEE E
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
WHERE E.DEPT_CODE = 'D2';

-- 오라클 전용
SELECT 
       E.EMP_NAME
     , J.JOB_NAME
     , D.DEPT_TITLE
     , L.LOCAL_NAME
  FROM EMPLOYEE E
     , JOB J
     , DEPARTMENT D
     , LOCATION L
 WHERE E.JOB_CODE = J.JOB_CODE
   AND E.DEPT_CODE = D.DEPT_ID
   AND D.LOCATION_ID = L.LOCAL_CODE
   AND E.DEPT_CODE = 'D2';






-- 5. 보너스포인트가 없는 직원들 중에서 직급코드가 
--    J4와 J7인 직원들의 사원명, 직급명, 급여를 조회하세요.
-- ANSI 표준
SELECT 
       E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE NVL(E.BONUS, 0) = 0 
   AND J.JOB_CODE IN('J4', 'J7');

-- 오라클 전용
SELECT 
       E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
  FROM EMPLOYEE E
     , JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
   AND NVL(E.BONUS, 0) = 0 
   AND J.JOB_CODE IN('J4', 'J7');






-- 6. 보너스포인트를 받는 직원들의 사원명, 
--    보너스포인트, 부서명, 근무지역명을 조회하세요.
-- ANSI 표준
SELECT 
       E.EMP_NAME
     , E.BONUS
     , D.DEPT_TITLE
     , L.LOCAL_NAME
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
 WHERE E.BONUS IS NOT NULL;

-- 오라클 전용
SELECT 
       E.EMP_NAME
     , E.BONUS
     , D.DEPT_TITLE
     , L.LOCAL_NAME
  FROM EMPLOYEE E
     , DEPARTMENT D
     , LOCATION L
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND D.LOCATION_ID = L.LOCAL_CODE
   AND E.BONUS IS NOT NULL;






-- 7. 해외영업팀에 근무하는 사원명, 
--    직급명, 부서코드, 부서명을 조회하세요.
-- ANSI 표준
SELECT 
       E.EMP_NAME
     , J.JOB_NAME
     , E.DEPT_CODE
     , D.DEPT_TITLE
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
 WHERE D.DEPT_TITLE LIKE '해외영업%';

-- 오라클 전용
SELECT 
       E.EMP_NAME
     , J.JOB_NAME
     , E.DEPT_CODE
     , D.DEPT_TITLE
  FROM EMPLOYEE E
     , JOB J
     , DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
AND D.DEPT_TITLE LIKE '해외영업%';






-- 8. 본인 급여 등급의 최소급여(MIN_SAL)를 초과하여 급여를 받는 직원들의
--    사원명, 직급명, 급여, 보너스포함 연봉을 조회하세요.
--    연봉에 보너스포인트를 적용한다.
-- ANSI 표준
SELECT 
       E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
     , (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
  JOIN SAL_GRADE S ON(E.SAL_LEVEL = S.SAL_LEVEL)
 WHERE E.SALARY > S.MIN_SAL;

-- 오라클 전용
SELECT 
       E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
     , (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12
  FROM EMPLOYEE E
     , JOB J
     , SAL_GRADE S
 WHERE E.JOB_CODE = J.JOB_CODE
   AND E.SAL_LEVEL = S.SAL_LEVEL
   AND E.SALARY > S.MIN_SAL;






-- 9. 한국과 일본에 근무하는 직원들의 
--    사원명, 부서명, 지역명, 국가명을 조회하세요.
-- ANSI 표준
SELECT 
       E.EMP_NAME
     , D.DEPT_TITLE
     , L.LOCAL_NAME
     , N.NATIONAL_NAME
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
  JOIN NATIONAL N ON(L.NATIONAL_CODE = N.NATIONAL_CODE)
 WHERE N.NATIONAL_NAME IN('한국', '일본');

-- 오라클 전용
SELECT 
       E.EMP_NAME
     , D.DEPT_TITLE
     , L.LOCAL_NAME
     , N.NATIONAL_NAME
  FROM EMPLOYEE E
     , DEPARTMENT D
     , LOCATION L
     , NATIONAL N
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND D.LOCATION_ID = L.LOCAL_CODE
   AND L.NATIONAL_CODE = N.NATIONAL_CODE
   AND N.NATIONAL_NAME IN('한국', '일본');






-- 10. 같은 부서에 근무하는 직원들의 사원명, 부서코드, 
--     동료이름을 조회하세요. self join 사용 (60행)
-- ANSI 표준
SELECT 
       E1.EMP_NAME 사원명
     , E1.DEPT_CODE 부서코드
     , E2.EMP_NAME 동료이름
  FROM EMPLOYEE E1
  JOIN EMPLOYEE E2 ON(E1.DEPT_CODE = E2.DEPT_CODE)
 WHERE E1.EMP_NAME != E2.EMP_NAME
 ORDER BY 1;

-- 오라클 전용
SELECT 
       E1.EMP_NAME 사원명
     , E1.DEPT_CODE 부서코드
     , E2.EMP_NAME 동료이름
  FROM EMPLOYEE E1
     , EMPLOYEE E2
 WHERE E1.DEPT_CODE = E2.DEPT_CODE
   AND E1.EMP_NAME != E2.EMP_NAME
 ORDER BY 1;

-- ---------------------------------------------------------------------
-- 함수 연습 문제



-- 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;