개발새발, 써놓기

-- 예제

-- 비회원 판매 현황 : 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;

예제를 통해 알아보자.

예제를 위한 테이블 생성 및 자료 삽입

CREATE TABLE grade1 
(
    grade VARCHAR2(10) PRIMARY KEY,
    score NUMBER(3)
);
INSERT INTO grade1 ( grade, score ) VALUES ('A', 90);
INSERT INTO grade1 ( grade, score ) VALUES ('B', 80);
INSERT INTO grade1 ( grade, score ) VALUES ('C', 70);
INSERT INTO grade1 ( grade, score ) VALUES ('D', 60);
INSERT INTO grade1 ( grade, score ) VALUES ('F', 0);
COMMIT;

CREATE TABLE score1
(
    hak VARCHAR2(10) PRIMARY KEY,
    score NUMBER(3)
);

INSERT INTO score1 ( hak, score ) VALUES('1', 75);
INSERT INTO score1 ( hak, score ) VALUES('2', 50);
INSERT INTO score1 ( hak, score ) VALUES('3', 90);
INSERT INTO score1 ( hak, score ) VALUES('4', 80);
INSERT INTO score1 ( hak, score ) VALUES('5', 65);
COMMIT;

위의 테이블형태로 Oracle에서 출력하고 싶다.

먼저

위 형태를 출력해보자.

SELECT hak, score, 
(SELECT MAX(score) FROM grade1 WHERE score <= score1.score) gscore
FROM score1;

SELECT hak, s1.score, grade FROM (
    SELECT hak, score, 
        (SELECT MAX(score) FROM grade1 WHERE score <= score1.score) gscore
    FROM score1
) s1
JOIN grade1 s2 ON s1.gscore = s2.score
ORDER BY hak;

ANY : 하나라도 참이면 참

-- 200만원보다 큰 값
SELECT empNo, name, sal
FROM emp
WHERE sal > ANY (2000000, 3000000, 4000000);

ALL : 모두 만족해야 참

-- 400만원 보다 sal이 큰 사람
SELECT empNo, name, sal
FROM emp
WHERE sal > ALL (2000000, 3000000, 4000000);

EXISTS : 하나 이상의 레코드가 존재하면 참 

SELECT bName FROM book
WHERE EXISTS (SELECT * FROM dsale WHERE qty >=10);

qty가 >= 10 인 레코드가 존재하므로 모든 bName이 나온다.

SELECT bName FROM book;

결국 위 쿼리와 같은 의미가 된다.

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

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

- 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;

이번에는 RIGHT OUTER JOIN 과 LEFT OUTER JOIN에 대해 알아보자.

예제를 통해 알아보자.

LEFT OUTER JOIN

b.Code, bName, sNum, qty를 출력하라.

 -- book(bCode, bName), dsale(bCode, sNum, qty)

-- 형식 1
SELECT b.bCode, bName, sNum, qty
FROM book b, dsale d
WHERE b.bCode = d.bCode;
-- EQUI JOIN

SELECT b.bCode, bName, sNum, qty
FROM book b, dsale d
WHERE b.bCode = d.bCode(+);
-- LEFT OUTER JOIN

-- 형식 2
SELECT b.bCode, bName, sNum, qty
FROM book b
JOIN dsale d ON b.bCode = d.bCode;
-- EQUI JOIN

SELECT b.bCode, bName, sNum, qty
FROM book b
LEFT OUTER JOIN dsale d ON b.bCode = d.bCode;
-- LEFT OUTER JOIN

일단 EQUI JOIN을 했을 때의 결과이다.

그냥 EQUI JOIN은 book 테이블과 dsale의 테이블의 교집합만을 구하므로, 팔린 책들만 나오는 것을 볼 수 있다.

LEFT OUTER JOIN을 사용하면, book 테이블에 있는 모든 책들이 나오고 dsale 에 있는 것과 겹치는 것은 JOIN이 되어 나온다.

LEFT 와 RIGHT 는 FROM 뒤에 써져있는 테이블을 기준으로 함에 유의하자.

WHERE 절을 사용해 JOIN할 경우 모든 자료가 나오는 테이블 말고 합쳐지는 테이블에 (+) 를 넣어준다.

LEFT OUTER JOIN을 이용할 때는 

FROM 모든 자료가 나올 테이블명 LEFT OUTER JOIN 그 테이블에 붙일 테이블명 으로 사용한다.

그럼 왼쪽이 다 나오고 오른쪽에 해당하는 테이블은 왼쪽과 교집합인 부분만 나온다.

다른 예를 통해서도 알아보자.

- 판매되지 않은 책만 출력하라.

위에 서 LEFT OUTER JOIN으로 판매된 책과 판매되지 않은 책을 출력했었다.

-- 판매되지 않은 책만 출력
SELECT b.bCode, bName, sNum, qty
FROM book b
LEFT OUTER JOIN dsale d ON b.bCode = d.bCode
WHERE d.bCode IS NULL;

JOIN이 있는 절까지를 테이블로 생각하면 편하다. 

JOIN절 이후에 WHERE 를 통해 조건을 줘서 판매가 되지 않은 책만 출력이 가능하다.

RIGHT OUTER JOIN

SELECT b.bCode, bName, sNum, qty
FROM dsale d, book b
WHERE d.bCode(+) = b.bCode;

SELECT b.bCode, bName, sNum, qty
FROM dsale d
RIGHT OUTER JOIN book b ON d.bCode = b.bCode;

이 쿼리의 결과는 dsale은 book테이블과 겹치는 것만 나오고 book 테이블은 다 나오는 것이므로, 아까 위에서 book 테이블과 dsale을 LEFT OUTER JOIN한 것과 동일하다.

몇 개의 예제를 통해 더 알아보자. >>

더보기

-- 회원 판매현황(구매한 회원만) 

SELECT sNum, sDate, s.cNum, m.cNum, userId
FROM sale s
JOIN member m ON s.cNum = m.cNum;

sale 테이블(판매정보 저장) 과 member 테이블(고객중에 회원으로 등록한 사람들)의 교집합만 출력하면 결과를 얻을 수 있다.

 

-- 회원 / 비회원 판매현황(회원은 구매한 회원만)

SELECT sNum, sDate, s.cNum, m.cNum, userId
FROM sale s
LEFT OUTER JOIN member m ON s.cNum = m.cNum;

sale 테이블에 있는 자료는 모두 나오되, 회원은 구매한 회원만 나와야 하므로 LEFT OUTER JOIN을 통해 쿼리를 작성하면 결과를 얻을 수 있다.

 

-- 회원 판매현황(한권도 구매하지 않은 회원도 출력)

SELECT sNum, sDate, s.cNum, m.cNum, userId
FROM sale s
RIGHT OUTER JOIN member m ON s.cNum = m.cNum;

sale 테이블에서 회원과의 교집합이 나오고 회원은 구매하지 않은 사람도 나오면 되므로 RIGHT OUTER JOIN을 통해 결과를 얻을 수 있다.

FULL OUTER JOIN

-- 회원/비회원 판매현황(한권도 구매하지 않은 회원도 출력)

sNum, sDate, cNum, userId 출력하시오.

SELECT sNum, sDate, s.cNum, m.cNum, userId
FROM sale s
FULL OUTER JOIN member m ON s.cNum = m.cNum;

비회원의 구매내역은 sale이 가지고 있으므로 sale의 테이블 모두가 나와야 한다. member테이블에는 회원의 목록이 있으므로 구매안한 사람도 나오려면 이 둘 테이블을 FULL OUTER JOIN해준다.

테이블의 JOIN 위치에 따른 결과 >>

더보기

테이블을 어떻게 먼저 JOIN하느냐에 따라 결과가 다르다.

-- 회원/비회원 판매현황 (한권도 구매하지 않은 회원도 출력)

sNum, sDate, cNum, cName, userId를 출력하시오.

SELECT sNum, sDate, s.cNum, m.cNum, cName, userId
FROM sale s
FULL OUTER JOIN member m ON s.cNum = m.cNum
FULL OUTER JOIN cus c ON c.cNum = s.cNum;

SELECT sNum, sDate, s.cNum, m.cNum, cName, userId
FROM cus c
FULL OUTER JOIN member m ON c.cNum = m.cNum
FULL OUTER JOIN sale s ON c.cNum = s.cNum;

 

두 번째 쿼리가 더 잘 짠 쿼리이다.

JOIN을 할 때 FROM 뒤에 있는 테이블이 가지고 있는 데이터 양보다 JOIN하는 테이블이 가지고 있는 데이터양이 적어은 것이 좋다.

첫 번재 쿼리는

sale 테이블에 모든 판매 정보가 있다. member 테이블과 FULL OUTER JOIN을 해서 모든 판매 정보와 회원의 판매정보를 모두 나오게 한다. (회원/비회원 판매정보) 거기에서 cus와 FULL OUTER JOIN을 통해 이름을 출력하게 한다. 

 

cus 테이블에는 모든 고객들이 있다. 회원 테이블과 FULL OUTER JOIN을 하면 모든 고객에서 회원인 사람과 아닌 사람들을 알 수 있게된다. (회원/비회원 여부) 거기에서 판매 테이블과 FULL OUTER JOIN을 하면 회원/비회원의 판매현황을 알 수 있다.

NATURAL JOIN 실습 시에는 SQLdeveloper 를 사용합시다.

https://www.oracle.com/tools/downloads/sqldev-downloads.html

여기서 공짜로 다운받아 사용가능합니다.

공짜이므로 약간 무겁고 잔렉이 자주 걸리는 단점이 있습니다.

NATURAL JOIN은 VSCode 에서는 오류가 발생한다.

따라서 sqldeveloper를 사용해서 실습하겠다.

SELECT bCode, bName, bPrice, pNum, pName, sDate, cNum, cName, qty, bPrice * qty amt
FROM book
NATURAL JOIN pub
NATURAL JOIN dsale
NATURAL JOIN sale
NATURAL JOIN cus;

NATURAL 조인은 두 테이블의 동일한 이름을 가진 컬럼을 모두 조인한다. 동일한 이름으로 컬럼을 찾는다. 별명사용이 불가능하다. 컬럼의 이름이 다르면 조인이 되지 않는다.

CROSS JOIN 은 서로 테이블에 있는 행들을 다 JOIN한다.

SELECT * FROM pub;

SELECT * FROM book;

SELECT p.pNum, pName, bCode, bName
FROM pub p
CROSS JOIN book b;

10 * 27 = 270 행이 인출됨을 알 수 있다. 실무에서는 많이 사용되진 않는 것 같다.

SELF JOIN 은 자기가 자기를 조인한다. 

이 조인의 활용 첫 번째는 자기자신을 참조키로 갖는 테이블에 사용해 분류할 때 사용한다.

SELECT * FROM bclass;

m001~m005 까지는 큰 범위의 분류를 주고

m001에 해당하는 것은 p001~p005 ... 로 주었다. 이것을 SELF JOIN 해보자.

SELECT b1.bcCode, b1.bcSubject, b2.bcCode, b2.bcSubject
FROM bclass b1
JOIN bclass b2 ON b1.bcCode = b2.pcCode;

큰 분류 안에 속하는 것들로 분류된 것을 볼 수 있다.

SELF JOIN의 활용 두번째. author 테이블을 SELF JOIN해서 저자가 두명인 책들을 뽑아보자.

SELECT * FROM author;

-- Author 테이블
SELECT a1.bCode, a1.aName, a2.aName
FROM author a1
JOIN author a2 ON a1.bCode = a2.bCode AND a1.aName < a2.aName
ORDER BY a1.bCode;

SELF 조인하면 저자가 2개 이상인 것은 여러행이 출력되기 때문에 

뒤에 AND a1.aName < a2.aName 을 해주면 한글의 UNICODE값을 비교해서 해당하는 값만 출력하기 때문에, 가나다라 순으로 뽑아준다.

저자가 3개 인 책은 9개의 행이 출력되어서 3개의 행이 나오긴 한다.

- 서울서점에 판매했던 책코드(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를 예제로 하여금 어떻게 쓰는지 알아보았다.