update

• 로우내의 컬럼 값을 수정하는 구문이다.
• update 테이블 명
  set 컬럼 = 값, 컬럼 = 값…
  where 조건문

ex)

-- 사원들의 부서 번호를 40번으로 변경한다.
update emp01
set deptno=40;

select * from emp01;

ex)

-- 사원들의 입사일을 오늘로 변경한다.
update emp01
set hiredate = sysdate;

ex)

-- 사원들의 직무를 개발자로 변경한다.
update emp01
set job = '개발자';

여러컬럼 업데이트

ex)

-- 사원들의 부서번호를 40, 입사일을 오늘, 직무를 개발자로 변경한다.
update emp01
set deptno = 40, hiredate = sysdate, job = '개발자';

ex)

-- 10번 부서에 근무하고 있는 사원들을 40번 부서로 수정한다.
update emp01
set deptno = 40
where deptno = 10;

ex)

-- SALESMAN들의 입사일을 오늘, COMM을 2000으로 수정한다.
update emp01
set hiredate = sysdate, comm = 2000
where job = 'SALESMAN';

ex)

-- 전체 사원의 평균 급여보다 낮은 사원들의 급여를 50% 인상한다.
update emp01
set sal = sal *1.5
where sal < (select avg(sal)
            from emp01);

ex)

-- MANAGER 사원들의 직무를 Developer로 변경한다.
update emp01
set job = 'Developer'
where job = 'MANAGER';

ex)

-- 30번 부서에 근무하고 있는 사원들의 급여를 전체 평균 급여로 설정한다.
update emp01
set sal = (select avg(sal)
          from emp01)
where deptno = 30;

ex)

-- BLAKE 밑에서 근무하고 있는 사원들의 부서를 DALLAS지역의 부서번호로 변경
update emp01
set deptno = (select deptno 
              from dept
              where loc = 'DALLAS')
where mgr = (select empno
            from emp
            where ename = 'BLAKE');

ex)

-- 20번 부서에 근무하고 있는 사원들의 직속상관을 KING으로 하고 급여를 전체급여의 최고액으로 설정
update emp01
set mgr = (select empno
          from emp01
          where ename = 'KING'),
    sal = (select max(sal)
          from emp01)
where deptno = 20;

ex)

-- 직무가 CLERK인 사원들의 직무와 급여액을 20번 부서에 근무하고 있는 사원중
-- 급여 최고액을 받는 사원의 직무와 급여액으로 변경한다.
update emp01
set (job, sal) = (select job, sal
                  from emp01
                  where sal = (select max(sal)
                              from emp01))
where job = 'CLERK';

Comment and share

insert

• 테이블에 새로운 로우를 추가하는 구문이다.
• insert into 테이블 명 (컬럼명.)
• values(값…)

ex)

create table emp01
as
select empno, ename, job from emp where 1=0;
-- 무조건 거짓이므로 로우를 가져오지 못한다. 그러나 구조를 가져올 수 있다.
-- emp01테이블에 emp테이블의 구조만 가져오겠다는 것이 된다.

ex)

-- 다음과 같은 사원 정보를 추가한다.
-- 1111 홍길동 인사
-- 2222 김길동 개발
-- 3333 최길동 인사
-- 4444 박길동 생산
insert into emp01 (empno, ename, job) -- 컬럼목록
values (1111, '홍길동', '인사'); -- 값을 1대1매칭으로 입력한다.

insert into emp01 (empno, ename, job) -- 컬럼목록
values (2222, '김길동', '개발'); -- 값을 1대1매칭으로 입력한다.

insert into emp01 (empno, ename, job) -- 컬럼목록
values (3333, '최길동', '인사'); -- 값을 1대1매칭으로 입력한다.

insert into emp01 (empno, ename, job) -- 컬럼목록
values (4444, '박길동', '생산'); -- 값을 1대1매칭으로 입력한다.

ex)

-- 컬럼 목록을 생략하는 경우
-- 모든 값을 입력할 경우에만 사용가능하고 순서대로 1대1 매칭된다.
insert into emp01
values (5555, '황길동', '생산');

ex)

-- 컬럼 목록에 모든 컬럼이 있지 않을 경우
-- 이경우 job에는 null값을 저장한다.
insert into emp01 (empno, ename)
values (6666, '이길동');

-- null을 명시적으로 저장
insert into emp01 (empno, ename, job)
values (7777, '박보검', null);

서브쿼리로 데이터 저장하기

• insert info 테이블명
  서브쿼리
• insert all
  into 테이블명 (컬럼명) values(컬럼명)
  서브쿼리

ex)

-- 서브쿼리로 데이터 저장하기
create table emp02
as
select empno, ename, job from emp where 1=0;

insert into emp02(empno,ename, job)
select empno, ename, job from emp;

insert into emp02
select empno, ename,job from emp;

select * from emp02;

ex)

-- insert all
create table emp03
as
select empno, ename, job from emp where 0=1;

create table emp04
as
select empno, ename, hiredate from emp where 1=0;

insert all
into emp03(empno, ename, job) values (empno, ename, job)
into emp04(empno, ename, hiredate) values (empno, ename, hiredate)
select empno, ename, job, hiredate from emp;

select * from emp03;
select * from emp04;

ex)

-- 사원번호 이름 급여를 저장할 수 있는 빈 테이블을 만들고
-- 급여가 1500이상인 사원들의 사원번호, 이름, 급여를 저장한다.
create table emp05
as
select empno, ename, sal from emp where 1=0;

insert into emp05(empno, ename, sal)
select empno, ename, sal from emp where sal>=1500;

select * from emp05;

Comment and share

Set

• 두 select문을 통해 얻어온 결과에 대해 집합 연산을 할 수 있는 명령문이다.
• 두 select문을 통해 가져온 컬럼의 형태가 완전히 일치해야 한다.
• UNION : 합집합
• UNIONALL : 합집합, 중복된 데이터를 모두 가져온다.
• INTERSECT : 교집합
• MINUS : 차집합

ex)

-- UNION : 합집합(중복없음)
select empno, ename, job, deptno
from emp
where deptno = 10
union
select empno, ename, job, deptno
from emp
where job = 'CLERK';

ex)

-- UNION ALL : 합집합(중복있음)
select empno, ename, job, deptno
from emp
where deptno = 10
union all
select empno, ename, job, deptno
from emp
where job = 'CLERK';

ex)

-- INTERSECT : 교집합
select empno, ename, job, deptno
from emp
where deptno = 10
intersect
select empno, ename, job, deptno
from emp
where job = 'CLERK';

ex)

-- MINUS : 차집합
select empno, ename, job, deptno
from emp
where deptno = 10
minus
select empno, ename, job, deptno
from emp
where job = 'CLERK';

Comment and share

결과가 하나 이상인 서브쿼리

– 서브쿼리를 통해 가져온 결과가 하나이상인 경우 결과를 모두 만족하거나 결과 중 하나만 만족하거나 해야하는 경우가 있다.

• 이때 다음과 같은 연산자를 사용하면 된다.
• IN : 서브쿼리의 결과 중 하나라도 일치하면 조건은 참이 된다.
• ANY, SOME : 서브쿼리의 결과와 하나이상 일치하면 조건은 참이된다.
  IN과는 달리 조건을 자유롭게 줄 수 있다.
• ALL : 서브쿼리의 결과와 모두 일치해야 조건은 참이된다.

ex)

-- 3000이상의 급여를 받는 사원들과 같은 부서에 근무하고 있는 사원의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where deptno in (select deptno
                from emp
                where sal>=3000);

ex)

-- 직무가 CLERK인 사원과 동일한 부서에 근무하고 있는 사원들의 사원번호, 이름, 입사일을 가져온다.
select empno, ename, hiredate
from emp
where deptno in (select deptno
                from emp
                where job = 'CLERK');

ex)

-- KING을 직속상관으로 가지고 있는 사원들이 근무하고 있는 근무 부서명, 지역을 가지고 온다.
select dname,loc
from dept
where deptno in (select deptno
                from emp
                where mgr = (select empno
                            from emp
                            where ename = 'KING'));

ex)

-- CLERK들의 직속상관의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where empno in (select mgr
                from emp
                where job = 'CLERK');

ex)

-- 각 부서별 급여 평균보다 더 많이 받는 사원의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal > all (select avg(sal)
                from emp
                group by deptno);

select empno, ename, sal
from emp
where sal > (select max(avg(sal))
            from emp
            group by deptno);

all을 사용하는 경우 대부분 max함수를 사용하여 대치가 가능하다.

ex)

-- 각 부서별 급여 최저치보다 더 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal > all (select min(sal)
                from emp
                group by deptno);

select empno, ename, sal
from emp
where sal > (select max(min(sal))
                from emp
                group by deptno);

ex)

-- SALESMAN보다 급여를 적게 받는 사원들의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal < all (select sal
                from emp
                where job = 'SALESMAN');

select empno, ename, sal
from emp
where sal <  (select min(sal)
            from emp
            where job = 'SALESMAN');

ex)

-- 각 부서별 최저 급여 액수 보다 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal> any (select min(sal)
                from emp
                group by deptno);

Comment and share

서브쿼리

• 쿼리문 안에 들어가는 쿼리문을 서브쿼리라고 한다.
• 쿼리문 작성시 사용되는 값을 다른 쿼리문을 통해 구해야 할 경우 사용한다.

ex)

-- SCOTT사원이 근무하고 있는 부서의 이름을 가져온다.
select dname
from dept
where deptno = (select deptno
                from emp
                where ename = 'SCOTT');
                
-- join으로도 가능하다.
select a2.dname
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.ename = 'SCOTT';

ex)

-- SMITH와 같은 부서에 근무하고 있는 사원들의 사원번호, 이름, 급여액, 부서이름을 가져온다.
select a1.empno, a1.ename, a1.sal, a2.dname
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.deptno = (select deptno
                                            from emp
                                            where ename = 'SMITH');

ex)

-- MARTIN과 같은 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.
select empno, ename, job
from emp
where job = (select job
            from emp
            where ename = 'MARTIN');

ex)

-- ALLEN과 같은 직속상관을 가진 사원들의 사원번호, 이름, 직속상관이름을 가져온다.
-- a : 사원의 정보
-- b : 직속상관 정보
select a.empno, a.ename, b.ename
from emp a, emp b
where a.mgr = b.empno and a.mgr = (select mgr
                                from emp
                                where ename = 'ALLEN');

ex)

-- WARD와 같은 부서에 근무하고 있는 사원들의 사원번호, 이름, 부서번호를 가져온다.
select a.empno, a.ename, b.deptno
from emp a, dept b
where a.deptno = b.deptno and a.deptno = (select deptno
                                        from emp
                                        where ename = 'WARD');

ex)

-- SALESMAN의 평균 급여보다 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal > (select avg(sal)
            from emp
            where job = 'SALESMAN');

ex)

-- DALLAS 지역에 근무하는 사원들의 평균급여를 가져온다.
select trunc(avg(sal))
from emp a, dept b
where a.deptno = b.deptno and b.loc = (select loc
                                    from dept
                                    where loc = 'DALLAS');

ex)

-- SALES 부서에 근무하는 사원들의 사원번호, 이름, 근무지역을 가져온다.
select a1.empno, a1.ename, a2.loc
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.deptno = (select deptno
                                            from dept
                                            where dname = 'SALES');

ex)

-- CHICAGO 지역에 근무하는 사원들 중 BLAKE가 직속상관인 사원들의 사원번호, 이름, 직무를 가져온다.
select empno, ename, job
from emp
where deptno = (select deptno
                from dept
                where loc = 'CHICAGO')
                and mgr = (select empno
                        from emp
                        where ename = 'BLAKE');

Comment and share

Self Join

• 같은 테이블을 두번 이상 조인하는 것을 의미한다.

ex)

-- SMITH 사원의 사원번호, 이름, 직속상관의 이름을 가져온다.
select a1.empno, a1.ename, a2.ename
from emp a1, emp a2
where a1.mgr = a2.empno and a1.ename = 'SMITH';

ex)

-- FORD사원 밑에서 일하는 사원들의 사원번호, 이름, 직무를 가져온다.
select a1.empno, a1.ename, a1.job
from emp a1, emp a2
where a1.mgr = a2.empno and a2.ename = 'FORD';

ex)

-- SMITH 사원의 직속상관과 동일한 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.
select a3.empno, a3.ename, a3.job
from emp a1, emp a2, emp a3
where a1.mgr = a2.empno and a2.job = a3.job and a1.ename = 'SMITH';

Outer join

• 조인 조건에 해당하지 않기 떄문에 결과에 포함되지 않는 로우까지 가져오는 조인이다.

ex)

-- 각 사원의 이름, 사원번호, 직장상사 이름을 가져온다. 단 직속상관이 없는 사원도 가져온다.
select a1.ename, a1.empno, a2.ename
from emp a1, emp a2
where a1.mgr = a2.empno(+);

null이 있는 쪽에 (+)한다.

ex)

-- 모든 부서의 소속 사원의 근무부서명, 사원번호, 사원이름, 급여를 가져온다.
select b.dname,a.ename,a.empno,a.sal
from emp a, dept b
where a.deptno(+) = b.deptno;

Comment and share

Join

• 두개 이상의 테이블에 있는 컬럼의 값을 한번에 가져오기 위해 사용하는 것이 조인이다.
• select 컬럼명 from 테이블1, 테이블2;
• 두개 이상의 테이블을 조인하게 되면 다 대 다의 관계로 가져오기 때문에 테이블1의 로우의수X테이블2의 로우의 수 만큼 로우를 가져오게 된다.
• 두개 이상의 테이블에서 가져온 결과중에 정확한 결과만 가져오기 위해 공통 부분을 이용한 조건문이 반드시 필요하다.

ex)

-- 사원테이블(emp)과 부서테이블(dept)을 join한다.
select * from emp;
-- 14행
select * from dept;
-- 4행

select * from emp a1, dept a2
-- 56행
where a1.deptno = a2.deptno;

ex)

-- 사원의 사원번호, 이름, 근무부서 이름을 가져온다.
select a1.empno, a1.ename, a2.dname
from emp a1,dept a2
where a1.deptno = a2.deptno;

ex)

-- 사원의 사원번호, 이름, 근무지역을 가져온다.
select a1.empno, a1.ename, a2.loc
from emp a1,dept a2
where a1.deptno = a2.deptno;

ex)

-- DALLAS에 근무하고 있는 사원들의 사원번호, 이름, 직무를 가져온다.
select a1.empno, a1.ename, a1.job
from emp a1, dept a2
where a1.deptno = a2.deptno and a2.loc = 'DALLAS';

ex)

-- SALES 부서에 근무하고 있는 사원들의 급여 평균을 가져온다.
select avg(a1.sal)
from emp a1, dept a2
where a1.deptno = a2.deptno and a2.dname = 'SALES';

ex)

-- 1982년 입사한 사원들의 사원번호, 이름, 입사일, 근무부서이름을 가져온다.
select a1.empno, a1.ename, a1.hiredate, a2.dname
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.hiredate between '1982/01/01' and '1982/12/31';

ex)

-- 각 사원들의 사원번호, 이름, 급여, 급여등급을 가져온다.
select a1.empno, a1.ename, a1.sal, a1.sal, a2.grade
from emp a1, salgrade a2
where a1.sal between a2.losal and a2.hisal;

ex)

-- SALES 부서에 근무하고 있는 사원의 사원번호, 이름, 급여등급을 가져온다.
select a1.empno, a1.ename, a2.grade
from emp a1, salgrade a2, dept a3
where a1.sal between a2.losal and a2.hisal and a1.deptno = a3.deptno
    and a3.dname = 'SALES';

ex)

-- 각 급여 등급별 급여의 총합과 평균, 사원의수, 최대급여, 최소급여를 가져온다.
select sum(a1.sal), trunc(avg(a1.sal)), count(a1.sal), max(a1.sal), min(a1.sal)
from emp a1, salgrade a2
where a1.sal between a2.losal and a2.hisal
group by a2.grade;

ex)

-- 급여등급이 4등급인 사원들의 사원번호, 이름, 급여, 근무부서이름, 근무지역을 가져온다.
select a2.empno, a2.ename, a2.sal, a3.deptno, a3.loc
from salgrade a1, emp a2, dept a3
where a2.sal between a1.losal and a1.hisal and a2.deptno = a3.deptno and a1.grade = 4;

Comment and share

Having

• Group By로 묶인 각 그룹들 중에 실제 가져올 그룹을 선택할 조건을 having 절에 작성한다.
• Having은 Group By절의 조건이 된다.

ex)

-- 부서별 평균 급여가 2000이상인 급여 평균을 가져온다.
select avg(sal)
from emp
group by deptno
having avg(sal) >= 2000;

ex)

-- 부서별 최대 급여액이 3000이하인 부서의 급여 총합을 가져온다.
select sum(sal)
from emp
group by deptno
having max(sal) <= 3000;

ex)

-- 부서별 최소 급여액이 1000이하인 부서에서 직무가 CLERK인 사원들의 급여 총합을 구한다.
select sum(sal)
from emp
where job = 'CLERK'
group by deptno
having min(sal) <= 1000;

ex)

-- 각 부서의 급여 최소가 900이상 최대가 10000이하인 부서의 사원들 중 1500이상의
-- 급여를 받는 사원들의 평균 급여액을 가져온다.
select avg(sal)
from emp
where sal >= 1500
group by deptno
having min(sal) >= 900 and max(sal) <= 10000;

Comment and share

Group By

• 그룹함수를 사용할 경우 selectfromwhere 절 까지 모두 수행하여 가져온 결과를 하나의 그룹으로 묶어 총합, 평균 등을 구할 수 있다.
• Group By절을 사용하면 select문을 수행하여 가져온 하나의 결과를 여러 그룹으로 나눠 그룹 각각의 총합과 평균 등을 구할 수 있다.

ex)

-- 각 부서별 사원들의 급여 평균을 구한다.
select deptno, AVG(sal)
from emp
group by deptno;

ex)

-- 각 직무별 사원들의 급여 총합을 구한다.
select job, sum(sal)
from emp
group by job;

ex)

-- 1500이상 급여를 받는 사원들의 부서별 급여 평균을 구한다.
select deptno, avg(sal)
from emp
where sal >= 1500
group by deptno;

Comment and share

그룹함수

ex)

-- 사원들의 급여 총합을 구한다.
select sum(sal)
from emp;

ex)

-- 사원들의 커미션을 가져온다.
select sum(comm)
from emp;

ex)

-- 급여가 1500이상인 사원들의 급여 총합을 구한다.
select sum(sal)
from emp
where sal >= 1500;

ex)

-- 20번 부서에 근무하고 있는 사원들의 급여 총합을 구한다.
select sum(sal)
from emp
where deptno = 20;

ex)

-- 직무가 SALESMAN인 사원들의 급여 총합을 구한다.
select sum(sal)
from emp
where job = 'SALESMAN';

ex)

-- 전사원의 급여 평균을 구한다.
select trunc(avg(sal))
from emp;

ex)

-- 커미션을 받는 사원들의 커미션 평균을 구한다.
select avg(comm)
from emp;

그룹함수의 경우 null값은 제외하고 계산한다.

ex)

-- 전 사원의 커미션 평균을 구한다.(nvl : null값을 0으로 바꿔준다.)
select trunc(avg(nvl(comm, 0)))
from emp;

nvl(,0)컬럼에 null값은 0으로 바꾼다.

ex)

-- 커미션을 받는 사원들의 급여 평균을 구한다.
select trunc(avg(sal))
from emp
where comm is not null;

ex)

-- 30번 부서에 근무하고 있는 사원들의 급여 평균을 구한다.
select trunc(avg(sal))
from emp
where deptno = 30;

ex)

-- 직무가 SALESMAN인 사원들의 급여 + 커미션 평균을 구한다.
select avg(sal + comm)
from emp
where job = 'SALESMAN';

ex)

-- 사원들의 총 수를 가져온다.
select count(empno)
from emp;

ex)

-- 그룹함수를 계산할 때는 무조건 null값은 제외하고 계산하다.
-- 그러므로 이런식으로 하는 것이 최선의 방법이다.
-- 사원들의 총 수를 가져온다.
select count(*)
from emp;

ex)

-- 사원들의 급여 최대, 최소값을 가져온다.
select max(sal), min(sal)
from emp;

Comment and share