210902_

--cart1에 상품추가
insert into cart1 values(seq_shop.nextval,1,2,sysdate);  --1번 나이키 추가
insert into cart1 values(seq_shop.nextval,5,1,sysdate);  --5번 추가
insert into cart1 values(seq_shop.nextval,2,3,sysdate); 
insert into cart1 values(seq_shop.nextval,4,2,sysdate); 
commit;

--shop에 없는 상품 추가
--오류 보고 -
--ORA-02291: 무결성 제약조건(ADMIN.CART_FK_NUM)이 위배되었습니다- 부모 키가 없습니다
--없는 번호
insert into cart1 values(seq_shop.nextval,7,2,sysdate);

--cart2에 상품추가
insert into cart2 values(seq_shop.nextval,3,2,sysdate);
insert into cart2 values(seq_shop.nextval,4,1,sysdate);


--cart1에 1번,5번 담김..1번 삭제
--오류 보고 -
--ORA-02292: 무결성 제약조건(ADMIN.CART_FK_NUM)이 위배되었습니다- 자식 레코드가 발견되었습니다
--on delete cascade를 안 해줬기에
delete from shop where num=1;

delete from shop where num=3; --3번은 cart에 없으므로 삭제됨


--cart2에 담긴 3,4번 삭제해보기
--on delete cascade 설정해놔서 cart2랑 shop 둘 다 삭제됨
delete from shop where num=3;



rollback;  --3번 삭제 취소

commit;


--join위해서 cart2에 insert 추가
insert into cart2 values(seq_shop.nextval,1,2,sysdate);
insert into cart2 values(seq_shop.nextval,3,1,sysdate);
insert into cart2 values(seq_shop.nextval,2,4,sysdate);
commit;

--테이블 출력
select * from shop;
select * from cart1;
select * from cart2;

--cart의 상품을 shop테이블과 join
--방법1..풀네임으로 쓰기
select cart1.idx, shop.num, shop.sangpum, shop.color, cart1.cnt, cart1.guipday 
from shop, cart1 where shop.num=cart1.num;
--방법2
select c.idx, s.num, s.sangpum, s.color, c.cnt, c.guipday from shop s, cart1 c
where s.num=c.num;
--방법3..join테이블에 중복되지 않는 컬럼명은 테이블명 생략 가능
select idx, c.num, sangpum, color, cnt, guipday from shop s, cart1 c
where s.num=c.num;



------------------------


--foreign key 사용해서 board, answer join 테이블 만들기

create table board(bno number(3) constraint board_pk_bno primary key,
writer varchar2(30), subject varchar2(30), writeday date);

--board에 insert
insert into board values(seq1.nextval, '요다','오늘 뭐하세요?',sysdate);
insert into board values(seq1.nextval, '림짱','점심 뭐 먹을까',sysdate);
insert into board values(seq1.nextval, '루크','운동하러 가야 되는데',sysdate);
insert into board values(seq1.nextval, '볼트','나 완전 빨라',sysdate);
insert into board values(seq1.nextval, '오월이','넷플에서 영화보자',sysdate);
commit;

--answer라는 테이블 생성, bno를 외부키로 설정할 예정
--원글 삭제시 댓글 자동삭제되도록 만들기
create table answer(num number(5) constraint answer_pk_num primary key,
bno number(3) constraint answer_fk_bno references board(bno) on delete cascade,
nickname varchar2(20), content varchar2(50));

select * from board;

--board에 댓글 추가해보기
--원하는 bno에 댓글 넣으려면 bno 번호 쓰기
insert into answer values(seq1.nextval, 15, '곰돌','공부여..');
insert into answer values(seq1.nextval, 16, '여엉','로제떡볶이에 볶읔밥!!');
insert into answer values(seq1.nextval, 19, '현자타임','운동 정말 싫어');
insert into answer values(seq1.nextval, 20, '루이비','고양이계의 우사인볼트');
insert into answer values(seq1.nextval, 23, '순탄이','뭐 볼까?');
commit;

--join으로 출력
--글번호,작성자,작성자글,댓글단사람,댓글내용,원글작성날짜
select b.bno 글번호, writer 작성자, subject 제목, nickname 댓글단사람, content 댓글, 
writeday 원글작성일 from board b, answer a where b.bno=a.bno;

--원글 삭제하면 댓글 삭제되게 해보기
delete from board where bno=15;

--확인
select * from answer;

rollback;

--board 테이블 삭제..불가능
--오류 보고 ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다
drop table board;

--자식테이블 answer 삭제
drop table answer;

--자식 테이블 삭제 후 부모테이블 board 삭제 가능
drop table board;



---------------------------------


--join 외부키 문재
--시퀀스 생성..seq_food 1부터 5씩 증가, 캐시o
--부모 테이블명: food(fno,food,price,shop(상호명), loc(위치)
--제약조건은 알아서 
--부모테이블 최소 5개 insert

--자식테이블: jumun(외부키는 food의 fno를 설정(부모데이터 삭제시 자동삭제)
--num,주문자,음식번호,주문자(name), 음식번호_외부키, 주소(addr주문자주소)

--출력
--주문번호 주문자 음식명 가격 상호명 가게위치 주문자주소
--오름차순 출력(주문자)


create sequence seq_food start with 1 increment by 5;


create table food(fno number(5) constraint food_pk_fno primary key, food varchar2(30),
price number(30), shop varchar2(30), loc varchar2(30));


insert into food values(seq_food.nextval,'로제떡볶이','7000','행컵','서울시 광진구');
insert into food values(seq_food.nextval,'김밥','2000','바르다김선생','서울시 종로구');
insert into food values(seq_food.nextval,'우동','5000','역전우동','서울시 강남구');
insert into food values(seq_food.nextval,'치즈돈까스','9000','미소야','경기도 하남시');
insert into food values(seq_food.nextval,'훠거','25000','훠궈야','서울시 구로구');
commit;

select * from food;

create table jumun(num number(5) constraint jumun_pk_num primary key,
fno number(5) constraint jumun_fk_fno references food(fno) on delete cascade,
name varchar2(20), addr varchar2(30));

insert into jumun values(seq1.nextval,16,'요다','서울시 동대문구');
insert into jumun values(seq1.nextval,26,'림짱','서울시 강북구');
insert into jumun values(seq1.nextval,36,'루크','서울시 서초구');
insert into jumun values(seq1.nextval,31,'오월이','서울시 성북구');
insert into jumun values(seq1.nextval,21,'순탄이','서울시 광진');
commit;

select * from jumun;

--주문번호 주문자 음식명 가격 상호명 가게위치 주문자주소
--오름차순 출력(주문자)

select num 주문번호, name 주문자 , food 음식명, price 가격, shop 상호명, loc 가게위치, addr
 주문자주소 from food f, jumun j where f.fno=j.fno order by name asc;



------------------------



--view

--view..우리가 만든 조인쿼리문 이용
create or replace view jumunja1 as select num 주문번호, name 주문자 , food 음식명, 
price 가격, shop 상호명, loc 가게위치, addr 주문자주소 from food f, jumun j 
where f.fno=j.fno order by name asc;

--view로 만든 jumunja1 조회
select * from jumunja1;


select * from sawon;

--부서별 인원수와 평균급여를 구해본 후 오류없을 시 buseoview로 생성
select buseo 부서명, count(buseo) 인원수, avg(pay) 평균급여 from sawon group by buseo;

--view로
create view buseoview as select buseo 부서명, count(buseo) 인원수, avg(pay) 평균급여 from sawon group by buseo;

select * from buseoview;

--Q.사 테이블에서 홍보부를 조회하는데 이름의 오름차순으로 조회해서 sawonhongbo 뷰 네임으로 생성
select * from sawon where buseo='홍보부' order by name;

create view sawonhongbo as select * from sawon where buseo='홍보부' order by name;

select * from sawonhongbo;

--뷰 삭제
drop view sawonhongbo;



---------------------------------