SQL - Group by, Having, Join

@markdown

## GROUP BY 절

____

- 그룹함수를 쓰되 어떤 컬럼 값을 기준으로 그룹함수를 적용할 경우 GROUP BY 절 뒤에 해당 컬럼을 기술

<pre><code class="sql" style="font-size:14px">SELECT 칼럼명, 그룹함수

FROM 테이블명

WHERE 조건 (연산자)

GROUP BY 칼럼명;

</code></pre>

- 합계, 평균, 최대값, 최소값 등을 어떤 칼럼을 기준으로 그 칼럼의 값 별로 조회 할 때 GROUP BY 절 뒤에 해당 칼럼을 기술하면 됨.

- GROUP BY 절을 사용할 때 주의할 점은 GROUP BY 절 다음에는 칼럼의 별칭을 사용할 수 없고, 반드시 칼럼명을 기술해야함

## HAVING 절

____

- 그룹의 결과를 제한할 때는 HAVING 절을 사용

- 부서별로 그룹지은 후(GROUP BY), 그룹 지어진 부서별 평균 급여가 2000 이상인(HAVING) 부서번호와 부서별 평균 급여를 출력

<pre><code class="sql" style="font-size:14px">SELECT deptno, avg(sal)

FROM emp

GROUP BY deptno

HAVING avg(sal) >= 2000

</code></pre>

## GROUP BY, HAVING 절 연습

____

- 각 부서별 인원수를 조회하되 인원수가 5명 이상인 부서만 출력되도록 하시오.

<pre><code class="sql" style="font-size:14px">SELECT department_id as 부서ID, count(employee_id) as 인원수

FROM employees

GROUP BY department_id

HAVING count(department_id) >= 5

ORDER BY 2 desc

</code></pre>

- 각 부서별 최대급여와 최소급여를 조회하시오.

- 단, 최대급여와 최소급여가 같은 부서는 직원이 한명일 가능성이 높기때문에

   조회결과에서 제외시킨다.

<pre><code class="sql" style="font-size:14px">SELECT department_id as 부서ID, MAX(salary) as 최대급여, MIN(salary) as 최소급여

FROM employees

GROUP BY department_id

HAVING  MAX(salary) != MIN(salary)

ORDER BY 2 desc

</code></pre>

- 부서가 50, 80, 110 번인 직원들 중에서 급여를 5000 이상 24000 이하를 받는

   직원들을 대상으로 부서별 평균 급여를 조회하시오.

- 단, 평균급여가 8000 이상인 부서만 출력되어야 하며, 출력결과를 평균급여가 높은

   부서면저 출력되도록 해야 한다.

<pre><code class="sql" style="font-size:14px">SELECT department_id as 부서ID, AVG(salary) as "부서별 평균 급여"

FROM employees

WHERE department_id IN(50, 80, 110)

AND salary BETWEEN 5000 AND 24000

GROUP BY department_id

HAVING  AVG(salary) >= 8000

ORDER BY 2 desc

</code></pre>

## JOIN

____

- 원하는 정보가 두 개 이상의 테이블에 나누어져 있을 때 한번의 질의로 정보를 가져오고 싶을 때 사용

### JOIN의 종류

____

 - Equi Join : 동일 칼럼을 기준으로 조인

 - Non-Equi Join : 동일 칼럼이 없이 다른 조건을 사용하여 조인

 - Outer Join : 조인 조건에 만족하지 않는 행도 나타냄

 - Seif Join : 한 테이블 내에서 조인합니다.

 - Cross Join : 테이블 1의 컬럼과 테이블 2의 컬럼을 조인하여 나올 수 있는 모든 경우의 수를 보여주는 조인

### Equi 조인

____

- equi join (DB 벤더 표준)

<pre><code class="sql" style="font-size:14px">SELECT empno, ename, dept.deptno, dname

FROM emp, dept

WHERE emp.deptno = dept.deptno

</code></pre>

- equi join (ANSI 표준)

<pre><code class="sql" style="font-size:14px">SELECT empno, ename, dept.deptno, dname

FROM emp JOIN dept

ON emp.deptno = dept.deptno

</code></pre>

<pre><code class="sql" style="font-size:14px">SELECT empno, ename, dept.deptno, dname

FROM emp JOIN dept USING(deptno)

</code></pre>

### Non-Equi 조인

____

- Non-Equi : 다른 연산자를 사용해서 조인한 쿼리(`=` 연산자 사용x)

<pre><code class="sql" style="font-size:14px">SELECT first_name, salary, grade ||'등급'

FROM employees e, salgrade s

WHERE e.salary BETWEEN s.losal AND s.hisal

</code></pre>

### Self 조인

____

- 같은 테이블 두번 참조하는 방법

- 한 테이블끼리 조인한다.

<pre><code class="sql" style="font-size:14px">SELECT  e1.empno, e1.ename, e2.empno, e2.ename

FROM emp e1, emp e2

WHERE e1.mgr = e2.empno

</code></pre>

- 매니저가 KING인 사원의 이름과 직급 출력

<pre><code class="sql" style="font-size:14px">SELECT e1.ename as 사원이름, e1.job as 직급

FROM emp e1, emp e2

WHERE e1.mgr = e2.empno

AND e2.ename = 'KING'

</code></pre>

- SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력

<pre><code class="sql" style="font-size:14px">SELECT e2.ename, e2.deptno

FROM emp e1, emp e2

WHERE e1.ename = 'SCOTT'

AND e1.deptno = e2.deptno

</code></pre>

### Left, Right, Full Outer 조인

____

<pre><code class="sql" style="font-size:14px">SELECT * FROM table1

[LEFT | RIGHT | FULL] Outer Join table2

</code></pre>

- Oracle Outer Join

  - 기준이 되는 테이블 값은 무조건 포함시켜 출력해라, 아닌 곳에는 (+) : outer join

<pre><code class="sql" style="font-size:14px">select e.first_name, d.department_name

from employees e, departments d

where e.department_id = d.department_id(+)

</code></pre>

- Sybase Outer Join

<pre><code class="sql" style="font-size:14px">select e.first_name, d.department_name

from employees e, departments d

where e.department_id *= d.department_id

</code></pre>

- ANSI 표준

  - 기준이 되는 쪽이 왼쪽일때 left outer, 오른쪽일 때 right outer

  - 둘다 포함되는 결과  조회할때는 Full outer

<pre><code class="sql" style="font-size:14px">select e.first_name, d.department_name

from employees e inner join departments d

on e.department_id = d.department_id

실행결과 : 107개 조회

</code></pre>

<pre><code class="sql" style="font-size:14px">select e.first_name, d.department_name

from employees e left outer join departments d

on e.department_id = d.department_id

</code></pre>

- 직원이 없는 부서도 포함해서 출력하는 쿼리

<pre><code class="sql" style="font-size:14px">select e.first_name, d.department_name

from employees e right outer join departments d

on e.department_id = d.department_id

실행결과 : 122개

</code></pre>

- 두 테이블에서 어느 한쪽에라도 있는 직원 또는 부서는 모두 출력하는 쿼리

<pre><code class="sql" style="font-size:14px">select e.first_name, d.department_name

from employees e full outer join departments d

on e.department_id = d.department_id

실행결과 : 123개

</code></pre>

### Natural Join

____

 - 알아서 같은 이름을 가진 칼럼끼리 조인 시켜준다(Equi 조인)

<pre><code class="sql" style="font-size:14px">select ename, dname

from  emp natural join dept

</code></pre>

### Cross 조인(카텐션 조인)

 - 칼럼끼리의 모든 경우의 수를 조인해 출력함

<pre><code class="sql" style="font-size:14px">select ename, dname

from  emp cross join dept

</code></pre>

## 조인 연습

____

- 부서이름과 부서가 속한 도시명(city)을 조회

<pre><code class="sql" style="font-size:14px">select d.department_name, l.city

from departments d, locations l

where d.location_id = l.location_id

</code></pre>

- 직원의 이름과 근무국가명을 조회 (employees, departments, locations, countries)

<pre><code class="sql" style="font-size:14px">select e.first_name, c.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

</code></pre>

- 직책(job_title)이 'Manager' 인 사람의 이름, 직책, 부서명을 조회

<pre><code class="sql" style="font-size:14px">select e.first_name as 이름, j.job_title as 직책, d.department_name as 부서명

from employees e, jobs j, departments d

where e.job_id = j.job_id

and e.department_id = d.department_id

and j.job_title like '%Manager%'

--끝에서 7번 자리 만큼 짤라서, manager 첫번째 문자만 대문자로 수정한 것과 비교

and substr(j.job_title, -7, 7) = initcap('manager')

</code></pre>

- 직원들의 이름, 입사일, 부서명을 조회

<pre><code class="sql" style="font-size:14px">select e.first_name, e.hire_date, d.department_name

from employees e, departments d

where e.department_id = d.department_id(+)

</code></pre>

- 직원들의 이름, 입사일, 부서명을 조회

- 단, 부서가 없는 직원이 있다면 그 직원정보도 출력결과에 포함

<pre><code class="sql" style="font-size:14px">select e.first_name, e.hire_date, d.department_name

from employees e, departments d

where e.department_id = d.department_id(+)

</code></pre>

- 직원의 이름과 직책(job_title)을 출력

- 단, 사용되지 않는 직책이 있다면 그 직책정보도 출력결과에 포함

<pre><code class="sql" style="font-size:14px">select e.first_name as 이름, j.job_title as 직책

from employees e, jobs j

where e.job_id(+) = j.job_id

</code></pre>

<pre><code class="sql" style="font-size:14px">select e.first_name as 이름, j.job_title as 직책

from jobs j left outer join employees e on e.job_id = j.job_id

where 1=1

</code></pre>

- 부서에 배정안된 kimbery도 출력하는 쿼리 작성

<pre><code class="sql" style="font-size:14px">select * from departments

select count(distinct department_id) from employees

실행결과 : 27개

</code></pre>