[Oracle] DML, CREATE 예제

1. EMP, DEPT, SALGRADE 테이블과 동일하게 NEW_EMP, NEW_DEPT, NEW_SALGRADE 테이블 만들기

create table new_emp(
    NEW_EMPNO	NUMBER(4)	not null,
    NEW_ENAME	VARCHAR2(10),
    NEW_JOB	    VARCHAR2(9),
    NEW_MGR	    NUMBER(4),
    NEW_HIREDATE	DATE,
    NEW_SAL	    NUMBER(7),
    NEW_COMM	NUMBER(7),
    NEW_DEPTNO	NUMBER(2)
);

create table new_dept(
    NEW_DEPTNO	NUMBER(2)	not null,
    NEW_DNAME	VARCHAR2(14),
    NEW_LOC	VARCHAR2(13)
);

create table new_salgrage(
    NEW_GRADE	NUMBER,
    NEW_LOSAL	NUMBER,
    NEW_HISAL	NUMBER
);
commit;

 

2. NEW_EMP 테이블에 EMP테이블에서 부서 번호가 20,30인 사원정보 등록

insert into new_emp(NEW_EMPNO, NEW_ENAME,NEW_JOB,NEW_MGR,NEW_HIREDATE,NEW_SAL,NEW_COMM,NEW_DEPTNO)
select * from emp
where deptno in (20,30);
commit;

 

3. 부서테이블에 부서번호 50번, 부서명 IT,  지역 LA 입력

insert into new_dept values(50,'IT','LA');
commit;

 

4. 사원번호 7703, 사원이름 TOM, 사원직무, SALESMAN, 상급자 사원번호 7566, 급여 1400, 커미션 0, 부서번호 20 사원 현재날짜로 등록

insert into new_emp 
values(7703, 'TOM', 'SALESMAN', 7566, sysdate, 1400, 0, 20);
commit;

 

5. 사원번호 7369의 사원직무를 ANALYST로 수정

update new_emp
set new_job = 'ANALYST'
where new_empno = 7369;
commit;

 

6. 부서번호 20번 직원의 급여 10프로 인상

update new_emp
set new_sal = new_sal*1.1
where new_deptno = 20;
commit;

 

7. EMP 테이블에서 10번 부서중 급여가 2000 이상인 직원만 NEW_EMP테이블에 등록

insert into new_emp(NEW_EMPNO, NEW_ENAME,NEW_JOB,NEW_MGR,NEW_HIREDATE,NEW_SAL,NEW_COMM,NEW_DEPTNO)
select * from emp
where deptno = 10
and sal >= 2000;
commit;

 

8. 상급자 사원번호가 7839인 사원의 상급자 사원번호를 7782로 변경

update new_emp
set new_mgr = 7782
where new_mgr = 7839;
commit;

 

9. 지역이 DALLAS인 사원들의 급여를 10 감액하여 수정

update new_emp
set new_sal = new_sal - 10
where new_deptno = (select deptno from dept where loc = 'DALLAS');
commit;

 

10. 사원번호 7566 삭제

delete from new_emp
where new_empno = 7566;
commit;

 

11. 상급자가 없는 사원의 MGR값을 NULL로 변경

update new_emp
set new_mgr = 'NULL'
where new_mgr is null;
commit;

 

12. 사원이름이 ALLEN, WARD, BLAKE 사원의 부서번호를 10으로 변경

update new_emp
set new_deptno = 10
where new_ename in ('ALLEN', 'WARD', 'BLAKE');
commit;

 

13. COMM이 NULL인 데이터를 0으로 수정

update new_emp
set new_comm = 0
where new_comm is null;
commit;