SQL 숫자함수, 그룹함수 정리

--dual(더미 테이블)
--출력 결과가 1개일 경우 사용되는 테이블 

--1일이 몇분인가?
select 60 * 24
from dept;

select * from dept;

select 60 * 24
from dual;

-- 숫자함수

--절대값(absolute)
select -10, abs(-10)
from dual;

--버림(floor)
select 34.7894, floor(34.7894)
from dual;

--반올림(5이상이면 올림, 5미만 버림) - round
select 34.5678, round(34.5678)
from dual;

select 12.3456, round(12.3456)
from dual;

--자릿수 포함
select 34.5678, round(34.5678, 2) -- 소숫점 이하 둘째짜리 까지(셋째자리에서 반올림)
from dual;

--소숫점 이상
select 34.5678, round(34.5678, -1) -- 소숫점 이상 1번째 (10의 자리까지)
from dual;

--소숫점 이하 버림(자릿수)
--trunc (truncate: 잘라내다)
select trunc(34.5678, 2)
from dual;

-- 나머지(%) : modualr
-- mod(피젯수, 젯수)
select mod(27,2) -- 27을 2로 나눈 나머지
from dual;

--올림 (ceil: 천정)
select ceil(34.5678)
from dual;

-- 문자 함수

-- 대소문자,  첫글자 
select 'Welcome to Oracle',
        upper('Welcome to Oracle'),
        lower('Welcome to Oracle'),
        initcap('Welcome to Oracle') -- intiial capitalize
from dual;

-- 문자열 길이
-- length
-- lengthb

select length('Oracle'), length('오라클'),
       lengthb('Oracle'), lengthb('오라클')
from dual;

-- 부분 문자열 : 자바-substring(시작위치, 끝위치) -> 오라클-substr(문자열, 시작위치, 갯수)
select substr('Welcome to Oracle', 4, 3)
from dual;

--부분 문자열 - 시작위치를 뒤에서부터 (음수)
select substr('Welcome to Oracle', -4, 3)
from dual;

select substr('월컴투오라클', 4, 3)
from dual;  --오라클

--특정 문자의 위치 : indexOf() -> instr(문자열, 문자)
select instr('Welcome to Oracle', 'O')
from dual;

-- 채우기(padding,  left padding, right padding) -padding-left, padding-right
-- lpad,rpad
select lpad('Oracle', 20, '#'), rpad('Oracle', 20, '#')
from dual;

-- 주민번호: 011231-1112222
select rpad(substr('011231-1112222', 1, 8), 14, '*')
from dual;

--p151 연습문제
--01. substr함수 이용해 9월에 입사한 사원 출력
select *
from emp
where 09 = substr(hiredate, 4, 2);

--02. substr함수 이용해 2003년에 입사한 사원 출력
select *
from emp
where '2003' = substr(hiredate, 1, 4);

--03. substr함수 이용해 "기"로 끝나는 사원 출력
select *
from emp
where '기' = substr(ename, 3, 1);

--04. 이름의 두번째 글짜에 "동"이 있는 사원 검색(instr 사용)
select *
from emp
where instr(ename, '동', 2, 1) = 2;

select *
from emp
where ename like '_동%';

--형변환 함수 - parseInt(), parseFloat()
-- to_char(), to_date(), to_number()
select sysdate, to_char(sysdate, 'YYYY/MM/DD'), to_char(sysdate, 'YYYY-MM-DD'),
      to_char(sysdate, 'YYYY-MM-DD DAY'), to_char(sysdate, 'YYYY-MM-DD DY'),
      to_char(sysdate, 'YYYY-MM-DD AM HH:MI:SS'), -- 12시간제
      to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') --24시간제
from dual;

--숫자형 문자형으로 변환하기
select 1230000, to_char(1230000), to_char(1230000,'L999,999,999')
from dual;

-- 그룹함수
-- 합계 - sum
-- 급여의 총합
select sum(sal)
from emp;

-- 급여 평균 -소숫점 이하 둘째자리
select round(avg(sal), 2)
from emp;

-- 최대값 - max, 최소값 - min 
select max(sal), min(sal)
from emp;

-- 갯수(count)
-- 사원수
select count(*)
from emp;

--직급의 수 -distinct: 중복제거
select count(distinct job)
from emp;

--주의 
-- 급여를 가장 많이 받는 사원의 이름과 급여 검색
select ename, sal
from emp
where sal = (select max(sal) from emp); --서브쿼리

select ename, max(sal)
from emp; --안됨 :  ename 값은 14개 max(sal)값은 1개 : 갯수 매칭 안됨

--group by 절 -> 그룹함수와 함께 사용

--부서번호, 급여 출력 (부서번호 순으로 정렬)
select deptno, sal
from emp
order by deptno;

select deptno
from emp
group by deptno;

select avg(sal)
from emp
group by deptno;

-- ~별 .. 구하기
select deptno, avg(sal) 급여평균, sum(sal) 급여합계
from emp
group by deptno;

-- 각 부서별 최소급여와 최대급여
select deptno, min(sal), max(sal)
from emp
group by deptno
order by deptno;

--group by에 적용되는 조건 - having 
-- 부서별 급여 평균이 500이상인 부서번호와 급여 평균 구하기
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) >= 500;

--급여가 500인 사원의 부서와 급여 구하기
select deptno, sal
from emp
where sal >= 500;

-- 최대 급여가 500을 초과하는 부서에 대해서 최대 급여와 최소 급여 구하기
select deptno, max(sal), min(sal)
from emp
group by deptno
having max(sal) > 500;

--직급이 사원인 사람을 제외하고 급여 총액이 1000이상인 직급별 급여 총액 구하기
select job, sum(sal)
from emp
where job != '사원'
group by job
having sum(sal) >= 1000;

--연습문제
-- 01. 급여 최고액, 최저액, 총액 및 평균급여 구하기
select max(sal), min(sal), sum(sal), avg(sal)
from emp;

-- 02. 담당 업무(job) 유형별로 급여 최고액, 최저액, 총액, 평균급여 구하기
select job, max(sal), min(sal), sum(sal), avg(sal)
from emp
group by job;

-- 03. 담당 업무 별 사원수 출력
select job, count(*)
from emp
group by job;

--04 과장의 수 출력하기
select job, count(*)
from emp
group by job
having job = '과장';

-- 05 급여 최고액, 급여 최저액의 차액 구하기 (별칭 부여)
select max(sal) - min(sal) 차액
from emp;

-- 06 직급별(job) 사원의 최저급여 출력
select job, min(sal)
from emp
group by job;

--07. 부서별 사원수 ,  평균 급여 구하기 (소숫점 이하 둘째자리까지)
select deptno, count(*), round(avg(sal), 2)
from emp
group by deptno;


--decode -> switch(deptno) {case, case, default }
--decode (표현식, 경우1, 결과1,
          --      경우2, 결과2,
--                결과n)

-- 사원명(emp), 부서번호(emp), 부서명(dname)
select * from dept; --부서명
select * from emp; -- 부서번호

select ename, deptno, decode(deptno, 10, '경리부',
                                     20, '인사부',
                                     30, '영업부',
                                     40, '전산부') 부서명
from emp;
-- case ~ end : if ~ else if ~ else
-- case when 조건1 then 결과1
--      when 조건2 then 결과2
--      when 조건3 then 결과3
--      else 결과n
-- end

select ename, deptno, case when deptno = 10 then '경리부'
                           when deptno = 20 then '인사부'
                           when deptno = 30 then '영업부'
                           when deptno = 40 then '전산부'
                    end 부서명
from emp;

--08 부서명, 지역명, 사원수, 부서내의 모든 사원의 평균급여 출력 -decode 사용
select * from dept;
select * from emp;

select deptno, decode(deptno, 10, '경리부',
                              20, '인사부',
                              30, '영업부',
                              40, '전산부') 부서명, decode(deptno, 10, '서울',
                                                                   20, '인천',
                                                                   30, '용인',
                                                                   40, '수원') 지역명, 
                                                                   count(*) 사원수, round(avg(sal), 2) 평균급여
from emp
group by deptno;