＼(^o^)／

--복함함수 사용하여 데이터 다뤄보기
--

/* SQL 작성순서 및 실행순서
        1  SELECT   5
        2  FROM     1
        3  WHERE    2
        4  GROUP BY 3
        5  HAVING   4
        6  ORDER BY 6
    */
    --lpad 활용하여 급여가 가장적은사람부터 많은사람까지 출력. 시각화 구현
SELECT last_name||' '||first_name as emp_name
    , to_char(hire_date,'yyyymmdd') as hire_dt
    , salary
    , department_id
    , lpad('■',round(salary/1000,0),'■') as sal_vi
FROM employees
order BY salary desc;

--연도별 입사자 수와 평균 급여 출력하기
SELECT substr(to_char(hire_date,'yyyymmdd'), 1, 4) as 년도
    , count(*) as 입사자수
    , round(avg(salary),2) as 평균연봉
        , lpad('■',count(*),'■') as 입사자_vi
from employees
group by substr(to_char(hire_date,'yyyymmdd'),1,4)
order by 입사자_vi
;

--직원들의 이름, 급여, 부서번호, 직업정보, 근속년수
SELECT last_name||' '||first_name as 이름
    , salary as 급여
    , department_id as 부서번호
    , job_id as 직업정보
    , round(months_between(sysdate, hire_date)/12) as 근속년수
from employees
where round(months_between(sysdate, hire_date)/12) >= 10
;


-- 직업정보 기준으로 직원수들이 몇명이고 평균급여는 얼마이고 근속년수가 가장적은대상자의 근속년수를 출력하는 sql
--jobid기준 직원수/평균급여/최소근속/최대근속년수- 직원수가 가장많은 직무별로 정렬
--이때 직원수가 1명인건 출력하지 않도록 하기위해서는,where절이 아닌 having 을 사용해야 함. 직원수가 한명을 초과하는것들. 
--원본데이터가 아닌 그룹핑된 데이터(count)기준으로 조건을 거는거기떄문에
SELECT job_id
    , count(*) as 직원수
    , round(avg(salary),2) as 평균급여
    , min(round(months_between(SYSDATE, hire_date)/12)) as 최소근속년수
    , max(round(months_between(sysdate, hire_date)/12)) as 최대근속년수
from employees
where round(months_between(sysdate, hire_date)/12) >= 10
group by job_id
having count(*) > 1
order by 직원수 desc;
;

★where 과 group by의 차이점 숙지. 

select 5 필수   마지막에수행 일련의 작업이 끝나고 난후 사용자가 확인할수있는 결과 출력물에대한 결과를 어떻게 정의할지에 대해 명령을 내려주는 부분. 
from      1 필수   
where     2 전체데이터에서 일부데이터를 선택할경우 사용하는것 / 전체로작업할지, 부분으로작업할지 체크하는 부분. 버릴 데이터가 있는지 없는지체크.
group by  3 where로 줄어든 결과를 가지고 일련의 집계, 그룹핑을 할지말지 체크하는 부분 
having   4 그룹핑된 결과에 대해 필터링 할지말지 추가적으로 사용하는 부분.
order by  6 select 후 특정부분 기준으로 정렬을 할지말지 결정한다. 

--소계를 낼수있는 rollup기능 : 합계도출- groupby에서 사용할수있음! / null값 처리하고 rollup 기능 사용
--주의할점 select절에서 옵션이 들어가고 함수가 적용되어서 결과가 바뀌었다는건, groupby에도 똑같이 들어가야한다는것!!
--rollup 사용한후에 셀렉트절에 가서 한번더 nvl사용해서 null값을 총합계로 바꿔야함 
--rollup이 실행되고 나면 새로운 값이 생성이 되는거기때문에 이결과컬럼에대한 제어는 select에서 할수있다. 

---law데이터 기준으로 필터링할떄는 where / 집계된 결과에서 필터링해서 표시하려면 having by 
SELECT department_id, count(*) as 건수
FROM employees
GROUP BY department_id
having count(*) > 5  -- 집계된 결과에서 필터링 해줄거기때문에 having절에는 집계 결과+조건이 나와야한다
ORDER BY 건수 desc
;

/* select 5 필수   마지막에수행 일련의 작업이 끝나고 난후 사용자가 확인할수있는 결과 출력물에대한 결과를 어떻게 정의할지에 대해 명령을 내려주는 부분. 
from      1 필수   
where     2 전체데이터에서 일부데이터를 선택할경우 사용하는것 / 전체로작업할지, 부분으로작업할지 체크하는 부분. 버릴 데이터가 있는지 없는지체크.
group by  3 where로 줄어든 결과를 가지고 일련의 집계, 그룹핑을 할지말지 체크하는 부분 
hav'ing   4 그룹핑된 결과에 대해 필터링 할지말지 추가적으로 사용하는 부분.
order by  6 select 후 특정부분 기준으로 정렬을 할지말지 결정한다. 

*/

SELECT *
FROM employees
WHERE department_id in(40, 50, 60); --Where절은 원본데이터를 바로 가지고 온후에 행을 일부만 선택할지 여부 선택.

SELECT job_id
    , count(*) as 직원수    --- jobid 별 직원수
    , round(avg(salary), 2) as 평균급여 ----- 그룹핑된 jobid별 급여의 평균
FROM employees
WHERE department_id in(40, 50, 60) 
group by job_id
having round(avg(salary) ,2) > 5000   ---직원들의 평균급여가 5000이상인 사람들만 표시
order by 3 desc
;

--소계를 낼수있는 rollup기능 합계도출- groupby에서 사용할수있음! / null값 처리하고 rollup 기능 사용
--주의할점 select절에서 옵션이 들어가고 함수가 적용되어서 결과가 바뀌었다는건, groupby에도 똑같이 들어가야한다는것!!
--rollup 사용한후에 셀렉트절에 가서 한번더 nvl사용해서 null값을 총합계로 바꿔야함 
--rollup이 실행되고 나면 새로운 값이 생성이 되는거기때문에 이결과컬럼에대한 제어는 select에서 할수있다. 
SELECT nvl(nvl(to_char(department_id),'대기'),'총합계') as 부서
       , count(*) as 건수   ---null값은 대기발령이라는 값을 넣어준다. nvl과 to_char활용
FROM employees
group by rollup(nvl(to_char(department_id),'대기'))   ---맨위에 null로 총합계건수가 표시된다. 
order by 건수 desc
;

/*GROUP BY 활용
--사원테이블의 부서ID 기준 대상자수출력
 SELECT department_id, count(*) as 건수
  from employees         
 GROUP BY department_id   
 ;
 from으로 테이블 가져오고/ group by로 행에대한 기준을 잡고/ 이후에 select에서 집계함수의 출력결과를 출력.
 내가 원하는 값을 별도의 파생컬럼으로 만들어서 집계해볼수 있음.
 */
 
 --CASE WHEN 사용자정의 함수를 사용하면 정말 다양한 조건과 컬럼들을 만들어서 다양한 관점으로 정보를 확인할수 있다! ! 
 SELECT CASE WHEN SALARY <= 7000 THEN '저소득자'
             WHEN SALARY <=15000 THEN '중위소득자'
             ELSE '고소득자' END AS 소득그룹
             ,ROUND(AVG(SALARY),2) AS 평균급여
FROM EMPLOYEES
GROUP BY CASE WHEN SALARY <= 7000 THEN '저소득자'
             WHEN SALARY <=15000 THEN '중위소득자'
             ELSE '고소득자' END ---소득그룹에 대한 정의가 그룹바이에 나와줘야 함.(컬럼명 X)
             ;

--직원들의 이름가지고 최대최소함수 사용
SELECT first_name||' ' ||last_name as name
from employees
ORDER BY name; 
-- 오름차순 정렬(디폴트 or asc/ 내림차순은 desc)
--name 자리에 해당하는 연산의 결과로 직접 넣어도되고 , 컬럼의 순서를 넣어줘도 됨 (name/ first_name||' ' ||last_name/ 1 모두 같은 결과)

--이름 기준으로 최대최소값
SELECT min(first_name||' '||last_name) as col1 --- 오름차순의 맨처음이름
      , max(first_name||' '||last_name) as col2     -----내림차순의 첫번째이름
from employees;

----부서기준으로  이름 최대최소값
SELECT department_id
      , min(first_name||' '||last_name) as col1 --- 오름차순의 맨처음이름
      , max(first_name||' '||last_name) as col2     -----내림차순의 첫번째이름
from employees
GROUP BY department_id;
 
--연습문제

--직원의 근속연수 구간별 직원수, 평균급여, 근속연수 구간별 최대 근속년수를 출력해보자
--2021년 1월 1일 기준으로 사원들의 근속연수를 구해보자
-- 근속연수구간(5년이하, 10년이하, 15년이핟, 20년이하, 20년초과) -- 18강 7분부터
 
--사용자 지정함수로 구간을 나타내고, 그룹바이로 구간을 묶고 그 그룹에대한 집계함수에대한 출력결과를 SELECT를 출력. 
SELECT CASE WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 5 THEN '5년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 10 THEN '10년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 15 THEN '15년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 20 THEN '20년이하' ELSE '25년초과' END AS 근속연수구간
        , COUNT(*) AS 직원수
        , ROUND(AVG(SALARY),2) AS 평균급여
        , MAX(ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0)) AS 구간별최대근속년수
FROM employees
GROUP BY CASE WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 5 THEN '5년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 10 THEN '10년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 15 THEN '15년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 20 THEN '20년이하' ELSE '25년초과' END ;
 

--그룹에대한이해, 그룹바이 사용법에대해 알아보기


/*
SELECT  --열 선택 
 FROM  -- 데이터 꺼내오는부분
 WHERE  -- 필터링, 행제어
 GROUP BY
 HAVING 
 ORDER BY
 
 --작성 순서/ 컴퓨터가 명령 수행하는 순서
 실질적으로 컴퓨터가 수행하는 절차는 FROM부터 이루어지고, 마지막에 내가 표시할 컬럼을 선택해서 정렬SELECT가 가장 마지막에 실행.
 
 GROUP BY> 피벗테이블 수행과 동일. 이 기능의 부분이 그룹바이의 역할임.
 
 
 
 */
 
 SELECT department_id, count(*) AS 건수
  FROM employees
  group by department_id   -- 이 기준을 잡는다면 SELECT에도 같이 보여주는 습관을 들이면 더 좋음. 어떤 건에대한 건수를 세는건지 알수없으니까.
  ;
  
SELECT 시군구명, SUM(전체카메라수) AS 카메라수
 FROM 지역별설치현황
 GROUP BY 시군구명;
 
SELECT 시군구명, COUNT(*) AS 건수
 FROM 지역별설치현황  --파일을 열고
 GROUP BY 시군구명;   --기준을 잡는다 / 그룹바이가 없으면 기준이 없는데 SUM이나 COUNT 집계를 하려고 하는건 불가능하므로 에러가 난다.
 
 --GROUP BY가 없으면 전체에대한 건수가 나옴.
 SELECT COUNT(*) AS 건수
 FROM 지역별설치현황
;
  

 SELECT 시군구명, 동리명, COUNT(*) AS 건수, SUM(전체카메라수) AS 카메라수
 FROM 지역별설치현황
 GROUP BY 시군구명, 동리명;
  

알것같은 내용들을 반복훈련하면서 숙달시키기, SQL구조 정확히 이해하는것이 중요하다.

헷갈릴떄는 쿼리 바로 작성해보고 실행하보기 / 어려울땐 단계별로 접근해서 해결하기 쉬운부분먼저 작성해가기. 

-/ 4분20초부터 다시듣기

/* 미완성
가장먼저 해결할수있는 부분부터 만들어나간다.
먼저 연봉산출을 위해 commission_pct 구한 후
급여+커미션) *12 = 연봉 산출하여 
이 연봉들을 묶어서 평균급여 최대 최소 구한다. 나중에 그룹바이 배웠을때 할거임.

사원테이블의 연봉산출은 아래와 같이 정의하기로 한다.
급여 = 급여+ 커미션
커미션이 null이면 아래와같이 비율을 조정햊 ㅜㄴ다.

1. 연봉산출 = 급여 (salary) + salary* comm
    comm이 null인 대상들에서
    -- 20000 초과 0.38
    -- 15000 초과 0.44
    -- 10000 초과 0.40
    --그외 0.35
    
1에 산출된 연봉에 대한 소득구간은 아래와 같이 정의한다.

2. 소득구간
    연봉이 0~70000에 해당하면 저소득(7만이하)
    70000 ~ 200000 중위소득(7만초과 20만이하)
    200000 ~ 250000 고소득(20만초과 ~ 25만이하)
    25만 초과하면 초과소득 */
    
 --   AVG(SALARY) AS 평균급여
--    , MAX(SALARY) AS 최대
 --   , MIN(SALARY) AS 최소
    
-- 사원테이블의 소득구간별 대상자수와 평균급여 최대, 최소 연봉을 구하시오

SELECT salary
    , CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END commission_pct
        , (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12 as 연봉
FROM employees;

-----------------------------------------------------------일부채워본거임 미완성
SELECT  CASE WHEN (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
              WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
              WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
              WHEN commission_pct is NULL then 0.35
              ELSE commission_pct END))*12 
        BETWEEN 0 AND 70000 THEN '저소득(7만이하)'
       WHEN (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12 
           > 70000 AND
           (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12 
           <= 200000 THEN '중위소득(7만초과 20만이하)'
      WHEN (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12 
           > 200000 AND
            (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12 
           <=250000 THEN '고소득(20만초과 ~ 25만이하)'
    WHEN (salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12 
           > 250000 THEN '초과소득' END AS 소득구간
           
    , ROUND(AVG(salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12) AS 평균급여
    , MAX(salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12 AS 최대연봉
    , MIN(salary+(salary * CASE WHEN salary> 20000 AND commission_pct IS NULL THEN 0.38
           WHEN salary> 15000 AND commission_pct IS NULL THEN 0.44
           WHEN salary> 10000 AND commission_pct IS NULL THEN 0.40 
           WHEN commission_pct is NULL then 0.35
           ELSE commission_pct END))*12 AS 최소연봉
FROM employees;

---다양한 조건 조합해서 사용자 정의 함수를 만들어볼수있음. 
/*단일 조건에 사용시
    1. CASE문  >>>함수가 아닌 표현식의 일종. 하나, 그이상의 연산자또는 함수를 조합해서 조건을 만드는 것임. 넓은 범위에 활용가능.
    CASE WHEN 특정 컬럼의 조건과 일치
        THEN 처리결과 [입력 값, 컬럼 ] ELSE 다른결과 END
        
    2. DECODE (조건컬럼, 조건1, 반환결과1, 조건2, 반환결과2) 사용방법은 간단하지만 동등비교에서만 사용가능한 함수, 오라클에서만 사용가능.
*/    
    SELECT department_id
        , DECODE(department_id, 100, 1, 0) as CHK1
        , CASE WHEN department_id = 100 THEN 1 ELSE 0 END CHK22
    FROM employees
    ;

/* 다중조건에 사용시
    1. CASE문
    CASE WHEN 특정 컬럼의 조건과 일치 THEN 처리결과[입력값, 컬럼] <---조건1
         WHEN 특정 컬럼의 조건과 일치 THEN 처리결과[입력값, 컬럼] <---조건2
         WHEN 특정 컬럼의 조건과 일치 THEN 처리결과[입력값, 컬럼] <---조건3
    ELSE 다른결과  END


    2. DECODE(조건컬럼, 조건1, 반환결과1, 조건2, 반환결과2)
*/
    SELECT department_id
        , DECODE(department_id, 90, 'A그룹', 60, 'B그룹', 100, 'C그룹', 'D그룹') as CHK1
        , CASE WHEN department_id = 90 THEN  'A그룹'
                WHEN department_id = 60 THEN  'B그룹'
                WHEN department_id = 100 THEN  'C그룹'
        ELSE 'D그룹' END CHK22
    FROM employees
    ;
    /*하나의 값에 1대1 대응하는 조건인경우 디코드랑 케이스를 동시에 사용가능, 오히려 케이스를 작성하는것보다 디코드가 효율적이어 보인다.
    이번엔 부서의 번호가 구간별로 잡힌다면 디코드를 사용할수있을것인가.?*/

--디코드는 1대1로 TRUE / FALSE에 대한 반환값을 확인할수있기때문에 범위조건으로 해결하려면 CASE문 사용해야함.
--해당하는 하나의 값에 대응되는 조건에 대해서는  디코드와 케이스를 동시에 사용할수있다
    SELECT department_id
        , DECODE(department_id, 90, 'A그룹', 60, 'B그룹', 100, 'C그룹', 'D그룹') as CHK1
        ,CASE WHEN department_id >= 90 THEN  'A그룹'
                WHEN department_id < 90 THEN  'B그룹'
    ---NULL값은 숫자로 인식하지 못하기때문에 예외처리를 해줘야함.            
                 ELSE '대기발령' END CHK22
    FROM employees;
    
  --CASE구문의 확장성을 경험하기 위한 문제
  /* 연습문제
    사원의 입사일 ~ 2020년 1월 1일까지의 근속개월수를 구해 근속년수로 산출하고
    근속년수가 15년을 초과하면 기본급여에 보너스 10,000을 10년초과 ~ 15년이하는 보너스 7,000DMF
    10년 이하는 5,000의 보너스를 지급하는 CASE문을 작성해보자.
    */
    SELECT hire_date AS 입사일
        ,round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date), 2) 근속개월
        , round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) 근속년수
        , salary as 급여
        
        ,CASE WHEN round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) > 15 THEN salary + 10000
                when round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) > 10 
                        and round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) <= 15  THEN salary + 7000
                when round(MONTHS_BETWEEN(TO_DATE('20200101', 'YYYYMMDD'), hire_date) /12 ,2) <= 10 THEN salary + 5000
            end as 보너스금액
    FROM employees;
    

질문하나 남겨놨음!

SELECT '20200501'+50
    FROM dual;
    
--50을 더하면 날짜가 제대로 더해지지 않기때문에 날짜형식으로 바꾼후 숫자를 더해준다. 중요!!
SELECT TO_DATE('20200501', 'YYYYMMDD') - 50
    FROM dual;
    
SELECT TO_DATE('20200501', 'YYYYMMDD') - TO_DATE('20200101', 'YYYYMMDD')
    FROM dual;

--아래와같이 출력하면 20200501 날짜만 보이게 되어있음 (TO_DATE 날짜타입이기 때문)
SELECT TO_DATE('20200501123959', 'YYYYMMDD HH:MI:SS')
    FROM dual;
    

--시분초를 같이 보여지게 하기 위해서는 CHAR 사용   1을 더하면 5/1에서 5/2일로 바뀜
SELECT TO_CHAR(TO_DATE('20200501123959', 'YYYYMMDD HH:MI:SS')+1
                , 'YYYYMMDD HH:MI:SS')
    FROM dual;

--시분초를 같이 보여지게 하기 위해서는 CHAR 사용   시간을 더해주기 위해서는 1/24 해서 더한다. (24시간중 1시간)
SELECT TO_CHAR(TO_DATE('20200501123959', 'YYYYMMDD HH:MI:SS')+8/24
                , 'YYYYMMDD HH:MI:SS')
    FROM dual;

--달수 차이
SELECT months_between(TO_DATE('20200501', 'YYYYMMDD'), TO_DATE('20000501', 'YYYYMMDD'))
FROM dual;

--5/1에서 19달 지난 날짜는?
SELECT add_months(TO_DATE('20200501', 'YYYYMMDD'), 19)
FROM dual;


--**날짜함수의 종류
/*
1. 월수 계산 MONTHS_BETWEEN
2. 월 덧셈 ADD_MONTHS
3. 돌아오는 요일 NEXT_DAY
4. 마지막 날 LAST_DAY
5. 날짜 반올림 ROUND
6. 날짜 버림 TRUNC
*/

SELECT MONTHS_BETWEEN(TO_DATE('20210331', 'YYYYMMDD'), TO_DATE('20210131','YYYYMMDD')) "3월과 1월의 개월차"
    , ADD_MONTHS(TO_DATE('20210131', 'YYYYMMDD'),5) "5개월후"
    , LAST_DAY(TO_DATE('20210101', 'YYYYMMDD')) "1월의 마지막 날"
--애러남    , NEXT_DAY(TO_DATE('20210131', 'YYYYMMDD'), 1) "1월31일 이후 돌아오는 일요일"
FROM DUAL;

>> alias 부분의 내용이 길어서 에러가 나는거고, "돌아오는일요일"로 바꾸면 출력됨!  (컬럼명은 30byte 이하여야 한다고 함. ) UTF-8인코딩기준

SELECT MONTHS_BETWEEN(TO_DATE('20210331', 'YYYYMMDD'), TO_DATE('20210131','YYYYMMDD')) "3월과 1월의 개월차"
    , ADD_MONTHS(TO_DATE('20210131', 'YYYYMMDD'),5) "5개월후"
    , LAST_DAY(TO_DATE('20210101', 'YYYYMMDD')) "1월의 마지막 날"
    , NEXT_DAY(TO_DATE('20210131', 'YYYYMMDD'), 1) "돌아오는일요일"
FROM DUAL;

--엑셀에서 합계/갯수/ 평균 쉽게 구하는것처럼 똑같이 오라클에서 해당값을 산출하는방법
SELECT SUM(salary) 급여합계
    ,AVG(salary) 평균급여
    ,MIN(salary) 최소급여
    ,MAX(salary) 최대급여
    ,COUNT(salary) 급여건수
    ,COUNT(commission_pct) 건수1   ----NULL값은제외하고 카운트됨.
    ,COUNT(department_id) 건수2    
    ,COUNT(*) 건수3    --전체에대한 행의갯수를 알고싶을떄는 고유한 값인 PK값을 컬럼으로 사용하거나 아니면
                        --COUNT안에 별표넣으면 해당 전체행중에 하나의 값이라도 존재하는 행의 갯수를 세주기때문에 전체건수 확인할수있음. 
FROM employees;

    

/* 데이터 정의함수 알아보기 */

-- 1. TO_NUMBER 문자를 숫자로
SELECT '123' + 100 FROM dual;     --문자타입과 숫자타입을 지정해서 계산해도 오랔르이 알아서 숫자로 변환해서 연산해줌.
SELECT TO_NUMBER('123') + 100     --TO NUMBER 사용해서 정확하게 문자타입 연산하는걸 추천, 위와같이 했을때 연산이 되지않는경우도 종종 있기때문임. 
    FROM dual;

-- 2. NVL NULL 값이 존재하는 것을 원하는 값으로 변경
SELECT salary, nvl(commission_pct,0)
    , salary + commission_pct   ----연산하는값중에 null이 있을때 결과는 무조건 null을 반환하게 되어있음.  >>값이null임
    , salary + nvl(commission_pct,0)   ----이런값들 연산하기위해서는 null값을 특정값으로 치환해줘야함. null을0으로치환했다. >>제대로값나옴
    FROM employees
;

-- 추가)) 2. NVL NULL 값이 존재하는 것을 원하는 값으로 변경--- 문자'a'로변경 
SELECT salary, nvl(TO_CHAR(commission_pct),'a') AS NULL값A로치환
/*null값을 a로바꾸려고하는데, 이때commission_pct의 데이터타입이 이미 숫자로 선언되어있기때문에 문자인 a로 바꾼다고하면 타입이 위배된다.
이럴때 옵션 없이 to_char 이용해서 commission_pct의 형태자체를 숫자로 바꾸면, NVL을 사용했을때 문자로 바꾸는 명령이 가능해짐. */
, salary + commission_pct  
, salary + nvl(commission_pct,0) 
    FROM employees
;

-- 3. TO_CHAR 문자/숫자/날짜 다루기
/*수치가 부적합하다는 에러가 나옴. 
입력한 2020/12/8은 문자형태이기때문에 TO_DATE사용해서 날짜형식으로 바꿔주고 변환된 날짜로 다시한번 세기를 출력한다.
*/
SELECT TO_CHAR(TO_DATE('2020/12/8') , 'cc') 
    ,TO_CHAR(TO_DATE('2020/12/8') , 'YYYY') 
    ,TO_CHAR(TO_DATE('2020/12/8') , 'YYY') 
    ,TO_CHAR(TO_DATE('2020/12/8') , 'YY') 
    ,TO_CHAR(TO_DATE('2020/12/8') , 'YEAR')
FROM dual;

SELECT TO_CHAR(TO_DATE('2020/12/8') , 'MM') 
    ,TO_CHAR(TO_DATE('2020/12/8') , 'MON') 
    ,TO_CHAR(TO_DATE('2020/12/8') , 'DAY') 
    ,TO_CHAR(TO_DATE('2020/12/8') , 'DY') 

FROM dual;


SELECT 
    TO_CHAR(TO_DATE('2020/12/8') , 'DDD') AS 올해몇일째
    ,TO_CHAR(TO_DATE('2020/12/8') , 'DD') AS 이번달몇일째
    ,TO_CHAR(TO_DATE('2020/12/8') , 'D') AS 이번주몇일째
    ,TO_CHAR(TO_DATE('2020/12/8') , 'Q') AS 몇분기
FROM dual;

-- 4. TO_DATE

SELECT TO_CHAR(TO_DATE('20201208132945','YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
    ,TO_CHAR(TO_DATE('20201208132945','YYYY-MM-DD HH24:MI:SS'), '"일자 : "YYYY-MM-DD "시간 : " HH24:MI:SS') 일시
    
FROM dual;


SELECT TO_CHAR(SALARY, '999,999.99')
FROM EMPLOYEES;

어려웠던 강의 4번째자리구해보라고 했는데 이건 답이없어서 맞는지모르겠다; 네번째자리수가 없는애들은 값 표시안하게 하고싶은데 이거나중에 더배워서 고쳐봐야할듯 ㅠ_ㅠ 헝

--문자열함수 알아보기 / 문자열을 다양한 함수를 이용해서 다뤄보기
/*
1. 대소문자 함수 알아보기
    LOWER, UPER, INITCAP
2. 특정문자열 추출
    SUBSTR
3. 문자열 길이 확인
    LENGTH
4. 문자열의 위치 확인
    INSTR
5. 문자열 변경하기
    REPLACE
6. 특정문자로 자리수 맞추기

*/

--문자열 함수 응용문제
-- 1. 대소문자 함수들이 어떻게 쓰이는지? 

--퍼스트네임에 소문자 E가들어가는 행 출력
SELECT *
    FROM employees
    WHERE first_name LIKE '%e%'
    ;
    
    
SELECT *   
    FROM employees
    WHERE first_name||' '||last_name LIKE '%e%' --파생컬럼을 만들어서 데이터 필터링. 따라서 데이터 출력은 원본에서 출력이 된다.
    ;
/* 
SELECT부터 작성해서 문장시작하지만,
FROM 부터 실행해서
WHERE로 필터링 한 후에 나중에 SELECT로 넘어가서 실행된다.
*/



/* 대소문자 구분없이 출력*/ -(대/소문자) 하나로 치환해서 검색을 했을때 원하는 결과를 수월하게 얻을수 있다. 
SELECT *   
    FROM employees
    WHERE upper(first_name||' '||last_name) LIKE '%E%'
    ;

SELECT first_name||' '||last_name as NAME 
    ,instr(first_name||' '||last_name, 'a') as 소문자a의위치   
    ,lower(first_name||' '||last_name) as 소문자이름       
    FROM employees
WHERE instr(lower(first_name||' '||last_name),'a') in (1, 5, 6, 7)  --대소문자 구분없이 a가 1,5,6,7번째 존재하는 애들만 표시
    ;
    


-- 2. 문자열을 자르는 함수들이 어떻게 쓰이는지? 

SELECT phone_number
    , substr(phone_number, 1, 3) as col1
    --
    , substr(phone_number, 5, 3) as col2    
    , substr(phone_number, 9, 4) as col3 
    , length(phone_number) as 자리수
   
   --
   , substr(phone_number, 5, length(phone_number)) as ph2  --폰넘버 5번째부터 자릿수끝까지 표시
   , instr(substr(phone_number, 5, length(phone_number)), '.') as 점의위치 --폰넘버 5번째부터 자릿수끝까지인 데이터에서 .의 위치를 표시해줌
   , substr(substr(phone_number, 5, length(phone_number)), 1, instr(substr(phone_number, 5, length(phone_number)), '.')-1) as 두번째 --자른번호에서 첫번째자리부터 .위치 전(-1)까지 표시해줘라. 
   , substr( substr(phone_number, 5, length(phone_number))
            , instr(substr(phone_number, 5, length(phone_number)), '.')+1
            , length( substr(phone_number, 5, length(phone_number)))
            ) as 세번째
  ----아래꺼는 마지막 네번째자리 혼자서 구해본거... 잘모르겠음..
  
  , substr( substr(phone_number, 5, length(phone_number)), instr(substr(phone_number, 5, length(phone_number)), '.')+1, length(phone_number)) as ph3--ph2의 데이터에서 .의위치+1부터 폰번호 길이끝까지 표시
  , substr( substr(substr(phone_number, 5, length(phone_number)), instr(substr(phone_number, 5, length(phone_number)), '.')+1, length(phone_number))
            , instr(substr( substr(phone_number, 5, length(phone_number)), instr(substr(phone_number, 5, length(phone_number)), '.')+1, length(phone_number)), '.') +1 
            , length(substr( substr(phone_number, 5, length(phone_number)), instr(substr(phone_number, 5, length(phone_number)), '.')+1, length(phone_number)))
            ) as 네번째
   FROM employees;

--문자열 함수 종류와 기능
/*
1. 대소문자 함수 알아보기
    LOWER, UPER, INITCAP
2. 특정문자열 추출
    SUBSTR
3. 문자열 길이 확인
    LENGTH
4. 문자열의 위치 확인
    INSTR
5. 문자열 변경하기
    REPLACE
6. 특정문자로 자리수 맞추기

*/

--어느 위치에와도 이 함수를 다양한 구조로 활용할수있음. 
SELECT 번지주소, LENGTH(번지주소) AS 주소자리수
FROM 지역별설치현황
WHERE LENGTH(번지주소) > 22 ;

-- 1. 대소문자 함수 알아보기
--    LOWER, UPER, INITCAP
SELECT last_name||' '||first_name as "name"
    , lower(last_name||' '||first_name) as "name_1"
    , upper(last_name||' '||first_name) as "name_2"
    , initcap(lower(last_name||' '||first_name)) as "name_3"      
FROM employees;
--대소문자 구분하는게 가독성에 좋음 

SELECT 번지주소, instr(번지주소, '동') as 동찾기
FROM 지역별설치현황;

--특정위치 키워드 찾아서 바꾸기
SELECT 번지주소, replace(번지주소, ' ', '**') as 키워드바꾸기
FROM 지역별설치현황;

--글자수 잘라보기 /주소에서 서울시만 빼서 표시
SELECT 번지주소, substr(번지주소, 1, 5) as 서울시
    , substr(번지주소, 11, 7) as 서울시
FROM 지역별설치현황;

--lpad 채울 숫자가 바이트임. 한글은2바이트, 영어는1바이트
SELECT 시도명
    , lpad(시도명, 20, '★') as "lpad확인"
    , rpad(시도명, 20, '★') as "rpad확인"
FROM 지역별설치현황;