[Oracle] GROUP BY & HAVING

-- NVL(컬럼명, 바꿀값1, 바꿀값2)
-- 해당 컬럼에 값이 있으면 바꿀값1로 변경,
-- 해당 컬럼이 NULL이면 바꿀값2로 변경

-- 직원 정보에서 보너스 포인트가 NULL인 경우 직원은0.5로
-- 보너스 포인트가 NULL이 아닌 경우 0.7로 변경하여 조회

SELECT
       EMP_NAME
     , BONUSa
     , NVL2(BONUS, 0.7, 0.5)
  FROM EMPLOYEE;

-- 선택함수
-- 여러 가지 경우에 선택할 수 있는 기능을 제공한다
-- DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2, ...)
SELECT
       EMP_ID
     , EMP_NAME
     , EMP_NO
     , DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여')
  FROM EMPLOYEE;

-- 아무 것도 해당하지 않을 때에는 마지막에 작성한 선택값을 무조건 선택한다.
SELECT
       EMP_ID
     , EMP_NAME
     , EMP_NO
     , DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여')
  FROM EMPLOYEE;

-- 직원의 급여를 인상하고자 한다.
-- 직급코드가 J7인 직원은 급여의 10%를 인상하고
-- 직급코드가 J6인 직원은 급여의 15%를 인상하고
-- 직급코드가 J5인 직원은 급여의 20%를 인상한다.
-- 그 외 직급의 직원은 5%만 인상한다.
-- 직원 테이블에서 직원명, 직급코드, 급여, 인상급여를 조회하세요
-- 인상급여는 위 조건에 해당하는 급여를 말함
SELECT
       EMP_NAME 직원명
     , JOB_CODE 직급코드
     , SALARY 급여
     , DECODE(JOB_CODE, 'J7', (SALARY * 1.1)
                      , 'J6', (SALARY * 1.15)
                      , 'J5', (SALARY * 1.2)
                            , (SALARY * 1.05)) AS 인상급여
  FROM EMPLOYEE;

-- CASE
--      WHEN 조건식 THEN 결과값
--      ELSE 결과값
-- END

SELECT
       EMP_NAME
     , JOB_COD
     , SALARY
     , CASE
         WHEN JOB_CODE = 'J7' THEN SALARY * 1.1
         WHEN JOB_CODE = 'J6' THEN SALARY * 1.15
         WHEN JOB_CODE = 'J5' THEN SALARY * 1.2
         ELSE SALARY * 1.05
        END AS 인상급여
  FROM EMPLOYEE;

-- 사번, 사원명, 급여를 EMPLOYEE 테이블에서 조회하고
-- 급여가 500만원 초과이면 '고급'
-- 급여가 300~500만원이면 '중급'
-- 그 이하면 '초급'으로 출력 처리하고 별칭은 구분으로 한다.
SELECT
       EMP_ID
     , EMP_NAME
     , SALARY
     , CASE
         WHEN SALARY > 5000000 THEN '고급'
         WHEN SALARY <= 3000000 THEN '초급'
         ELSE '중급'
        END AS 구분
  FROM EMPLOYEE;

--함수 연습문제
--
--1. 직원명과 주민번호를 조회함
--  단, 주민번호 9번째 자리부터 끝까지는 '*'문자로 채움
--  예 : 홍길동 771120-1******
SELECT
       EMP_NAME 직원명
     , RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*') 주민번호
  FROM EMPLOYEE;

--2. 직원명, 직급코드, 연봉(원) 조회
--  단, 연봉은 ￦57,000,000 으로 표시되게 함
--     연봉은 보너스포인트가 적용된 1년치 급여임
SELECT
       EMP_NAME 직원명
     , JOB_CODE 직급코드
     , TO_CHAR((SALARY * 12 * (NVL(BONUS, 0) + 1)), 'L999,999,999') AS "연봉(원)"
  FROM EMPLOYEE;

--3. 부서코드가 D5, D9인 직원들 중에서 2004년도에 입사한 직원의.
--   사번 사원명 부서코드 입사일 조회
SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , HIRE_DATE
  FROM EMPLOYEE
 WHERE DEPT_CODE IN('D5', 'D6')
   AND EXTRACT(YEAR FROM HIRE_DATE) = 2004; 

--4. 직원명, 입사일, 입사한 달의 근무일수 조회
--   단, 주말도 포함함
SELECT
       EMP_NAME
     , HIRE_DATE
     , LAST_DAY(HIRE_DATE) - HIRE_DATE + 1 AS "입사한 달의 근무일수"
  FROM EMPLOYEE;

--5. 직원명, 부서코드, 생년월일, 나이(만) 조회
--   단, 생년월일은 주민번호에서 추출해서, 
--   ㅇㅇ년 ㅇㅇ월 ㅇㅇ일로 출력되게 함.
--   나이는 주민번호에서 추출해서 날짜데이터로 변환한 다음, 계산함
SELECT
       EMP_NAME 직원명
     , DEPT_CODE 부서코드
--     , TO_DATE((SUBSTR(EMP_NO, 1, 6), 'RRRR"년 "MM"월 "DD"일"'))
     , SUBSTR(EMP_NO, 1, 2) || '년 ' || SUBSTR(EMP_NO, 3, 2) || '월 ' || SUBSTR(EMP_NO, 5, 2) || '일' 생년월일
     , TO_CHAR(SYSDATE, 'YYYY') - TO_NUMBER('19' || SUBSTR(EMP_NO, 1, 2)) 나이
  FROM EMPLOYEE;

--6. 직원들의 입사일로 부터 년도만 가지고, 각 년도별 입사인원수를 구하시오.
--  아래의 년도에 입사한 인원수를 조회하시오.
--  => to_char, decode, count 사용
--
--	-------------------------------------------------------------
--	전체직원수   2001년   2002년   2003년   2004년
--	-------------------------------------------------------------
SELECT
       COUNT(EMP_ID) 전체직원수
     , COUNT(TO_CHAR(DECODE(EXTRACT(YEAR FROM HIRE_DATE), '2001', EMP_ID))) AS "2001년"
     , COUNT(TO_CHAR(DECODE(EXTRACT(YEAR FROM HIRE_DATE), '2002', EMP_ID))) AS "2002년"
     , COUNT(TO_CHAR(DECODE(EXTRACT(YEAR FROM HIRE_DATE), '2003', EMP_ID))) AS "2003년"
     , COUNT(TO_CHAR(DECODE(EXTRACT(YEAR FROM HIRE_DATE), '2004', EMP_ID))) AS "2004년"                                                               
  FROM EMPLOYEE;

--7.  부서코드가 D5이면 총무부, D6이면 기획부, D9이면 영업부로 처리하시오.
--   단, 부서코드가 D5, D6, D9 인 직원의 정보만 조회함
--  => case 사용
--   부서코드 기준 오름차순 정렬함.
SELECT
       EMP_ID
     , EMP_NAME
     , EMP_NO
     , PHONE
     , JOB_CODE
     , SAL_LEVEL
     , SALARY
     , BONUS
     , MANAGER_ID
     , HIRE_DATE
     , ENT_DATE
     , ENT_YN
     , CASE
         WHEN DEPT_CODE = 'D5' THEN '총무부'
         WHEN DEPT_CODE = 'D6' THEN '기획부'
         ELSE '영업부'
       END AS 부서코드
  FROM EMPLOYEE
 WHERE DEPT_CODE IN('D5', 'D6', 'D9')
 ORDER BY 부서코드;

-- ORDER BY 절 : SELECT한 컬럼을 가지고 정렬을 할 때 사용함
-- ORDER BY 컬럼명 | 컬럼별칭 | 컬럼나열순번[ASC] | [DESC
-- ORDER BY 컬럼명 정렬방식, 컬럼명 정렬방식, 컬럼명 정렬방식, ...
-- 첫 번째 기준으로 하는 컬럼에 대해 정렬하고
-- 같은 값들에 대해 두 번째 기준으로 하는 컬럼에 대해 다시 정렬
-- 

/*
 5 : SELECT 컬럼명 AS 별칭, 계산식, 함수식
 1 : FROM 참조할테이블명
 2 : WHERE 컬럼명 | 함수식 비교연산자 비교값
 3 : GROUP BY 그룹묶을컬럼명
 4 : HAVING 그룹함수식 비교연산자 비교값
 6 : ORDER BY 컬럼명 | 별칭 | 컬럼순번 정렬방식 [NULLS FIRST | LAST]
*/

SELECT
       DEPT_CODE
     , COUNT(*) 사원수
  FROM EMPLOYEE
 GROUP BY DEPT_CODE;

SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
     , COUNT(*)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
     , JOB_CODE
 ORDER BY 1;

-- 직원 테이블에서 부서 코드별 그룹을 지정하여
-- 부서코드 그룹별 급여의 합계, 그룹별 급여의 평균(내림처리하여 정수로), 인원수 조회하고
-- 부서코드 순으로 오름차순 정렬하세요
SELECT
       DEPT_CODE 부서코드
     , SUM(SALARY) 합계
     , FLOOR(AVG(SALARY)) 평균
     , COUNT(*) 인원수
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
 ORDER BY DEPT_CODE;
       
-- 직원 테이블에서 직급코드, 보너스를 받는 사원 수를 조회하여
-- 직급코드 순으로 오름차순 정렬하세요
SELECT
       JOB_CODE
       , COUNT(*)
  FROM EMPLOYEE
 WHERE BONUS IS NOT NULL
 GROUP BY JOB_CODE
 ORDER BY JOB_CODE;
 
-- 직원 테이블에서 주민번호 8번째 자리를 조회하여
-- 1이면 남, 2이면 여로 결과 조회하고
-- 성별별 급여 평균(내림처리), 급여 합계, 인원수 조회한 뒤
-- 인원수로 내림차순 정렬하세요
SELECT
       DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') 성별
     , FLOOR(AVG(SALARY)) 급여평균
     , SUM(SALARY) 급여합계
     , COUNT(*) 인원수
  FROM EMPLOYEE
 GROUP BY SUBSTR(EMP_NO, 8, 1)
 ORDER BY 인원수 DESC;

-- GROUP BY 절 : 같은 값들이 여러 개 기록된 컬럼을 가지고
--               같은 값들을 하나의 그룹으로 묶음
-- GROUP BY 컬럼명 | 함수식, ...
-- 여러 개의 값을 묶어서 하나의 그룹으로 처리할 수도 있다.
-- 그룹으로 묶은 값에 대해서 SELECT 절에서 그룹함수를 사용한다.

-- HAVING절 : 그룹함수로 구해올 그룹에 대한 조건을 설정할 때 사용한다.
-- HAVING 컬럼명 | 함수식 비교연산자 비교값

-- 급여를 300만원보다 많이 받는 직원의 부서별 평균 급여 조회
SELECT
       DEPT_CODE
     , FLOOR(AVG(SALARY)) 평균
  FROM EMPLOYEE
 WHERE SALARY > 3000000
 GROUP BY DEPT_CODE
 ORDER BY 1;

-- 부서별 급여 평균이 300만원을 초과하는 그룹의 부서코드와 평균 급여 조회
SELECT
       DEPT_CODE
     , FLOOR(AVG(SALARY)) 평균
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING FLOOR(AVG(SALARY)) > 3000000
 ORDER BY 1;

-- 부서별 그룹의 급여 합계 중 9백만원을 초과하는 부서코드와 급여 합계 조회
SELECT
       DEPT_CODE 부서코드
     , SUM(SALARY) 합계
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING SUM(SALARY) > 9000000
ORDER BY 1;

-- 급여 합계가 가장 많은 부서의 부서 코드와 급여 합계를 구하세요
SELECT
       MAX(SUM(SALARY)) 합계
  FROM EMPLOYEE
 GROUP BY DEPT_CODE;

SELECT
       DEPT_CODE 부서코드
     , SUM(SALARY) 급여합계
  FROM EMPLOYEE
  GROUP BY DEPT_CODE 
HAVING SUM(SALARY) = 17700000;

SELECT
       DEPT_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
                        FROM EMPLOYEE
                       GROUP BY DEPT_CODE
                     );

-- 과제
-- 워크북 SELECT - 함수 10~15번 (15번은 ROLLRP문제)
-- 워크북 SELECT - OPTION 1~5번
-- 자바 워크샵 02