본문 바로가기
데이터베이스/ORACLE

[Oracle] SQL 작업 내역 (20.09.23-1)

산을넘는다 2020. 9. 23. 21:13 
SELECT * FROM TABS;
SELECT * FROM CARD_PRODUCT_LIST;
SELECT * FROM CARD_CONTRACT;
SELECT * FROM COMMON_CODE;
SELECT * FROM MEMBER WHERE ID = 'HELLO';

SELECT * FROM OPEN_CARD_TRANSACTION;

DELETE FROM OPEN_CARD_TRANSACTION;
--COMMIT;
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('1', TO_DATE('2018-01-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('2', TO_DATE('2018-02-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드','일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('3', TO_DATE('2018-03-01 19:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('4', TO_DATE('2018-04-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('5', TO_DATE('2018-05-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드','일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('6', TO_DATE('2018-06-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('7', TO_DATE('2018-07-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('8', TO_DATE('2018-08-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('9', TO_DATE('2018-09-01 13:12:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('10', TO_DATE('2018-10-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('11', TO_DATE('2018-11-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드','일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('12', TO_DATE('2018-12-01 19:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('13', TO_DATE('2019-01-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('14', TO_DATE('2019-02-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드','일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('15', TO_DATE('2019-03-01 19:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('16', TO_DATE('2019-04-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('17', TO_DATE('2019-05-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드','일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('18', TO_DATE('2019-06-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('19', TO_DATE('2019-07-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('20', TO_DATE('2019-08-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('21', TO_DATE('2019-09-01 13:12:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('22', TO_DATE('2019-10-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('23', TO_DATE('2019-11-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드','일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('24', TO_DATE('2019-12-01 19:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--
--
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('25', TO_DATE('2020-01-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('26', TO_DATE('2020-02-01 22:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드','일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('27', TO_DATE('2020-03-01 19:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('28', TO_DATE('2020-04-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('29', TO_DATE('2020-05-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드','일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('30', TO_DATE('2020-06-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '89000', 'SKT', 'I13', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
INSERT INTO OPEN_CARD_TRANSACTION VALUES('31', TO_DATE('2020-02-01 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '360000', '롯데백화점', 'I8', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
INSERT INTO OPEN_CARD_TRANSACTION VALUES('32', TO_DATE('2020-01-07 20:30:55', 'YYYY-MM-DD HH24:MI:SS'), '127000', '롯데백화점', 'I8', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
INSERT INTO OPEN_CARD_TRANSACTION VALUES('33', TO_DATE('2020-03-05 13:12:55', 'YYYY-MM-DD HH24:MI:SS'), '260000', '롯데백화점', 'I8', '9203101937988', '신한카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법

INSERT INTO OPEN_CARD_TRANSACTION VALUES('34', TO_DATE('2020-02-15 15:27:55', 'YYYY-MM-DD HH24:MI:SS'), '79000', '이마트인천점', 'I7', '9203101937988', '국민카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
INSERT INTO OPEN_CARD_TRANSACTION VALUES('35', TO_DATE('2020-03-11 17:36:55', 'YYYY-MM-DD HH24:MI:SS'), '32000', '이마트인천점', 'I7', '9203101937988', '국민카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
INSERT INTO OPEN_CARD_TRANSACTION VALUES('36', TO_DATE('2020-01-27 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '9200', '이마트인천점', 'I7', '9203101937988', '국민카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법
INSERT INTO OPEN_CARD_TRANSACTION VALUES('37', TO_DATE('2020-01-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '2400', '이마트인천점', 'I7', '9203101937988', '국민카드', '일시불'); -- 거래번호, 거래시간, 거래금액, 거래내용, 거래점코드, 주민번호, 결제수단, 결제방법

INSERT INTO OPEN_CARD_TRANSACTION VALUES('38', TO_DATE('2020-05-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '500000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 

INSERT INTO OPEN_CARD_TRANSACTION VALUES('38', TO_DATE('2020-01-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '320000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
INSERT INTO OPEN_CARD_TRANSACTION VALUES('39', TO_DATE('2020-02-27 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '2000', '이마트인천점', 'I7', '9203101937988', '국민카드', '일시불'); 
INSERT INTO OPEN_CARD_TRANSACTION VALUES('40', TO_DATE('2020-03-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '120000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
INSERT INTO OPEN_CARD_TRANSACTION VALUES('41', TO_DATE('2020-04-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '150000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
INSERT INTO OPEN_CARD_TRANSACTION VALUES('42', TO_DATE('2020-05-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '70000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
INSERT INTO OPEN_CARD_TRANSACTION VALUES('43', TO_DATE('2020-06-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '80000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
INSERT INTO OPEN_CARD_TRANSACTION VALUES('44', TO_DATE('2020-07-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '70000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
INSERT INTO OPEN_CARD_TRANSACTION VALUES('45', TO_DATE('2020-08-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '65000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
INSERT INTO OPEN_CARD_TRANSACTION VALUES('46', TO_DATE('2020-09-10 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '74000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('47', TO_DATE('2020-10-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '76000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('48', TO_DATE('2020-11-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '150000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 
--INSERT INTO OPEN_CARD_TRANSACTION VALUES('49', TO_DATE('2020-12-28 20:50:55', 'YYYY-MM-DD HH24:MI:SS'), '110000', '현대백화점', 'I8', '9203101937988', '국민카드', '일시불'); 



--ROLLBACK;
--COMMIT;

SELECT * FROM MEMBER WHERE ID = 'HELLO';
SELECT * FROM CARD_CONTRACT WHERE RESI_NUM = 9203101937988;

SELECT * FROM CARD_TRANSACTION;
UPDATE CARD_TRANSACTION SET CARD_TRANSACTION_AMOUNT = (CARD_TRANSACTION_AMOUNT / 2) WHERE CARD_NUM = 9770810030728330;
UPDATE CARD_TRANSACTION SET CARD_TRANSACTION_AMOUNT = (CARD_TRANSACTION_AMOUNT / 2) WHERE CARD_NUM = 9770810030728331;

--COMMIT;

SELECT * FROM CARD_TRANSACTION;

-- 하나카드 거래내역만
SELECT CARD_TRANSACTION_NUM
        ,TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS')AS CARD_TRANSACTION_TIME
        , CARD_TRANSACTION_AMOUNT
        , CARD_TRANSACTION_CONTENT
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.INDUSTRY_CODE) AS INDUSTRY_CODE
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.PAYMENT) AS PAYMENT
FROM CARD_TRANSACTION T
WHERE CARD_NUM IN(
                    SELECT CARD_NUM
                    FROM CARD_CONTRACT 
                    WHERE RESI_NUM = 9203101937988);
                    

-- 하나카드 거래내역만(MEANS 추가)                 
SELECT CARD_TRANSACTION_NUM
        ,TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS')AS CARD_TRANSACTION_TIME
        , CARD_TRANSACTION_AMOUNT
        , CARD_TRANSACTION_CONTENT
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.INDUSTRY_CODE) AS INDUSTRY_CODE
        ,(SELECT '하나카드' FROM DUAL) AS MEANS
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.PAYMENT) AS PAYMENT
FROM CARD_TRANSACTION T
WHERE CARD_NUM IN(
                    SELECT CARD_NUM
                    FROM CARD_CONTRACT 
                    WHERE RESI_NUM = 9203101937988);
                    
                    
-- UNION ALL                    
SELECT CARD_TRANSACTION_NUM
        ,TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS')AS CARD_TRANSACTION_TIME
        , CARD_TRANSACTION_AMOUNT
        , CARD_TRANSACTION_CONTENT
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.INDUSTRY_CODE) AS INDUSTRY_CODE
        ,(SELECT '하나카드' FROM DUAL) AS MEANS
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.PAYMENT) AS PAYMENT
FROM CARD_TRANSACTION T
WHERE CARD_NUM IN(
                    SELECT CARD_NUM
                    FROM CARD_CONTRACT 
                    WHERE RESI_NUM = 9203101937988)
        AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = '202009'
UNION ALL
SELECT CARD_TRANSACTION_NUM
        , TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS')AS CARD_TRANSACTION_TIME
        , CARD_TRANSACTION_AMOUNT
        , CARD_TRANSACTION_CONTENT
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = O.INDUSTRY_CODE) AS INDUSTRY_CODE
        , MEANS
        , PAYMENT
FROM OPEN_CARD_TRANSACTION O
WHERE RESI_NUM = 9203101937988 
        AND MEANS IN ('신한카드')
        AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = '202009'
ORDER BY CARD_TRANSACTION_TIME;    
     
                    
select * from open_card_transaction;

--

SELECT CARD_TRANSACTION_NUM AS num
        ,TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS') AS CARD_TRANSACTION_TIME
        , CARD_TRANSACTION_AMOUNT AS amount
        , CARD_TRANSACTION_CONTENT AS content
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.INDUSTRY_CODE) AS code
        ,(SELECT '하나카드' FROM DUAL) AS means
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.PAYMENT) AS payment
FROM CARD_TRANSACTION T
WHERE CARD_NUM IN(
                    SELECT CARD_NUM
                    FROM CARD_CONTRACT 
                    WHERE RESI_NUM = 9203101937988)
        AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = '202009'
UNION ALL
SELECT CARD_TRANSACTION_NUM AS num
        , TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS') AS CARD_TRANSACTION_TIME
        , CARD_TRANSACTION_AMOUNT AS amount
        , CARD_TRANSACTION_CONTENT AS content
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = O.INDUSTRY_CODE) AS code
        , MEANS
        , PAYMENT
FROM OPEN_CARD_TRANSACTION O
WHERE RESI_NUM = 9203101937988 
        AND MEANS IN ('신한카드')
        AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = '202009'
ORDER BY CARD_TRANSACTION_TIME;

--

WITH TMP AS(
    SELECT CARD_TRANSACTION_NUM AS num
            ,TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS') AS CARD_TRANSACTION_TIME
            , CARD_TRANSACTION_AMOUNT AS amount
            , CARD_TRANSACTION_CONTENT AS content
            ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.INDUSTRY_CODE) AS code
            ,(SELECT '하나카드' FROM DUAL) AS means
            ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.PAYMENT) AS payment
    FROM CARD_TRANSACTION T
    WHERE CARD_NUM IN(
                        SELECT CARD_NUM
                        FROM CARD_CONTRACT 
                        WHERE RESI_NUM = 9203101937988)
            AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = '202009'
    UNION ALL
    SELECT CARD_TRANSACTION_NUM AS num
            , TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS') AS CARD_TRANSACTION_TIME
            , CARD_TRANSACTION_AMOUNT AS amount
            , CARD_TRANSACTION_CONTENT AS content
            ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = O.INDUSTRY_CODE) AS code
            , MEANS
            , PAYMENT
    FROM OPEN_CARD_TRANSACTION O
    WHERE RESI_NUM = 9203101937988 
            AND MEANS IN ('신한카드')
            AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = '202009'
    ORDER BY CARD_TRANSACTION_TIME)
--SELECT * FROM TMP WHERE ROWNUM <= 10;
SELECT * FROM TMP;
    
    
--
WITH TMP AS(
    SELECT CARD_TRANSACTION_NUM
            ,TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS')AS CARD_TRANSACTION_TIME
            , CARD_TRANSACTION_AMOUNT
            , CARD_TRANSACTION_CONTENT
            ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.INDUSTRY_CODE) AS INDUSTRY_CODE
            ,(SELECT '하나카드' FROM DUAL) AS MEANS
            ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.PAYMENT) AS PAYMENT
    FROM CARD_TRANSACTION T
    WHERE CARD_NUM IN(
                        SELECT CARD_NUM
                        FROM CARD_CONTRACT 
                        WHERE RESI_NUM = 9203101937988))
SELECT * FROM TMP WHERE ROWNUM <= 10;


SELECT TO_CHAR(SYSDATE-100, 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(SYSDATE-3, 'YYYYMM') FROM DUAL;

-- 2주 전
SELECT TO_CHAR(SYSDATE-14, 'YYYYMMDD') FROM DUAL;


-- 한달 전 
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMMDD') FROM DUAL;

-- 이번 달
SELECT TO_CHAR(SYSDATE, 'YYYYMM') FROM DUAL;


--

WITH TMP AS(
    SELECT CARD_TRANSACTION_NUM AS num
            ,TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS') AS CARD_TRANSACTION_TIME
            , CARD_TRANSACTION_AMOUNT AS amount
            , CARD_TRANSACTION_CONTENT AS content
            ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.INDUSTRY_CODE) AS code
            ,(SELECT '하나카드' FROM DUAL) AS means
            ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.PAYMENT) AS payment
    FROM CARD_TRANSACTION T
    WHERE CARD_NUM IN(
                        SELECT CARD_NUM
                        FROM CARD_CONTRACT 
                        WHERE RESI_NUM = 9203101937988)
            --AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = '202009'
            AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = (SELECT TO_CHAR(SYSDATE, 'YYYYMM') FROM DUAL)
            --AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMMDD') >= (SELECT TO_CHAR(SYSDATE-14, 'YYYYMMDD') FROM DUAL)
    UNION ALL
    SELECT CARD_TRANSACTION_NUM AS num
            , TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS') AS CARD_TRANSACTION_TIME
            , CARD_TRANSACTION_AMOUNT AS amount
            , CARD_TRANSACTION_CONTENT AS content
            ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = O.INDUSTRY_CODE) AS code
            , MEANS
            , PAYMENT
    FROM OPEN_CARD_TRANSACTION O
    WHERE RESI_NUM = 9203101937988 
            AND MEANS IN ('신한카드')
            --AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = '202009'
            AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMM') = (SELECT TO_CHAR(SYSDATE, 'YYYYMM') FROM DUAL)
            --AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYYMMDD') >= (SELECT TO_CHAR(SYSDATE-14, 'YYYYMMDD') FROM DUAL)

    ORDER BY CARD_TRANSACTION_TIME)
SELECT * FROM TMP WHERE ROWNUM <= 100;


--
SELECT * FROM CARD_TRANSACTION WHERE CARD_TRANSACTION_NUM = '10527509';
SELECT * FROM CARD_TRANSACTION WHERE INDUSTRY_CODE = 'I3';
UPDATE CARD_TRANSACTION SET INDUSTRY_CODE = 'I13' WHERE CARD_TRANSACTION_CONTENT = 'SKT';
COMMIT;

--UPDATE CARD_TRANSACTION SET CARD_TRANSACTION_CONTENT = '광명카센터' WHERE CARD_TRANSACTION_NUM = '10527509';

SELECT * FROM COMMON_CODE;


-- 사용자 I13(통신) 사용내역
SELECT CARD_TRANSACTION_NUM
        ,TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY.MM.DD HH24:MI:SS')AS CARD_TRANSACTION_TIME
        , CARD_TRANSACTION_AMOUNT
        , CARD_TRANSACTION_CONTENT
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.INDUSTRY_CODE) AS INDUSTRY_CODE
        ,(SELECT '하나카드' FROM DUAL) AS MEANS
        ,(SELECT VALUE FROM COMMON_CODE WHERE CODE = T.PAYMENT) AS PAYMENT
FROM CARD_TRANSACTION T
WHERE CARD_NUM IN(
                    SELECT CARD_NUM
                    FROM CARD_CONTRACT 
                    WHERE RESI_NUM = 9203101937988)
    AND T.INDUSTRY_CODE = 'I13';

-- 사용자(9203101937988) 통신료 1/3 하기. 과금유저로 설정.
UPDATE CARD_TRANSACTION SET CARD_TRANSACTION_AMOUNT = (CARD_TRANSACTION_AMOUNT/3) 
WHERE CARD_NUM IN(
                    SELECT CARD_NUM
                    FROM CARD_CONTRACT 
                    WHERE RESI_NUM = 9203101937988);
COMMIT;




WITH whole AS
(
-- 1. 컬럼을 가공하여 조건주지 말고 -> 조건을 가공
SELECT industry_code cd
     , card_transaction_amount amt
  FROM card_transaction
 WHERE card_num IN (SELECT card_num FROM card_contract WHERE resi_num = '9203101937988')
   AND card_transaction_time >= TO_DATE('2020'||'01', 'yyyymm')
   AND card_transaction_time <  ADD_MONTHS(TO_DATE('2020'||'01', 'yyyymm'), 1)
 UNION ALL
SELECT industry_code cd
     , card_transaction_amount amt
  FROM open_card_transaction
 WHERE resi_num = '9203101937988'
   AND means IN ('신한카드', '국민카드')
   AND card_transaction_time >= TO_DATE('2020'||'01', 'yyyymm')
   AND card_transaction_time <  ADD_MONTHS(TO_DATE('2020'||'01', 'yyyymm'), 1)
)
-- 2. SUM(CASE 를 이용한 피벗 구현으로 테이블 읽기 횟수 줄이기(8회 -> 1회) --
SELECT NVL(SUM(CASE WHEN cd IN ('I1', 'I2'      ) THEN amt END), 0) type1
     , NVL(SUM(CASE WHEN cd IN ('I4', 'I5', 'I6') THEN amt END), 0) type2
     , NVL(SUM(CASE WHEN cd IN ('I7', 'I8'      ) THEN amt END), 0) type3
     , NVL(SUM(CASE WHEN cd IN ('I9'            ) THEN amt END), 0) type4
     , NVL(SUM(CASE WHEN cd IN ('I10'           ) THEN amt END), 0) type5
     , NVL(SUM(CASE WHEN cd IN ('I11'           ) THEN amt END), 0) type6
     , NVL(SUM(CASE WHEN cd IN ('I12'           ) THEN amt END), 0) type7
     , NVL(SUM(CASE WHEN cd IN ('I13'           ) THEN amt END), 0) type8
  FROM whole
;




WITH whole AS
(
-- 1. 컬럼을 가공하여 조건주지 말고 -> 조건을 가공
SELECT industry_code cd
     , card_transaction_amount amt
  FROM card_transaction
 WHERE card_num IN (SELECT card_num FROM card_contract WHERE resi_num = '9203101937988')
   AND card_transaction_time >= TO_DATE('2020'||'01', 'yyyymm')
   AND card_transaction_time <  ADD_MONTHS(TO_DATE('2020'||'01', 'yyyymm'), 1)
 UNION ALL
SELECT industry_code cd
     , card_transaction_amount amt
  FROM open_card_transaction
 WHERE resi_num = '9203101937988'
   AND means IN ('신한카드', '국민카드')
   AND card_transaction_time >= TO_DATE('2020'||'01', 'yyyymm')
   AND card_transaction_time <  ADD_MONTHS(TO_DATE('2020'||'01', 'yyyymm'), 1)
)
-- 3. 2단계 집계로 CASE 함수 사용 횟수 줄이기( 8천만회(천만건*8번) -> 96회(12종*8번) ) --
SELECT NVL(SUM(CASE WHEN cd IN ('I1', 'I2'      ) THEN amt END), 0) type1
     , NVL(SUM(CASE WHEN cd IN ('I4', 'I5', 'I6') THEN amt END), 0) type2
     , NVL(SUM(CASE WHEN cd IN ('I7', 'I8'      ) THEN amt END), 0) type3
     , NVL(SUM(CASE WHEN cd IN ('I9'            ) THEN amt END), 0) type4
     , NVL(SUM(CASE WHEN cd IN ('I10'           ) THEN amt END), 0) type5
     , NVL(SUM(CASE WHEN cd IN ('I11'           ) THEN amt END), 0) type6
     , NVL(SUM(CASE WHEN cd IN ('I12'           ) THEN amt END), 0) type7
     , NVL(SUM(CASE WHEN cd IN ('I13'           ) THEN amt END), 0) type8
  FROM (SELECT cd
             , SUM(amt) amt
          FROM whole
         GROUP BY cd
        )
;
 

WITH WHOLE AS (SELECT CARD_TRANSACTION_TIME, CARD_TRANSACTION_AMOUNT, CARD_TRANSACTION_CONTENT, INDUSTRY_CODE
                FROM CARD_TRANSACTION
                WHERE CARD_NUM IN(SELECT CARD_NUM FROM CARD_CONTRACT WHERE RESI_NUM = '9203101937988')
                      AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY') = '2020'
                      AND TO_CHAR(CARD_TRANSACTION_TIME, 'MM') = '01'
                       
                UNION ALL
                 
                SELECT CARD_TRANSACTION_TIME, CARD_TRANSACTION_AMOUNT, CARD_TRANSACTION_CONTENT, INDUSTRY_CODE
                 FROM OPEN_CARD_TRANSACTION    
                 WHERE RESI_NUM = '9203101937988'
                      AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY') = '2020'
                      AND TO_CHAR(CARD_TRANSACTION_TIME, 'MM') = '01'   
                      AND MEANS IN('신한카드', '국민카드')
                )
SELECT SUBQUERY1.RESULT AS TYPE1, SUBQUERY2.RESULT AS "TYPE2", SUBQUERY3.RESULT AS "TYPE3", SUBQUERY4.RESULT AS "TYPE4",
        SUBQUERY5.RESULT AS "TYPE5", SUBQUERY6.RESULT AS "TYPE6", SUBQUERY7.RESULT AS "TYPE7", SUBQUERY8.RESULT AS "TYPE8"
FROM (SELECT * 
       FROM (SELECT SUM(RESULT) AS RESULT
             FROM(
                    SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
                    FROM WHOLE
                    GROUP BY INDUSTRY_CODE
                    HAVING INDUSTRY_CODE IN('I1', 'I2')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL))
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY1,
       
(SELECT * 
       FROM (SELECT SUM(RESULT) AS RESULT
             FROM(
                    SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
                    FROM WHOLE
                    GROUP BY INDUSTRY_CODE
                    HAVING INDUSTRY_CODE IN('I4', 'I5', 'I6')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL))
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY2,
       
     (SELECT * 
       FROM (SELECT SUM(RESULT) AS RESULT
             FROM(
                    SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
                    FROM WHOLE
                    GROUP BY INDUSTRY_CODE
                    HAVING INDUSTRY_CODE IN('I7', 'I8')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL))
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY3,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I9')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY4,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I10')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY5,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I11')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY6,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I12')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY7,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I13')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY8
WHERE ROWNUM <=1;



with whole as 
 (
  select card_transaction_time, card_transaction_amount, card_transaction_content, industry_code
    from card_transaction
   where card_num in (select card_num from card_contract where resi_num = '9203101937988')
     and extract(year from card_transaction_time) = 2020
     and extract(month from card_transaction_time) = 1
  union all
  select card_transaction_time, card_transaction_amount, card_transaction_content, industry_code
     from open_card_transaction     
   where resi_num = '9203101937988'
     and extract(year from card_transaction_time) = 2020
     and extract(month from card_transaction_time) = 1
     and means in('신한카드', '국민카드')
 )
select result1 as TYPE1
     , result2 as TYPE2
     , result3 as TYPE3
     , result4 as TYPE4
     , result5 as TYPE5
     , result6 as TYPE6
     , result7 as TYPE7
     , result8 as TYPE8
from 
(
  select sum(case when industry_code in ('I1', 'I2') then card_transaction_amount else 0 end) as result1
       , sum(case when industry_code in ('I4', 'I5', 'I6') then card_transaction_amount else 0 end) as result2
       , sum(case when industry_code in ('I7', 'I8') then card_transaction_amount else 0 end) as result3
       , sum(case when industry_code in ('I9') then card_transaction_amount else 0 end) as result4
       , sum(case when industry_code in ('I10') then card_transaction_amount else 0 end) as result5
       , sum(case when industry_code in ('I11') then card_transaction_amount else 0 end) as result6
       , sum(case when industry_code in ('I12') then card_transaction_amount else 0 end) as result7
       , sum(case when industry_code in ('I13') then card_transaction_amount else 0 end) as result8
    from whole
  group by industry_code
);


SELECT * FROM CARD_CONTRACT;

SELECT CARD_PRODUCT_CODE, COUNT(CARD_PRODUCT_CODE) 
FROM CARD_CONTRACT
GROUP BY(CARD_PRODUCT_CODE);

SELECT CARD_PRODUCT_CODE, COUNT(CARD_PRODUCT_CODE) 
FROM CARD_CONTRACT
WHERE TO_CHAR(REG_DATE, 'YYYYMM') >= '201801'
GROUP BY(CARD_PRODUCT_CODE);


SELECT CARD_PRODUCT_CODE, COUNT(CARD_PRODUCT_CODE) 
FROM CARD_CONTRACT
WHERE TO_CHAR(REG_DATE, 'YYYYMM') = '202009'
GROUP BY(CARD_PRODUCT_CODE);

SELECT CARD_PRODUCT_CODE, COUNT(CARD_PRODUCT_CODE) 
FROM CARD_CONTRACT
WHERE REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE);


SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS CARD_PRODUCT_NAME, COUNT(CARD_PRODUCT_CODE) AS CNT
FROM CARD_CONTRACT C
WHERE CARD_PRODUCT_CODE IN (4,5,7) AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE);


SELECT * FROM CARD_CONTRACT;
SELECT * FROM CARD_PRODUCT_LIST;

-- 카드 상품 가입 더미데이터 삽입 사용자주민: 3203102941474 : 2번카드 가지고있음. 신경쓰지말고, 인기카드순위 조작에 쓰자!
INSERT INTO CARD_CONTRACT VALUES('9770810030731831', '7', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731832', '7', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731833', '7', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731834', '7', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731835', '5', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731836', '5', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731837', '5', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731838', '4', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731839', '4', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));


INSERT INTO CARD_CONTRACT VALUES('9770810030731840', '14', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731841', '14', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731842', '14', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731843', '14', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731844', '16', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731845', '16', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731846', '16', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731847', '15', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731848', '15', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731849', '17', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731850', '18', '3203102941474',  '000', '1234', TO_DATE(180101, 'YY/MM/DD'));


INSERT INTO CARD_CONTRACT VALUES('9770810030731851', '12', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731852', '12', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731853', '12', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731854', '1', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731855', '1', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731856', '2', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));
INSERT INTO CARD_CONTRACT VALUES('9770810030731857', '3', '3203102941474',  '000', '1234', TO_DATE(200901, 'YY/MM/DD'));

--COMMIT;



SELECT * FROM CARD_CONTRACT;
--COMMIT;


--ROLLBACK;
--COMMIT;


SELECT * FROM CARD_PRODUCT_LIST
WHERE CARD_PRODUCT_CODE IN(4, 5, 7);


SELECT * 
FROM CARD_PRODUCT_LIST
WHERE (ANNUAL_FEE BETWEEN 0 AND 10000);


SELECT * 
FROM CARD_PRODUCT_LIST
WHERE ANNUAL_FEE  > 10000 AND ANNUAL_FEE <= 20000;


SELECT * 
FROM CARD_PRODUCT_LIST
WHERE ANNUAL_FEE > 20000;

--
select count(*) from card_transaction;

-- 용량 확인하기. 
select sum(bytes)/1024/1024/1024 from dba_data_files;



SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS CARD_PRODUCT_NAME, COUNT(CARD_PRODUCT_CODE) AS CNT
FROM CARD_CONTRACT C
WHERE CARD_PRODUCT_CODE IN (4,5,7) AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE);

SELECT * FROM CARD_PRODUCT_LIST;
--INSERT INTO CARD_PRODUCT_LIST VALUES('19', '모두의 건강', 'CR', 15000,    '0', '0','0','0','0','0','0','0','0','0','0','0','0',    '0','0','0','0','0','0','0','0','0','0','0','0','0',    '0', '0', '0', '0.05', '0', '0', '0.05', '0', '0.05', '0', '0.05', '0.05', '0',    '0','0','0','0','0','0','0','0','0','0','0','0','0');
--DELETE FROM CARD_PRODUCT_LIST WHERE CARD_PRODUCT_CODE = '19';
--COMMIT;


SELECT * FROM CARD_PRODUCT_LIST;

SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE CARD_PRODUCT_CODE IN (4,5,7) AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;


SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE CARD_PRODUCT_CODE IN (4,5,7) AND REG_DATE = TO_DATE('2009', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE);


-- 20대
SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE SUBSTR(RESI_NUM, 0, 2) >=  '92' 
    AND SUBSTR(RESI_NUM, 0, 2) <=  '99'
    AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;

--30대
SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE SUBSTR(RESI_NUM, 0, 2) >=  '82' 
    AND SUBSTR(RESI_NUM, 0, 2) <=  '91'
    AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;


--40대
SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE SUBSTR(RESI_NUM, 0, 2) BETWEEN  '72' AND '81'
    AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;


--40대
SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE SUBSTR(RESI_NUM, 0, 2) BETWEEN  '72' AND '81'
    AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;


-- 연회비별(0 ~ 10000)
SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE CARD_PRODUCT_CODE IN(12, 1, 2, 3)
    AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;

SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE CARD_PRODUCT_CODE IN(12, 1, 2, 3)
    AND REG_DATE = TO_DATE('2009', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;

-- 연회비별(10000 ~ 20000)
SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE CARD_PRODUCT_CODE IN(8, 7, 16, 10, 6, 5, 19, 9, 13, 4)
    AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;

-- 연회비별(2만원 이상)
SELECT (SELECT CARD_PRODUCT_NAME FROM CARD_PRODUCT_LIST L WHERE L.CARD_PRODUCT_CODE = C.CARD_PRODUCT_CODE ) AS cardName , COUNT(CARD_PRODUCT_CODE)  AS count
FROM CARD_CONTRACT C
    WHERE CARD_PRODUCT_CODE IN(11, 14, 15, 18, 17)
    AND REG_DATE = TO_DATE('1801', 'YY/MM') 
GROUP BY(CARD_PRODUCT_CODE)
ORDER BY count DESC;


SELECT * FROM CARD_PRODUCT_LIST;
SELECT * FROM CARD_CONTRACT;

'데이터베이스 > ORACLE' 카테고리의 다른 글

[ORACLE] 인덱스 생성 전후, 쿼리 튜닝 전후 속도  (0) 2020.09.29
[Oracle] 천만 건 업데이트 속도 : 7시간  (0) 2020.09.26
[Oracle] CARD_TRANSACTION INDEX 생성  (0) 2020.09.15
[Oracle] ORA-30009: Not enough memory for CONNECT BY operation  (0) 2020.09.13
[Oracle] 두 테이블 곱하기 : 길이가 다른 테이블을 from절에 두면, 두개의 테이블이 곱해진다 (랜덤 데이터 생성에 응용)  (0) 2020.09.12

'데이터베이스/ORACLE' Related Articles

티스토리툴바