--그룹함수(그룹함수는 행이 없어도 결과 값이 반드시 있다.)
-- 행의 집합에 적용해서 그룹 당 하나의 결과를 생성하는 함수
-- (행의 집합은 테이블 혹은 (그룹화된 테이블)
-- avg(컬럼/표현식) 평균값(null값은 무시)
-- count(컬럼/표현식) (null 아닌)행의 갯수
-- count(*) 모든 행의 갯수
-- max(컬럼/표현식) 최대값(null값은 무시)
-- min(최소값 (null 값은 무시)
-- sun(합계(null값은 무시)
-- select절에 그룹함수가 사용될 때는 컬럼명이나 단일행 함수를 사용할 수 없다.
-- char, varchar2, number, date 타입이 그룹함수에서 연산가능한 데이타 유형이다.
--student 테이블에서 가장 큰키는?
SELECT MAX(height)
FROM STUDENT;
--student 테이블에서 가장 작은키는
SELECT MIN(height)
FROM STUDENT;
--student 테이블에서 평균 키는
SELECT AVG(height)
FROM STUDENT;
--EMPLOYEES 테이블에서 최소 입사일, 최대 입사일은
SELECT MIN(HIRE_DATE) "최초입사일", MAX(hire_date) "최종입사일"
FROM EMPLOYEES;
--알파벳 순서가 가장빠르고 가장 느린것
SELECT MIN(last_name), MAX(last_name)
FROM EMPLOYEES;
--employees 테이블에서 최고 월급
SELECT MAX(salary)
FROM EMPLOYEES;
--보너스를 받는 넘이 몇명?
SELECT COUNT(commission_pct)
FROM EMPLOYEES;
--50번 부서의 인원
SELECT COUNT(*)
FROM departments
WHERE department_id = 50;
--부서별 평균 급여 ㅊ울력
SELECT DEPARTMENT_ID, ROUND(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;
--연도별 사원수 구하기
SELECT TO_CHAR(hire_date, 'YYYY') "입사년도", COUNT(*) "사원수"
FROM EMPLOYEES
GROUP BY TO_CHAR(hire_date, 'YYYY')
ORDER BY 1;
--부서별/업무(직종별) 사원수 계산
SELECT DEPARTMENT_ID "부서번호", job_id "부서명", COUNT(*) "사원수"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID;
--WHERE 절에서 그룹함수를 사용할수 없다. -> group 핑이 되기전 상태이기 때문에 실행순서상 그룹함수를 이용한 연산이 불가능하다.
--그래서 having절이 존재한다.
SELECT department_id, ROUND(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) < 5000
ORDER BY DEPARTMENT_ID;
--GROUP BY와 HAVING
-- - GROUP BY 는 테이블의 행을 더 작은 그룹으로 나눈다.
-- - GROUP BY 컬럼명 혹은 GROUP BY 표현식
-- - GROUP BY와 그룹함수를 같이 사용하면
-- GROUP BY를 사용해서 나눠진 각각의 그룹에 그룹함수가 적용된다.
-- - GROUP BY절에 등장한 컬럼이나 표현식은 SELECT절에 사용할 수 있다.
-- - HAVING 그룹에 그룹함수가 적용된 후에 조건을 만족하는 행만 표시할 수 있다.
실행순서
WHERE, GROUP BY, 그룹함수, HAVING
-WHERE은 그룹핑할 대상을 줄여준다.
-GROUP BY는 행을 작은 그룹으로 나눈다.
-GROUP BY에 의해서 나눠진 각각의 그룹에 그룹함수가 실행된다.
-HAVING을 사용하면 그룹결과에서 조건을 만족하는 행만 표시한다.
--남녀 인원수
SELECT DECODE (SUBSTR(JUMIN, 7, 1), '1', '남', '2', '여') as "성별", COUNT(*) "성별 인원수"
FROM STUDENT
GROUP BY SUBSTR(JUMIN, 7, 1);
--성별인원수
SELECT SUBSTR(NAME, 0, 1) "성", COUNT(*) "성별 인원수"
FROM EMP2
GROUP BY SUBSTR(NAME, 0, 1)
HAVING COUNT(*) > 1;
--월별 생일자
SELECT TO_CHAR(BIRTHDAY, 'mm') "생일", COUNT(*) "명수"
FROM EMP2
GROUP BY TO_CHAR(BIRTHDAY, 'mm');
--1. 모든 사원의 급여 최고액, 최저액, 총액, 평균액을 표시
SELECT MAX(SALARY), MIN(SALARY), SUM(SALARY), AVG(SALARY)
FROM EMPLOYEES;
--2. 각업(JOB_ID)별 급여 최고액, 최저액, 총액, 평균액을 표시
SELECT JOB_ID, MAX(SALARY), MIN(SALARY), SUM(SALARY), AVG(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;
--3. 업무(JOB_ID)별 사원수 표시
SELECT JOB_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;
--4. 급여 최고액과 최저액의 차액 표시
SELECT MAX(SALARY), MIN(SALARY), MAX(SALARY) - MIN(SALARY) "차액"
FROM EMPLOYEES;
--5. 관리자 번호 및 해당 관리자에 속한 사원들의 최저 급여를 표시 (관리자가 없거나, 최저 급여가 6,000 미만인 그룹은 제외)
SELECT MANAGER_ID "매니져 아이디", MIN(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID
HAVING MIN(SALARY) > 6000
ORDER BY MANAGER_ID;
--6. 각 부서별 부서이름, 사원수, 부서내 사원의 평균 급여를 표시
SELECT D.DEPARTMENT_NAME "부서이름", COUNT(*) "사원수", ROUND(AVG(E.SALARY)) "사원 평균 급여"
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
ORDER BY "사원수";
--담당자가 있는 부서의 부서명과 담당자 이름 표시
SELECT D.DEPARTMENT_NAME "이부서의", E.LAST_NAME "담당자"
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID = D.MANAGER_ID;
--서브 쿼리 Chen보다 많이 받는놈
SELECT *
FROM EMPLOYEES
WHERE SALARY > (
SELECT salary
FROM EMPLOYEES
WHERE last_name = 'Chen'
)
ORDER BY SALARY;
--서브쿼리
-- - 다른 select문에 내포된 select문이다.
-- - where절, having절, from절 등에 포함시킬 수 있다.
-- - 서브쿼리는 괄호로 묶는다.
-- - 비교조건의 오른쪽에 서브쿼리를 위치 시킨다.
-- - 서브쿼리안에서 order by를 사용하지 않는다.
-- - 단일행 서브쿼리와 다중행 서브쿼리가 있으며, 각각 적합한 연산자가가 따로 있다.
-- - 단일행 서브쿼리 연산자
--최소급여와 같은 급여를 받는 직원의 이름, 업종 급여별 표시
SELECT last_name, JOB_ID, SALARY
FROM EMPLOYEES
WHERE salary = (
SELECT MIN(salary) FROM EMPLOYEES
);
--최소급여랑 그거랑 같은 급여를 받는 소속된 부서의 평균 급여를 표시
SELECT ROUND(AVG(SALARY)) "평균급여"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY)
FROM EMPLOYEES)
);
--부서별 평균 급여가 전체 최소급여 4배를 넘는 부서의 이름과 평균급여를 표시
SELECT D.DEPARTMENT_NAME, ROUND(AVG(E.SALARY))
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
HAVING AVG(SALARY) > (SELECT MIN(SALARY) * 4 FROM EMPLOYEES);
--Band와 같은 직종에 근무하고, banda보다 급여를 많이 받는 사원의 이름 급여를 표시
SELECT last_name, SALARY
FROM EMPLOYEES
WHERE JOB_ID = (
SELECT job_id
FROM EMPLOYEES
WHERE last_name = 'Banda')
AND SALARY > (
SELECT salary FROM employees WHERE last_name = 'Banda'
)
ORDER BY SALARY;
