[SQLD 2-01] ★LEFT OUTER JOIN★RANK()★DENSE_RANK()★GROUPBY★LPAD()★UPDATE★UNION★UNION ALL★MINUS★INTERSECT★기존 데이터 변경, 데이터타입 변경★CASE WHEN THEN

1. LEFT OUTER JOIN

CREATE TABLE 고객 (
    고객번호 INT,
    고객명 VARCHAR2(50)
);

CREATE TABLE 주문 (
    주문번호 INT,
    고객번호 INT,
    주문금액 INT
);

INSERT INTO 고객 (고객번호, 고객명) VALUES (1, '김대원');
INSERT INTO 고객 (고객번호, 고객명) VALUES (2, '노영미');
INSERT INTO 고객 (고객번호, 고객명) VALUES (3, '김경진');
INSERT INTO 고객 (고객번호, 고객명) VALUES (4, '박하연');

INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2001,1,40000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2002,2,15000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2003,2,7000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2004,2,8000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2005,2,20000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2006,3,5000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2007,3,9000);

고객

주문

SELECT A.고객번호 , (SELECT  SUM(X.주문금액)
                 FROM 주문 X
                 WHERE X.고객번호 = A.고객번호) AS 주문합계금액
FROM 고객 A
WHERE A.고객번호 IN(3,4);

SELECT A.고객번호, SUM(B.주문금액) AS 주문합계금액
FROM 고객 A
LEFT OUTER JOIN
주문 B ON B.고객번호 = A.고객번호
WHERE A.고객번호 IN (3,4)
GROUP BY A.고객번호
;

==> LEFT OUTER JOIN ==> 왼쪽으로 OUTER JOIN

 

2. RANK() , ROW_NUMBER()

EMP TABLE

SELECT * FROM EMP
WHERE SAL > (SELECT SAL FROM (SELECT SAL, ROW_NUMBER() OVER (ORDER BY  SAL DESC) AS RK FROM EMP WHERE SAL < 1300) WHERE RK = 1);

==> RANK() ==> 동일순위 허용 ==> 서브쿼리에서 다중 행 반환 ==> 오류 발생 ==> 1 2 2 4

 

==> DENSE_RANK() ==> 동일순위 허용 ==> 1 2 2 3

 

==> 1300 이하인 SAL 중에서 제일 큰거를 RK 열의 1번째(제일 큰거) 의 SAL 반환

 

3. GROUP BY

SELECT EMPNO, ENAME , SAL , DEPTNO FROM EMP
WHERE (DEPTNO , SAL) IN (SELECT DEPTNO,  MAX(SAL) FROM EMP
                                                  GROUP BY DEPTNO);

==> DEPTNO , SAL 열이 (DEPTNO 로 GROUP 화 하였을 때 , SAL 중에 제일 큰

4. LPAD 

SELECT SAL , LPAD(SAL, 4 , '0') FROM EMP;

LPAD

==> 왼쪽부터 0을채우고 4개로 채우기

 

SELECT MAX(LPAD(SAL, 4 , '0') || ENAME) FROM EMP WHERE LPAD(SAL, 4 , '0')=1250;

MAX(LPAD)

5. UPDATE 

UPDATE T2
SET C1 = 4
WHERE C1 = 2;

6. UNION , UNION ALL , MINUS , INTERSECT

CREATE TABLE T1 (
    C1 INT
);

CREATE TABLE T2 (
    C1 INT
);

CREATE TABLE T3 (
    C1 INT
);
INSERT INTO T1 (C1) VALUES (1);
INSERT INTO T1 (C1) VALUES (2);
INSERT INTO T1 (C1) VALUES (3);


INSERT INTO T2 (C1) VALUES (1);
INSERT INTO T2 (C1) VALUES (2);

INSERT INTO T3 (C1) VALUES (2);
INSERT INTO T3 (C1) VALUES (3);

1> UNION ALL

SELECT C1 FROM T1 UNION ALL SELECT C1 FROM T2

UNION ALL

==> 모조리 합하기!

 

2> UNION

SELECT C1 FROM T1 UNION SELECT C1 FROM T2

union

==> 중복된거 하나로 만든 합집합(PYTHON의 SET과 비슷)

 

3> MINUS

SELECT C1 FROM T1 MINUS SELECT C1 FROM T2

MINUS

==> 차집합

 

4> INTERSECT

SELECT C1 FROM T1 INTERSECT SELECT C1 FROM T2

INTERSECT

==> 교집합

 

7. 데이터 타입 변경 , 기존 DATA 변경

ALTER TABLE T1 MODIFY C1 VARCHAR2(10);

==> T1 테이블의 C1 열의 데이터타입을 변경

 

UPDATE T1 SET C1 = CASE WHEN C1 = 1 THEN 'A'
                        WHEN C1 = 2 THEN 'A'
                        WHEN C1 = 3 THEN 'B'
                   END;

==> C1 열의 값 변경

 

ALTER TABLE T1 ADD C2 NUMBER(10);

==> C2 열 생성 및 데이터 타입 설정

UPDATE T1 SET C2 = 1;

==> C2 열의 모든 값 1로 변경

T1

 

ALTER TABLE T1 ADD C2 NUMBER(10);
UPDATE T1 SET C2 = 1;


UPDATE T2 SET C1 = CASE WHEN C1 = 1 THEN 'B'
                        WHEN C1 = 4 THEN 'C'
                   END;

INSERT INTO T2 (C1) VALUES ('C');
ALTER TABLE T2 ADD C2 NUMBER(10);
UPDATE T2 SET C2 = 1;

SELECT * FROM T2;

T2

SELECT C1 , SUM(C2) AS C2 , SUM(C3) AS C3
FROM (SELECT C1 , C2 , NULL AS C3 FROM T1
                                  UNION ALL
                                  SELECT C1 , NULL AS C2 ,C3 FROM T2)
GROUP BY C1 ORDER BY C1;

==> UNION ALL에서 NULL값 끼리는 GROUP BY 안된다.