Story of Hyun

-- UNION : 합집합, 선택 데이터를 한개씩만 선택

select deptno from emp

union

select deptno from dept;

-- UNION ALL : 모든 데이터 선택

select deptno from emp

union all

select deptno from dept;

-- INTERSECT : 공통 데이터만 선택

select deptno from emp

intersect

select deptno from dept;

-- MINUS 

select deptno from dept

minus

select deptno from emp;

create table memberInfo(

memNum number(5) primary key,

memId varchar2(12) not null,

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 varchar2(13) not null,

writedate date default sysdate,

lastvisitdate date default sysdate,

memLevel char(1) default 'D');

alter table memberInfo add constraint uk_memid_memberInfo unique (memId);

create sequence memberInfosqu start with 1 increment by 1;

create table freeBoard(

boardNum number(5) primary key,

memId varchar2(12) not null,

subject varchar2(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 (memId);

create sequence freeBoardsqu start with 1 increment by 1;

-- 오라클을 이용한 paging

-- 시퀀스 확인

select * from user_sequences;

select * from freeboard;

@z://filefolder/freeboard.sql;

select * from user_constraints;

alter table freeboard

drop constraint fk_memId_freeboard;

select * from freeboard;

commit;

select * from freeboard order by boardnum desc;

selcelt * from

 (select * from

  (select * from 

   (select boardnum, memid, subject, hit, to_char(writedate, 'mm:ss') from freeboard order by boardnum desc)

  where rownum <= 15 order by rownum desc)

 where rownum <= 5)

order by boardNum desc;

-- 한 페이지에 4개씩 출력할 경우 1page 선택

selcelt * from

 (select * from

  (select * from 

   (select boardnum, memid, subject, hit, to_char(writedate, 'mm:ss') from freeboard order by boardnum desc)

  where rownum <= 4*1 order by rownum desc)

 where rownum <= 4)

order by boardNum desc;

-- FROM 절에 서브쿼리

select * from emp where deptno=30;

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

select * from (select * from emp where sal > (select avg(sal) from emp where deptno=20)) a, emp b

where a.empno=b.empno and b.mgr is not null and b.deptno!=20;

-- 다중열 서브쿼리

-- 쌍비교

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;