[1일1코딩][SQL][Oracle] NULL과 DEFAULT 우선순위 및 MERGE INTO 해보기

 

 MERGE INTO 코드

https://gent.tistory.com/406

일단 대강 명령어들 위치는 이해됬는데 구체적으로 안에서 어떻게 작동하는지는 

이번 한번으로는 이해가 안되서 또 봐야할 것 같다

---

 

ALTER로 DEFAULT값을 지정해줬어도 해당 컬럼값에 NULL을 넣는다고 하면 
DEFAULT가 안들어가고 NULL값이 그대로 들어가며, 

(CO1)처럼 값 하나만 지정하고 다른 컬럼값을 지정안하면 이때 DEFAULT값이 들어간다

 

--------------------------------------------------------------------
---------------------- SQLD 30회 기출  42번 -------------------------

-- MERGE INTO 코드
CREATE TABLE SQLD_30_42_01(
COL1   VARCHAR2(10),
COL2   VARCHAR2(10),
COL3   NUMBER
);

CREATE TABLE SQLD_30_42_02(
COL1   VARCHAR2(10),
COL2   VARCHAR2(10),
COL3   NUMBER
);

INSERT INTO SQLD_30_42_01 VALUES('A', 'X', 1);
INSERT INTO SQLD_30_42_01 VALUES('B', 'Y', 2);
INSERT INTO SQLD_30_42_01 VALUES('C', 'Z', 3);
INSERT INTO SQLD_30_42_01 VALUES('X', 'T', 1);

SELECT * FROM SQLD_30_42_01;

INSERT INTO SQLD_30_42_02 VALUES('A', 'X', 1);
INSERT INTO SQLD_30_42_02 VALUES('B', 'Y', 2);
INSERT INTO SQLD_30_42_02 VALUES('C', 'Z', 3);
INSERT INTO SQLD_30_42_02 VALUES('D', '가', 4);
INSERT INTO SQLD_30_42_02 VALUES('E', '나', 5);

SELECT * FROM SQLD_30_42_02;

MERGE INTO SQLD_30_42_01 A
    USING SQLD_30_42_02 B
        ON(A.COL1 = B.COL1)
    WHEN MATCHED THEN 
        UPDATE SET A.COL3 = 4
            WHERE A.COL3 = 2
        DELETE WHERE A.COL3 <= 2
    WHEN NOT MATCHED THEN
        INSERT(A.COL1, A.COL2, A.COL3)
        VALUES(B.COL1, B.COL2, B.COL3);






--------------------------------------------------------------------
---------------------- SQLD 30회 기출  47번 -------------------------

/*
ALTER로 DEFAULT값을 지정해줬어도 해당 컬럼값에 NULL을 넣는다고 하면 
DEFAULT가 안들어가고 NULL값이 그대로 들어가며, 

(CO1)처럼 값 하나만 지정하고 다른 컬럼값을 지정안하면 이때 DEFAULT값이 들어간다

*/

CREATE TABLE SQLD_47(
COL1   VARCHAR2(30),
COL2   NUMBER
);

INSERT INTO SQLD_47(COL1, COL2) VALUES('BC',NULL); 
ALTER TABLE SQLD_47 MODIFY COL2 DEFAULT 10;
INSERT INTO SQLD_47(COL1, COL2) VALUES('XY',NULL); 
INSERT INTO SQLD_47(COL1) VALUES('EXD');
SELECT * FROM SQLD_47;
SELECT SUM(COL2) FROM SQLD_47;
SELECT * FROM SQLD_47;
rollback;

--------------------------------------------------------------------
-------------- SQLD 34회 기출  26번 -----------------------
DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
DROP TABLE T4;

CREATE TABLE T1(
COL1 NUMBER
);
CREATE TABLE T2(
COL1 NUMBER
);
CREATE TABLE T3(
COL1 NUMBER
);
CREATE TABLE T4(
COL1 NUMBER
);

INSERT INTO T1 VALUES(1);
INSERT INTO T1 VALUES(2);
INSERT INTO T1 VALUES(3);
INSERT INTO T1 VALUES(4);
SELECT * FROM T1;
INSERT INTO T2 VALUES(1);
INSERT INTO T2 VALUES(2);
INSERT INTO T2 VALUES(3);
INSERT INTO T2 VALUES(NULL);
SELECT * FROM T2;
INSERT INTO T3 VALUES(1);
INSERT INTO T3 VALUES(NULL);
INSERT INTO T3 VALUES(3);
INSERT INTO T3 VALUES(5);
SELECT * FROM T3;
INSERT INTO T4 VALUES(1);
INSERT INTO T4 VALUES(2);
INSERT INTO T4 VALUES(5);
INSERT INTO T4 VALUES(6);
SELECT * FROM T4;

SELECT *
FROM T1 ,T2, T3
WHERE T1.COL1 = T2.COL1(+)
AND T2.COL1 = T3.COL1(+);
SELECT COUNT(*)
FROM T1 ,T2, T3
WHERE T1.COL1 = T2.COL1(+)
AND T2.COL1 = T3.COL1(+);

SELECT *
FROM T2, T3
WHERE T2.COL1 = T3.COL1(+);

SELECT COUNT(*)
FROM T1 ,T2, T3, T4
WHERE T1.COL1 = T2.COL1(+)
AND T2.COL1 = T3.COL1(+)
AND T3.COL1 = T4.COL1;
SELECT *
FROM T1 ,T2, T3, T4
WHERE T1.COL1 = T2.COL1(+)
AND T2.COL1 = T3.COL1(+)
AND T3.COL1 = T4.COL1;