데이터베이스 20180816 (학원과제)

함수 최종 연습 문제

1. 직원명과 이메일 , 이메일 길이를 출력하시오

  이름     이메일 이메일길이

ex) 홍길동 , hong@kh.or.kr      13

2. 직원의 이름과 이메일 주소중 아이디 부분만 출력하시오

ex) 노옹철 no_hc

ex) 정중하 jung_jh

3. 60년생의 직원명과 년생, 보너스 값을 출력하시오 

그때 보너스 값이 null인 경우에는 0 이라고 출력 되게 만드시오

    직원명    년생      보너스

ex) 선동일 62 0.3

ex) 송은희 63  0

4. '010' 핸드폰 번호를 쓰지 않는 사람의 수를 출력하시오 (뒤에 단위는 명을 붙이시오)

   인원

ex) 3명

5. 직원명과 입사년월을 출력하시오 

단, 아래와 같이 출력되도록 만들어 보시오

    직원명 입사년월

ex) 전형돈 2012년12월

ex) 전지연 1997년 3월

6. 직원명과 주민번호를 조회하시오

단, 주민번호 9번째 자리부터 끝까지는 '*' 문자로 채워서출력 하시오

ex) 홍길동 771120-1******

7. 직원명, 직급코드, 연봉(원) 조회

  단, 연봉은 ￦57,000,000 으로 표시되게 함

     연봉은 보너스포인트가 적용된 1년치 급여임

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

9. 직원명, 입사일, 오늘까지의 근무일수 조회 

* 주말도 포함 , 소수점 아래는 버림

10. 모든 직원의 나이 중 가장 많은 나이와 가장 적은 나이를 출력 하여라. (나이만 출력)

11.  회사에서 야근을 해야 되는 부서를 발표하여야 한다.

 부서코드가 D5,D6,D9  야근, 그외는 야근없음 으로 출력되도록 하여라. 

 출력 값은 이름,부서코드,야근유무 (부서코드 기준 오름차순 정렬함.)

   (부서코드가 null인 사람도 야근없음 임)

12. 직원명, 부서코드, 생년월일, 나이(만) 조회

   단, 생년월일은 주민번호에서 추출해서, 

   ㅇㅇ년 ㅇㅇ월 ㅇㅇ일로 출력되게 함.

   나이는 주민번호에서 추출해서 날짜데이터로 변환한 다음, 계산함

* 주민번호가 이상한 사람들은 제외시키고 진행 하도록(200,201,214 번 제외)

* HINT : NOT IN 사용

13. 직원들의 입사일로 부터 년도만 가지고, 각 년도별 입사인원수를 구하시오.

  아래의 년도에 입사한 인원수를 조회하시오. 마지막으로 전체직원수도 구하시오

  => to_char, decode, sum 사용

-------------------------------------------------------------------------

1998년   1999년   2000년   2001년   2002년   2003년   2004년  전체직원수

-------------------------------------------------------------------------

14.  부서코드가 D5이면 총무부, D6이면 기획부, D9이면 영업부로 처리하시오.

   단, 부서코드가 D5, D6, D9 인 직원의 정보만 조회함

  => case 사용

   부서코드 기준 오름차순 정렬함.

===================================================================

===================================================================

---

---

해답

/*

1. 직원명과 이메일 , 이메일 길이를 출력하시오 -> 성공

  이름     이메일 이메일길이

ex) 홍길동 , hong@kh.or.kr      13

*/

select * from EMPLOYEE;

select EMP_NAME as 이름,EMAIL as 이메일, (LENGTH(EMAIL)) as "이메일 길이" from EMPLOYEE;

===================================================================

/*

2. 직원의 이름과 이메일 주소중 아이디 부분만 출력하시오

ex) 노옹철 no_hc

ex) 정중하 jung_jh

*/

select * from EMPLOYEE;

select EMP_NAME as 직원이름 ,RTRIM(RTRIM(EMAIL,'kh.or.kr'),'@') as 아이디 from EMPLOYEE;

===================================================================

/*

3. 60년생의 직원명과 년생, 보너스 값을 출력하시오 

그때 보너스 값이 null인 경우에는 0 이라고 출력 되게 만드시오

    직원명    년생      보너스

ex) 선동일 62 0.3

ex) 송은희 63  0

*/

select * from EMPLOYEE;

select EMP_NAME as 직원명,SUBSTR(EMP_NO,1,2) as 년생 ,NVL(BONUS,0) as 보너스 from EMPLOYEE where SUBSTR(EMP_NO,1,2) between 60 and 69;

===================================================================

/*

4. '010' 핸드폰 번호를 쓰지 않는 사람의 수를 출력하시오 (뒤에 단위는 명을 붙이시오)

   인원

ex) 3명

*/

select * from EMPLOYEE;

select count(PHONE)||'명' as 인원 from EMPLOYEE where SUBSTR(PHONE,1,3)!='010';

===================================================================

/*

5. 직원명과 입사년월을 출력하시오 

단, 아래와 같이 출력되도록 만들어 보시오

    직원명 입사년월

ex) 전형돈 2012년12월

ex) 전지연 1997년 3월

*/

select * from EMPLOYEE;

select EMP_NAME as 직원명, TO_CHAR(HIRE_DATE,'YYYY"년"MM"월"') as 입사년월 from EMPLOYEE;

===================================================================

/*

6. 직원명과 주민번호를 조회하시오

단, 주민번호 9번째 자리부터 끝까지는 '*' 문자로 채워서출력 하시오

ex) 홍길동 771120-1******

*/

select EMP_NAME as 직원명,RPAD((SUBSTR(EMP_NO,1,8)),14,'*') as 주민번호 from EMPLOYEE;

===================================================================

/*

7. 직원명, 직급코드, 연봉(원) 조회

  단, 연봉은 ￦57,000,000 으로 표시되게 함

     연봉은 보너스포인트가 적용된 1년치 급여임

*/

select * from EMPLOYEE;

select EMP_NAME as 직원명, JOB_CODE as 직급코드, TO_CHAR(salary*12+(salary*NVL(BONUS,0)*12),'L999,999,999') as "연봉(원)" from EMPLOYEE; 

===================================================================

/*

8. 부서코드가 D5, D9인 직원들 중에서 2004년도에 입사한 직원의 사번 사원명 부소코드 입사일 

조회

*/

select * from EMPLOYEE;

select EMP_ID as 사번, EMP_NAME as 사원명, DEPT_CODE as 부서코드, HIRE_DATE as 입사일

from EMPLOYEE where (EXTRACT(YEAR FROM HIRE_DATE))=2004 and DEPT_CODE IN ('D5','D9');

===================================================================

/*

9. 직원명, 입사일, 오늘까지의 근무일수 조회 

* 주말도 포함 , 소수점 아래는 버림

*/

select EMP_NAME as 직원명, HIRE_DATE as 입사일, Floor(SYSDATE-HIRE_DATE) as 근무일수 from EMPLOYEE;

===================================================================

/*

10. 모든 직원의 나이 중 가장 많은 나이와 가장 적은 나이를 출력 하여라. (나이만 출력)

*/

select * from EMPLOYEE;

--방식1 (이방식도 좋긴한데 너무 막무가내식이라 바꿨다아래껄로!!

select 2018-(1900+ MIN(SUBSTR(EMP_NO,1,2)))||'살' as "최대 나이", 2018-(1900+MAX(SUBSTR(EMP_NO,1,2))) ||'살' as "최소 나이" from EMPLOYEE;

--방식2

select MAX((TO_CHAR((SYSDATE),'YYYY'))-(TO_CHAR(TO_DATE((SUBSTR(EMP_NO,1,6))),'YYYY'))) as "최대 나이",

MIN((TO_CHAR((SYSDATE),'YYYY'))-(TO_CHAR(TO_DATE((SUBSTR(EMP_NO,1,6))),'YYYY'))) as "최소 나이"

from EMPLOYEE where EMP_ID NOT IN('200','201','214');

===================================================================

/*

11.  회사에서 야근을 해야 되는 부서를 발표하여야 한다.

 부서코드가 D5,D6,D9  야근, 그외는 야근없음 으로 출력되도록 하여라. 

 출력 값은 이름,부서코드,야근유무 (부서코드 기준 오름차순 정렬함.)

   (부서코드가 null인 사람도 야근없음 임)

*/

select * from EMPLOYEE;

select EMP_NAME as 이름,DEPT_CODE, case when DEPT_CODE='D5' then '야근'

when DEPT_CODE='D6' then '야근' when DEPT_CODE='D9' then '야근' ELSE

'야근없음' END as 야근여부

from EMPLOYEE order by DEPT_CODE asc; 

===================================================================

/*

12. 직원명, 부서코드, 생년월일, 나이(만) 조회

   단, 생년월일은 주민번호에서 추출해서, 

   ㅇㅇ년 ㅇㅇ월 ㅇㅇ일로 출력되게 함.

   나이는 주민번호에서 추출해서 날짜데이터로 변환한 다음, 계산함

* 주민번호가 이상한 사람들은 제외시키고 진행 하도록(200,201,214 번 제외)

* HINT : NOT IN 사용

*/

select* from EMPLOYEE;

select EMP_NAME as 직원명,DEPT_CODE as 부서코드,(TO_CHAR(TO_DATE((SUBSTR(EMP_NO,1,6)),'YYMMDD'),'YY"년 "MM"월 "DD"일"')) as 생년월일,

(TO_CHAR((SYSDATE),'YYYY'))-(TO_CHAR(TO_DATE((SUBSTR(EMP_NO,1,6))),'YYYY'))+1 as 나이계산 from EMPLOYEE where EMP_ID NOT IN('200','201','214');

===================================================================

/*

13. 직원들의 입사일로 부터 년도만 가지고, 각 년도별 입사인원수를 구하시오.

  아래의 년도에 입사한 인원수를 조회하시오. 마지막으로 전체직원수도 구하시오

  => to_char, decode, sum 사용

-------------------------------------------------------------------------

1998년   1999년   2000년   2001년   2002년   2003년   2004년  전체직원수

-------------------------------------------------------------------------

*/

select COUNT(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'1998',0)) ||'명' as "1998년",SUM(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'1999',1))||'명' as "1999년",

SUM(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'2000',1))||'명' as "2000년", SUM(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'2001',1))||'명' as "2001년",

COUNT(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'2002',1))||'명' as "2002년", COUNT(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'2003',1))||'명' as "2003년",

SUM(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'2004',1))||'명' as "2004년" ,COUNT(TO_CHAR(HIRE_DATE,'YYYY'))||'명' as "전체직원수" from EMPLOYEE;

===================================================================

/*

14.  부서코드가 D5이면 총무부, D6이면 기획부, D9이면 영업부로 처리하시오.

   단, 부서코드가 D5, D6, D9 인 직원의 정보만 조회함

  => case 사용

   부서코드 기준 오름차순 정렬함.

*/

select * from EMPLOYEE;

select EMP_NAME as 이름,DEPT_CODE as 부서코드,case when DEPT_CODE ='D5' then '총무부' when DEPT_CODE='D6' then '기획부' when DEPT_CODE='D9' then '영업부' END as 부서

from EMPLOYEE where DEPT_CODE IN('D5','D6','D9') order by DEPT_CODE asc ;

--선생님코드2번

select EMP_NAME, SUBSTR(EMAIL,1,INSTR(EMAIL,'@',1,1)-1) FROM EMPLOYEE;

--선생님코드3번

select EMP_NAME, TO_NUMBER(SUBSTR(EMP_NO,1,2)),NVL(BONUS,0) FROM EMPLOYEE where to_NUMBER(SUBSTR(EMP_NO,1,2)) BETWEEN 60 and 69;

--선생님코드4번

select COUNT(EMP_NAME)||'명' FROM EMPLOYEE WHERE PHONE NOT LIKE '010%';

--선생님코드5번

select EMP_NAME, EXTRACT(YEAR FROM HIRE_DATE) ||'년' || EXTRACT(MONTH FROM HIRE_DATE) || '월' FROM EMPLOYEE;

--선생님코드6번

select EMP_NAME, SUBSTR(EMP_NO,1,8) || '******' FROM EMPLOYEE;

--선생님코드7번

select EMP_NAME, JOB_CODE, TO_CHAR(((SALARY+(SALARY*NVL(BONUS,0)))*12),'L999,999,999') FROM EMPLOYEE;

--선생님코드8번

select EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE FROM EMPLOYEE where DEPT_CODE IN('D5','D9') AND SUBSTR(HIRE_DATE,1,2)=04;

--선생님코드9번

select EMP_NAME, HIRE_DATE, FLOOR(SYSDATE-HIRE_DATE) FROM EMPLOYEE;

--선생님코드9번

select EXTRACT(YEAR FROM SYSDATE) - (1900+SUBSTR(EMP_NO,1,2)) FROM EMPLOYEE;

--선생님코드10번

select MAX(EXTRACT(YEAR FROM SYSDATE) - (1900+SUBSTR(EMP_NO,1,2))), MIN(EXTRACT(YEAR FROM SYSDATE) - (1900+SUBSTR(EMP_NO,1,2))) FROM EMPLOYEE;

--선생님코드11번

select EMP_NAME,DEPT_CODE, CASE WHEN DEPT_CODE='D5' OR DEPT_CODE='D6' OR DEPT_CODE='D9' THEN '야근' ELSE '야근없음' END FROM EMPLOYEE;

--선생님코드12번

select EMP_NAME, DEPT_CODE, SUBSTR(EMP_NO,1,2) || '년'|| SUBSTR(EMP_NO,3,2) || '월' || SUBSTR(EMP_NO,5,2) || '일' as 생년월일,

(EXTRACT(YEAR FROM SYSDATE) - (1900+SUBSTR(EMP_NO,1,2)))+1 나이 FROM EMPLOYEE where EMP_ID NOT IN(200,201,214);

--선생님코드12번 -> 만나이계산

select EMP_NAME, DEPT_CODE, SUBSTR(EMP_NO,1,2) || '년'|| SUBSTR(EMP_NO,3,2) || '월' || SUBSTR(EMP_NO,5,2) || '일' as 생년월일,

FLOOR((SYSDATE-TO_DATE(SUBSTR(EMP_NO,1,6)))/365) as 만나이 FROM EMPLOYEE where EMP_ID NOT IN(200,201,214);

--선생님코드13번

SELECT 

COUNT(DECODE(TO_CHAR(EXTRACT(YEAR FROM HIRE_DATE)),'1998',1)) "1998년",

COUNT(DECODE(TO_CHAR(EXTRACT(YEAR FROM HIRE_DATE)),'1999',1)) "1999년",

COUNT(DECODE(TO_CHAR(EXTRACT(YEAR FROM HIRE_DATE)),'2000',1)) "2000년",

COUNT(DECODE(TO_CHAR(EXTRACT(YEAR FROM HIRE_DATE)),'2001',1)) "2001년",

COUNT(DECODE(TO_CHAR(EXTRACT(YEAR FROM HIRE_DATE)),'2002',1)) "2002년",

COUNT(DECODE(TO_CHAR(EXTRACT(YEAR FROM HIRE_DATE)),'2003',1)) "2003년",

COUNT(DECODE(TO_CHAR(EXTRACT(YEAR FROM HIRE_DATE)),'2004',1)) "2004년",

COUNT(*) "전체인원수"

FROM EMPLOYEE;

--선생님코드14번

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;