데이터베이스 조인, 그룹함수

--조인, 그룹함수

--조인(join): 등가조인,  비등가조인, 포괄조인, 자체조인
  - from절에 조인하려는 table들을 기술한다.   (from절은 table이 ....n개(여러개) 올수있다)
  - where절에 최소 table의 수-1 만큼의 조건이 있어야한다
  - table의 별명(alias) 사용
  



-- 등가조인: 정확하게 1:1로 매칭이 되는데
select last_name, department_name, salary         department_name은 departments테이블에 있어서
from employees, departments      -> 이렇게 테이블을 2개 쓰게 되면 두 테이블에 있는게 곱해져서 나온다
        employees에 107건 departments에 27건 -> 곱해져서 2889건이 나온다
    employees와 departments 각각의 데이터 마다 대응이 되서 곱해져서 나온다.
1            a           1 a .......
2            b           1 b
3            c           1 c
4            d           1 d


★ 원하는 데이터 끼리만 가져올려면?
select last_name, department_name, salary        
from employees e, departments d where e.department_id = d.department_id;
위의 조건식을 통해서 employees에도 있고 departments에도 있는

department_id의 값이 양쪽 테이블에서 같은 값을 가질 때의 데이터만 가져 올 수 있다.
이때 employees를 alias써서 e departments를 alias써서 d   
where절도 e.department_id = d.department_id로 쓰면 오류 안난다.  

 department_id = d.department<-이렇게는 오류남 
106건나옴
이때 alias 안쓰면 employees.department_id    departments.department_id 이렇게 모두 써야한다.


select last_name, department_id
from employees where department_id is null;  -> null값이 한명 있어서 
employees 107건인데 106건만 나왔다


여러 테이블 연결되어 하나의 테이블 처럼 사용하는 조인 -> 외래키와 주키로 테이블들을 연결시켜준다.
외래키인 테이블에서는 n개(여러개)일수 있지만 주키(기본키)인 테이블에서는 하나만 존재해야한다.



-- 비등가조인: 1:1로 값이 매칭이 되지 않는다
select * from job_grades;
GRADE_ LOWEST_SAL HIGHEST_SAL
------ ---------- -----------
A            1000        2999         
B            3000        5999
C            6000        9999
D           10000       14999
E           15000       24999
F           25000       40000


select last_name, salary, grade_level     
from employees e, job_grades
where e.salary between lowest_sal and highest_sal;    -> 범위의 값을 가져올때



-- 포괄조인(outer join)

문제: 몇개에 부서에 직원들이 배정이 되어있나?
 select distinct department_id from employees;
 DEPARTMENT_ID
-------------
          100
           30

           90
           20
           70
          110
           50
           80
           40
           60

DEPARTMENT_ID
-------------
           10

12 rows selected.    null 빼고 10개에 부서에 직원들이 배정

-- 106
select last_name, department_name
from employees e, departments d
where e.department_id = d.department_id;

select last_name, department_name 
from employees e, departments d
where e.department_id(+) = d.department_id;    -> 직원이 없는 부서여도 부서이름만 나오게 해라

select last_name, department_name 
from employees e, departments d
where e.department_id = d.department_id(+);    -> 부서 없어도 이름만 출력되게 해라 



문제: 모든 사원들의 이름, 부서 이름 및 부서번호를 출력하시오.
select last_name, department_name, e.department_id
from employees e, departments d
where e.department_id = d.department_id;

->   select절에서 department_id를 출력할때 employees와 departments 둘다에 department_id가
     있으므로 department_id를 employees와 departments 둘 중 어디것을 출력할지 지정해줘야한다.
   e.department_id 나 d.department_id


문제: 커미션을 받는 모든 사람들의 이름, 부서명, 지역ID 및 도시 명을 출력하시오
select e.last_name, d.department_name, d.location_id, l.city
from employees e, departments d, locations l
where d.location_id = l.location_id and d.department_id =e.department_id
and e.commission_pct is not null;



-- 자체조인: 다른것들은 여태 다른테이블이였는데 이제 같은테이블에서 조인이 됨
  ex) manager_id가 employee_id 된다
  
문제: 50부서의 직원이름과 상관의 이름을 조회하시오
select e.last_name, e.manager_id, m.last_name
from employees e, employees m
where e.manager_id = m.employee_id and m.department_id=50;

from절은 같은 employees지만 두개의 별명으로 지정 employees e, employees m


문제: 직원들의 이름, 직종, 관리자이름, 관리자급여를 조회하시오
select e.last_name, e.job_id, m.last_name, m.salary
from employees e, employees m
where e.manager_id = m.employee_id;




-- 그룹함수(집합함수)
-- avg(salary) salary의 평균, max(), min(), sum() 전체 합계, count(salary) 월급 받는 사람의 수
-- 숫자에 사용되는 그룹함수: avg(), sum()
-- 문자, 숫자에 사용되는 그룹함수: max(), min()


select avg(salary), max(salary), min(salary), sum(salary), count(salary)
from employees;

 -그룹으로 알고싶을때 group by를 쓴다
select department_id, avg(salary), max(salary), min(salary), sum(salary), count(salary)
from employees
group by department_id;
-> group by로 그룹화한 department_id가 select절에도 나와야 부서별 월급평균, 최대값 등등을 알수있다.


-- 부서별 급여의 평균, 최대,최소,합계,직원수
select d.department_name, avg(salary), max(salary), 
min(salary), sum(salary), count(salary)
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name;


-- 평균급여가 6000이상인 직종의 평균급여를 조회하시오
 -having: group by에 대한 조건절    where는 select에 대한 조건절
 
select e.job_id, avg(salary)
from employees e
group by e.job_id
having avg(salary) >= 6000;


-- 50, 80부서에서 평균급여가 10000이상인 직종을 조회하시오
select job_id, avg(salary)
from employees
where department_id in(50,80)
group by job_id
having avg(salary)>=10000;

select count(manager_id) from employees;
-> column값 (여기선 manager_id)를 넣었을땐 null값 포함 안된다.
select count(*) from employees;
-> *는 null값 까지 전체를 포함한다.


문제: 회사 전체의 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 출력하시오
select max(salary), min(salary), sum(salary), avg(salary)
from employees;



문제: 각 직종별  최대급여, 최소급여, 급여 총합 및 평균급여를 출력하시오
   단 최대급여는 MAX 최소급여는 MIN 급여총합은 SUM 평균급여는 AVG로 출력되게하고 직업을 오름차순으로 정렬하라
 
select job_id, max(salary) MAX, min(salary) MIN, sum(salary) SUM, avg(salary) AVG
from employees
group by job_id
order by job_id;


문제: 동일한 직업을 가진 사원들의 총 수를 출력하시오
select job_id, count(job_id)
from employees
group by job_id;

문제: 매니저로 근무하는 사원들의 총 수를 출력하시오
select count(distinct manager_id)          
from employees;
-> manager_id를 가진사람 중 중복을 제거하고 count를 한다.

문제: 사내의 최대 급여 및 최소 급여의 차이를 출력하시오.
select max(salary)-min(salary)
from employees;

문제: 매니저의 사번 및 그 매니저 밑 사원들 중 최소 급여를 받는 사원의 급여를 출력하시오.
  매니저가 없는 사람들은 제외한다. 최소급여가 5000미만인 경우는 제외한다. 급여 기준 역순으로 조회한다.
  
select e.employee_id, min(e.salary)
from employees e,employees m
where m.manager_id=e.employee_id and m.manager_id is not null
group by m.manager_id
having min(e.salary) >= 5000
order by e.salary desc;

답
select manager_id, min(salary) 
from employees
where manager_id is not null
group by manager_id
having min(salary) >= 5000
order by min(salary) desc;


문제: 급여 합계가 10,000보다 많은 직원의 부서, 직종별 급여합계, 평균급여, 직원 수를 출력하세요
   급여 sum_sal는 3자리마다 ","형식으로
   평균급여 avg_sal는 소수점 이하 2자리까지 출력하세요

select department_id, to_char(sum(salary),'999,999') sum_sal, to_char(avg(salary),'999,999,99') avg_sal,
 count(*)
from employees
group by job_id, department_id          
having sum(salary)>=10000
order by job_id, department_id