2019. 4. 5. 22:41ㆍDateBase
==================group by (그룹핑)================
group by
그룹을 지정하여 처리할수 있게해주는것
순서
select ~ // 조회할 컬럼 *필수 실행순서 : 5
from ~ // 조회대상 테이블 *필수 실행순서 : 1
where ~ // 조회할 행을 제한 실행순서 : 2
group by ~ // 조회된 행을 그룹핑 실행순서 : 3
having ~ // 그룹함수를 사용해서 행을 제한 실행순서 : 4
order by ~ // 조회된 행을 정렬 실행순서 : 6
1. 테이블을 선택한다.
2. 테이블의 행에서 조건을 만족하는 행만 선택한다.
3. 선택된 행을 그룹핑한다.
4. 그룹핑된 행에 그룹함수를 사용해서 행을 제한한다.
5. 그룹핑된 행에 그룹함수를 적용한다.
6. 정렬한다.
* 그룹함수와 컬럼을 같이 적을 수 없다.
(단, group by절에 등장한 컬럼 혹은 표현식은 그룹함수와 같이 적을 수 있다.)
* where절에서는 그룹함수를 조건식에 사용할 수 없다.
* group by절에서는 별칭을 사용할 수 없다.
* having절은 반드시 group by가 필요하다.
* having절은 그룹함수를 조건식에 사용할 수 있다.
* 그룹함수는 한번만 중첩이 가능하다.
예) sum(count(*)) <---- 가능 avg(sum(count(*))) <---- 불가능
예시) department_id가 같은것끼리 묶고 갯수 조회하기
select department_id, count(*)
from EMPLOYEES
group by department_id;
※. group by와 같이쓸때는 group by에 포함된것만 select에 사용이 되어 같이 쓸수있다.
아래처럼 할 경우 에러가 난다.
예시2) 부서별 사원수를 집계했을 때, 사원수가 30명 이상인 부서아이디와 사원수를 출력해라
select department_id, count(*)
from EMPLOYEES
group by department_id
having count(*) >= 30;
예시3) 부서별 평균 급여를 조회할때 평균급여가 10000달러 미만인 부서의 아이디와 평균급여 조회
SELECT department_id, trunc(avg(salary)) 평균급여
FROM EMPLOYEES
where department_id is not null
group by department_id
order by department_id;
예시4) 급여등급별 사원수를 조회하기
SELECT jg.gra, count(*)
FROM employees e, job_grades jg
WHERE e.salary >= jg.lowest_salary
and e.salary <= jg.HIGHEST_SALARY
group BY jg.gra
order by 1;
예시5) 급여별 사원수를 조회하기
-- 2000 10 2000~2999
SELECT trunc(salary,-3), count(*)
from employees
group by trunc(salary,-3);
order by 1;
예시6) 이름의 시작 알파벳별로 사원수 조회하기
select substr(first_name,1,1) 이름_시작_알파벳, count(*)
FROM employees
group by SUBSTR(first_name,1,1)
order by 1;
예시7) 부서별 직종별 사원수 조회하기
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY 1, 2;
예시8) 부서 소재지(lOCATION의 CITY)별 사원수 조회하기
SELECT L.CITY, COUNT(*)
FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.DEPARTMENT_ID IS NOT NULL
GROUP BY L.CITY
ORDER BY 1;
예시9) 각부서별 최고 급여를 조회하기
SELECT DEPARTMENT_NAME, MAX(SALARY)
FROM employees E, departments D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
group by D.DEPARTMENT_NAME
order BY 1;
---------------------------------서브 쿼리---------------------------------
서브쿼리란
-다른 SQL문의 where절에서 사용되는 쿼리
select ~
from ~
where 컬럼 연산자 (select 컬럼
from ~
where ~)
-서브쿼리를 작성하여 알 수 없는 값을 조건값으로 사용하기 위해서
예시) 전체 사원의 평균급여보다 급여를 적게 받는 사원의 아이디, 이름, 급여를 조회하기
-- 1. 평균급여를 계산한다.
SELECT AVG(SALARY)
FROM EMPLOYEES;
-- 2. 평균급여보다 급여를 적게 받는 사람
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < 6461;
-- 3. 서브쿼리로 한번에 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
FROM EMPLOYEES);
예시2) 'Neena'랑 같은 해에 입사하고 'Neena'에게 보고하는 사원의 아이디, 아름, 입사일 조회
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'YYYY') = ( SELECT TO_CHAR(HIRE_DATE,'YYYY')
FROM EMPLOYEES
WHERE FIRST_NAME = 'Neena');
AND DEPARTMENT_ID = ( SELECT MANAGER_ID
FROM EMPLOYEES
WHERE FIRST_NAME='Neena');
예시3) 'Steven'랑 같은 해에 입사한 사원의 아이디, 아름, 입사일 조회
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'YYYY') = ( SELECT TO_CHAR(HIRE_DATE,'YYYY')
FROM EMPLOYEES
WHERE FIRST_NAME = ('Steven'));
위와같이 에러가 나는데 이유는 같은것을 찾아야하는데 서브쿼리에서
찾은 값이 단일행일수 있지만 다중행일수도 있어서 IN이 안전함
아래같이 고쳐준다
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'YYYY') IN ( SELECT TO_CHAR(HIRE_DATE,'YYYY')
FROM EMPLOYEES
WHERE FIRST_NAME = ('Steven'));
예시4) Sundita 직원과 같은 부서에 일하고, Sundita보다 급여를 많이 받는 사원 조회하기
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE FIRST_NAME='Sundita')
AND SALARY > (SELECT SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = 'Sundita');
예시5) 급여를 가장 적게 받는 사원과 같은 해에 입사한 사원의 이름, 입사일, 급여 조회
SELECT FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'yyyy') = (SELECT TO_CHAR(HIRE_DATE,'YYYY')
FROM EMPLOYEES
WHERE SALARY = ( SELECT MIN(SALARY)
FROM EMPLOYEES));
* where절에서는 그룹함수를 조건식에 사용할 수 없다.
그래서 서브쿼리를 또 적은것이다.
---------------------------------------오라클 에러------------------------------------------
1. 테이블 이름이 틀린경우
ORA-00942: table or view does not exist
Error code: 942
SQL state: 42000
2. 컬럼의 이름이 틀린경우
ORA-00904: "틀린컬럼명": invalid identifier
Error code: 904
SQL state: 42000
3. 콤마가 누락된 경우
ORA-00923: FROM keyword not found where expected
Error code: 923
SQL state: 42000
4. 콤마가 너무 많은 경우
ORA-00936: missing expression
Error code: 936
SQL state: 42000
5. FROM 키워드에 오타가 있거나 FROM이 없는경우
ORA-00923: FROM keyword not found where expected
Error code: 923
SQL state: 42000
6. SELECT 키워드에 오타가있거나 SELECT가 없는경우
ORA-00900: invalid SQL statement
Error code: 900
SQL state: 42000
7. WHERE 키워드가 오타가있거나 WHERE가 없는경우
ORA-00933: SQL command not properly ended
Error code: 933
SQL state: 42000
