KB IT’s Your Life 4기 - Java SQL 20일차
그룹함수
count(*)
- null 값을 포함한 행의 개수를 셈
- null 값을 세는 유일한…
min, max
AVG
- 10번째 부서의 평균 연봉
UNION - 합집합
GROUP BY
- 모든 부서의 연봉 평균을 구하고 싶을 때, UNION을 부서별로 모두 구하지 않고, GROUP BY를 이용해서 모든 부서의 연봉 평균을 구한다.
(+) SELECT문 실행 순서 FROM 테이블 명 - > WHERE조건식 -> GROUP BY -> HAVING -> SELECT -> ORDER BY
(+) SELECT문 작성 순서 SELECT ->FROM 테이블 명 - > WHERE조건식 -> GROUP BY -> HAVING ->ORDER BY
(+) GROUP BY 작성 주의사항 GROUP BY에 사용된 속성만 SELECT절에 이용된다. 그리고 집계함수만 사용 가능.
ROUND
소수점 하나 밑에서 반올림
ORDER BY
-
1일 때, DEPARTMENT_ID가 정렬
-
-
3일 때, ROUND(AVG(SALARY), 1)가 정렬
-
(+) DESC
업무별, 부서별 평균
HAVING
ROLLUP
학년별 인원수 집계
1학년 1반 30
2반 25
3반 25
1학년 80 --이렇게 구해주는 것이 ROLLUP함수이다.
-
roolup 빼면 부서별 사람 인원수와 평균 연봉이 그저 나열된다.
-
이것과 같음.
select department_id, JOB_ID, count(*), sum(salary)
from employees
GROUP BY ROLLUP(department_id, JOB_ID)
ORDER BY 1
- 부서 아이디 별로 COUNT와 SUM을 보여주고
- 가장 밑에는 전체에 대한 COUNT와 SUM을 보여줌.
CUBE
- 모든 경우의 수의 총계
-
중요도 낮음. 쓸일이 거의 없음
- ROLLUP과의 차이점
ROLLUP(부서,업무)
부서별 업무별 COUNT(*)
10 10 ..
10 20 ..
10 30 ..
10 부서전체 ..
20 10 ..
20 20 ..
20 30 ..
20 부서전체 ..
전체 직원 ..
CUBE(부서,업무)
부서별 업무별 COUNT(*)
10 10 ..
10 20 ..
10 30 ..
10 부서전체 ..
20 10 ..
20 20 ..
20 30 ..
20 부서전체 ..
전체 부서 10 ..
전체 부서 20 ..
전체 부서 30 ..
전체 직원 ..
-
CUBE 예시 설명 하나 더
``` GROUP BY ROLLUP(부서) 영업부 5 -> ROLLUP 개발부 4 -> ROLLUP
ROLLUP은 왼쪽의 것을 기준으로 묶인다.
GROUP BY ROLLUP(부서, 직급) 영업부 사원 3 영업부 대리 2 영업부 5 -> ROLLUP 개발부 사원 2 개발부 대리 2 개발부 4 -> ROLLUP 전체 9 -> ROLLUP
GROUP BY CUBE(부서, 직급) 영업부 사원 3 영업부 대리 2 영업부 5 -> ROLLUP, CUBE 개발부 사원 2 개발부 대리 2 개발부 4 -> ROLLUP, CUBE 사원 5 -> CUBE 대리 4 -> CUBE 전체 9 -> ROLLUP, CUBE
- GROUP BY ROLLUP(부서, 직급) = GROUP BY(부서,직급) + GROUP BY 부서 + 전체 집계함수
- GROUP BY CUBE(부서, 직급) = GROUP BY(부서,직급) + GROUP BY 부서+ GROUP BY 직급 + 전체 집계함수
### GROUPING SETS
- GROUP BY GROUPING SETS(부서, 직급) = GROUP BY 부서+ GROUP BY 직급
### JOIN (************중요***************)
- 카티션 곱
A_T 10 A
B_T 20 B
A_T + B_T -> 10 A 10 B 20 A 20 B
- 
- 카티션 곱이 일어나니까, 이 중 필요한 칼럼들만 간추리기 위해 WHERE절을 이용.
- JOIN을 할 때, WHERE절을 사용하지 않으면, 원하지 않는 카티션 곱 발생
### EQUL JOIN (이퀴 조인)
- 두 개의 테이블을 연결 할 때, EQUAL 연산을 이용한다. 상단의 조인방식의 이름임.
두 테이블이 모두 연결이 되어야만 출력이 되고, 연결이 안되어있는 것도 연결시키고 싶을 때, OUTER JOIN을 사용한다. (NULL값이 있는 애가 있을 때, 그 컬럼도 JOIN시키고 싶다 = OUTER JOIN_)
SELECT e.employee_id, e.first_name, e.salary, d.department_name, d.location_id, l.city, C.country_id FROM employees e, departments d, locations l, countries c WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND c.country_id = l.country_id AND e.job_id = ‘SA_MAN’
### OUTER JOIN
- 부서명과 부서장 이름(first_name)을 출력,부서장이 없어도 출력
```sql
SELECT D.DEPARTMENT_ID, D.MANAGER_ID, D.DEPARTMENT_NAME, E.FIRST_NAME
FROM departments D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID(+);
–부서27개 –부서명과 부서장 이름을 출력, 부사장이 없어도 출력 –부서의 도시명 출력 (도시명 없어도 출력)
SELECT D.DEPARTMENT_ID, D.MANAGER_ID, D.DEPARTMENT_NAME, E.FIRST_NAME, L.city
FROM departments D, EMPLOYEES E, Locations l
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID(+) and d.location_id =L.location_id(+);
(+)없이 inner join해도 된다. 왜냐하면 null값이 있는게 없기 때문.
SELF JOIN
(+) 붙인 쪽에 NULL이었던 값도 들어감.
ANSI JOIN
NATURAL JOIN
SELECT e.first_name, department_id, d.department_name
from employees e Natural join departments d
--join 조건에 해당하는 공통 칼럼은 alias 불가.
where department_id in(10,20);
USING JOIN
--사원명, 급여, 관리자명, 관리자급여, 근무 도시명 추가
select e.first_name 사원명, e.salary 급여, mgr.first_name 관리자명,
mgr.salary 관리자급여, l.city 근무도시명
from employees e
join employees mgr on(e.manager_id = mgr.employee_id)
join departments d on (d.department_id = e.department_id)
join locations l on(l.location_id = d.location_id);
OUTER JOIN
서브쿼리
SELECT문 안에 SELECT문
SELECT JOB_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID=139;
SELECT FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE JOB_ID=(SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 139);
--사원들의 평균 급여보다 많이 받는 사원들의 이름, 급여를 조회
SELECT FIRST_NAME, SALARY, (SELECT AVG(SALARY)
FROM EMPLOYEES)
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES)
-- 급여가 가장 많은 사원의 이름, 급여, 입사일 조회
select first_name, salary
from employees
where salary =(select max(salary) from employees );
-- 부서별 최고 급여
select employee_id, first_name
from employees
where salary = (select department_id, max(salary) --여러값과 =연산자로 비교를 못해서 오류 발생
from employees
group by department_id);
--- 오류 해결
select employee_id, first_name
from employees
where salary < all(select max(salary) --여러값과 =연산자로 비교를 못해서 오류 발생
from employees
group by department_id);
상호연관 서브쿼리
select *
from employees e
where salary > (select avg(salary)
from employees
where department_id = e.department_id);
FROM절 서브쿼리
- 부서별 평균 급여보다 더 많은 급여자
성능을 높이려면 서브쿼리보다 인라인, 조인을 이용해야함.
### TOP-N 서브쿼리
스칼라 서브쿼리
- 값 하나만 반환
- 복잡해서 쓰지 않는 것을 추천