정렬(order by)과 통계(count, sum, avg, max, min)

˙order by 테이블 형식

ORDER BY == Sorting

SELECT      COLUMN VALUE SUBQUERY
FROM    TABLE   SUBQUERY
WHERE IF
ORDER BY COLUMN ASC     오름차순(생략)
                DESC    내림차순

DESC 테이블명 -> 명세 -> 잘안씀

˙order by 연습

-- ALIAS
SELECT employee_id, first_name, salary * 12 AS annual
FROM employees
-- ORDER BY salary DESC;
ORDER BY annual DESC;

-- NULL FIRST
SELECT commission_pct
FROM employees
ORDER BY commission_pct NULLS FIRST;
-- NULL LAST
SELECT commission_pct, salary
FROM employees
ORDER BY commission_pct NULLS LAST, salary DESC;

-- 입사순으로 정렬, 급여 역순
SELECT hire_date, salary, job_id
FROM employees
ORDER BY hire_date ASC, salary DESC;

-- 업무명으로 정렬, 급여역순으로 정렬
SELECT job_id, salary
FROM employees
ORDER BY job_id ASC, salary DESC;

/*
    DISTINCT : 중복 생략 -> 폭넓은 사용이 불가능
*/
SELECT DISTINCT job_id
FROM employees;

˙GROUP BY : 그룹으로 묶는 기능
     HAVING : 그룹으로 묶은 후의 조건

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY department_id ASC;

SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;

˙통계
    COUNT - 그룹함수
    SUM - 합계
    AVG - 평균
    MAX - 최대
    MIN - 최소

SELECT job_id, COUNT(employee_id)
FROM employees
GROUP BY job_id;
-- 밑이랑 위랑 같음 
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;

SELECT job_id, COUNT(*), SUM(salary), AVG(salary), MAX(salary), MIN(salary)
FROM employees
GROUP BY job_id;

SELECT job_id, COUNT(*), SUM(salary), AVG(salary)
FROM employees
GROUP BY job_id
HAVING SUM(salary) >= 100000;

-- 급여가 5000이상 받는 사원만으로 합계를 내서 job으로 그룹화하여
-- 급여의 합계가 20000을 초과하는 job_id명을 구하라
SELECT job_id, SUM(salary)      --                          4
FROM employees
WHERE salary >= 5000            -- 묶기 전 조건 : 필터링    1
GROUP BY job_id                 --                          2
HAVING SUM(salary) >= 20000;    -- 묶은 후의 조건           3