[2일차] DO IT 오라클 데이터베이스/p127 ~ 240/ 그룹함수, 조인,집계함수

-- upper : 문자열을 모두 대문자로 변환
-- lower : 문자열을 모두 소분자로 변환
-- initcap : 첫글자를 대문자로 나머지 문자를 소문자로 변환후 반환

select ename, upper(ename), lower(ename), initcap(ename) from emp;

-- select * from 게시판 테이블 where 게시판 제목 열 like '%Oracle%' or 게시판 본문 열 like '%Oracle%';

select * from emp where upper(ename) = upper('smith');

select * from emp;

select * from emp where upper(ename) like upper('%smith%');

select upper(ename) from emp;

-- 문자열 길이 구하는 length 함수
select ename, length(ename) from emp;

select ename, length(ename) from emp where length(ename) >= 5;

select length('한글'),  lengthb('한글') from dual;

select * from emp where length(job) >= 6;


--  문자열 일부를 추출하는 substr 함수
--  substr(문자열 데이터, 시작위치, 추출길이) - 문자열 데이터의 시작 위치부터 추출 길이만큼 추출. 시작 위치가 음수일 경우 마지막 위치부터 올라간 위치에서 시작)
--  substr(문자열 데이터, 시작위치) - 문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출 시작위치가 음수일 경우 마지막 위치 부터 올라간 위치에서 끝까지 추출함)


select job, substr(job,1,2),substr(job,3,2),substr(job,5) from emp;

select substr(ename,3) from emp;

select job, substr(job, -length(job)),substr(job, -length(job),2), substr(job, -3) from emp;


-- INSTR(문자열, 검색할 문자, 시작지점, n번째 검색단어) 함수는 찾는 문자의 위치를 반환하는것입니다.

-- 찾는 문자가 없으면 0을 반환합니다.

-- 찾는 단어의 앞글자의 인덱스를 반환합니다.

-- 기본으로 왼쪽부터 시작하여 우측방향으로 스캔합니다.

-- 시작지점에 음수를 쓸경우 우측에서 시작하기때문에 스캔방향이 좌측방향입니다.


select instr('hello, oracle!','l') as instr_1, instr('hello, oracle!', 'l', 5) as instr_2, instr('hello, oracle!', 2, 2) as instr_3 from dual;

select * from emp where instr(ename, 'S') > 0;

select * from emp where ename like '%S%';

-- REPLACE 사용법(문자열, 바꿀문자열, 바뀔문자열) REPLACE(STR, TARGET_STR, REPLACE_STR)

select '010-1234-5678' as REPLACE_BEFORE, 
replace('010-1234-5678','-',' ') as replace_1,replace('010-1234-5678','-') as replace_2 from dual; 



--LPAD 함수는 지정한 길이 만큼 왼쪽부터 특정문자로 채워준다.

-- 함수  :  LPAD("값", "총 문자길이", "채움문자")


--RPAD 함수는 지정한 길이 만큼 오른쪽부터 특정문자로 채워준다.

-- 함수  :  RPAD("값", "총 문자길이", "채움문자")


select 'oracle',lpad('oracle',10,'#') as lpad_1,rpad('oracle',10,'*') as rpad_1,lpad('oracle',10) as lpad_2,rpad('oracle',10) as rpad_2 from dual;

select rpad('971225-', 14, '*') as rpad_jmno, rpad('010-1234-', 13,'*') as rpad_phone from dual;


--concat 사용법 : 문자열1 || 문자열2 || 문자열3.........

select concat(empno, ename) , concat(empno, concat(' : ', ename)) from emp where ename = 'SMITH';


--TRIM 함수는 문자열의 양쪽 공백(스페이스바)을 제거한다.

-- 함수  :  TRIM("문자열")

--LTRIM 함수는 문자열의 왼쪽(좌측) 공백  제거, 문자 왼쪽 반복적인 문자를 제거를 한다. 

-- 함수  :  LTRIM("문자열", "옵션") 

--RTRIM 함수는 문자열의  오른쪽(우측) 공백  제거, 문자 왼쪽 반복적인 문자를 제거를 한다. 

-- 함수  :  RTRIM("문자열", "옵션") 


select '[' || trim(' _ _oracle_ _ ') || ']' as trim, '[' || trim(leading from ' _ _oracle_ _ ') || ']' 
as trim_leading, '[' || trim(trailing from ' _ _oracle_ _ ') || ']' as trim_trailing, 
'[' || trim(both from ' _ _oracle_ _ ') || ']' as trim_both from dual;


select '[' || trim('_' from' _ _oracle_ _ ') || ']' as trim, '[' || trim(leading '_' from ' _ _oracle_ _ ') || ']' 
as trim_leading, '[' || trim(trailing '_' from ' _ _oracle_ _ ') || ']' as trim_trailing, 
'[' || trim(both '_' from ' _ _oracle_ _ ') || ']' as trim_both from dual;


select '[' || trim(' _oracle_ ') || ']' as trim,'[' || ltrim(' _oracle_ ') || ']' as ltrim,'[' || ltrim('< _oracle_> ', '_<') || ']' as ltrim_2,
'[' || rtrim(' _oracle_ ') || ']' as rtrim,'[' || rtrim(' <_oracle_>', '>_') || ']' as rtrim_2 from dual;


-- round : 반올림  trunc : 버림한 값을 반환  ceil : 큰 정수중 가장 작은 정수를 반환 floor : 지정된 숫자보다 작ㅇ느 정수 중 가장 큰 정수를 반환 mod : 나머지 값 반환
-- 함수 : ROUND("값", "자리수")

select round(1234.5678) as round, 
round(1234.5678, 0) as round_0,
round(1234.5678, 1) as round_1,
round(1234.5678, 2) as round_2,
round(1234.5678, -1) as round_minus1,
round(1234.5678, -2) as round_minus2 from dual;


-- 함수  :  TRUNC("값", "옵션")  소수점 절삭 날짜의 시간을 없앨때 사용
select trunc(1234.5678) as trunc, 
trunc(1234.5678, 0) as trunc_0,trunc(1234.5678, 1) as trunc_1,trunc(1234.5678, 2) as trunc_2,
trunc(1234.5678, -1) as trunc_minus1,trunc(1234.5678, -2) as trunc_minus2 from dual;


select trunc(1539.125023, 4) as truncex1,trunc(4586.89453,2) as truncex2,trunc(2560.48522,-1) as truncex3 
from dual;

-- CEIL 함수는 올림값을 반환하는 함수입니다.
-- FLOOR 함수는 내림값을 반환하는 함수입니다.

select ceil(3.14),floor(3.14),ceil(-3.14),floor(-3.14) from dual;


--MOD(n, m) 함수는  n/m 의 나머지를 반환하는 함수입니다. REMAINDER 라는 나머지 구하는 함수도 있다.
--REMAINDER(n2,n1)  MOD(n2,n1)

SELECT MOD(10,3), REMAINDER(10,3) FROM DUAL;

--동작방식
--MOD : n2 - n1 * FLOOR (n2 / n1)
--REMAINDER : n2 - n1 * ROUND (n2 / n1)

select mod(15, 6), mod(10,2), mod(11,2) from dual;


select sysdate as now, sysdate-1 as yesterday, sysdate+1 as tomorrow from dual;

select sysdate, add_months(sysdate, 3) from dual;

-- 입사 10주년이 되는 사원 데이터 출력 
select empno, ename, hiredate , add_months(hiredate, 120) as work10year from emp;

-- 입사 32년 미만인 사원 데이터 출력
select empno, ename, hiredate, sysdate from emp where add_months(hiredate, 510) > sysdate;

select * from emp;

select sysdate, add_months(sysdate,6) from dual;

--MONTHS_BETWEEN ( date1, date2 )   두 날짜 간의 개월 수 차이를 구하는 함수 

select empno, ename, hiredate, sysdate, months_between(hiredate, sysdate) as months1,months_between(sysdate, hiredate) as months2,
trunc(months_between(sysdate,hiredate)) as months3 from emp;


-- next_day( 기준날짜, 찾을 요일 ) : 기준 날짜에서 가장 가까운 요일날짜
-- last day( 기준 날짜 ) : 이 달의 마지막 날짜

select sysdate, next_day(sysdate, '월요일'), last_day(sysdate) from dual;

select sysdate, round(sysdate, 'cc') as format_cc, round(sysdate, 'yyyy') as format_yyyy,round(sysdate, 'q') as format_q,
round(sysdate, 'ddd') as format_ddd,round(sysdate, 'hh') as format_hh from dual;


select sysdate, trunc(sysdate, 'cc') as format_cc, trunc(sysdate, 'yyyy') as format_yyyy,trunc(sysdate, 'q') as format_q,
trunc(sysdate, 'ddd') as format_ddd,trunc(sysdate, 'hh') as format_hh from dual;

select empno, ename, empno + '500' from emp where ename = 'SMITH';

--select 'ABCD' + empno, empno from emp  where ename = 'SMITH'; ORA-01722: 수치가 부적합합니다

--TO_CHAR(날자또는숫자[,형식]);  날짜, 숫자 데이터를 문자 데이터로 변환함 

select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') as 현재날짜시간 from dual;

select sysdate, to_char(sysdate, 'mm') as mm, to_char(sysdate, 'mon') as mon,
to_char(sysdate, 'month') as month,to_char(sysdate, 'dd') as dd,to_char(sysdate, 'dy') as dy,to_char(sysdate, 'day') as day from dual;

select sysdate, to_char(sysdate, 'mm') as mm, to_char(sysdate, 'mon', 'nls_date_language = korean') as mon_kor,
to_char(sysdate, 'mon', 'nls_date_language = japanese') as mon_jpn,
to_char(sysdate, 'mon', 'nls_date_language = english') as mon_eng,
to_char(sysdate, 'month', 'nls_date_language = korean') as month_kor,
to_char(sysdate, 'month', 'nls_date_language = japanese') as month_jpn,
to_char(sysdate, 'month', 'nls_date_language = english') as month_eng from dual;

select sysdate, to_char(sysdate, 'mm') as mm, 
to_char(sysdate, 'dd') as dd,
to_char(sysdate, 'dy', 'nls_date_language = korean') as dy_kor,
to_char(sysdate, 'dy', 'nls_date_language = japanese') as dy_jpn,
to_char(sysdate, 'dy', 'nls_date_language = english') as dy_eng,
to_char(sysdate, 'day', 'nls_date_language = korean') as day_kor,
to_char(sysdate, 'day', 'nls_date_language = japanese') as day_jpn,
to_char(sysdate, 'day', 'nls_date_language = english') as day_eng from dual;

select sysdate, to_char(sysdate, 'hh24:mi:ss') as hh24miss,
to_char(sysdate, 'hh12:mi:ss am') as hhmiss_am,
to_char(sysdate, 'hh:mi:ss p.m.') as hhmiss_pm from dual;


select sal,
to_char(sal, '$999,999') as sal_$,
to_char(sal, 'L999,999') as sal_L,
to_char(sal, '999,999.00') as sal_1,
to_char(sal, '000,999,999.00') as sal_2,
to_char(sal, '000999999.99') as sal_3,
to_char(sal, '999,999,00') as sal_4 from emp;


--SELECT TO_NUMBER([컬럼명]) FROM [테이블명]
select 1300 - '1500', '1300' + 1500 from dual;
--select '1,300' - '1,500' from dual;
select to_number('1,300', '999,999') - to_number('1,500', '999,999') from dual;


--SELECT TO_DATE([컬럼명],[포맷형식])FROM [테이블명]
select to_date('2021-01-12', 'yyyy-mm-dd') as todate1, to_date('20210112','yyyy-mm-dd') as todate2 from dual;

select * from emp where hiredate > to_date('1981/06/01', 'yyyy-mm-dd');


select to_date('49/12/10' , 'yy/mm/dd') as yy_year_49,to_date('49/12/10' , 'rr/mm/dd') as rr_year_49,
to_date('50/12/10' , 'yy/mm/dd') as yy_year_50,to_date('50/12/10' , 'rr/mm/dd') as rr_year_50,
to_date('51/12/10' , 'yy/mm/dd') as yy_year_51,to_date('51/12/10' , 'rr/mm/dd') as yy_year_51 from dual;

-- NVL 함수 [ NVL( 대상 , null인 경우 값 ) ] 

select empno, ename, sal, comm, sal+comm, nvl(comm, 0), sal+nvl(comm, 0) from emp;


--NVL2 함수 [ NVL2( 대상, null 아닌경우 값, null인 경우 값 ) ]

select empno, ename, comm, nvl2(comm, 'o' , 'x'), nvl2(comm, sal*12+comm, sal*12) as annsal from emp;

--DECODE(컬럼,조건,TRUE 결과값,FALSE 결과값)


select empno, ename, job, sal, 

decode(job, 'MANAGER' , sal*1.1, 'SALESMAN', sal*1.05,'ANALYST', sal, sal*1.03) as upsal from emp;


--CASE 대상값 when 비교값1 then 처리1
--
--                  when 비교값2 then 처리2
--
--               ………
--
--               else 디폴트처리
--
--END;

--CASE 대상값 when 비교조건1 then 처리1
--
--               when 비교조건2 then 처리2
--
--               ………
--
--              else 디폴트처리
--
--END;

select empno, ename, job, sal, 
case job
         when 'MANAGER' then sal*1.1
         when 'SALESMAN' then sal*1.05
         when 'ANALYST' then sal
         else sal*1.03
end as upsal
from emp;


select empno, ename, comm, 
case 
       when comm is null then '해당사항 없음'
       when comm = 0 then '수당없음'
       when comm > 0 then '수당 : ' || comm 
       end as comm_text
from emp;

select sum(sal) from emp;

--select ename, sum(sal) from emp;

select sum(comm) from emp;

select sum(distinct sal), sum(all sal), sum(sal) from emp;

select count(*) from emp;

select count(*) from emp where deptno = 30;

select count(distinct sal), count(all sal), count(sal) from emp;

select count(comm) from emp;

select count(comm) from emp where comm is not null;

select max(sal) from emp where deptno = 10;

select min(sal) from emp where deptno = 10;


-- 입사일 기준으로 가장 최근에 입사한 사원을 출력
select max(hiredate) from emp where deptno = 20;

-- 입사일 기준으로 가장 오래된 사원을 출력
select min(hiredate) from emp where deptno = 20;

-- 부서번호가 30인 사원들의 평균 급여를 출력 
select avg(sal) from emp where deptno = 30;

select avg(distinct sal) from emp where deptno = 30;

select avg(comm) from emp where deptno = 30;


--GROUP BY
--
--- 데이터들을 원하는 그룹으로 나누는 역할을 합니다.
--
--- 어떤 기준으로 그룹할 경우 다른 값들을 가져올때 역시 합쳐서 가져와야한다.
--
--  (앞서 알아보았던 집계함수 SUM, MIN, MAX, AVG, COUNT 등이 함께 사용된다. 
--
--- 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절에 둘다 명시해 주어야 한다.
--
--  (여기서 집계함수와 사용되는 컬럼은 GROUP BY절에 명시해줄 필요는 없다.)
--
--- GROUP BY 절의 위치는 WHERE 와 ORDER BY절 사이에 위치한다.


--SELECT [GROUP BY 절에 지정된 컬럼1] [GROUP BY별로 집계할 값] 
--
--FROM [테이블 명] 
--
--GROUP BY [ 그룹으로 묶을 컬럼 값 ]

--SELECT [컬럼1],[컬럼2],[컬럼3]... FROM [테이블 이름] GROUP BY [그룹핑 할 컬럼]


--SELECT depart_no , SUM(salary)
--
--FROM emp GROUP BY depart_no ORDER BY depart_no;


--SELECT [GROUP BY 절에 지정된 컬럼1] [GROUP BY별로 집계할 값] 
--
--FROM [테이블 명] 
--
--GROUP BY [ 그룹으로 묶을 컬럼 값 ]
--
--HAVING [조건 추가] ;

--GROUP BY [그룹핑할 컬럼] HAVING [조건]


--SELECT depart_no, SUM(salary), SUM(commission), job
--
--FROM emp WHERE job != 'sales'
--
--GROUP BY depart_no HAVING SUM(commission) >= 2000 ORDER BY sum(commission) DESC;


select avg(sal), '10'  as deptno from emp where deptno = 10 union all
select avg(sal), '20'  as deptno from emp where deptno = 20 union all
select avg(sal), '30'  as deptno from emp where deptno = 30;


select avg(sal), deptno from emp group by deptno;

select deptno, job, avg(sal) from emp group by deptno, job order by deptno, job;

--select  ename, deptno, avg(sal) from emp group by deptno;

select * from emp;


select deptno, job, avg(sal) from emp 
group by deptno, job 
having avg(sal) >= 2000
order by deptno, job;


--ORA-00934: 그룹 함수는 허가되지 않습니다
--select deptno, job, avg(sal) from emp
--where avg(sal) >= 2000
--group by deptno, job
--order by deptno, job;


select deptno, job, avg(sal) from emp 
group by deptno, job
having avg(sal) >= 2000
order by deptno, job;


select deptno, job, avg(sal) from emp
where sal <= 3000
group by deptno, job
having avg(sal) >= 2000
order by deptno, job;

--   HAVING
--
--- GROUP BY 사용시 조건 값
--
--- WHERE 절에서는 집계함수를 사용할 수 없다.
--
--- HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.


-- 집계함수 
-- SELECT (COUNT/MAX/MIN/AVG/SUM/STDDEV)([컬럼명]) FROM [테이블명];
--
--(필요한 집계함수를 쓰고 괄호안에 컬럼명을 집어넣어주는 방식의 사용법입니다.)
--
--
--
--1) COUNT
--
-- : COUNT 함수는 결과 개수 즉 SELECT 문으로 검색되는 데이터 수를 반환합니다.
--
--
--
-- SELECT COUNT(emp_no) FROM emp;
--
-- SELECT COUNT(DISTINCT emp_no) FROM emp;
--
-- SELECT COUNT(*) FROM emp;
--
--
--
--COUNT의 기본 사용법은 컬럼명을 지정해주는 방식입니다
--
--사용처에 따라서 DISTINCT 나 * 써주시면 됩니다.
--
--(단. 컬럼명을 지정해주지 않고 * 이것을 썼을경우는 null값 역시 개수에 포함되어 반환합니다.)
--
--
--2) MAX , MIN
--
-- : MAX와 MIN 함수는 각각 선택된 컬럼값 중에서 최대값과 최소값을 반환합니다.
--
-- SELECT MAX(emp_no) , MIN(emp_no) FROM emp;
--
--MAX와 MIN 은 위에서 언급한거와같이 숫자에서만 사용이 가능한게아니라 모든 자료형 타입에서 사용이 가능합니다.
--

--3) SUM
--
-- : SUM 함수는 선택된 컬럼의 합을 반한합니다.
--
-- SELECT SUM(salary) FROM emp;
--
-- SELECT SUM(DISTINCT salary) FROM emp;
--
--보통 컬럼명을 넣고 사용하는데 SUM 역시 DINSTINCT가 사용이 가능합니다.
--

--4) AVG
--
-- : AVG 함수는 선택된 컬럼의 평균 값을 반환 합니다.
--
-- SELECT AVG(salary) FROM emp;
--
-- SELECT ROUND(AVG(salary),1) FROM emp;
--

--평균값을 구하므로 소숫점이 굉장히 길어질수있기때문에 보통 ROUND 함수와 병행하여 사용하는 경우가 많습니다.
--

--5) STDDEV
--
-- : STDDEV 함수는 선택된 컬럼의 표준 편차 값을 반환 합니다.

-- SELECT STDDEV(salary) FROM emp;
--
--보통 통계를 낼때 사용하는 표준편차입니다.


--  ROLLUP 연산자
--  GROUP BY절에 있는 컬럼들을 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고,
--  각 그룹에 계산함수를 적용한다.
--  GROUP BY절의 결과는 누적 계산 결과이다.
-- 
--
-- CUBE 연산자
-- - GROUP BY절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.
-- 
--
--  GROUPING 함수
--  각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다.
--  해당컬럼에 대해 계산되었다면 0, 그렇지 않다면(컬럼값이 NULL) 1을 반환한다.
--  GROUP BY절에 나타나는 컬럼에 적용된다.


select deptno, job, count(*), max(sal), sum(sal), avg(sal) from emp
group by deptno, job order by deptno, job;


select deptno, job, count(*), max(sal), sum(sal), avg(sal) from emp
group by rollup(deptno, job);

select deptno, job, count(*), max(sal), sum(sal), avg(sal) from emp
group by cube(deptno, job) order by deptno, job;


select deptno, job, count(*) from emp group by deptno, rollup(job);

select deptno, job, count(*) from emp group by job, rollup(deptno);

--GROUPING SETS( 컬럼, 컬럼, 컬럼, ... )

--GROUPING SETS( (컬럼그룹), (컬럼그룹), (컬럼그룹), ... )

select deptno, job, count(*) from emp group by grouping sets(deptno, job) 
order by deptno, job;

select deptno, job, count(*), max(sal), sum(sal), avg(sal),
grouping(deptno), grouping(job) from emp
group by cube(deptno, job) order by deptno, job;


select deptno, job, count(*), max(sal), sum(sal), avg(sal), 
grouping(deptno), grouping(job) from emp
group by cube(deptno, job) order by deptno, job;

select decode(grouping(deptno), 1, 'ALL_DEPT', deptno)  as deptno,
decode(grouping(job), 1, 'ALL_JOB' , job) as job,
count(*), max(sal), sum(sal), avg(sal) from emp
group by cube(deptno, job) order by deptno, job;

 

select deptno, job, count(*), sum(sal), grouping(deptno), grouping(job), grouping_id(deptno, job) from emp
group by cube(deptno, job) order by deptno, job;


--LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명]) 

select ename from emp where deptno = 10;

select deptno, ename from emp group by deptno, ename;

select deptno, listagg(ename, ', ') within group(order by sal desc) as enames from emp group by deptno;

--pivot 함수는 row단위를column단위로 변경

select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;

select * from(select deptno, job, sal from emp) pivot(max(sal) for deptno in (10,20,30)) order by job;

select * from(select job, deptno, sal from emp) pivot(max(sal) 
for job in ('CLERK' as CLERK, 'SALESMAN' as SALESMAN, 'PRESIDENT' as PRESIDENT, 'MANAGER' as MANAGER, 'ANALYST' as ANALYST))
order by deptno;


select deptno, 
max(decode(job, 'CLERK' , sal)) as "CLERK",
max(decode(job, 'SALESMAN' , sal)) as "SALESMAN",
max(decode(job, 'PRESIDENT' , sal)) as "PRESIDENT",
max(decode(job, 'MANAGER' , sal)) as "MANAGER",
max(decode(job, 'ANALYST' , sal)) as "ANALYST"  from emp
group by deptno order by deptno;


select * from (select deptno, 
                        max(decode(job, 'CLERK' , sal)) as "CLERK",
                        max(decode(job, 'SALESMAN' , sal)) as "SALESMAN",
                        max(decode(job, 'PRESIDENT' , sal)) as "PRESIDENT",
                        max(decode(job, 'MANAGER' , sal)) as "MANAGER",
                        max(decode(job, 'ANALYST' , sal)) as "ANALYST" from emp
                        group by deptno order by deptno)
                        
            unpivot(sal for job in (clerk, salesman, president, manager, analyst)) 
order by deptno, job;