0812_Oracle : EQUI JOIN (2)

실습에 사용하는 테이블 관계를 나타낸 ERD

- 서울서점에 판매했던 책코드(bCode), 책이름(bName), 책가격 (bPrice), 판매날짜(sDate), 고객코드(cNum), 고객이름(cNAme), 수량(qty), 총액을 출력하라.

-- 형식 1, WHERE로 JOIN했을 시
SELECT b.bCode, bName, bPrice, sDate, s.cNum, cName, qty, bPrice * qty amt
FROM BOOK b, dsale d, sale s, cus c
WHERE b.bCode = d.bCode AND d.sNum = s.sNum AND s.cNum = c.cNum AND cName = '서울서점'
ORDER BY cNum;

-- 형식 2
SELECT b.bCode, bName, bPrice, sDate,
    s.cNum, cName, qty, bPrice * qty amt
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE cName = '서울서점'
ORDER BY cNum;

cNum을 보면 숫자가 다른데, 서울서점의 이름인 곳이 중복적으로 있었음을 확인할 수 있다.

 

- 판매된 책코드(bCode), 책이름(bName), 판매수량의 합(qty), 판매금액합 : 책코드 오름차순 으로 출력하라

SELECT b.bCode 책코드, bName 책이름, SUM(qty) "판매수량의 합", SUM (bPrice * qty) "판매금액 합"
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
ORDER BY b.bCode;

- 판매된 책코드(bCode), 책이름(bName), 판매수량의 합(qty) : 단 판매수량이 80개 이상인 것만 출력

SELECT * FROM (
SELECT b.bCode 책코드, bName 책이름, SUM(qty) "판매수량의 합"
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
ORDER BY b.bCode
) WHERE "판매수량의 합" >= 80;

SELECT b.bCode 책코드, bName 책이름, SUM(qty) "판매수량의 합"
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
HAVING SUM(qty) >= 80;

GROUP BY로 정렬한 것은 HAVING을 써서 해당하는 조건을 만족하는 것만 출력하게 할 수 있다.

- 판매된 책코드(bCode), 책이름(bName) 중복을 배제하고 출력하라.

-- 서브쿼리
SELECT bCode, bName 
FROM BOOK
WHERE bCode IN (SELECT bCode FROM dsale);

-- 조인
SELECT b.bCode "판매된 책코드", bName "책이름"
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
HAVING SUM(qty) > 0;

-- DISTINCT
SELECT DISTINCT b.bCode "판매된 책코드", bName "책이름"
FROM book b
JOIN dsale d ON b.bCode = d.bCode;

- 판매된 책 중 판매권수의 합이 가장 큰 책코드, 책이름 출력

SELECT b.bCode 책코드, bName 책이름, SUM(qty) "판매수량의 합",
    RANK() OVER(ORDER BY SUM(qty) DESC) 순위
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName;
-- 이 쿼리로 팔린 것들의 순위를 매길 수 있다.

-- 서브쿼리 방법-1
SELECT 책코드, 책이름 FROM (
    SELECT b.bCode 책코드, bName 책이름,
    RANK() OVER(ORDER BY SUM(qty) DESC) 순위
    FROM book b
    JOIN dsale d ON b.bCode = d.bCode
    GROUP BY b.bCode, bName
) WHERE 순위 = 1;

-- 방법-2
SELECT b.bCode 책코드, bName 책이름
FROM book b
JOIN dsale d ON b.bCode = d.bCode
GROUP BY b.bCode, bName
HAVING SUM(qty) = (
    SELECT MAX( SUM(qty))
    FROM book b1
    JOIN dsale d1 ON b1.bCode = d1.bCode
    GROUP BY b1.bCode, bName
);

-- 판매현황 : 책코드(bCode), 책이름(bName), 책가격(bPrice), 출판사번호(pNum), 출판사이름(pName), 판매일자(sDate), 판매고객번호(cNum), 판매고객이름(cName), 판매수량(qty), 금액(bPrice * qty)

book (bCode, bName, pNum, bPrice)

pub (pNum, pName)

sale (sNum, sDate, cnum)

dsale (sNum, bCode, qyt)

cus (cNum, cName)

 

- 올해의 판매현황을 출력하시오.

SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate,
    s.cNum, cName, qty, bPrice * qty amt
FROM book b
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(sDate, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY');

JOIN 이 끝나는 곳까지를 테이블이라고 생각하면 편하다. 이 후에 WHERE을 붙여 조건을 주면 그 조건에 해당하는 값들만 출력된다.

- 작년의 판매현황을 출력하시오.

 

- 고객번호(cNum), 고객이름(cName), 년도, 판매금액합 : 고객번호오름차순, 년도오름차순

book(bCode, bPrice)

dsale (sNum, bCode, qty)

sale ( sNum, sDate, cNum)

cus ( cNum, cName)

SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYY') 년도, SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYY')
ORDER BY cNum, 년도;

-- 고객의 작년과 올해 판매 현황

-- 고객의 이번달 판매금액(고객코드, 고객명, 금액) / 소비자는 고객(회원)일 수 있고 비회원일 수 있음 여기서는 고객만!

-- 고객의 전달 판매금액(고객코드, 고객명, YYYYMM, 금액)

SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYY') 년도, SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(sDate, 'YYYY') <= 1
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYY')
ORDER BY cNum, 년도;

-- 고객의 이번달 판매금액(고객코드, 고객명, 금액)
SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYYMM'), SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(sDate, 'YYYYMM') = TO_CHAR(SYSDATE, 'YYYYMM')
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYYMM')
ORDER BY cNum;

-- 고객의 전달 판매금액(고객코드, 고객명, YYYYMM, 금액)
SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYYMM') 날짜, SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') = TO_CHAR(sDate, 'YYYYMM')
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYYMM')
ORDER BY cNum;

SELECT s.cNum, cName, TO_CHAR(sDate, 'YYYYMM') 날짜, SUM(bPrice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(SYSDATE - INTERVAL '1' MONTH, 'YYYYMM') = TO_CHAR(sDate, 'YYYYMM')
GROUP BY s.cNum, cName, TO_CHAR(sDate, 'YYYYMM')
ORDER BY cNum;

ADD_MONTHS('날짜', 더하고 싶은 숫자) 를 하면 알아서 1달을 빼준다. 여기서 YYYYMM을 뽑아서 바로 빼지 않는 이유는 202101에서 1달을 빼면 202100이 되기 때문에 알아서 날짜연산을 해주는 ADD_MONTHS를 이용해야한다. (혹은 INTERVAL '숫자' 단위 를 이용한다)

작년과 올해의 판매현황

고객의 이번달 판매현황

고객의 저번달 판매현황

여기까지 EQUI JOIN과 EQUI JOIN를 예제로 하여금 어떻게 쓰는지 알아보았다.