Oracle Quiz3

[테이블 관계]

DML사용하기

1. CUSTOMER 테이블생성하시오.

-모든컬럼은 VARCHAR2타입

-id컬럼은 3자리,password컬럼은 3자리,name컬럼은 10자리이다.

-id컬럼에 컬럼레벨로 PK제약조건추가한다.

create table customer(id varchar2(3) primary key,password varchar2(3),name varchar2(10))

2. 3명의 고객정보를 추가하시오

'id1', 'p1', '고객1'

'id2', 'p2', '고객2'

'id3', 'p3', '고객3'

insert into customer values('id1', 'p1', '고객1')

insert into customer values('id2', 'p2', '고객2')

insert into customer values('id3', 'p3', '고객3')

3. CUSTOMER테이블의 구조를 복사하여 CUSTOMER1테이블에 붙여넣기하시오.단, CUSTOMER1테이블은 자료가 없어야한다.

create table customer1 as select * from customer where 1=0

4. ORDER_INFO테이블에 info_id컬럼추가하시오. 단, CUSTOMER테이블의 id컬럼을 외래키로 참조한다.

alter table order_info add info_id varchar2(3)

alter table order_info add foreign key (info_id) references customer(id)

//한번에 쓰기

alter table order_info add info_id varchar2(3) constraint order_info_id references customer(id)

5. ORDER_INFO테이블에 3건의 주문자정보를 변경하시오

info_id='id2' info_no=1

info_id='id1' info_no=2

info_id='id2' info_no=3

update order_info set info_id='id2' where info_no in (1,3)

update order_info set info_id='id1' where info_no=2

6. ORDER_LINE테이블에 7건의 주문상세정보를 추가하시오.

(1, 'F001', 2)

(1, 'F002', 1)

(2, 'F003', 5)

(3, 'F004', 1)

(3, 'F001', 1)

(3, 'F002', 1)

(3, 'F003', 1)

insert into order_line values(1, 'F001', 2)

insert into order_line values(1, 'F002', 1)

insert into order_line values(2, 'F003', 5)

insert into order_line values(3, 'F004', 1)

insert into order_line values(3, 'F001', 1)

insert into order_line values(3, 'F002', 1)

insert into order_line values(3, 'F003', 1)

7. 'id2'고객의 주문번호, 주문시간, 상품번호, 상품명, 가격, 수량, 예상금액을 출력하시오. 예상금액은 가격*수량이다.

단, 최근주문번호부터 출력하고, 동일주문번호의 상품번호는 오름차순으로 출력한다.

select info_no 주문번호, to_char(info_date, 'HH:MI') 주문시간, line_prod_no 상품번호, prod_price 가격, line_quantity 수량, 

prod_price *line_quantity 예상금액

from order_info join order_line on info_no=line_info_no

join product on line_prod_no=prod_no

where info_id=(select id from customer where id='id2')

order by 주문번호 desc, 상품번호

8. 'F002'상품의 총주문수량과 총예상금액을 출력하시오.

select (select sum(line_quantity) from order_line where line_prod_no='F002') 총주문수량, 

((select sum(line_quantity) from order_line where line_prod_no='F002')*prod_price) 총예상금액

from product

where prod_no='F002'

9. 상품별 상품번호, 상품명, 총주문수량을 출력하시오.

select line_prod_no,prod_name, sum(line_quantity) 

from order_line join product on line_prod_no=prod_no

group by line_prod_no, prod_name

order by line_prod_no

10. order_info테이블의 주문번호가 3번인 행을 삭제하시오.

단, 주문번호3번에 해당하는 order_line도 삭제되어야한다.

     hint: cascade

alter table order_line drop constraint order_line_info_fk

alter table order_line add foreign key (line_info_no) references order_info(info_no) ON DELETE CASCADE

delete from order_info where info_no=3