240529 6주차 수요일 - DB 04

조인종류

비교 조건 (<, =, >)을 만족하는 Join이 Theta Join이고 그중 비교 조건이 (=)일 때 Equal Join이 된다.
출처 : https://blog.naver.com/jinsol1/100024771465

 

 

54.부서명과    매니저    이름, 도시를    출력하시오.
select department_name,first_name,city from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id 
and d.manager_id=e.employee_id;
SELECT DEPARTMENT_NAME, FIRST_NAME, CITY FROM DEPARTMENTS D JOIN EMPLOYEES E ON 
(D.MANAGER_ID=E.EMPLOYEE_ID) JOIN LOCATIONS L USING (LOCATION_ID);


55.국가명, 도시, 부서명을 출력하시오. 
​select country_name,city,department_name from departments d join locations l 
on (d.location_id=l.location_id) join countries c on (l.country_id=c.country_id);
​select country_name,city,department_name from departments join locations  using (location_id) join countries using (country_id);

56.2000~2005년 사이의 모든 업무에 대하여 업무명, 부서명, 사원의 성, 업무시작일을 출력하시오. 
select job_title,department_name,last_name,start_date from job_history join jobs using (job_id)  
join departments using (department_id) join employees  using (employee_id)
where to_char(start_date,'YYYY') between 2000 and 2005;
​

​

57.업무명과 사원들의 평균임금을 출력하시오. 
select job_title,avg(salary) from employees join jobs using (job_id) group by job_title;
​

​

58.업무명, 사원 이름, 해당 사원의 임금과 해당 업무의 최고임금 간의 차액을 출력하시오. 
select job_title,first_name,salary-max_salary from employees natural join jobs;
​

​

59.커미션을 받고 30번 부서에 속한 사원의 성, 업무명을 출력하시오. 
​select job_title,last_name from employees natural join jobs where commission_pct is not null and department_id=30;

​

60.현재 임금이 15000 이상인 사원이 지금까지 수행했던 업무들을 출력하시오. 
select first_name||' '||last_name,job_title from employees e join job_history h using (employee_id) 
join jobs j on (j.job_id=h.job_id) where salary>=15000;
​

​

61.5년 이상 근무한 모든 매니저들의 부서명, 매니저 이름, 매니저의 임금을 출력하시오. 
select department_name,first_name||' '||last_name,salary from departments d 
join employees on (d.manager_id=employee_id) where to_char(sysdate,'YYYY')-to_char(hire_date,'YYYY')>5;
​
select e.first_name||' '||e.last_name from employees e,employees m  where e.manager_id=m.employee_id and e.hire_date<m.hire_date;


​

62.자신의 매니저보다 먼저 입사한 사원 이름을 출력하시오. 
select first_name||' '||last_name from employees e where hire_date< (select hire_date from employees m where e.manager_id=m.employee_id);
​

​

63.사원이 6개월 미만으로 근무한 업무에 대하여 사원 이름, 업무명을 출력하시오. 
select first_name||' '||last_name,job_title from employees e,jobs j,job_history h where h.employee_id=e.employee_id and h.job_id=j.job_id and months_between(end_date,start_date) < 6;
​
​

64.사원의 이름과 그 사원이 근무하는 국가를 출력하시오. 
select first_name||' '||last_name, country_name from employees e, departments d, locations l, countries c 
where e.department_id=d.department_id and d.location_id=l.location_id and l.country_id=c.country_id ;

select first_name||' '||last_name, country_name from employees join departments using (department_id) 
join locations using (location_id) join countries using (country_id);


​
65.부서명, 평균임금, 부서 내에서 커미션을 받은 사원의 수를 출력하시오. 
select department_name, avg(salary), count(commission_pct) from employees e, departments d where e.department_id=d.department_id 
group by department_name;
​

​

66.시애틀에 위치한 부서 어디든 사원이 5명 이상 입사한 달을 출력하시오. 
select to_char(hire_date,'MM-YYYY') from employees e,departments d,locations l where e.department_id=d.department_id 
and d.location_id=l.location_id and city='Seattle' group by to_char(hire_date,'MM-YYYY') having count(*) >5 ;
​

​

67.최고임금이 10000이 넘는 부서의 세부사항을 출력하시오. 
select * from departments where department_id in ( select department_id from employees group by department_id
having max(salary)>10000);
​

​

68.'Smith'에 의해 관리되는 부서의 세부사항을 출력하시오. 
select * from departments where manager_id in ( select employee_id from employees where first_name='Smith');
​

​

69.올해 입사한 사원의 업무 세부사항을 출력하시오. 
select * from jobs where job_id in ( select job_id from employees where to_char(hire_date,'YYYY')=to_char(sysdate,'YYYY'));
​

​

70.과거에 다른 어떠한 업무도 수행하지 않은 사원을 출력하시오. 
select * from employees where employee_id not in (select employee_id from job_history);
​

​

71.과거에 다른 업무를 한 적이 있는 사원의 업무와 평균임금을 출력하시오. 
select job_title,avg(salary) from employees join jobs using (job_id) where employee_id in (select employee_id from job_history)
group by job_title;
​

​

72.5명 이상의 사원이 속한 부서만 선정하여 부서가 있는 국가명, 도시, 총 부서의 수를 출력하시오. 
SELECT country_name,city,count(department_id) from countries c, departments d,locations l where d.department_id in 
(select department_id from employees group by department_id having count(department_id)>=5) 
and d.location_id=l.location_id and l.country_id=c.country_id
group by country_name,city;
​

​

73. 5명 이상의 사원을 관리하는 매니저의 세부사항을 출력하시오. 
select first_name||' '||last_name from employees where employee_id in
(select manager_id from employees group by manager_id having count(*)>=5);
​

​

74. 커미션을 받지않은 사원들의 사원 이름, 과거 업무명, 업무시작일, 업무종료일을 출력하시오. 
select first_name||' '||last_name,j.job_title,start_date,end_date from employees e,jobs j ,job_history h 
where commission_pct is null and e.employee_id=h.employee_id and h.job_id=j.job_id;
​

​

75.지난 2년 간 사원이 새로 들어오지 않은 부서를 출력하시오. 
select department_name from departments where department_id in
(select distinct department_id from employees where (sysdate-hire_date)/365>2);
​

76.과거에 다른 업무를 수행했던 사원들 중에 최고임금이 10000 이상인 사원이 속한 부서의 세부사항을 출력하시오. 
select * from departments where department_id in (select e.department_id from job_history j,employees e where j.employee_id=e.employee_id  having max(salary)>=10000 group by e.department_id);
​

​

77.과거에 IT Programmer로 일했던 사원들의 현재 업무를 출력하시오. 
select job_title from employees join jobs using (job_id) where employee_id in 
(select employee_id from job_history where job_id in 
(select job_id from jobs where job_title='Programmer'));
​

​

78.각 부서에서 가장 높은 임금을 받는 사원을 출력하시오. 
select first_name||' '||last_name from employees where salary in (select max(salary) from employees group by department_id);
​

​

79.사번이 105번인 사원이 있는 도시를 출력하시오. 
select city from locations where location_id in (select location_id from departments where department_id in (select department_id from employees where employee_id=105));
​
select city from locations join departments using (location_id) join employees using (department_id)
where employee_id=105;
​

80.모든 사원들 중 3번째로 높은 임금은 얼마인지 출력하시오. 

select salary from employees e where 2= (select count(distinct salary) from employees where salary>e.salary);