0813_Oracle : RIGHT OUTER JOIN, LEFT OUTER JOIN (2) 예제를 통한 실습

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

몇 가지 예제를 통해서 좀 더 실습을 해보자.

 

- 비회원 판매현황을 출력하시오.

- cNum, cName, bCode, bName, sDate, bPrice, userID, qty 출력

SELECT s.cNum, cName, b.bCode, bName, s.sDate, bPrice, userId, qty
FROM book b
JOIN dsale d ON d.bCode = b.bCode
JOIN sale s ON s.sNum = d.sNum
JOIN cus c ON c.cNum = s.cNum
LEFT OUTER JOIN member m ON m.cNum = c.cNum
WHERE userId IS NULL;

-- 회원 판매 현황

SELECT s.cNum, cName, b.bCode, bName, s.sDate, bPrice, userId, qty
FROM book b
JOIN dsale d ON d.bCode = b.bCode
JOIN sale s ON s.sNum = d.sNum
JOIN cus c ON c.cNum = s.cNum
LEFT OUTER JOIN member m ON m.cNum = c.cNum
WHERE userId IS NOT NULL;

-- 고객별 누적판매 금액 (고객번호 cNum, 고객명 cName, 판매금액합) 출력

-- 단, 고객중 책을 한권도 구매하지 않은 고객도 출력

SELECT s.cNum, cName, NVL(SUM(bPrice*qty), 0) 합
FROM book b
JOIN dsale d ON d.bCode = b.bCode
JOIN sale s ON s.sNum = d.sNum
RIGHT OUTER JOIN cus c ON c.cNum = s.cNum
GROUP BY s.cNum, cName
ORDER BY cNum;

-- 고객별 누적 판매 금액 현황

SELECT s.cNum, cName, NVL(SUM(bPrice*qty), 0) 합
FROM book b
JOIN dsale d ON d.bCode = b.bCode
JOIN sale s ON s.sNum = d.sNum
JOIN cus c ON c.cNum = s.cNum
GROUP BY s.cNum, cName
ORDER BY cNum;

-- 고객별 누적판매 금액의 비율

SELECT s.cNum, cName, NVL(SUM(bPrice*qty), 0) 합,
    ROUND(RATIO_TO_REPORT(SUM(bPrice*qty)) OVER() * 100, 1) || '%' 비율
FROM book b
JOIN dsale d ON d.bCode = b.bCode
JOIN sale s ON s.sNum = d.sNum
JOIN cus c ON c.cNum = s.cNum
GROUP BY s.cNum, cName
ORDER BY cNum;