데이터베이스 20180820 (학원5) 학원 과제

--1. 2020년 12월 25일이 무슨 요일인지 조회하시오.

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

--3. 이름에 '형'자가 들어가는 직원들의 사번, 사원명, 부서명을 조회하시오.

--4. 해외영업부에 근무하는 사원명, 직급명, 부서코드, 부서명을 조회하시오.

--5. 보너스포인트를 받는 직원들의 사원명, 보너스포인트, 부서명, 근무지역명을 조회하시오.

--6. 부서코드가 D2인 직원들의 사원명, 직급명, 부서명, 근무지역명을 조회하시오.

--7. 급여등급테이블의 최대급여(MAX_SAL)보다 많이 받는 직원들의 사원명, 직급명, 급여, 연봉을 조회하시오.

-- (사원테이블과 급여등급테이블을 SAL_LEVEL컬럼기준으로 조인할 것)

--8. 한국(KO)과 일본(JP)에 근무하는 직원들의 사원명, 부서명, 지역명, 국가명을 조회하시오.

--9. 같은 부서에 근무하는 직원들의 사원명, 부서명, 동료이름을 조회하시오. (self join 사용)

--10. 보너스포인트가 없는 직원들 중에서 직급이 차장과 사원인 직원들의 사원명, 직급명, 급여를 조회하시오. 단, join과 IN 사용할 것

--11. 재직중인 직원과 퇴사한 직원의 수를 조회하시오.

이거하기

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

내주신 문제

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

--1. 2020년 12월 25일이 무슨 요일인지 조회하시오.

select to_char(to_date(20201225),'day') 요일 from dual;

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

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

--ORACLE 방식

select e.emp_name 사원명, e.emp_no 주민번호, d.dept_title 부서명, j.job_name 직급명 from

employee e, department d, job j where e.emp_no like '%-2%' and 

(substr(e.emp_no,1,2) between '70' and '80')and 

e.emp_name like '%전%' and e.job_code=j.job_code and e.dept_code=d.dept_id;

--ANSI 방식

select emp_name 사원명, emp_no 주민번호, DEPARTMENT.dept_title 부서명, JOB.job_name 직급명 from

EMPLOYEE JOIN DEPARTMENT on (dept_code=Department.dept_id)

JOIN JOB on (EMPLOYEE.job_code=job.job_code) where (emp_no like '%-2%') and (substr(emp_no,1,2) between '70' and '80') and (emp_name like '%전%');

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

--3. 이름에 '형'자가 들어가는 직원들의 사번, 사원명, 부서명을 조회하시오.

--ORACLE 방식

 select e.emp_id 사번, e.emp_name 사원명, d.dept_TITLE 부서명 from employee e, department d 

 where e.emp_name like '%형%'and d.dept_id = e.dept_code;

--ANSI 방식

select emp_id 사번, emp_name 사원명, DEPARTMENT.dept_TITLE 부서명 from EMPLOYEE 

JOIN DEPARTMENT on (DEPT_CODE=DEPARTMENT.dept_id) where EMP_NAME like '%형%';

 

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

--4. 해외영업부에 근무하는 사원명, 직급명, 부서코드, 부서명을 조회하시오.

--ORACLE 방식

select e.emp_name 사원명, j.job_name 직급명, e.dept_code 부서코드 ,d.dept_title 부서명 

from employee e,department d,job j 

where d.DEPT_ID=e.dept_code and e.job_code=j.job_code  and

 d.DEPT_TITLE in('해외영업1부','해외영업2부');

--ANSI 방식

select emp_name 사원명, JOB.job_name 직급명, EMPLOYEE.DEPT_CODE 부서코드, DEPARTMENT.dept_title 부서명 from EMPLOYEE

JOIN DEPARTMENT on (dept_code=DEPARTMENT.dept_id)

JOIN JOB USING (job_code) where DEPARTMENT.dept_title like '해외영업%';

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

--5. 보너스포인트를 받는 직원들의 사원명, 보너스포인트, 부서명, 근무지역명을 조회하시오.

select * from location;

select * from department;

select * from employee;

--ORACLE 방식

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 (bonus is not null);  

--ANSI 방식

select emp_name 사원명, bonus 보너스포인트, dept_title 부서명, local_name 근무지역명

from employee 

FULL JOIN DEPARTMENT ON(dept_code=dept_id)

FULL JOIN LOCATION ON(location_id=local_code) where BONUS is not null;

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

--6. 부서코드가 D2인 직원들의 사원명, 직급명, 부서명, 근무지역명을 조회하시오.

--ORACLE 방식

select e.emp_name 사원명, j.job_name 직급명, d.dept_title 부서명, l.local_name 근무지역명

from employee e, department d,job j, location l where e.dept_code='D2' and 

e.dept_code=d.dept_id and l.local_code= d.location_id and e.job_code = j.job_code ;

--ANSI 방식

select employee.emp_name 사원명, job.job_name 직급명 , DEPARTMENT.dept_title 부서명, location.local_name 근무지역명

from EMPLOYEE JOIN DEPARTMENT on (EMPLOYEE.DEPT_CODE=DEPARTMENT.DEPT_ID)

JOIN LOCATION ON (LOCATION.local_code=DEPARTMENT.location_id)

JOIN JOB USING(job_code) where EMPLOYEE.dept_code='D2';

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

--7. 급여등급테이블의 (최대급여(MAX_SAL)-50000000)보다 많이 받는 직원들의 사원명, 직급명, 급여, 연봉을 조회하시오.

-- (사원테이블과 급여등급테이블을 SAL_LEVEL컬럼기준으로 조인할 것)

--ORACLE 방식

select e.emp_name 사원명, j.job_name 직급명,e.salary 급여, (salary*12+salary*12*NVL(BONUS,0)) 연봉 from employee e, job j, sal_grade s 

where j.job_code=e.job_code and s.sal_level=e.sal_level and e.salary>(s.max_sal-500000);

--ANSI 방식

select EMP_NAME 사원명, JOB.job_name 직급명, salary 급여, (salary*12+salary*12*NVL(BONUS,0)) 연봉 from EMPLOYEE

JOIN JOB USING (job_code)

JOIN SAL_GRADE USING(sal_level) where EMPLOYEE.salary > (sal_grade.max_sal-500000);

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

--8. 한국(KO)과 일본(JP)에 근무하는 직원들의 사원명, 부서명, 지역명, 국가명을 조회하시오.

--ORACLE 방식

select e.emp_name 사원명, d.dept_title 부서명,l.local_name 지역명, n.national_name 국가명 

from employee e, department d,location l,national n 

where d.location_id=l.local_code  and e.dept_code=d.dept_id and

n.NATIONAL_CODE=l.NATIONAL_CODE and l.national_code in ('KO','JP');

--ANSI 방식

select EMPLOYEE.emp_name 사원명, DEPARTMENT.dept_title 부서명, LOCATION.local_name 지역명, national.national_name 국가명 from EMPLOYEE

JOIN DEPARTMENT ON (dept_code=department.dept_id)

JOIN LOCATION ON (DEPARTMENT.location_id=LOCATION.local_code)

JOIN NATIONAL ON(LOCATION.NATIONAL_CODE=national.national_code) where LOCATION.national_code in ('KO','JP');

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

--9. 같은 부서에 근무하는 직원들의 사원명, 부서명, 동료이름을 조회하시오. (self join 사용)

--ORACLE 방식 

select  e1.emp_name 사원명,d.dept_title 부서명,e2.emp_name 동료이름 

from employee e1, employee e2, department d where (d.dept_id=e2.dept_code) and (e1.dept_code=d.dept_id) and (e1.emp_name<> e2.emp_name)  order by 1;

--ANSI 방식

select e1.emp_name 사원명, department.dept_title 부서명,e2.emp_name 동료이름

from employee e1 JOIN employee e2 ON (e1.emp_name<>e2.emp_name)

JOIN DEPARTMENT ON (department.dept_id=e1.dept_code) 

where (e1.dept_code=department.dept_id) and (e2.dept_code=department.dept_id) order by 1;

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

--10. 보너스포인트가 없는 직원들 중에서 직급이 차장과 사원인 직원들의 사원명, 직급명, 급여를 조회하시오. 단, join과 IN 사용할 것

--ORACLE 방식

select e.emp_name 사원명, j.job_name 직급명, e.salary 급여 from employee e, job j where (e.bonus is null)

and (j.job_name in ('차장','사원')) and (e.job_code=j.job_code);

--ANSI 방식

select EMPLOYEE.emp_name 사원명, job.job_name 직급명, employee.salary 급여 from EMPLOYEE 

JOIN JOB USING (job_code) where (employee.bonus is null) and (job.job_name in ('차장','사원'));

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

--11. 재직중인 직원과 퇴사한 직원의 수를 조회하시오.

--방법1

select count(e1.ent_date)-count(e2.ent_date) as 재직,count(e2.ent_date) 퇴사 from employee e1, employee e2

where (e1.ent_date is not null);

--방법2

select CASE WHEN ENT_YN='Y' THEN '퇴사' WHEN ENT_YN='N' THEN '재직' END 재직여부, count(*) "인원 수" FROM EMPLOYEE group by CASE WHEN ENT_YN='Y'

THEN '퇴사' WHEN ENT_YN='N' THEN '재직' END; 

--방법3

select DECODE(SUBSTR(ENT_YN,1,1),'Y','퇴사','N','재직') 재직여부 , count(*) "인원 수" from employee group by DECODE(SUBSTR(ENT_YN,1,1),'Y','퇴사','N','재직');

--방법4

select case when SUBSTR(ENT_YN,1,1)='Y' then '퇴사' when SUBSTR(ENT_YN,1,1)='N' then '재직'

ELSE '종규' END 재직여부, count(*) "인원 수" from EMPLOYEE group by case when SUBSTR(ENT_YN,1,1)='Y' then '퇴사' when SUBSTR(ENT_YN,1,1)='N' then '재직'

ELSE '종규' END;

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