개발새발, 써놓기

-- 테이블에 컬럼 추가

ALTER TABLE 테이블명 ADD (컬럼명 자료형(크기));

-- 테이블 컬럼폭 또는 크기 변경

ALTER TABLE 테이블명 MODIFY (컬럼명 자료형(크기));

-- 테이블 컬럼명 변경

 ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 새로운컬럼명

-- 테이블 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명

-- ORACLE 11g 에서 페이징 처리 방법(3페이지, 20개 건너뛰고 10개 가져오기)

SELECT * FROM (
    SELECT ROWNUM rnum, tb.* FROM (
        SELECT num, name, subject, hitCount, reg_date
        FROM bbs
        [ WHERE 조건 ]
        ORDER BY num DESC
    ) tb WHERE ROWNUM <= 30
) WHERE rnum >=21;

-- ORACLE 12c 이상에서 페이징 처리 방법(3페이지, 20개 건너뛰고 10개 가져오기)

SELECT num, name, subject, hitCount, reg_date
FROM bbs
[ WHERE 조건 ]
ORDER BY num DESC
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

    VARCHAR2(길이) : 가변 길이 문자 데이터를 저장. 최대 길이는 4000자. 반드시 길이를 명시
    NCHAR, NVARCHAR2 : 다양한 언어의 문자 값(국가별 언어 집합 : NLS)을 저장하고 조회 가능
    NUMBER : 숫자 - NUMBER(P, S)
        P(1~38)는 정밀도로 전체 자리수를 나타내며 기본 값이 38이고 S(-84~127)는 소수점 이하의 자릿수이다.
    DATE : 날짜, 『년/월/일 시:분:초』
    TIMESTAMP : 날짜, 『년/월/일 시:분:초.밀리초』
    CLOB : 대용량의 문자 저장

-- 예제

-- 비회원 판매 현황 : 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을 하면 회원/비회원의 판매현황을 알 수 있다.