오라클 08) 북테이블 정규화, 3테이블, 더블조인, 시퀀스

--책 테이블 정규화
--컬럼 : BOOKCODE(책코드), BOOKNAME(책이름), GUBUN(분류코드), PUBCODE(출판사코드), PRICE(가격), SALEPER(할인율), REGDATE(등록일자)

--분류코드
DROP TABLE GUBUN;

CREATE TABLE GUBUN(
GCODE CHAR(3),
GNAME VARCHAR2(50) NOT NULL,
CONSTRAINT PK_GUBUN_GCODE PRIMARY KEY (GCODE)
);

INSERT INTO GUBUN (GCODE,GNAME)  VALUES ('C01','IT');
INSERT INTO GUBUN (GCODE,GNAME)  VALUES ('C02','요리');
INSERT INTO GUBUN (GCODE,GNAME)  VALUES ('C03','만화');

SELECT * FROM GUBUN;

--출판사 코드
DROP TABLE PUB;

CREATE TABLE PUB(
PCODE CHAR(4),
PNAME VARCHAR2(50) NOT NULL,
PTELL VARCHAR2(20),
CONSTRAINT PK_PUB_PCODE PRIMARY KEY (PCODE)
);

INSERT INTO PUB (PCODE,PNAME,PTELL)  VALUES ('P001','창비','111-111-1111');
INSERT INTO PUB (PCODE,PNAME,PTELL)  VALUES ('P002','어울림','222-222-2222');
INSERT INTO PUB (PCODE,PNAME,PTELL)  VALUES ('P003','세광','333-333-3333');


SELECT * FROM PUB;

--북 코드
DROP TABLE BOOK2;

CREATE TABLE BOOK2(
BOOKCODE CHAR(4),
BOOKNAME VARCHAR2(50) NOT NULL,
GCODE CHAR(3) NOT NULL,
PCODE CHAR(4) NOT NULL,
PRICE NUMBER(10) DEFAULT 0 NOT NULL,
SALEPER NUMBER(5,2),
REGDATE DATE DEFAULT SYSDATE,
CONSTRAINT PK_BOOK_BOOKCODE PRIMARY KEY (BOOKCODE),
CONSTRAINT FK_BOOK_GCODE FOREIGN KEY (GCODE) REFERENCES GUBUN(GCODE),
CONSTRAINT FK_BOOK_PCODE FOREIGN KEY (PCODE) REFERENCES PUB(PCODE)
);

INSERT INTO BOOK2 (BOOKCODE,BOOKNAME,GCODE,PCODE,PRICE,SALEPER)  VALUES ('B001','엘사','C01','P001',15000,0.2);
INSERT INTO BOOK2 (BOOKCODE,BOOKNAME,GCODE,PCODE,PRICE,SALEPER)  VALUES ('B002','안나','C02','P002',10000,0.3);
INSERT INTO BOOK2 (BOOKCODE,BOOKNAME,GCODE,PCODE,PRICE,SALEPER)  VALUES ('B003','올라프','C03','P003',12000,0.1);



SELECT * FROM BOOK2;


--판매 테이블
DROP TABLE BSALES;

CREATE TABLE BSALES(
SEQ NUMBER(7),
SALEDATE CHAR(10) NOT NULL,
BOOKCODE CHAR(4) NOT NULL,
QTY NUMBER(3),
AMOUNT NUMBER(10) NOT NULL,
REGDATE DATE DEFAULT SYSDATE,
CONSTRAINT PK_BSAELS_SEQ PRIMARY KEY (SEQ),
CONSTRAINT FK_BSAELS_BOOKCODE FOREIGN KEY (BOOKCODE) REFERENCES BOOK2(BOOKCODE)
);

-- 자동으로 SEQ 증가
INSERT INTO BSALES (SEQ,SALEDATE,BOOKCODE,QTY,AMOUNT) VALUES ((SELECT NVL(MAX(SEQ) + 1,1) FROM BSALES),'2021-04-30','B001', 2, 28000);
INSERT INTO BSALES (SEQ,SALEDATE,BOOKCODE,QTY,AMOUNT) VALUES (2,'2021-04-30','B002',3, 21000);
--시퀀스를 이용한 자동으로 SEQ 증가
INSERT INTO BSALES (SEQ,SALEDATE,BOOKCODE,QTY,AMOUNT) VALUES (S_SEQ.NEXTVAL,'2021-04-30','B003',3, 20000);
INSERT INTO BSALES (SEQ,SALEDATE,BOOKCODE,QTY,AMOUNT) VALUES (S_SEQ.NEXTVAL,'2021-04-30','B002',3, 21000);
INSERT INTO BSALES (SEQ,SALEDATE,BOOKCODE,QTY,AMOUNT) VALUES (S_SEQ.NEXTVAL,'2021-04-29','B002',3, 21000);
INSERT INTO BSALES (SEQ,SALEDATE,BOOKCODE,QTY,AMOUNT) VALUES (S_SEQ.NEXTVAL,'2021-04-28','B002',3, 21000);

--가장 큰 SEQ 조회
SELECT NVL(MAX(SEQ) + 1,1) FROM BSALES;


--시퀀스 생성
DROP SEQUENCE S_SEQ;
CREATE SEQUENCE S_SEQ;
SELECT S_SEQ.NEXTVAL FROM DUAL;  --자동 번호 증가


SELECT * FROM BSALES;


--기간안의 매출 조회 리스트
SELECT *
FROM BSALES
WHERE SALEDATE > '2021-04-29';

SELECT *
FROM BSALES S INNER JOIN BOOK2 B ON (S.BOOKCODE = B.BOOKCODE)
WHERE SALEDATE
BETWEEN '2021-04-28' AND '2021-04-30';



--책코드의 매출 조회 리스트
SELECT S.BOOKCODE, SUM(S.AMOUNT)
FROM BSALES S INNER JOIN BOOK2 B ON (S.BOOKCODE = B.BOOKCODE)
GROUP BY S.BOOKCODE;


--아래 다 그룹바이
--일자별 매출 조회(세일로 그룹바이) 집계 / 2개 조인
SELECT SALEDATE, SUM(AMOUNT)
FROM BSALES
GROUP BY SALEDATE;


-- 책 코드별 매출 조회 집계
SELECT S.BOOKCODE, SUM(S.AMOUNT)
FROM BSALES S INNER JOIN BOOK2 B ON(S.BOOKCODE = B.BOOKCODE)
GROUP BY S.BOOKCODE;



--품목별 매출 합계 집계  3개 조인
SELECT G.GNAME ,B.GCODE, SUM(S.AMOUNT) AMOUNT
FROM BSALES S INNER JOIN BOOK2 B ON(S.BOOKCODE = B.BOOKCODE)
INNER JOIN GUBUN G ON (B.GCODE = G.GCODE)
GROUP BY B.GCODE, G.GNAME;


SELECT G.GNAME ,B.GCODE, SUM(S.AMOUNT) AMOUNT
FROM BSALES S INNER JOIN BOOK2 B ON(S.BOOKCODE = B.BOOKCODE)
INNER JOIN GUBUN G ON (B.GCODE = G.GCODE)
GROUP BY B.GCODE, G.GNAME;

--조인 방법 2
SELECT*
FROM BSALES S, BOOK2 B, GUBUN G
WHERE S.BOOKCODE = B.BOOKCODE (+) 
AND B.GCODE = G.GCODE;


--출판사별 매출 조회 집계
SELECT P.PCODE, MAX(P.PNAME), SUM(S.AMOUNT)
FROM BSALES S LEFT JOIN BOOK2 B ON(S.BOOKCODE = B.BOOKCODE)
RIGHT JOIN PUB P ON (B.PCODE = P.PCODE)
GROUP BY P.PCODE;






COMMIT;