Story of Hyun

-- 다중열 서브쿼리

-- 쌍비교

select * from emp where (sal, deptno) in (select sal, deptno from emp where deptno=30 and comm is not null);

select empno, ename, job, deptno from emp where(job, sal) in (select job, min(sal) from emp group by job) order by job;

-- 비쌍비교

select empno, sal, deptno from emp where sal in (select sal from emp where deptno=30 and comm is not null)

and

deptno in (select deptno from emp where deptno=30 and comm is not null);

-- 다중행 서브쿼리

select * from emp where deptno=10 or deptno=20 or deptno=30;

select * from emp where deptno in(10,20,30);

-- 담당업무 최고 급여에 해당하는 사원은?

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

select ename from emp where sal in(select max(sal) from emp group by job);

-- 업무별 최대급여를 받는 사원의 사원번호, 이름을 출력하라

select empno, ename, job, sal from emp where (job, sal) in (select job, max(sal) from emp group by job);

-- 부서코드별 최고급여를 받는 사원은?

select * from emp where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

-- ANY : 서브쿼리 결과값 중 하나만 만족하면 값을 리턴

-- 업무가 SALESMAN인 사원의 최소급여보다 많으면서 부서번호가 20이 아닌 사원의 이름과 급여, 부서코드를 출력하라.

select ename, sal, deptno from emp where sal > (select min(sal) from emp where job='SALESMAN') and deptno!=20;

select ename, sal, deptno from emp where sal > any(select min(sal) from emp group by job) and deptno!=20;

-- ALL : 서브쿼리 결과값 중 모든 값이 만족해야 리턴

-- 부서별 평균을 구하여 평균보다 급여가 많ㅇ느 사원 선택

select * from emp where sal > all(select avg(sal) from emp group by deptno);

select * from emp where deptno!=20 and sal > all(select sal from emp where job='SALESMAN');

-- EXISTS : 값의 존재 유무

-- 사원을 관리하는 관리자만 선택

select * from emp e where exists(select empno from emp where mgr=e.empno);

-- SUBQUERY : 쿼리문 안에 쿼리문을 넣어서 단일행으로 만드는 것

select * from emp where sal>=(select sal from emp where ename='JAMES');

-- MARTIN과 같은 부서에 근무하는 사원은?

select * from emp where deptno=(select deptno from emp where ename='MARTIN');

-- EMP 테이블에 레코드를 선택하되 ADAMS보다 급여가 많고 SCOTT보다 급여를 적게 받는 사원을 선택하라.

select * from emp where

 sal > (select sal from emp where ename='ADAMS')

 and

 sal < (select sal from emp where ename='SCOTT');

-- SCOTT과 같은 업무를 하는 사원은?

select * from emp where job = (select job from emp where ename='SCOTT');

-- 사원번호 7499인 사원과 같은 업무를 하거나, 사원번호 7566의 급여보다 많은 사원은?

select * from emp where

 job = (select job from emp where empno=7499)

 or

 sal > (select sal from emp where empno=7566);

-- 급여가 평균보다 작은 사원은?

select avg(sal) from emp;

select * from emp where sal<2073;

select * from emp where sal < (select avg(sal) from emp);

-- 부서코드 20번에 해당하는 사원의 평균급여보다 많은 급여를 받는 사원은?

select * from emp where sal > (select avg(sal) from emp where deptno=20);

-- SALESMAN의 최저 급여보다 급여를 많이 받는 사원중 부서코드가 20번에 부서에 해당하는 사원은?

select * from emp where sal > (select min(sal) from emp where job='SALESMAN')

and

deptno=20;

-- MANAGER의 평균급여보다 많이 받는 사원과 부서코드 10번 부서의 최소 급여보다 적게 받는 사원은?

select * from emp where sal > (select avg(sal) from emp where job='MANAGER')

or

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

-- 사원번호 7782와 같은 업무를 하는 사원의 평균급여보다 적게 받는 사원은?

select * from emp where sal < (select avg(sal) from emp where job = (select job from emp where empno=7782));

-- COALESCE : NULL이 아닌 첫번째 값 선택

select coalesce(null, null, 'A', 'B', 'C') from dual;

select ename, comm, mgr, sal, coalesce(comm, mgr, sal) from emp;

-- NULLIF : 같으면 NULL, 다르면 첫번째 값 출력

select nullif('AAA', 'AAA') from dual;

select nullif('AAA', 'BBB') from dual;

-- CASE 문장 : IF ~ ELSE문과 같은 기능

select * from empcopy;

-- EMPCOPY 테이블에서 부서코드를 10-ACCOUNTING, 20-RESEARCH, 30-SALES, 그 외-ETC로 선택

select ename, deptno, case deptno

 when 10 then 'accounting'

 when 20 then 'research'

 when 30 then 'sales'

 else 'etc'

end

from empcopy order by deptno asc; -- deptno 기준 오름차순 정렬 / asc 생략 가능

-- EMPCOPY 테이블에서 급여가 0~1000불 까지는 1호봉, 1000~2000불 까지는 2호봉, 2000~3000불 까지는 3호봉, 3000~4000불 까지는 4호봉, 4000불 이상은 5호봉

select ename, sal, case

 when sal>=0 and sal<=1000 then '1호봉'

 when sal between 1000 and 2000 then '2호봉'

 when sal between 2000 and 3000 then '3호봉'

 when sal between 3000 and 4000 then '4호봉'

 when sal>=4000 then '5호봉'

end

from empcopy;

-- MERGE : 병합

select * from emp;

-- EMP 테이블 DEPTNO가 30인 사원을 포함하는 EMPTEST 테이블을 생성

create table emptest

as

select * from emp where deptno=30;

select * from emptest;

select * from emp;

delete from emp where empno=4444; -- emp 테이블에 불필요한 레코드 삭제

commit;

-- EMP 테이블을 EMPTEST 테이블로 병합

-- EMPTEST 테이블에 이미 있는 사원은 급여를 15% 인상하고,

-- EMPTEST 테이블에 없는 사원은 추가한다.

merge into emptest -- 타겟 테이블명

 using emp

 on (emptest.empno = emp.empno) -- 사원 번호를 비교 기준

when matched then -- 양쪽 사원번호가 이미 있는 경우(업데이트)

 update set emptest.sal = emptest.sal*1.15

when not matched then -- 사원번호가 없는 경우(인설트)

 insert values(emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal, emp.comm, emp.deptno);

select * from emptest;

-- EMP 테이블에서 담당업무가 SALESMAN이 아닌 사원을 포함한 EMP_EX 테이블을 생성한다.

create table emp_ex

as

select * from emp where job!='salesman';

select * from emp_ex;

-- EMP 테이블에서 담당업무가 SALESMAN인 사원을 포함한 EMP_SALESMAN 테이블을 생성한다.

create tavle emp_salesman

as

select * from emp where job='salesman';

select * from emp_salesman;

-- EMP_SALESMAN 테이블에 EMP_EX 테이블을 병합하되, 이미 있는 사원은 급여를 10% 인상하고, 보너스를 50% 인상하며, 없는 사원은 EMP_SALESMAN 테이블에 사원을 추가하라.

merge into emp_salesman s

 using emp_ex e

 on(s.empno = e.empno)

when matched then

 update set s.sal=s.sal*1.1, e.comm=s.comm*1.5

when not matched then

 insert values(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno);

select * from emp_salesman;

-- 가방 판매쇼핑몰의 회원가입과 회원만 글을 쓸 수 있는 자유게시판의 테이블을 설계하여 제약조건을 설정한다.

create table membersInfo(

 memNum number(5) primary key,

 memID varchar2(12) not null,  -- unique

 memPwd varchar2(12) not null,

 memName varchar(10) not null,

 birthday date,

 zipcode number(5),

 addr1 varchar2(50),

 addr2 varchar2(30),

 email varchar2(20) not null,

 tel varchar(13) not null,

 writedate date default sysdate,

 lastvisitdate date default sysdate,

 memLevel char(1) default 'D'

);

-- memId 필드는 중복허용하지 않는다.

alter table memberInfo

add constraint UK_MEMID_MEMBERINFO unique (memId);

create table freeBoard(

 boardNum number(5) primary key,

 memId varchar2(12) not null,  -- foreign key

 subject varchar(100) not null,

 content clob not null,

 hit number(3) default 0,

 writedate date default sysdate

);

alter table freeBoard

add constraint FK_memid_freeboard

foreign key (memid) references MEMBERINFO(memberid);

select * from user_constraints;

create sequence memberInfoSqu

start with 1

increment by 1;

create sequence freeBoard

start with 1

increment by 1;

select * from user_sequences;

-- 외부파일의 쿼리문 실행

@c://fileFolder/member.ddl  -- @경로값

-- 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;