0813_Oracle : 숙제 (문제풀기) 못풀음 ㅠㅠ [21.08.23 답 추가]

-- 예제

 

-- 비회원 판매 현황 : cNum, cName, bCode, bName, sDate, bPrice, qty

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

book(bCode, bPrice)

dsale(sNum, bCode, qty)

sale(sNum, cNum)

cus(cNum, cName)

SELECT c.cNum, cName, d.bCode, bName, sDate, bPrice, qty
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
RIGHT OUTER JOIN cus c ON c.cNum = s.cNum;

답 >>

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

 

 

-- 년도별 고객 누적 판매금액이 가장 많은 값 출력

-- 판매년도, cNum, cName, bPrice* qty 합

-- 년도 오름차순으로

SELECT c.cNum, cName, 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 c.cNum = s.cNum
GROUP BY s.cNum, cName;

이 쿼리로 실행해봐도 자꾸 오류가 발생해서 

비교해보았더니 GROUP BY할때 s.cNum으로 GROUP 화 한거면 SELECT 에도 c.cNum이 아니라 s.cNum으로 써줘야 한다.

SELECT TO_CHAR(sdate, 'YYYY') 년도, s.cNum, cName, SUM(bPrice * qty) 합, 
    RANK() OVER(PARTITION BY TO_CHAR(sdate, 'YYYY') ORDER BY SUM(bPrice * qty) DESC) 순위
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON c.cNum = s.cNum
GROUP BY s.cNum, cName, TO_CHAR(sdate, 'YYYY');

여기서 년도별로 1등만 출력하면 된다.

WITH sb AS (
SELECT TO_CHAR(sdate, 'YYYY') 년도, s.cNum, cName, SUM(bPrice * qty) 합, 
    RANK() OVER(PARTITION BY TO_CHAR(sdate, 'YYYY') ORDER BY SUM(bPrice * qty) DESC) 순위
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON c.cNum = s.cNum
GROUP BY s.cNum, cName, TO_CHAR(sdate, 'YYYY')
)
SELECT 년도, cNum, cName, 합 FROM sb
WHERE 순위 = 1;

-- 년도의 월별 서적의 판매 수량의 합 구하기 : 년도 오름차순, 책코드 오름차순

-- 년도 책코드 책이름 1월 2월 3월 ... 12월

SELECT TO_CHAR(sDate, 'YYYY')년도, TO_CHAR(sDate, 'MM') 월, b.bCode 책코드, bName 책이름, qty 판매수량
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
GROUP BY b.bCode, bName, TO_CHAR(sDate, 'YYYY'), TO_CHAR(sDate, 'MM'), qty
ORDER BY 년도, 책코드;

일단 년도, 월 별로 몇개가 판매되었는지 구해보았다.

 

답 확인 >>

SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, bName, qty
FROM  book b
JOIN  dsale d ON  b.bCode = d.bCode
JOIN  sale s  ON  d.sNum = s.sNum;

SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, bName, 
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '01', qty)),0) M01,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '02', qty)),0) M02,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '03', qty)),0) M03,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '04', qty)),0) M04,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '05', qty)),0) M05,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '06', qty)),0) M06,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '07', qty)),0) M07,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '08', qty)),0) M08,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '09', qty)),0) M09,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '10', qty)),0) M10,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '11', qty)),0) M11,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '12', qty)),0) M12
FROM  book b
JOIN  dsale d ON  b.bCode = d.bCode
JOIN  sale s  ON  d.sNum = s.sNum
GROUP BY TO_CHAR(sDate, 'YYYY'), b.bCode, bName
ORDER BY 년도, bCode;

-- 년도별 소계
SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, bName, 
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '01', qty)),0) M01,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '02', qty)),0) M02,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '03', qty)),0) M03,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '04', qty)),0) M04,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '05', qty)),0) M05,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '06', qty)),0) M06,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '07', qty)),0) M07,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '08', qty)),0) M08,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '09', qty)),0) M09,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '10', qty)),0) M10,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '11', qty)),0) M11,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '12', qty)),0) M12
FROM  book b
JOIN  dsale d ON  b.bCode = d.bCode
JOIN  sale s  ON  d.sNum = s.sNum
GROUP BY TO_CHAR(sDate, 'YYYY'), ROLLUP((b.bCode, bName))
ORDER BY 년도, bCode;

-- 년도별 소계, 총계
SELECT TO_CHAR(sDate, 'YYYY') 년도, b.bCode, bName, 
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '01', qty)),0) M01,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '02', qty)),0) M02,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '03', qty)),0) M03,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '04', qty)),0) M04,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '05', qty)),0) M05,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '06', qty)),0) M06,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '07', qty)),0) M07,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '08', qty)),0) M08,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '09', qty)),0) M09,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '10', qty)),0) M10,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '11', qty)),0) M11,
	NVL(SUM(DECODE(TO_CHAR(sDate, 'MM'), '12', qty)),0) M12
FROM  book b
JOIN  dsale d ON  b.bCode = d.bCode
JOIN  sale s  ON  d.sNum = s.sNum
GROUP BY ROLLUP(TO_CHAR(sDate, 'YYYY'), (b.bCode, bName))
ORDER BY 년도, bCode;