개발새발, 써놓기

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은 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를 예제로 하여금 어떻게 쓰는지 알아보았다.

EQUI JOIN에 대해 알아보자.

EQUI JOIN은 테이블과 테이블을 연결하고 교집합만을 가져오는 JOIN이다. 가장 기본적인 JOIN이다.

예제를 통해 알아보자.

SELECT * FROM pub;
    -- pNum, pName, pTel
SELECT * FROM book;
    -- bCode, bName, bPrice, bcCode, bNum(책코드, 책이름, 가격, 분류코드, 출판사번호)

두 개의 테이블에 무엇이 있는지 확인

두 테이블을 JOIN 해보자.

-- 방법 1 : WHERE 로 JOIN한다.
SELECT bCode, bName, book.pNum, pName, bPrice 
FROM book, pub
WHERE book.pNum = pub.pNum;

-- 방법 2 : JOIN 을 이용한다.
SELECT bCode, bName, b.pNum, pName, bPrice 
FROM book b
JOIN pub p ON b.pNum = p.pNum;

테이블 book은 b로 별명을 주었기 때문에 book테이블의 pNum은 b.pNum 으로 표현가능하다. 두 테이블에 같은 값을 가지고 있는 pNum을 이용해 EQUI JOIN을 했기 때문에 중복되는 pNum을 SELECT에서 가져오려면 컬럼명 앞에 테이블명을 주어야한다. 안쓰면 에러(ORA-00918 : 컬럼의 정의가 애매하다. )가 발생한다. 주로 이런 상황에서는 먼저있는 테이블을 쓰기 때문에 b.pNum으로 주었다. p.pNum으로 줘도 상관은 없다.

WHERE절을 이용해서도 JOIN이 가능하지만 JOIN까지를 한 테이블로 생각하고 WHERE로 조건을 주는 것이 나는 더 편하기 때문에 방법 2를 주로 사용할 예정이다. (아니면 WHERE 절에 조인도 하고 조건도 줘야되서 조금 헷갈린다. 특히 LEFT OUTER JOIN 나 RIGHT OUTER JOIN사용 시 엄청 헷갈림)

판매현황을 알아보는 실습을 해보자.

테이블 (컬럼) 정보

book (bCode, bName, pNum, bPrice, bcCode)

pub (pNum, pName, pTel)

sale (sNum, sDate, cNum)

dsale (dNum, sNum, bCode, qyt)

cus (cNum, cName, cTel)

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

-- EQUI JOIN 조건에 해당하는 것만 가져온다.

-- 방법 1
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, 
	s.cNum, cName, qty, bPrice * qty amt
FROM BOOK b, pub p, dsale d, sale s, cus c
WHERE b.pNum = p.pNum AND b.bCode = d.bCode AND d.sNum = s.sNum AND s.cNum = c.cNum;

-- 방법 2
SELECT b.bCode, pName, bPrice, b.pNum, pName, sDate,
    d.sNum, 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;

-- 방법 3
SELECT bCode, pName, bPrice, pNum, pName, sDate,
    cNum, cName, qty, bPrice * qty amt
FROM book b
JOIN pub USING(pNum)
JOIN dsale USING(bCode)
JOIN sale USING(sNum)
JOIN cus USING(cNum);
-- 컬럼명이 다를 수 있어서 방법2의 EQUI JOIN을 더 많이사용한다.

-- 판매된 책코드(bCode), 책이름(bName), 판매수량(qty) 를 출력하라.

SELECT b.bCode, bName, qty
FROM book b
JOIN dsale d ON b.bCode = d.bCode
ORDER BY b.bCode;

판매수량은 dsale 테이블이 가지고 있으므로 판매된 책코드와 책이름을 알아보기 위해서는 book과 dsale테이블을 JOIN해준다.

-- 판매된 책코드(bCode), 책이름(bName), 판매수량의 합(qty) : 책코드 오름차순

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;

SUM() 을 사용할 때 GROUP BY를 넣어서 책코드와 책이름을 묶어주어야 한다.

-- 0812.sql 파일 108줄까지 포스팅

만들 테이블의 ERD

문제 1

score 테이블과 injeok 테이블을 조인하여 다음의 컬럼을 출력한다.

-- 출력할 컬럼

hak, ban, 구분, name, com, excel, word, tot, ave, 학급석차, 학년석차

-- 처리조건

tot = com+excel+word

ave = (com+excel+word) / 3 -> 소수점 2째자리 반올림 ★ ROUND(ave, 1) 로 바꿔야 한다. (수정 21.08.13) 2의 자리에서 반올림에서 소수점 1자리까지 표시하는 것이다. 밑에 사진은 그냥 소수점 2자리까지 표시인 것으로 두겠다.

구분 : gubun 1 -> 1학기중간, 2 -> 1학기기말, 3 -> 2학기중간, 4 -> 2학기기말

학급 및 학년 석차 : 구분 별 학급 및 학년 석차

SELECT hak, ban, 
	DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분, 
	name, com, excel, word, com+excel+word tot, 
    ROUND((com+excel+word) / 3, 1) ave, 
    RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
    RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
LEFT OUTER JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;
-- LEFT OUTER JOIN을 활용하면, 시험을 치지 않은 학생들도 같이나옴. 
-- 이 자료에서는 시험을 안친 학생이 없어서 상관없음.
-- FOREIGN KEY 의 제약 조건으로 인해 injeok이 없으면 score 자료도 없게 제약조건을 둔 것

-- 하지만 시험을 치지 않은 학생들을 배제하고 나오게 하려면 EQUI JOIN을 쓴다.
SELECT hak, ban, 
	DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분, 
	name, com, excel, word, com+excel+word tot, 
    ROUND((com+excel+word) / 3, 1) ave, 
    RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
    RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;

-- 여기서 학번이 기본키이기 때문에 꼭 학번이 존재한다.
SELECT hak, ban, 
	DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분, 
	name, com, excel, word, com+excel+word tot, 
    ROUND((com+excel+word) / 3, 1) ave, 
    RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
    RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
RIGHT OUTER JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;

문제 2

score 테이블과 injeok 테이블을 조인하여 다음의 컬럼을 출력한다.

-- 출력할 컬럼

학년, 반, 구분, 이름, 총점, 평균, 판정

-- 처리조건

구분 : gubun 1-> 1학기중간, 2 -> 1학기기말, 3 -> 2학기중간, 4 -> 2학기기말 로 표시

판정

세 과목 모두 40점 이상이고, 평균 60점 이상 : 합격

세 과목 중 한과목이라도 40점 미만이고, 평균 60점 이상이면 : 과락

그렇지 않으면 불합격으로 처리한다.

SELECT hak 학년, ban 반, DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분, 
    name 이름, com+excel+word 총점, 
    ROUND((com+excel+word) / 3, 1) 평균, 
    (CASE 
        WHEN com >= 40 AND excel >= 40 AND word >= 40 AND (com+excel+word) / 3 >= 60 THEN '합격'
        WHEN (com+excel+word)/3 >= 60 THEN '과락'
        ELSE '불합격'
    END) 판정
FROM score s
JOIN injeok i ON s.hakbeon = i.hakbeon;

확인을 위해 출력한 테이블

-- 모든 데이터 사전 테이블 정보 확인
SELECT COUNT(*) FROM DICTIONARY;
SELECT * FROM DICTIONARY;

-- 현재 사용자의 모든 객체 정보
SELECT * FROM user_objects;

-- 테이블 정보 확인
SELECT * FROM user_tables;
SELECT * FROM tabs;
SELECT * FROM tab;