[오라클] PK - FK , 도시락 배달 테이블 생성하기

PK 와 FK의 관계를 가진 테이블을 생성하기

1) 생성 --> 부모 테이블 먼저 자식 테이블을 나중에
2) 삭제나 수정 --> 자식 테이블 먼저, 부모 테이블을 나중에

 

member 테이블 생성하기

DROP TABLE members;

CREATE TABLE members(
    seqno NUMBER CONSTRAINTS member_seq_pk PRIMARY KEY,
    user_id VARCHAR2(10) CONSTRAINTS member_id_uq UNIQUE,
    user_name VARCHAR2(20) CONSTRAINTS member_name_nn NOT NULL,
    birthday DATE CONSTRAINTS member_birth_nn NOT NULL,
    gender CHAR(1) CONSTRAINTS member_gender_ck CHECK (gender IN('F','M'))
    -- member_use CHAR(1)
);

DROP SEQUENCE member_seq;
CREATE SEQUENCE member_seq;

INSERT INTO members
VALUES(
    member_seq.NEXTVAL
    , 'SON'
    , '손오공'
    , '00/01/23'
    ,'M'
);
    
INSERT INTO members
VALUES(
    member_seq.NEXTVAL
    , 'JEON'
    , '전우치'
    , '93/12/05'
    , 'M'
    );
    
INSERT INTO members
VALUES(
    member_seq.NEXTVAL
    , 'KIM'
    , '김순희'
    , '22/01/03'
    , 'F'
    );
    
COMMIT;


SELECT * FROM members;

 

purchase 테이블 생성

DROP TABLE PURCHASE;

CREATE TABLE purchase (
    seqid NUMBER CONSTRAINT purchase_seq_pk PRIMARY KEY,
    item VARCHAR2(50) CONSTRAINT purchase_item_nn NOT NULL,
    price NUMBER(10) DEFAULT 10000,
    pday DATE DEFAULT SYSDATE,
    quantity NUMBER(3) DEFAULT 1,
    seqno NUMBER REFERENCES members(seqno) ON DELETE CASCADE
    -- 부모 타입과 완전히 동일해야한다.
);

/* 
외래키 FK 는 부모의 PK 로 생성하는 것이 보통이나 UNIQUE 도 가능하다.
  
CREATE TABLE purchase2(
    seqid NUMBER CONSTRAINTS purchase_seq_pk PRIMARY KEY,
    item VARCHAR2(50) CONSTRAINTS purchase_item_nn NOT NULL,
    price NUMBER(10) DEFAULT 10000,
    pday DATE DEFAULT SYSDATE,
    quantity NUMBER(3) DEFAULT 1,
    userid VARCHAR2(10) REFERENCE members(seqno) ON DELETE CASCADE
    -- 이름이 달라도 상관없다.
);

*/
DROP SEQUENCE purchase_seq;

CREATE SEQUENCE purchase_seq;


-- 손오공이 책을 1권 구매
INSERT INTO purchase
(seqid, item, price,seqno)
VALUES
(purchase_seq.NEXTVAL, '자바의 정석', 24000 ,1);

-- 전우치가 책을 2권 구매
INSERT INTO purchase
(seqid, item, price,seqno)
VALUES
(purchase_seq.NEXTVAL, '혼공 파이썬', 16000*2 ,2);

-- 김순희가 V3 1 카피 구매
INSERT INTO purchase
(seqid, item, price,seqno)
VALUES
(purchase_seq.NEXTVAL, 'V3', 20000 ,3);

-- 손오공이 '기계식 청축 키보드' 를 1개 구매
INSERT INTO purchase
(seqid, item, price,seqno)
VALUES
(purchase_seq.NEXTVAL, '기계식 청축 키보드', 320000 ,1);

SELECT * FROM purchase;

 

[문제] 구매 번호, 구매인 이름, 아이템명, 구매 가격을 조회하시오

SELECT p.seqid, m.user_name, p.item, p.price
FROM members m, purchase p
WHERE m.seqno = p.seqno;

 

도시락 배달 테이블 생성 연습문제

다음과 같은 조건으로 3개의 테이블과 1개의 requence 객체를 생성하는 쿼리문을 작성하시오.

1) 테이블 : lunchbox (판매하는 도시락 정보)

컬럼명	타입과 길이	제약 조건	기본값
lunchbox_id	가변길이 문자열 10자리	PK	없음
menu_title	가변길이 문자열 50자리	not null	없음
price	숫자 7자리	not null	없음

CREATE TABLE lunchbox(
    lunchbox_id VARCHAR2(10) PRIMARY KEY,
    menu_title VARCHAR2(50) NOT NULL, 
    price NUMBER(7) NOT NULL
);

 

2) 테이블 : customer (고객 정보 테이블)

컬럼명	타입과 길이	제약 조건	기본값
cust_id	가변길이 문자열 10자리	PK	없음
cust_name	가변길이 문자열 30자리	not null	없음
address	가변길이 문자열 100자리	not null	없음
phone	가변길이 문자열 20자리	not null	없음

CREATE TABLE customer(
    cust_id VARCHAR2(10) PRIMARY KEY
    , cust_name VARCHAR2(30) NOT NULL
    , address VARCHAR2(100) NOT NULL
    , phone VARCHAR2(20) NOT NULL
);

 

3) 테이블 : order (주문 정보 테이블)

컬럼명	타입과 길이	제약 조건	기본값
order_id	숫자	PK	없음
cust_id	가변길이 문자열 10자리	FK	없음
lunchbox_id	가변길이 문자열 10자리	FK	없음
order_count	가변길이 문자열 20자리	not null	1
order_date	날짜		sysdate

CREATE TABLE orders(
    order_id NUMBER(10) PRIMARY KEY
    , cust_id VARCHAR2(10) REFERENCES customer(cust_id) ON DELETE CASCADE 
    , lunchbox_id VARCHAR2(10) REFERENCES lunchbox(lunchbox_id) ON DELETE CASCADE
    , order_count VARCHAR2(20) DEFAULT 1 NOT NULL
    , order_date DATE DEFAULT SYSDATE
);

 

4) 시퀀스 : order_seq

주문 정보 테이블에서 사용할 일련번호를 위한 sequence 객체

CREATE SEQUENCE order_seq;

 

lunchbox 테이블에 도시락 종류 5개 이상 필요한 만큼 입력하고 입력된 결과를 조회

insert into lunchbox values ('menu01', '숯불직화구이', 6500);
insert into lunchbox values ('menu02', '치킨제육', 4400);
insert into lunchbox values ('menu03', '돈까스도련님', 7000);
insert into lunchbox values ('menu04', '매화(치킨, 연어구이)', 10000);
insert into lunchbox values ('menu05', '순살 고등어 간장구이', 12000);

SELECT * FROM lunchbox;
COMMIT;

 

고객 정보 5명 이상 필요한 만큼 입력하고 입력된 결과를 조회

insert into customer values ('hong','홍길동','서울시 강남구 역삼동','010-1111-2222');
insert into customer values ('lim','임꺽정', '부산시 충장로1가','010-2222-1212');
insert into customer values ('son','전우치', '인천시 연수구','010-3333-3434');
insert into customer values ('park','박가가','광주시 무산대로 1234','010-1234-5656');
insert into customer values ('kim','김나나', '서울시 동작구 사당동','010-5678-8989');
SELECT * FROM customer;
COMMIT;

 

고객이 도시락을 주문한 정보 10개 이상 필요한 만큼 order 테이블에 입력하고 입력된 결과를 조회

insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'hong', 'menu01', 2);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'lim', 'menu02', 3);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'son', 'menu05', 1);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'kim', 'menu03', 1);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'hong', 'menu04', 5);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'hong', 'menu01', 1);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'son', 'menu02', 3);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'park', 'menu05', 1);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'kim', 'menu02', 1);
insert into orders (order_id, cust_id, lunchbox_id, order_count) values(order_seq.nextval, 'son', 'menu05', 1);
SELECT * FROM orders;
COMMIT;

 

“치킨” 이라는 단어가 포함된 도시락 종류를 조회하는 쿼리문을 작성하시오

SELECT lunchbox_id, menu_title
FROM lunchbox
where menu_title like '%치킨%';

 

도시락 가격이 10,000원 이하인 도시락을 조회하는 쿼리문을 작성하시오

SELECT price, menu_title
FROM lunchbox
where price <= 10000;

 

고객이 자신이 주문한 도시락 내역을 아래와 같은 정보로 조회하는 쿼리문을 작성하시오.

고객이름 / 도시락 이름 / 가격 / 주문수량 / 주문금액 / 주문날짜

select c.cust_name "고객 이름", l.menu_title "도시락 이름 ", l.price "가격", o.order_count "주문 수량"
        , l.price*o.order_count "주문 금액", o.order_date "주문 날짜"
from lunchbox l, orders o, customer c
where l.lunchbox_id = o.lunchbox_id
and
o.cust_id = c.cust_id;

 

 

 

조인 추가 문제

사원 별로 이른과 급여 , 부서번호 및 자신 부서의 평균 급여를 조회하시오. (평균 급여는 소수점 2자리까지 출력)

SELECT FIRST_NAME "이름", LAST_NAME "성", SALARY "급여", DEPARTMENT_ID "부서번호", "부서 평균 급여"
FROM EMPLOYEES OUTER JOIN
(SELECT DEPARTMENT_ID, TO_CHAR(ROUND(AVG(SALARY), 2), '9999,999.99') AS "부서 평균 급여"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID) tbl
USING (DEPARTMENT_ID);