oracle Select 활용 3(Group by, Order by)

-- ORDER BY : 정렬

-- ASC 오름 차순
SELECT * FROM emp
--ORDER BY sal ASC;
ORDER BY hiredate ASC;
-- DESC 내림차순

SELECT * FROM emp
--ORDER BY sal DESC;
ORDER BY hiredate DESC;

-- ALIAS
-- 오름차순으로 연봉을 정렬해라 
SELECT empno, ename, sal * 12 AS annsal
FROM emp
ORDER BY annsal ASC;
-- 누룽지 늬낌 

-- NULL 값을 먼저 올리고 그후로 정렬 
SELECT
    *
FROM emp
ORDER BY comm NULLS FIRST; 

-- NULL 값을 마지막으로 내리고 그후로 정렬 
SELECT
    *
FROM emp
ORDER BY comm NULLS LAST; 

-- hr 계정
-- 연속 정렬
SELECT employee_id, job_id, salary
FROM employees
ORDER BY job_id ASC, salary DESC;

-- 문자열을 합하는 기능
SELECT first_name || ' ' || last_Name
FROM employees;

--무조건 붙여서만 나온다.
SELECT concat(first_name, last_name )
FROM employees;

-- month_between : 두날짜간의 월수 (월 간격)
-- 더 큰쪽의 달을 앞에 두어야 한다
select MONTHS_BETWEEN( '2020-05-04', '2019-12-13')
FROM dual;
-- 소수점 처리 
--select trunc(MONTHS_BETWEEN( '2020-05-04', '2019-12-13'))
select round(MONTHS_BETWEEN( '2020-05-04', '2019-12-13'))
FROM dual;

-- add_months : 특정 날짜 월에 정수에 더한 다음 해당 날짜를 반환
SELECT add_months('2019-12-13', 3)
FROM dual;

-- 날짜 정보에서 특정한 연도, 월, 일
SELECT EXTRACT(year from sysdate) as 연도,
       EXTRACT(month from sysdate) as 월,
       EXTRACT(day from sysdate) as 일
FROM dual;

--시간 지정을 안해주면 현재시간이 들어간다 
--SELECT EXTRACT(year from TO_DATE('191001 12', 'YYMMDD HH')) as 연도,
SELECT EXTRACT(year from TO_DATE('191001', 'YYMMDD')) as 연도,
       EXTRACT(month from TO_DATE('19/11/01')) as 월,
       EXTRACT(day from TO_DATE('19/11/01')) as 일
FROM dual;

-- 날짜 정보에서 특정한 시, 분, 초
SELECT EXTRACT(hour from CAST(sysdate AS TIMESTAMP)) as 시,
       EXTRACT(minute from CAST(sysdate AS TIMESTAMP)) as 분,
       EXTRACT(SECOND from CAST(sysdate AS TIMESTAMP)) as 초
FROM dual;

-- 그룹으로 묶는 기능 
-- 동일한것이 있을떄 묶는다.
-- 컬럼을 확인하고 묶기 (직업, 부서번호 등)
SELECT DISTINCT department_id, employee_id
FROM employees
ORDER BY department_id ASC;

-- GROUP BY
-- 묶어버렸기때문에 다른걸 표현할 수 없다.
-- 묶은것만 볼수있다.
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY department_id ASC;


/*
GROUP 함수
COUNT
SUM
AVG
MAX
MIN
*/

--에러 발생 그룹
SELECT COUNT(salary), COUNT(*), SUM(salary), AVG(salary),
    SUM(salary) / COUNT(*), job_id --AVG와 같다 
FROM employees
WHERE job_id = 'IT_PROG';


-- 선거철에 지역별로 묶을때 사용
SELECT COUNT(salary), COUNT(*), SUM(salary), AVG(salary),
    SUM(salary) / COUNT(*), job_id --AVG와 같다 
FROM employees
--WHERE job_id = 'IT_PROG'
GROUP BY job_id;

/*
NVL(대상이 되는 컬럼, 0)   NUMBER
대상이 되는 컬럼 != NULL --> 대상이 되는 컬럼 
대상이 되는 컬럼 == NULL --> 0

NVL(대상이 되는 컬럼, '없음')    VARCHAR2
대상이 되는 컬럼 != NULL --> 대상이 되는 컬럼 
대상이 되는 컬럼 == NULL --> '없음'

NVL2(대상이 되는 컬럼, 1, 0) NUMBER 일때
대상이 되는 컬럼 != NULL --> 1
대상이 되는 컬럼 == NULL --> 0
*/

SELECT first_name, NVL(commission_pct, 0)
FROM employees;

SELECT first_name, NVL2(commission_pct, '있음', '없음')
FROM employees;

SELECT department_id, SUM(salary), max(salary), trunc(avg(salary))
FROM employees
GROUP BY department_id
ORDER BY department_id ASC;

-- HAVING
-- 무조건 GROUP BY와 같이 사용해야 한다.
SELECT job_id, sum(salary)
FROM employees
GROUP BY job_id
HAVING sum(salary) >= 100000
ORDER BY SUM(salary) DESC;

-- 급여가 5000불 이상 받는 사원만으로 합계를 내서 업무로 그룹화 하여
-- 급여 합계가 20000불을 초과하는 업무명을 구하라 

--GROUP BY는 무조건 WHERE전 후에 나와야한다. ORDER BY도 마찬가지 
SELECT job_id, count(*), sum(salary), round(avg(salary))
FROM employees
WHERE salary > 5000
GROUP BY job_id
HAVING SUM(salary) > 20000;