[오라클] 4월 18일 예제

여기 좀 중요한거 많음

UPDATE student
SET sname = TRIM(sname);

--
-- 1. dml
일부 컬럼만 저장
    ㄴ
    ;

INSERT INTO emp(eno, ename, job, hdate, sal)
values (3006, '홍길동', '개발', sysdate, 3000);

commit;

insert into emp
values (3000, '임꺽정', '설계', 2008, sysdate, 3000, 200, 30);
commit;


--commit, rollback
--commit은 작업의 완료
--rollback은 작업의 취소, commit되기 전의 변경사항을 모두 취소.


insert into emp
values (3000, '장길산1', '분석', 2008, sysdate, 3000, 100, 20);
insert into emp
values (3001, '장길산2', '분석', 2008, sysdate, 3000, 100, 20);
insert into emp
values (3002, '장길산3', '분석', 2008, sysdate, 3000, 100, 20);
insert into emp
values (3003, '장길산4', '분석', 2008, sysdate, 3000, 100, 20);
insert into emp
values (3004, '장길산5', '분석', 2008, sysdate, 3000, 100, 20);
commit;
select *
from emp;

rollback;

create table emp_copy1
(

    eno   varchar2(4),
    ename varchar2(20),
    job   varchar2(10),
    mgr   varchar2(10),
    hdate date,
    sal   number(5),
    comm  number(5),
    dno   varchar(2)
);
select *
from emp_copy1;


--emp 테이블에서 dno 이 30인 데이터들만 가져와서 저장

insert into emp_copy1
select *
from emp
where dno = '30';

commit;

select *
from EMP_COPY1;

--course_prfess테이블

create table course_prefess
(
    cno    varchar2(8),
    cname  varchar2(20),
    st_num number(1, 0),
    pno    varchar2(8),
    pname  varchar(20)


);
ㅁㅁㅁㅁ 코스랑 프로페서 조인해서
ㅁㅁㅁㅁㅁ 새로운 테이블에 코스랑 프로페서 조인하기
commit;

insert into course_prefess
select cno, cname, ST_NUM, PROFESSOR.PNO, PROFESSOR.PNAME
from PROFESSOR,
     COURSE
where COURSE.PNO = PROFESSOR.PNO(+);


commit;

select *
from course_prefess;

delete
from course_prefess;

ㅁ ㅁ ㅁ pdate
ㅁ    ㅁ ㅁ ㅁ ㅁ ㅁ ㅁ emp copy1의 mgr을 모두 0001로 만들어라
update EMP_COPY1
set mgr = '0001';

select *
from EMP_COPY1;

rollback;
ㅁ ㅁ ㅁ ㅁ사원번호 3005번 보너스 1800으로 수정
update emp_copy1
set comm = 1800
where eno = '3005';

ㅁ ㅁ ㅁ ㅁ 사원번호 3005번 보너스 1800으로 수정 연산사용
update emp_copy1
set comm = comm * 3
where eno = '3005';

select *
from emp_copy1;



ㅁ ㅁ ㅁ ㅁ ㅁprofessor 테이블의 hiredate를 +20년 해서 업데이트
update PROFESSOR
set HIREDATE = add_months(HIREDATE, 12 * 20);

select *
from PROFESSOR;

--emp copy의 데이터 삭제

select *
from EMP_COPY1;
delete emp_copy1;


ㅁㅁㅁㅁㅁㅁ  emp의 전체 데이터를 emp_copy 에 저장
insert into emp_copy1
select *
from EMP;


select *
from emp_copy1;

ㅁㅁㅁㅁㅁㅁ 30 40 번 부서 사원들 60번 부서로 통합 , 보너스가 현재 보너스ㅡ의 * 1.5로 으로 될 수 있도록
update emp_copy1
set dno  = 60,
    comm = comm * 1.5
where dno in (20, 30);

ㅁㅁㅁㅁㅁㅁㅁㅁㅁ  dept_copy 테이블에 dept 를 복사해버리는거
create table dept_copy
as
select *
from dept;

select *
from dept_copy;


서브쿼리를 이용한 데이터 수정
부서번호가 20, 30인놈들 50으로 통합
dno, dname, loc을 바꿔라


update dept_copy
set (dno, dname, loc)= (select dno, dname, loc from DEPT where dno = '50')

where dno in ('20', '30');


40번 부서의 director을 emp 테이블의 급여가 제일 높은 사원으로 업데이트
    ;

update dept_copy
set (DIRECTOR) = (select eno
                  from emp
                  where SAL = (select max(sal)
                               from emp))
where DNO = 40;



select eno
from emp
where SAL = (select max(sal)
             from emp);



update dept_copy
set DIRECTOR = (select eno from emp where sal = (select max(sal) from EMP))
where DNO = 40;

update dept_copy
set DIRECTOR = 10
where DNO = 40;

update dept_copy
set DIRECTOR = (select eno from emp where sal = (select max(sal) from emp))
where dno = 40;

--1-4 delete from
-- 전체 데이터 삭제  -> 조건절 생략
delete
from emp_copy1;

select *
from emp_copy1;


-- 일부 데이터만 삭제 -> where절로 조건 추가


delete
from emp_copy1
where job = '지원';

where절에 서브쿼리를 사용하여 특정 데이터 삭제

ㅁㅁㅁㅁㅁㅁ emp_copy 에서 급여가 4000 이상되는 사원 정보 삭제
delete
from emp_copy1
where eno in (select eno
              from emp_copy1
              where sal > 4000);

-- ㅁㅁㅁㅁㅁㅁㅁㅁㅁ  dept_copy 테이블에 dept 를 복사해버리는거
-- create table dept_copy
-- as
-- select *
-- from dept;


ㅁㅁㅁㅁㅁ student 테이블 참조해서 st_copy 테이블 생성
    ㅁㅁㅁㅁ score 학생별 기말고사 성적 평균이 66 이상인 학생정보 st_copy에서 삭제

create table st_copy as
select *
from student;



;

delete
from st_copy
where (sname, sno ) in (select sname,sno
                        from student
                                 natural join SCORE
                        group by sname, sno
                        having avg(result)>=60);


select avg(result), sname
from student
         natural join SCORE
group by sname
having avg(result)>=60
;





ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ course professor1이라는 테이블을 만드는데 cno, cname, pno, pname avgres
    이걸 course, score, professor 에서 가져와라

create table course_professor1(cno, cname, pno, pname, avgres)
as
select cno, c.cname, pno, p.pname, round(avg(result), 2)
from course c
         natural join SCORE sc
         natural join PROFESSOR p
group by cno, c.cname, pno, p.pname
;

select *
from course_professor1;



ㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁㅁ student의 sno, sname, major, syear
    score의 result, scgrade의 grade 를 가지는 테이블을 생성해채ㅕ;



create table hahaha(sno, sname, major, syear, result, grade)
as
select s.sno, s.sname, s.major, s.syear, sc.result, scg.grade
from student s,
     score sc,
     SCGRADE scg
where s.SNO = sc.SNO
  and sc.RESULT between scg.LOSCORE and scg.HISCORE;

select *
from hahaha;

delete
from hahaha;
drop table hahaha;


select s.sno, s.sname, s.major, s.syear, sc.result, scg.grade
from student s,
     score sc,
     SCGRADE scg
where s.SNO = sc.SNO
  and sc.RESULT between scg.LOSCORE and scg.HISCORE;

select s.sno, s.sname, s.major, s.syear, sc.result, scg.grade
from student s
         join score sc
              on sc.SNO = s.SNO
         join SCGRADE scg
              on sc.RESULT between scg.LOSCORE and scg.HISCORE;



컬럼을 추가하는 add
emp copy  테이블에 add 칼럼 추가
;

    select * from emp_copy1;

    alter table emp_copy1
    add addr varchar2(50);

select * from emp_copy1;



컬럼명을 변경하는 rename
    emp_copy 테이블의 sal 컬럼을 salary 변경

    ;



alter table emp_copy1
rename column sal to salary;

컬럼의 데이터 타입을 변경하는 modify

emp_copy테이블의 eno 컬럼의 데이터 타입을 varchar2(4) -> varchar2(10);

    ;
    alter table emp_copy1
    modify eno varchar2(10);



컬럼을 삭제하는 drop



;
    alter table emp_copy1
    drop column addr;

테이블을 삭제하는 drop;

    create table score_copy1
    as select * from score;



select * from score_copy1;
스코어 테이블 삭제
;
    drop table score_copy1;


타입스탬프로 딜리트 된 데이터 조회

select * from emp_copy1
as of timestamp (systimestamp - interval '10' minute );

select * from emp_copy1;


리네임

rename emp_copy1 to emp_temp1;

select * from emp_temp1;

테이블의 데이터를 모두 삭제하는 truncate
    ;

    truncate table emp_temp1;


select * from emp_temp1;