[Database] 16장 다른 Table로부터 데이터 입력, 테이블 복사

 

생성해야 하는 테이블과 데이터

 

-- 재고 테이블 생성
create table remain(
    remain_year     char(04)        not null,   -- 해당년도
    remain_prod     varchar2(10)    not null,   -- 상품 코드
    remain_j_00     number(5),                  -- 전년 재고
    remain_i        number(5),                  -- 입고
    remain_o        number(5),                  -- 출고
    remain_j_99     number(5),                  -- 현재 재고
    remain_date     date,                       -- 처리일자
    constraint pk_remain primary key(remain_year, remain_prod), -- 년도별로 재고관리를 함.
    constraint fr_remain_prod foreign key(remain_prod) references prod(prod_id)
    );

 

 

 

다른 Table로부터 데이터 입력

 

타 테이블을 이용하여 데이터를 삽입하는 방법

 

-- 데이터 삽입
-- 2023년도 마감처리
insert into remain(remain_year, remain_prod, remain_j_00, remain_i, remain_o, remain_j_99, remain_date)
    select '2024', prod_id, to_number(substr(prod_id, -2)), 10, 7, to_number(substr(prod_id, -2))+10-7, sysdate from prod;

-- 2019년도 마감처리
insert into remain(remain_year, remain_prod, remain_j_00, remain_i, remain_o, remain_j_99, remain_date)
    select '2020', prod_id, to_number(substr(prod_id, -2)), 20, 8, to_number(substr(prod_id, -2))+20-8, sysdate from prod;

    
-- 데이터 수정
update buyer set buyer_charger = '우수처'
    where exists (select sum(cart.cart_qty * prod.prod_sale) -- 수량 * 판매 금액
        from prod, cart
        where cart.cart_no like '2020%'
        and cart.cart_prod = prod.prod_id
        and prod.prod_buyer = buyer.buyer_id
        having sum(cart.cart_qty * prod.prod_sale) > 80000000);

 

데이터 삽입 결과 확인

 

 

 

 

• 상품 모두의 2020년도 판매수량을 합산하여 상품테이블의 총판매수량 column 갱신

-- 1. 상품 테이블의 총판매수량 조회
select prod_id 상품코드, sum(nvl(prod_qtysale, 0)) 총판매수량
    from prod
    group by prod_id;

 

결과 화면1

 

 

-- 2. 장바구니 테이블에서 2020년도 상품별 판매수량 합산하여 조회
select prod_id 상품코드, sum(nvl(cart_qty, 0)) 매출수량
    from prod left outer join cart
    on (prod_id = cart_prod and cart_no like '2020%')
    group by prod_id;

 

 

-- 3. 2020년도 상품별 판매수량 합산하여 갱신하시오?
update prod
    set prod_qtysale = (
        select nvl(sum(cart_qty), 0)
        from cart
        where cart_prod = prod_id and cart_no like '2020%');

 

 

-- 4. 수행 결과 확인(인증)
select sum(nvl(prod_qtyin, 0)), sum(nvl(prod_qtysale, 0))
    from prod;

select sum(buy_qty)
    from buyprod
    where buy_date between '2020-01-01' and '2020-12-31';

select sum(cart_qty)
    from cart
    where cart_no like '2020%';

 

결과 화면2

 

 

 

• 상품 모두의 2020년도 입고수량, 판매수량을 합산하여 재고수(remain)테이블의 입고, 출고, 현 재고 coulmn을 수정

-- 1. 2020년도 입고수량 및 판매수량을 서브쿼리로 조회
select prod_id, 
    (select sum(nvl(buy_qty, 0)) from buyprod
        where prod_id = buy_prod
        and to_char(buy_date, 'yyyy') = '2020') as int_amt,
    (select sum(nvl(cart_qty, 0)) from cart
        where prod_id = cart_prod
        and cart_no like '2020%') as out_amt
    from prod;

 

 

-- 2. 1) 번에의 쿼리를  Inline View 로 적용하여 재고수 테이블 수정
-- ( 다중행, 다중열 서브쿼리는 자주 사용하지는 않지만 Update 할 때 유용하게 사용할 수 있음.)
update remain
    set (remain_i, remain_o, remain_j_99) =
        (select in_amt, out_amt, remain_j_00 + in_amt - out_amt
        from
            (select prod_id,
                (select sum(nvl(buy_qty,0))
                    from buyprod
                    where prod_id = buy_prod
                    and to_char(buy_date, 'yyyy') = '2020') as in_amt,
                (select sum(nvl(cart_qty,0))
                    from cart
                    where prod_id = cart_prod
                    and cart_no like '2020%') as out_amt
            from prod) a
        where remain_prod = a.prod_id)

 

 

-- 3. 수행 결과 확인(검증)
select * from remain where remain_year = '2020';

select sum(nvl(remain_i, 0)), sum(nvl(remain_o, 0))
    from remain
    where remain_year = '2020';
    
select sum(buy_qty) from buyprod where buy_date between '2020-01-01' and '2020-12-31';

select sum(cart_qty) from cart where cart_no like '2020%';

 

결과 화면3

 

결과 화면4

 

 

 

• 재고 수 테이블에서 2020년도 자료 중 입고수량 + 출고수량이 20개 이상인 자료를 삭제하시오.

-- 1. 조회
select remain_year 연도, remain_prod 상품, remain_i 입고량, remain_o 출고량, remain_i + remain_o 합계수량
    from remain
    where remain_year = '2020'
    and (nvl(remain_i, 0) + nvl(remain_o, 0)) >= 200;

 

결과 화면5

 

 

-- 2. 삭제
delete from remain where remain_year = '2020'
    and (nvl(remain_i, 0) + nvl(remain_o, 0)) >= 200;

 

결과 화면6

 

 

 

• 2020년도 구매금액이 3천만원 이상인 회원의 마일리지 점수를 20만으로 하여 수정하시오.

-- 2020년도 구매금액이 3천만원 이상인 회원의 마일리지 점수를 20만으로 하여 수정하시오.
select mem_id 회원ID, mem_name 성명, mem_mileage 마일리지 
    from member
    where exists(select sum(cart.cart_qty * prod.prod_sale) 
        from prod, cart
        where cart.cart_no like '2020%'
        and cart.cart_prod = prod.prod_id
        and cart.cart_member = member.mem_id
        having sum(cart.cart_qty * prod.prod_sale) > 30000000);

 

결과 화면7

 

 

-- 1. 상품 테이블의 총 입고수량 조회
select prod_id 상품코드, sum(nvl(prod_qtyin, 0)) 총입고수량
    from prod
    group by prod_id;

 

결과 화면8 : 원래는 총판매수량이 0으로 나옴 (업데이트 한 뒤 결과물이라 위와 같이 나온 것임)

 

 

-- 2. 모든 상품의 2020년도 상품별 입고수량 합산하여 조회
select prod.prod_id 상품코드, sum(nvl(buyprod.buy_qty,0)) 매입수량
    from prod left outer join buyprod 
        on (prod.prod_id = buyprod.buy_prod and buyprod.buy_date between '2020-01-01' and '2020-12-31')
    group by prod.prod_id;
    
select prod.prod_id 상품코드, sum(nvl(buyprod.buy_qty,0)) 매입수량
    from prod, buyprod 
    where prod.prod_id = buyprod.buy_prod 
    and buyprod.buy_date between '2020-01-01' and '2020-12-31'
    group by prod.prod_id
    order by prod_id;

 

결과 화면9

 

 

-- 3. 2020년도 상품별 입고수량 합산하여 갱신
update prod
    set prod.prod_qtyin = 
        (select sum (buyprod.buy_qty)
            from buyprod
            where prod.prod_id = buyprod.buy_prod
            and buyprod.buy_date between '2020-01-01' and '2020-12-31'
        );

 

 

-- 4. 수행 확인
select prod_id 상품코드, sum(nvl(prod_qtyin, 0)) 총입고수량
    from prod
    group by prod_id;

 

결과 화면10

 

 

 

• 상품 모두의 2020년도 판매수량을 합산하여 상품테이블의 총판매수량 column 갱신하시오.

-- 상품 모두의 2020년도 판매수량을 합산하여 상품테이블의 총판매수량 column 갱신
-- 1. 상품 테이블의 총판매수량 조회
select prod_id 상품코드, sum(nvl(prod_qtysale, 0)) 총판매수량
    from prod
    group by prod_id;

 

결과 화면11 : 원래는 총판매수량이 0으로 나옴 (업데이트 한 뒤 결과물이라 위와 같이 나온 것임)

 

 

-- 2. 장바구니 테이블에서 2020년도 상품별 판매수량 합산하여 조회
select prod_id 상품코드, sum(nvl(cart_qty, 0)) 매출수량
    from prod left outer join cart
    on (prod_id = cart_prod and cart_no like '2020%')
    group by prod_id;

 

결과 화면12

 

 

-- 3. 2020년도 상품별 판매수량 합산하여 갱신하시오?
update prod
    set prod_qtysale = (
        select nvl(sum(cart_qty), 0)
        from cart
        where cart_prod = prod_id and cart_no like '2020%');

 

 

-- 4. 수행 결과 확인(인증)
select sum(nvl(prod_qtyin, 0)), sum(nvl(prod_qtysale, 0))
    from prod;

select sum(buy_qty)
    from buyprod
    where buy_date between '2020-01-01' and '2020-12-31';

select sum(cart_qty)
    from cart
    where cart_no like '2020%';

 

결과 화면13

 

 

 

• 상품 모두의 2020년도 입고수량, 판매수량을 합산하여 재고수(remain)테이블의 입고, 출고, 현 재고 coulmn을 수정하시오.

-- 상품 모두의 2020년도 입고수량, 판매수량을 합산하여 재고수(remain)테이블의 입고, 출고, 현 재고 coulmn을 수정
-- 1. 2020년도 입고수량 및 판매수량을 서브쿼리로 조회
select prod_id, 
    (select sum(nvl(buy_qty, 0)) from buyprod
        where prod_id = buy_prod
        and to_char(buy_date, 'yyyy') = '2020') as int_amt,
    (select sum(nvl(cart_qty, 0)) from cart
        where prod_id = cart_prod
        and cart_no like '2020%') as out_amt
    from prod;

 

결과 화면14

 

 

-- 2. 1) 번에의 쿼리를  Inline View 로 적용하여 재고수 테이블 수정
-- ( 다중행, 다중열 서브쿼리는 자주 사용하지는 않지만 Update 할 때 유용하게 사용할 수 있음.)
update remain
    set (remain_i, remain_o, remain_j_99) =
        (select in_amt, out_amt, remain_j_00 + in_amt - out_amt
        from
            (select prod_id,
                (select sum(nvl(buy_qty,0))
                    from buyprod
                    where prod_id = buy_prod
                    and to_char(buy_date, 'yyyy') = '2020') as in_amt,
                (select sum(nvl(cart_qty,0))
                    from cart
                    where prod_id = cart_prod
                    and cart_no like '2020%') as out_amt
            from prod) a
        where remain_prod = a.prod_id)
    where remain_year = '2020';

 

 

-- 3. 수행 결과 확인(검증)
select * from remain where remain_year = '2020';

 

결과 화면15

 

 

-- 3. 수행 결과 확인(검증)
select sum(nvl(remain_i, 0)), sum(nvl(remain_o, 0))
    from remain
    where remain_year = '2020';

 

결과 화면16

 

 

+ 2024년도 마감처리, 2019년도 마감처리 작업

-- 2023년도 마감처리
insert into remain(remain_year, remain_prod, remain_j_00, remain_i, remain_o, remain_j_99, remain_date)
    select '2024', prod_id, to_number(substr(prod_id, -2)), 10, 7, to_number(substr(prod_id, -2))+10-7, sysdate from prod;

-- 2019년도 마감처리
insert into remain(remain_year, remain_prod, remain_j_00, remain_i, remain_o, remain_j_99, remain_date)
    select '2020', prod_id, to_number(substr(prod_id, -2)), 20, 8, to_number(substr(prod_id, -2))+20-8, sysdate from prod;

select * from remain;

 

 

 

테이블 생성 (복사)

 

-- table 생성 구문
create table 테이블 이름
    as 서브쿼리;

 

-- 재고수 테이블을 복사하여 remain2 테이블을 생성하시오.
create table remain2
    as select * from remain;

 

결과 화면17

 

 

-- delete와 truncate의 차이
-- delete : 삭제 후 롤백 가능
-- truncate : 삭제 후 롤백 불가능 (커밋까지 완료함)

-- 조회 & 롤백
select * from remain2;
rollback;


-- 테이블의 내용을 모두 삭제
-- 롤백 시 테이블 내용 삭제 전으로 돌아감
delete from remain2;

-- 삭제 후 커밋 -> 롤백 시 돌아가지 않음
truncate table remain2;

-- 추후 테스트용 테이블 삭제
drop table remain2;

 

 

• 재고 수 테이블에서 2020년도 자료 중 입고수량 + 출고수량이 200개 이상인 자료를 삭제하시오.

-- 1. 조회
select remain_year 연도, remain_prod 상품, remain_i 입고량, remain_o 출고량, remain_i + remain_o 합계수량
    from remain
    where remain_year = '2020'
    and (nvl(remain_i, 0) + nvl(remain_o, 0)) >= 200;

 

 

-- 2. 삭제
delete from remain where remain_year = '2020'
    and (nvl(remain_i, 0) + nvl(remain_o, 0)) >= 200;