[데이터베이스] hr 스키마 연습문제 (3_6차)

-----------------------------------------------------------outer join
1. 사원이 소속되지 않은 부서를 포함하여, 부서이름과 해당 부서에 소속된 사원 이름(first_name)을 출력하라.

(부서는 전체 출력=left join)

 

select d.department_name, e.first_name
from departments d
left join employees e on d.department_id = e.department_id;

 

DEPARTMENT_NAME                                              FIRST_NAME
------------------------------------------------------------ ----------------------------------------
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting

DEPARTMENT_NAME                                              FIRST_NAME
------------------------------------------------------------ ----------------------------------------
Payroll

122 행이 선택되었습니다.

2. 업무가 배정되지 않은 사원과 사원이 배정되지 않은 업무를 포함하여, 모든 사원 이름(first_name)과 업무 이름(job_title)을 출력하라.

(사원과 업무 전체 출력 =full join)

 

select e.first_name, j.job_title
from employees e
full join jobs j on e.job_id = e.job_id;

 

FIRST_NAME                               JOB_TITLE
---------------------------------------- ----------------------------------------------------------------------
Eleni                                    Purchasing Clerk
Eleni                                    Stock Manager
Eleni                                    Stock Clerk
Eleni                                    Shipping Clerk
Eleni                                    Programmer
Eleni                                    Marketing Manager
Eleni                                    Marketing Representative
Eleni                                    Human Resources Representative
Eleni                                    Public Relations Representative

2033 행이 선택되었습니다.

 

3. 부서가 배치되지 않은 위치(locations)를 포함하여 모든 위치의 도시이름(city)와 부서이름을 출력하라.

(위치는 전체 출력 = location 쪽으로 조인)

 

select l.city, d.department_name
from locations l
left join departments d on l.location_id = d.location_id;

 

CITY                                                         DEPARTMENT_NAME
------------------------------------------------------------ ------------------------------------------------------------
Utrecht
Mexico City
Sao Paulo
Venice
Whitehorse
Hiroshima
Bern
Tokyo
Beijing
Bombay

43 행이 선택되었습니다.

 

4. 위치 번호가 1700인 부서중에서, 부서장(manager)이 배치되지 않은 부서를 포함하여, 모든 부서의 부서장의 이름(first_name)과 부서이름을 부서 이름의 역순으로 출력하라.

------------?

select e.first_name, d.department_name
from employees e
right join departments d on e.employee_id = d.manager_id
where d.location_id = '1700' or d.manager_id is null
order by d.department_name desc;

 

FIRST_NAME                               DEPARTMENT_NAME
---------------------------------------- ------------------------------------------------------------
                                         Government Sales
Nancy                                    Finance
Steven                                   Executive
                                         Corporate Tax
                                         Control And Credit
                                         Contracting
                                         Construction
                                         Benefits
Jennifer                                 Administration
Shelley                                  Accounting

21 행이 선택되었습니다.

 

 

--------------------------------------------------------------부속질의

1. 최고의 급여를 받고 있는 사원의 first_name을 출력하라.

 

select first_name
from employees
where salary=(select max(salary) from employees);

FIRST_NAME
----------------------------------------
Steven

 

2. 사원의 수가 5명 이상인 부서의 부서 이름을 출력하라.---------------

 

select department_name
from departments
where department_id in(select department_id

                                       from employees

                                       group by department_id

                                       having count(*) >= 5);

DEPARTMENT_NAME
------------------------------------------------------------
Purchasing
Finance
Sales
Shipping
IT

 

3. 업무 이름에 Purchasing이 들어간 업무를 하고 있는 사원의 first_name을 출력하라.

 

select first_name
from employees
where job_id in (select job_id

                           from jobs

                           where job_title like '%Purchasing%');

FIRST_NAME                               JOB_ID
---------------------------------------- --------------------
Den                                      PU_MAN
Alexander                                PU_CLERK
Shelli                                   PU_CLERK
Sigal                                    PU_CLERK
Guy                                      PU_CLERK
Karen                                    PU_CLERK

6 행이 선택되었습니다.

 

4. 소속 사원이 없는 부서의 이름을 출력하라.

 

select department_name
from departments
where department_id not in (select distinct department_id

                                              from employees 

                                              where department_id is not null);

DEPARTMENT_NAME
------------------------------------------------------------
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC

DEPARTMENT_NAME
------------------------------------------------------------
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll

16 행이 선택되었습니다.

 

---------------------------------------------------------------집합연산

5. 배당된 사원이 없는 업무의 이름(job_title)을 출력하라.

 

 

 

6. Shipping 부서와 Human Resources 부서의 부서 이름과 주소 (address)를 출력하라. 집합연산을 이용한다.

 

 

 

7. IT 부서와 Finance 부서 둘 다가 같이 위치한 Country_ID를 출 력하라. 집합연산을 이용한다.