오라클 05) 서브쿼리, 더블조인

--상품 판매

--상품 마스터 (부모)
DROP TABLE ITEM;  --테이블을 삭제, 삭제해서 다시 만들면 됨

CREATE TABLE ITEM ( -- 테이블 생성
ITEMCODE VARCHAR2(13) NOT NULL,   --상품 코드
ITEMNAME VARCHAR2(100) NOT NULL ,   --상품명
PRICE NUMBER(7) NOT NULL,  
BIGO VARCHAR2(100) NULL, -- 비고
REGDATE DATE DEFAULT SYSDATE, --등록일자  
CONSTRAINT PK_ITEMCODE PRIMARY KEY(ITEMCODE)  --PK  (하나만 잡아야 유일키)
);

DESC ITEM; --속성 확인
SELECT * FROM ITEM;


--세일즈 테이블 (자식)
DROP TABLE SALES;  --테이블 삭제

CREATE TABLE SALES(
SALEDATE VARCHAR2(10),  --판매날짜
SEQ NUMBER(5),  --순번
ITEMCODE VARCHAR2(13) NOT NULL,  --아이템코드
QTY NUMBER(3) NOT NULL,  -- 판매수량
AMOUNT NUMBER(10) NOT NULL,  --판매금액
REGDATE DATE DEFAULT SYSDATE,  --등록일자
CONSTRAINT PK_SALEDATE_SEQ PRIMARY KEY (SALEDATE,SEQ),  -- pk
CONSTRAINT FK_ITEMCODE_ITEM FOREIGN KEY (ITEMCODE) REFERENCES ITEM(ITEMCODE)  --포링키 //어떤 코드를 참조할지 얘기 해줘야댐, 참조가 되는 키는 유일키
);

DESC SALES; --속성 확인
SELECT * FROM SALES;

--상품 마스터 추가
INSERT INTO ITEM (ITEMCODE,ITEMNAME,PRICE) VALUES ('8801','사이다',1000);
INSERT INTO ITEM (ITEMCODE,ITEMNAME,PRICE) VALUES ('8802','콜라',1200);
INSERT INTO ITEM (ITEMCODE,ITEMNAME,PRICE) VALUES ('8803','빅맥',6000);
INSERT INTO ITEM (ITEMCODE,ITEMNAME,PRICE) VALUES ('8804','츄파츕스',500);
SELECT * FROM ITEM;

--판매 추가
INSERT INTO SALES (SALEDATE,SEQ,ITEMCODE,QTY,AMOUNT) VALUES ('2021-04-29',1,'8801',2,2*1000);
INSERT INTO SALES (SALEDATE,SEQ,ITEMCODE,QTY,AMOUNT) VALUES ('2021-04-29',2,'8802',5,5*1200);
INSERT INTO SALES (SALEDATE,SEQ,ITEMCODE,QTY,AMOUNT) VALUES ('2021-04-30',2,'8804',5,5*500);
INSERT INTO SALES (SALEDATE,SEQ,ITEMCODE,QTY,AMOUNT) VALUES ('2021-04-30',3,'8803',6,6*6000);
INSERT INTO SALES (SALEDATE,SEQ,ITEMCODE,QTY,AMOUNT) VALUES ('2021-04-31',4,'8803',3,2*6000);

SELECT * FROM SALES;


--일일 상품 판매 리스트
SELECT S.SALEDATE, S.SEQ, S.ITEMCODE, I.ITEMNAME, S.QTY, S.AMOUNT
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE);


--일일 상품별 집계 판매
SELECT S.SALEDATE, S.ITEMCODE, MAX(I.ITEMNAME) ITEMNAME, SUM(S.QTY) QTY, SUM(S.AMOUNT) AMOUNT
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE)
GROUP BY S.SALEDATE, S.ITEMCODE 
HAVING SUM(S.QTY)>3  
ORDER BY S.SALEDATE, S.ITEMCODE;


--상품별 집계 판매

SELECT S.ITEMCODE, I.ITEMNAME, SUM(S.QTY) QTY, SUM(S.AMOUNT) AMOUNT
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE)
GROUP BY S.ITEMCODE, I.ITEMNAME
ORDER BY S.ITEMCODE;


--일일 상품별 집계 판매
SELECT S.SALEDATE, S.ITEMCODE, MAX(I.ITEMNAME) ITEMNAME, SUM(S.QTY) QTY, SUM(S.AMOUNT) AMOUNT
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE)
GROUP BY S.SALEDATE, S.ITEMCODE  --그룹으로 묶어서 결합
HAVING SUM(S.QTY)>3   --그룹바이 이후의 조건문, 알리아스명이 생성전이어서 SUM
ORDER BY S.SALEDATE, S.ITEMCODE;


--서브쿼리
--평균판매수량보다 적은 판매 상품 리스트
--1) 평균 판매 수량을 구함(AVGQTY)
--2) 평균 미달 상품 리스트

--평균 판매 수량 구하기
SELECT AVG(QTY) AVGQTY 
FROM SALES;

--평균 미달 상품 리스트 구하기
SELECT S.*, I.ITEMNAME
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE)
WHERE QTY < 4;


--WHERE절 서브쿼리
--하나로 만들기 (서브 쿼리) 쿼리문 안에 셀렉트 또 써버리기~
SELECT S.*, I.ITEMNAME
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE)
WHERE QTY < (SELECT AVG(QTY) AVGQTY FROM SALES);

--SELECT절 서브쿼리
--상품 판매 리스트 + 평균 판매 수량  
SELECT S.*, I.ITEMNAME,(SELECT AVG(QTY) AVGQTY FROM SALES) AS AVGOTY
FROM SALES S INNER JOIN ITEM I ON(S.ITEMCODE = I.ITEMCODE);


--더미 테이블을 이용해서 테스트(소수점 자르기)
SELECT TRUNC(2.6666,2) 
FROM DUAL;  --더미 테이블

--상품 판매 리스트 + 평균 판매 수량  (소수점자르기)
SELECT S.*, I.ITEMNAME,TRUNC( (SELECT AVG(QTY) AVGQTY FROM SALES),2) AS AVGOTY
FROM SALES S INNER JOIN ITEM I ON(S.ITEMCODE = I.ITEMCODE);


--FROM절 서브쿼리
--상품 판매 리스트 + 상품별 판매 수량 평균
--상품 판매 리스트
SELECT S.*,I.ITEMNAME
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE);

--상품별 판매 수량 평균
SELECT ITEMCODE, AVG(QTY) QTY  --이름을 QTY로 바꿈
FROM SALES 
GROUP BY ITEMCODE;

--상품 판매 리스트 + 상품별 판매 수량 평균 // 프롬절 조인  //더블조인
SELECT S.*,I.ITEMNAME, IA.QTY QTYAVG--이름을 QTYAVG로 바꿈
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE)
INNER JOIN (SELECT ITEMCODE,AVG(QTY) QTY FROM SALES GROUP BY ITEMCODE) IA ON(S.ITEMCODE = IA.ITEMCODE);


--서브쿼리 연습
--상품 마스터에서 판매되는 평균 단가보다 큰것
SELECT S.*,I.ITEMNAME
FROM SALES S INNER JOIN ITEM I ON (S.ITEMCODE = I.ITEMCODE);

--상품 마스터
SELECT *
FROM ITEM;

--상품마스터의 평균 단가
SELECT AVG(PRICE)
FROM ITEM;

--최종
SELECT *
FROM ITEM
WHERE PRICE > (SELECT AVG(PRICE) FROM ITEM);


--상품 마스터 + 총 판매금액
SELECT *
FROM ITEM;

SELECT SUM(AMOUNT) AVGQTY 
FROM SALES;

--최종
SELECT I.*, CH.AVGQTY
FROM ITEM I INNER JOIN SALES S ON (I.ITEMCODE = S.ITEMCODE)
INNER JOIN (SELECT SUM(AMOUNT) AVGQTY FROM SALES) CH ON (I.ITEMCODE = S.ITEMCODE);


--최종 (셀렉트 서브쿼리)
SELECT i.*, (SELECT SUM(AMOUNT) AVGQTY FROM SALES)
FROM ITEM i;  --상속관계에 있는 컬럼이 중복되어 이름을 따로 지정 ㄱㄱ



--상품 마스터 + 상품별 판매 금액  --다함
SELECT I.ITEMCODE , SUM(AMOUNT) AVGQTY
FROM ITEM I INNER JOIN SALES S ON (I.ITEMCODE = S.ITEMCODE) 
GROUP BY I.ITEMCODE;

--상품 가격만
SELECT ITEMCODE, SUM(AMOUNT) AS SUM
FROM SALES 
GROUP BY ITEMCODE;

-- 최종
SELECT I.*, CC.AC
FROM ITEM I INNER JOIN (SELECT ITEMCODE, SUM(AMOUNT) AS AC FROM SALES GROUP BY ITEMCODE) CC ON (I.ITEMCODE = CC.ITEMCODE);


--용훈이가 알려준거
SELECT I.*, S.SUM
FROM ITEM I INNER JOIN (SELECT ITEMCODE, SUM(AMOUNT) SUM FROM SALES GROUP BY ITEMCODE) S ON (I.ITEMCODE = S.ITEMCODE);

--해석
--상품 마스터
SELECT *
FROM ITEM;

--상품별 판매 금액
SELECT ITEMCODE, SUM(AMOUNT) SUM 
FROM SALES 
GROUP BY ITEMCODE;

--최종 해석
SELECT I.*, NVL(S.AMOUNT,0) AMOUNT --어마운트 값이 널 이라면 0으로 처리
FROM ITEM I LEFT JOIN (SELECT ITEMCODE, SUM(AMOUNT) AMOUNT FROM SALES GROUP BY ITEMCODE) S ON (I.ITEMCODE = S.ITEMCODE)
ORDER BY I.ITEMCODE;
--위에꺼는 동등조인으로 하나도 안팔리면 안나올수 있으므로 아이템을 중심으로 조인 (레프트 조인)


--추가  (날짜 조건 추가)
SELECT I.ITEMCODE, I.ITEMNAME, I.PRICE, NVL(S.AMOUNT,0) AMOUNT, REGDATE, NVL(I.BIGO,' ') BIGO
FROM ITEM I LEFT JOIN 
        (SELECT ITEMCODE, SUM(AMOUNT) AMOUNT 
        FROM SALES 
        WHERE SALEDATE = '2021-04-30'
        GROUP BY ITEMCODE) S 
        ON (I.ITEMCODE = S.ITEMCODE)
ORDER BY I.ITEMCODE;





SELECT *
FROM ITEM;
SELECT *
FROM SALES;






COMMIT;