Database-function(2)

-- Oracle :문자,숫자,날짜
-- to_char() : 숫자 -> 문자(%100,000), 날짜 -> 문자(2018년 12월 12일) (형식 정의를 위해서)  60%
-- to_date() : 문자 -> 날짜 
-- to_number() : 문자 -> 숫자 (자동 형변환이 있어서 잘 안사용한다.)

/*
-- 오라클 기본 타입(데이터 타입)
create table 테이블명(컬럼명 타입) ;

문자 타입
--char(20) >> 20byte >> 고정길이 문자열 >> '홍길동' >> 6byte임에도 20byte를 사용한다.
--varchar2(20) >> 20byte >> 가변길이 문자열 >> '홍길동' >> 6byte만 사용한다.

고정된 데이터 : 남 , 여 >> 처리하려면 char 써라 (2byte)
성능상의 문제가 존재한다. char가 varchar2보다 검색을 우선시 한다. 

unicode (2byte) : 한글 , 영문자, 특수문자 , 공백 >> 2byte
nchar(20) >> 문자의 갯수 >> 실제 byte는 *2하면 된다. >> 40 byte
nvarchar2(20) 

*/

--- 1. to_number()
select 1+1 from dual;
select '1'+1 from dual;

select to_number('1') + 1 from dual;
select '1' + '1' from dual;

-- 2. to_char()  : 표에있는 형식화를 가지고 
/*
9 9 의 수는 출력 폭을 결정 999999 1234 
0 무효의 0 을 출력 099999 001234 
$ 달러 기호 $999999 $1234 
L 지역 화패 기호 L999999 \1234 
. 명시한 위치에 소수점 999999.99 1234.00 
, 명시한 위치에 콤마 999,999 1,234 
MI 우측에 마이너스 기호(음수 값) 999999MI 1234- PR 음수를 “()”로 묶는다 999999PR <1234> 
EEEE 과학적인 부호 표기 99.999EEEE 1.234E+03 
V 10 을 n 번 곱합니다. 9999V99 123400 
B 0 을 0 이 아닌 공백으로 출력 B9999.99 1234.00 
*/
select sysdate from dual;

select sysdate || '일' from dual; --날짜 , 문자 결합하면 >> 문자

--정식표현
select to_char(sysdate) || '일' from dual;

select sysdate , to_char(sysdate,'YYYY') || '년' as "YYYY"
,to_char(sysdate,'YEAR')
, to_char(sysdate,'YYYY') || '년' as "YYYY"
, to_char(sysdate,'MM')
, to_char(sysdate,'DD')
, to_char(sysdate,'DAY')
, to_char(sysdate,'DY')
from dual;

--Quiz
--입사일이 12월인 사원들의 사번, 이름, 입사일, 입사년도, 입사월을 출력하세요
select EMPNO,ENAME,HIREDATE,to_char(hiredate,'MM')
from emp
where  to_char(HIREDATE,'MM') = 12;

select to_char(hiredate,'YYYY MM DD') as "일자" from emp;

select to_char(hiredate,'YYYY"년"MM"월"DD"일"') from emp;

select '>' || to_char(12345,'9999999999999') || '<' from dual;
select '>' || to_char(12345,'9999999999999') || '<' from dual;
select '>' || to_char(12345,'0000000000000') || '<' from dual;

select '>' || to_char(12345,'$9,999,999,999,999') || '<' from dual; --문자가 출력되는 것이다.

select to_char(sal,'$999,999') as "sal" from emp;

-- to_date() 문자를 날짜로
select to_date('2018-12-12','YYYY-MM-DD') from dual; --2018-12-12 00:00:00

select '2018-12-12' + 100 from dual;  --error
select to_date('2018-12-12') + 100 from dual;

-- nvl(),nvl2() --> null 처리 함수 , decode() 함수 --> java if.. , case() 함수 ->> java switch..
select comm, nvl(comm,0) from emp;

create table t_emp(
 id number(6),
 job varchar2(20)
);
insert into t_emp(id,job) values(100,'IT');
insert into t_emp(id,job) values(200,'SALES');
insert into t_emp(id,job) values(300,'MGR');
insert into t_emp(id) values(400);
insert into t_emp(id,job) values(500,'MGR');
commit;

select * from t_emp;

--1.nvl()
select id, job, nvl(job,'Empty...') from t_emp;
--2.nvl2() , null 처리 -> null인 경우와 null이 아닌 경우 둘다 정의 해야 나온다. nvl2(collum,null인 경우, null이 아닌 경우)
select id, job, nvl2(job,'AAA','BBB') from t_emp; 
select id, job, nvl2(job,job || '입니다','empty') from t_emp;
--3.decode POINT (일반 sql은 제어문이 없다 if, switch)
--decode(표현식,조건1,결과1,조건2,결과2,조건3,결과3...........,나머지 결과값);
--통계 데이터 추출
select id, job, decode(id , 100 , 'IT', 200 , 'SALES', 300 , 'MGR' , 'ETC') as "decodejob" from t_emp;

select job , decode(job,'IT',1) from t_emp;

--활용(통계) : count 는 null을 못 센다. 그러므로 포함된 것의 갯수를 셀 수 있다.
select count(decode(job,'IT',1)) as "IT" , count(decode(job,'SALES',1)) as "SALES" , count(decode(job,'MGR',1)) as "MGR"
from t_emp;

/*
emp 테이블에서 부서번호가 10이면 '인사부' , 20이면 '관리부' , 30이면 '회계부' , 나머지는 '기타부서'
*/
select * from emp;
select ename, deptno, decode (deptno , 10 , '인사부' , 20 , '관리부', 30 , '회계부' , '기타부서')
from emp
order by deptno asc;

create table t_emp2(
id number(2),
jumin char(7)
);
insert into t_emp2(id,jumin) values(1, '1234567');
insert into t_emp2(id,jumin) values(2, '2234567');
insert into t_emp2(id,jumin) values(3, '3234567');
insert into t_emp2(id,jumin) values(4, '4234567');
insert into t_emp2(id,jumin) values(5, '9234567');
commit;
/*
t_emp2 table에서 id,jumin 데이터를 출력하되 jumin컬럼의 앞자리가 1이면 남성
2이면 여성 , 3이면 중성 그외는 기타로 출력하세요
컬럼명은 gender로 하세요
*/
select id ,decode(substr(jumin,1,1), 1 ,'남성' , 2 ,'여성', 3,'중성','기타') as "gender"
from t_emp2;

/*
부서번호가 20번이 사원중에서 SMITH라는 이름을 가진 사원이라면 HELLO문자 출력하고
부서번호가 20번인 사원중에서 SMITH라는 이름을 가진 사원이 아니라면 WORLD문자 출력하고
부서번호가 20번인 사원이 아니라면 ETC라는 문자를 출력하세요
*/
select deptno,ename, decode(deptno,20,decode(ename,'SMITH','HELLO','WORLD'),'ETC') as "decode" from emp; 

--4. CASE문
/*
CASE 조건 WHEN 결과1 THEN 출력1
                 WHEN 결과2 THEN 출력2
                 WHEN 결과3 THEN 출력3
                 WHEN 결과4 THEN 출력4
                 ELSE 출력5
END "컬럼명
*/
create table t_zip(
  zipcode number(10)
);

insert into t_zip(zipcode) values(2);
insert into t_zip(zipcode) values(31);
insert into t_zip(zipcode) values(32);
insert into t_zip(zipcode) values(33);
insert into t_zip(zipcode) values(41);
commit;

select * from t_zip;

select '0' || to_char(zipcode) ,
          case zipcode when 2 then '서울'
                              when 31 then '경기'
                              when 32 then '강원'
                              when 41 then '제주'
                              else '기타지역'
          end "regionname"
from t_zip; --자동 형변환되므로 문제 없다.

/*
사원테이블에서 사원급여가 1000달러 이하면 4급
1001 달러 2000달러 이하면 3급
2001 달러 3000달러 이하면 2급
3001 달러 4000달러 이하면 1급
4001 달러 이상이면 특급을 부여하는 데이터를 출력하세요

1. case 컬럼명 when 결과 then 출력 ( = )
2. case when 컬럼명 조건비교구문 then    (ex: sal < 2000 )**************************

*/
select case when sal <= 1000 then '4급'
                   when sal between 1001 and 2000 then '3급'
                   when sal between 2001 and 3000 then '2급'
                   when sal between 3001 and 4000 then '1급'
                   else '특급'
          end "급여 등급" , empno , ename , sal
from emp
order by "급여 등급" asc;

-- 집계함수는 무조건 group by 절과 같이 사용해야 한다. 
-- 모든 집계함수는 null값을 무시한다. select절에 집계함수 외에 다른 컬럼이 오면 반드시 group by절에 명시되어야 한다.
-- 무조건 컬럼1개로 결과 값 나온다. 여러개를 한 select로 사용가능 하다.
-- 1. count(*) , count(컬럼명) >> 데이터 건수를 센다.
select count(*) from emp; --줄 수를 세는 것이다.

select count(empno) from emp; --데이터의 건수를 세는 것이다. (단 null을 무시한다.)

select count(comm) from emp; --null을 무시하니까 6건만 나온다.
select comm from emp;

select count(nvl(comm,0)) from emp; -- null을 0으로 바꾸어서 센다.

-- 2. sum() >> 합계
--급여의 합을 구해라
select sum(sal) from emp;
--회사 수당이 총 얼마 지급되지?(평균)
select sum(comm) as "sum" from emp; --4330
--회사 규정에 따라 나뉜다.
select trunc(avg(comm)) as "avg" from emp; --721 null값을 무시해서 건수가 6이 된다.
--검증
select trunc(avg(nvl(comm,0))) from emp; --null을 0으로 바꾸어서 14명으로 바뀐다.

--null이 포함되어 있는지 확인하는 quary *********************************
select count(*) from emp where comm is null;

-- 3. avg()
--평균 급여를 구해라
select round(avg(sal)) from emp;
--부서별 평균 월급을 구해라
select deptno,avg(sal) from emp; -- deptno , avg의 값의 수가 달라서 error

select deptno, avg(sal) --deptno를 지우면 무슨 값의 평균인지 모른다.
from emp
group by deptno;            --값의 수가 같아서 실행 된다.
/*
grouping 원리
distinct
order by
group by
*/
--직종별 평균 급여, 급여 합, 최대 급여,건수를 출력하시오.
select job , avg(sal) as "평균급여" , sum(sal) as "급여 합", max(sal) as "최대급여", count(nvl(sal,0)) as "건수" 
from emp
group by job;

--부서별, 직종별 급여의 합을 구하세요.
select deptno , job , sum(sal) , count(sal)
from emp
group by deptno,job
order by deptno asc;
/*
select       5
from         1
where       2
group by   3
having       4
order by    6
*/

-- 4. max()
select max(sal) from emp;
-- 5. min()
select min(sal) from emp;
-- 6. ETC....... >> group by having 절
/*
직종별 평균 급여가 3000$ 이상인 사원의 직종과 평균 급여를 출력하세요
--where에 평균 급여 >=3000 사용하려면 group by가 실행 되야 하는데 where에서 사용을 못한다.
--group by에 조건을 거는 것이 답이다. 
--group by 조건 거는 것은 : having 절
*/
select job,avg(sal)
from emp
group by job
having avg(sal) >= 3000;

/*
사원 테이블에서 직종별 급여의 합을 출력하되 수당은 지급받고 급여의 합이 5000$ 이상인 사원들의 목록을 출력하세요
급여의 합이 낮은 순으로 출력
*/
select * from emp;
select job , sum(sal)
from emp
where comm is not null
group by job
having sum(sal) >= 5000
order by sum(sal) asc;
/*
사원테이블에서 부서 인원이 4명 보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하세요
*/
select deptno, count(ENAME), sum(sal)
from emp
group by deptno
having count(deptno) > 4;

/*
사원테이블에서 직종별 급여의 합이 5000을 초과하는 직종과 급여의 합을 출력하세요
단 판매직종(salesman)은 제외하고 급여합으로 내림차순 정렬하세요
*/
select job, sum(sal)
from emp
where substr(job,1,1) != 'S'
group by job
having sum(sal) > 5000
order by sum(sal) desc;