Story of Hyun

-- SEQUENCE : 유일한 값을 생성해주는 오라클 객체 / 번호를 자동으로 생성하는 객체

create sequence memberSqu

start with 10   -- 시작값

increment by 10;    -- 증가값

create sequence membersqc start with 1 increment by 1;

create sequence hyun start with 100 increment by 100;

select * from membertbl;

insert into membertbl(num, name, addr, phone)

values(membersqu.nextVal, 'ccc', 'cccccc', '010-444-4444');

select membersqu.currval from dual;  -- 시퀀스의 현재값

select membersqu.nextval from dual;  -- 시퀀스의 다음값

-- 시퀀스 목록 확인

select * from user_sequences;

--시퀀스 수정

alter sequence hyun increment by 200;

--시퀀스 삭제

drop sequence hyun;

-- 1부터 3씩 증가하는 시퀀스 생성, 객체명 : testSqu

create sequence testSqu start with 1 increment by 3;

-- testSqu의 증가값을 10단위로 수정

alter sequence testSqu increment by 10;

-- 시퀀스 목록 확인

select * from user_sequences;

-- testSqu 시퀀스 객체 삭제

drop sequence testSqu;

-- GROUP BY절과 HAVING절

-- GROUP BY : 테이블의 행들을 원하는 그룹으로 나누기

-- 부서별 그룹의 합계

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

-- 담당업무별 급여의 합계와 평균 구하기

select job 담당업무, sum(sal) 급여합, ceil(avg(sal)) 급여평균 from emp group by job order by job;

-- 부서별 급여의 합계와 평균, 보너스의 합계와 평균을 구하되 81년도에 입사한 사원만으로 통계를 구하라

select deptno 부서, sum(sal) 급여합계, ceil(avg(sal)) 급여평균, sum(nvl(comm, 0)) 보너스합계, ceil(avg(nvl(comm, 0))) 보너스평균 from emp where hiredate like '81%' group by deptno;

-- ROLLUP : GROUP BY절에 의해서 그룹 지어진 집합결과에 대해서 좀 더 상세한 정보를 반환

-- 담당업무별 급여의 합계를 구하라. 담당업무를 오름차순으로 정렬하라

select job, sum(sal) from emp group by rollup(job) order by job;

-- 1차 분류 부서코드(deptno), 2차 분류 담당업무(job)로 급여의 합계와 평균을 출력

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

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

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

select deptno, job from emp order by deptno asc, job asc;

-- JOIN

-- 참고용 : 추후 진행

-- 사원명, 부서코드, 부서명, 부서위치 선택

select ename, emp.deptno, dname, loc from emp, dept where emp.deptno=dept.deptno;

select emp.ename, emp.deptno, dept.dname, dept.loc from emp, dept where emp.deptno=dept.deptno;

select e.ename, e.deptno, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno;

select ename, e.deptno, dname, loc from emp e, dept d where e.deptno=d.deptno;

-- GROUP BY HAVING : 그룹함수의 통계 결과를 이용하여 조건설정

-- 담당업무별 급여의 합계를 구하여, 합계가 5,000불 이상인 담당업무만 출력하라

select job 담당업무, sum(sal) 급여합계 from emp group by job having sum(sal)>5000;

-- 부서코드별 사원수를 구하여 사원수가 4명 이상인 부서만 출력하라.

select * from emp;

select deptno 부서코드, count(*) 사원수 from emp group by deptno having count(*)>=4;

-- 입사년도별 급여의 합계와 사원수를 구하고, 사원수가 5명 미만인 입사년도를 출력하되 담당업무가 PRESIDENT는 제외하라

select to_char(hiredate, 'yyyy') 입사년도, sum(sal) 급여합계, count(*) 사원수 from emp where job!='PRESIDENT' group by to_char(hiredate, 'yyyy') having count(*)<5;

-- 그룹 함수 : 통계 함수 / 여러 행 또는 테이블 전체의 행에 대해 함수가 적용

-- COUNT : 갯수 구하기

select count(empno) from empcopy;

select count(comm) from empcopy;

select count(ename) from empcopy;

select count(job) from empcopy;

select count(*) from empcopy;

select ename, count(hiredate) from emp; -- error :그룹함수는 그룹함수끼리만 사용 가능

-- 81년도에 입사한 사원수

select count(*) from emp where to_char(hiredate, 'yy')='81';

select count(*) from emp where hiredate like '81%';

-- MANAGER는 몇명인가?

select count(*) from emp where hiredate like '81%' and job='MANAGER';

-- MAX : 최대값

select max(sal) from emp;

select max(comm) from emp;

-- MIN : 최소값

select min(sal) from emp;

select min(comm) from emp;

-- 부서코드 20인 사원은 최대 급여와 최소 급여는 얼마인가?

select max(sal), min(sal) from emp where deptno=20;

-- SUM : 합계

-- 급여 총액

select sum(sal) from emp;

-- 보너스 총액

select sum(comm) from emp;

-- AVG : 평균

select avg(sal) from emp;

select round(avg(sal), 2) from emp;

-- 보너스 평균

select avg(comm) from emp;

-- null을 포함한 보너스 평균

select avg(nvl(comm, 0)) from emp;

select round(avg(nvl(comm, 0)), 2) from emp;

-- 부서코드가 20인 사원의 급여의 합과 평균을 구하라

select sum(sal), round(avg(sal), 2) from emp where deptno=20;

-- 담당업무가 SALESMAN인 사원중 부서코드가 30인 사원의 보너스의 합과 평균을 구하라

select sum(comm), round(avg(nvl(comm, 0)), 2) from emp where job='SALESMAN' and deptno=30;

-- STDDEV : 표준 편차

-- 급여에 대한 표준편차

select round(stddev(sal), 2) from emp;