[Oracle SQL] 조인 고급 예제 풀이 (2)

📚Join - hr

1. 이름에 'T'를 포함하고 있는 사원과 같은 부서에서 근무하는 사원의 이름과 사원번호 출력

select e2.employee_id, e2.first_name, nvl(e1.first_name, '관리자없음')as 관리자명
from employees e1, employees e2
where e1.employee_id(+)=e2.manager_id
and e2.first_name like '_t%';
정답코드:
select e.employee_id, e.first_name, nvl(m.first_name,'관리자없음')
from employees e, employees m
where e.manager_id=m.employee_id(+)
and e.first_name like '_t%';

✏️ first_name의 테이블 설정을 잘못했다.
✏️한 행에서 벗어난 데이터를 구할 때에는 동일한 테이블을 조인하여 구한다. (자체조인)

2. 각 부서별로 평균급여(salary)을 급여가 높은 순서대로 출력하세요.단, 급여는 소수이하 자리수는 반올림하여 나타내며 급여가 5000이상인 데이터만 조회합니다.

select d.department_name, round(avg(e.salary))as 평균연봉
from departments d, employees e
where d.department_id=e.department_id
and e.salary>=5000
group by department_name
order by 평균연봉 desc;

3.직원중 현재시간 기준으로 근무 개월수가 20년(12*20개월) 보다 많은(초과) 사람의 first_name, salary, hire_date, department_name 을 출력하시오.

나의 코드:
select e.first_name, e.salary, e.hire_date, d.department_name
from employees e, departments d
where e.department_id=d.department_id
and e.hire_date <= '07-nov-02'; 	//현재 날짜에서 20년을 뺌

select e.first_name, e.salary, e.hire_date, d.department_name
from employees e, departments d
where e.department_id=d.department_id
and to_char(e.hire_date, 'yy') <= to_char(sysdate, 'yy') - 20; 
// hiredate와 sysdate의 연도를 각각 도출해서 h.date<=s.date-20 연삭식을 구함  

정답코드:
select e.first_name, e.salary, e.hire_date, d.department_name
  2  from employees e, departments d
  3  where e.department_id=d.department_id
  4  and months_between(sysdate,e.hire_date)>12*20;

✏️ a날짜와 b날짜 사이의 개월 수 차이를 구하는 months_between 함수를 기억했더라면...🥹

4. 각 직책별로 직원수를 출력하세요. 단, 해당 직원이 없는 job_title에 대해서도 모두 출력합니다.(해당 job_title이 없다고 하더라도 추후에 발생시 적용될 수 있도록 작성하세요.)

select j.job_title, count(e.employee_id) 
from employees e, jobs j
where e.job_id=j.job_id(+)
group by job_title;
정답코드:
select j.job_title, count(e.employee_id)
  2  from jobs j, employees e
  3  where j.job_id=e.job_id(+)
  4  group by j.job_title;

✏️'~별로 ' : group by이용 해야함