조인 잔뜩있음. 실습도 잔뜩있음.. ORACLE 20220322

● INNER JOIN
SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼=테이블2.컬럼;

CREATE OR REPLACE VIEW ORDER1VIEW AS 
SELECT 
    ITEMORDER1.*, MEMBER1.USERNAME, MEMBER1.USERADDR
FROM MEMBER1, (
    SELECT 
        ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.* 
    FROM 
        ITEM1, ORDER1 
    WHERE 
        ITEM1.ITEMNO=ORDER1.ORDITEM) ITEMORDER1
WHERE MEMBER1.USERID=ITEMORDER1.ORDID;

SELECT * FROM ORDER1VIEW;

INSERT INTO ORDER1(ORDNO, ORDCNT, ORDDATE, ORDITEM, ORDID)
    VALUES(SEQ_ORDER1_ORDNO.NEXTVAL, 10, CURRENT_DATE, 1001, 'A');

● ANSI SQL(ORACLE, MYSQL, MSSQL...)
SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.컬럼=테이블2.컬럼;

SELECT 
    ITEMORDER1.*, MEMBER1.USERNAME, MEMBER1.USERADDR
FROM MEMBER1 INNER JOIN (
    SELECT 
        ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.* 
    FROM 
        ITEM1, ORDER1 
    WHERE 
        ITEM1.ITEMNO=ORDER1.ORDITEM) ITEMORDER1
ON MEMBER1.USERID=ITEMORDER1.ORDID;

---

● LEFT OUTER JOIN(왼쪽: 전체 + 오른쪽: 일치하는 것만)
SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼=테이블2.컬럼(+);

SELECT
    * 
FROM
    ITEM1, ORDER1 
WHERE
    ITEM1.ITEMNO=ORDER1.ORDITEM(+);

● ANSI SQL
SELECT * FROM 테이블1 LEFT OUTER JOIN 테이블2 ON 테이블1.컬럼=테이블2.컬럼;

SELECT 
    * 
FROM 
    ITEM1 
LEFT OUTER JOIN 
    ORDER1
ON
    ITEM1.ITEMNO=ORDER1.ORDITEM;

---

● RIGHT OUTER JOIN

SELECT *
FROM
    ORDER1, MEMBER1
WHERE 
    ORDER1.ORDID(+)=MEMBER1.USERID; 

● ANSI SQL
SELECT * FROM T1 RIGHT OUTER JOIN T2 WHERE 조건;

---

● FULL OUTER JOIN(ONLY ANSI)

SELECT * FROM ORDER1 FULL OUTER JOIN MEMBER1
ON ORDER1.ORDID=MEMBER1.USERID;

---

SELECT
    ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.*
FROM
    ITEM1, ORDER1
WHERE
    ITEM1.ITEMNO=ORDER1.ORDITEM AND ITEM1.ITEMPRICE >= 500;

● ANSI

SELECT
    ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.*
FROM
    ITEM1 INNER JOIN ORDER1
ON
    ITEM1.ITEMNO=ORDER1.ORDITEM
WHERE
    ITEM1.ITEMPRICE >= 500;

---

● 내장함수(TO_CHAR)

SELECT
    NO, NAME, PAY, TO_CHAR(PAY, '999,999,999') PAYMENT,
    REGDATE, TO_CHAR(REGDATE, 'YYYY"년 "MM-DD HH24:MI:SS')
FROM 
    EMPLOYEE; 

---

● 실습

SELECT * FROM DEPARTMENT;

DELETE FROM DEPARTMENT;

INSERT INTO DEPARTMENT(NO, NAME, AREA)
VALUES (101, '영업부', '');
INSERT INTO DEPARTMENT(NO, NAME, AREA)
VALUES (102, '총무부', '');
INSERT INTO DEPARTMENT(NO, NAME, AREA)
VALUES (103, '기획부', '');
INSERT INTO DEPARTMENT(NO, NAME, AREA)
VALUES (104, '홍보부', '');
COMMIT;

SELECT * FROM EMPLOYEE;

DELETE FROM EMPLOYEE;

CREATE SEQUENCE SEQ_EMP_NO START WITH 1001 INCREMENT BY 1 NOMAXVALUE NOCACHE;

● 번호, 이름, 부서번호(101 - 104), 상사번호, 직급, 급여, 등록일)

INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '가나다', 101, NULL, '부장', 3000000, CURRENT_DATE);
INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '홍길동', 102, 1001, '과장', 2500000, CURRENT_DATE);
INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '장길산', 103, 1002, '부장', 3500000, CURRENT_DATE);
INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '임꺽정', 104, 1003, '대리', 3000000, CURRENT_DATE);
INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '이민정', 101, 1004, '대리', 4000000, CURRENT_DATE);
INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '소지품', 102, 1005, '부장', 3500000, CURRENT_DATE);
INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '모니터', 103, 1006, '과장', 3000000, CURRENT_DATE);
INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '키보드', 104, 1007, '차장', 2500000, CURRENT_DATE);
INSERT INTO EMPLOYEE(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP_NO.NEXTVAL, '마우스', 101, 1008, '차장', 2000000, CURRENT_DATE);
COMMIT;

● 부서테이블 영업부-대전, 총무부-서울, 나머지-부산 설정

UPDATE DEPARTMENT SET AREA =
   CASE
       WHEN (NAME = '영업부') THEN '대전'
       WHEN (NAME = '총무부') THEN '서울'
       ELSE '부산'
   END;

● 사원테이블에서 번호, 이름, 직급을 번호 순으로 내림차순 조회

SELECT 
   NO, NAME, POSITION
FROM 
   EMPLOYEE 
ORDER BY NO DESC;

● 사원테이블에서 급여가 0~200이면 세금을 5%, 201~300 10%, 나머지는 15%로 표시(번호, 이름, 급여, 세금)

SELECT NO, NAME, PAY, 
CASE 
    WHEN (PAY >=0 AND PAY<=2000000) THEN PAY*0.05 
    WHEN (PAY >=2010000 AND PAY<=3000000) THEN PAY*0.1 
    ELSE PAY*0.15
END TEX 
FROM EMPLOYEE;

● 사원테이블에서 영업부와 총무부만 이름으로 오름차순 조회

SELECT
   *
FROM
   EMPLOYEE
WHERE 
   DEPTNO=101 OR DEPTNO=102
ORDER BY NAME ASC;

● 사원테이블과 부서테이블 INNER JOIN하여 조회(번호, 이름, 급여, 부서명, 지역)

SELECT 
   DEPARTMENT.NO, EMPLOYEE.NAME, EMPLOYEE.PAY, DEPARTMENT.NAME, DEPARTMENT.AREA
FROM 
   DEPARTMENT, EMPLOYEE
WHERE 
   DEPARTMENT.NO=EMPLOYEE.DEPTNO;
      

---

● 그룹. 반별 학생 수

SELECT SDCLS, COUNT(*) FROM STUDENT1 GROUP BY SDCLS;

● 반별 합계

SELECT SDCLS, SUM(SCOREKOR) FROM STUDENT1 GROUP BY SDCLS;

● 수학점수가 50점 이상인 학생들의 반별 평균점수

SELECT SDCLS, 
    ROUND(AVG(SCOREMATH), 1)
FROM STUDENT1 WHERE SCOREMATH >= 50 GROUP BY SDCLS;

● 반별 평균 수학점수가 50점 이상인 것

SELECT SDCLS, ROUND(AVG(SCOREMATH), 1)
FROM STUDENT1 GROUP BY SDCLS HAVING ROUND(AVG(SCOREMATH), 1) >= 50;

● 분별 합계

SELECT TO_CHAR(REGDATE, 'YYYY-MM-DD HH24:MI'), COUNT(*)
FROM STUDENT1 GROUP BY TO_CHAR(REGDATE, 'YYYY-MM-DD HH24:MI');

---

● 실습

SELECT * FROM ORDER1;
SELECT * FROM ITEM1;
SELECT * FROM MEMBER1;

SELECT ORDITEM FROM (
    SELECT
        ITEMORDER1.*, MEMBER1.USERNAME, MEMBER1.USERADDR
    FROM MEMBER1, (
        SELECT
            ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ITEM1.ITEMQTY, ORDER1.*
        FROM
            ITEM1, ORDER1
        WHERE
            ITEM1.ITEMNO=ORDER1.ORDITEM) ITEMORDER1
    WHERE MEMBER1.USERID=ITEMORDER1.ORDID) ORDER1VIEW
GROUP BY ORDITEM;

SELECT * FROM ORDER1VIEW;

● 물품별 주문수량 합계
먼저 필요한 테이블들을 JOIN함. WHERE 외래키

SELECT * FROM ORDER1 O, ITEM1 I WHERE O.ORDITEM=I.ITEMNO;

● 물품코드, 주문수량합계를 물품코드로 그루핑

SELECT O.ORDITEM 물품코드, SUM(O.ORDCNT) 주문수량합계 
FROM ORDER1 O, ITEM1 I WHERE O.ORDITEM=I.ITEMNO 
GROUP BY O.ORDITEM;

● 물품별 재고수량 합계
JOIN: SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼=테이블2.컬럼;

SELECT * FROM ORDER1 O, ITEM1 I WHERE O.ORDITEM=I.ITEMNO;

SELECT I.ITEMNO 물품코드, SUM(I.ITEMQTY) 재고수량합계 
FROM ORDER1 O, ITEM1 I 
WHERE O.ORDITEM=I.ITEMNO GROUP BY I.ITEMNO;

● 재고수량 100개 미만인 물품 개수

SELECT COUNT(*) FROM ITEM1 WHERE ITEMQTY < 100;

● 시간대별 주문수량 합계

SELECT TO_CHAR(ORDDATE, 'YYYY-MM-DD HH24') 시간, SUM(ORDCNT) 주문수량 
FROM ORDER1 
GROUP BY TO_CHAR(ORDDATE, 'YYYY-MM-DD HH24') ;

● 고객별 주문수량, 주문금액 합계

SELECT * FROM ORDER1 O, ITEM1 I WHERE O.ORDITEM=I.ITEMNO;

SELECT O.ORDID, COUNT(*), SUM(ITEMPRICE*ORDCNT) 
FROM ORDER1 O, ITEM1 I WHERE O.ORDITEM=I.ITEMNO 
GROUP BY O.ORDID;

● 멤버1, 주문자아이디, 주문수량, 주문금액합계 조회

SELECT * FROM MEMBER1 M1, (
    SELECT O.ORDID, COUNT(*) 주문수량, SUM(ITEMPRICE*ORDCNT) 주문금액합계
    FROM ORDER1 O, ITEM1 I WHERE O.ORDITEM=I.ITEMNO 
    GROUP BY O.ORDID) T1 
WHERE M1.USERID = T1.ORDID;

● 주문수량이 30개 미만인 주문내역의 개수
주문수량 30개 미만 조회

SELECT * FROM ORDER1 O, ITEM1 I WHERE O.ORDITEM=I.ITEMNO AND O.ORDCNT<30;

조회한 것의 개수

SELECT COUNT(*) FROM ORDER1 O, ITEM1 I WHERE O.ORDITEM=I.ITEMNO AND O.ORDCNT<30;

● 지역별 주문수량 합계

SELECT * FROM ORDER1 O, MEMBER1 M WHERE O.ORDID=M.USERID;

SELECT M.USERADDR 지역, SUM(O.ORDCNT) 주문수량합계 
FROM ORDER1 O, MEMBER1 M WHERE O.ORDID=M.USERID 
GROUP BY M.USERADDR;

● MEMBER2+ITEM2를 조인하여 물품번호, 물품명, 가격, 수량, 판매자아이디, 판매자 이름 조회

SELECT 
    I.ITEMNO 물품번호, I.ITEMNAME 물품명, 
    I.ITEMPRICE 가격, I.ITEMQTY 수량,
    M.USERID 판매자아이디, M.USERNAME 판매자이름
FROM 
    ITEM2 I, MEMBER2 M
WHERE
    I.ITEMSELLER = M.USERID;

● 판매자가 B인 물품의 물품번호, 가격, 판매자이름, 나이 조회

SELECT M.USERID, I.ITEMNO, I.ITEMPRICE, M.USERNAME, M.USERAGE 
FROM ITEM2 I, MEMBER2 M 
WHERE I.ITEMSELLER = M.USERID AND M.USERID = 'B';

● 판매자별 물품수량, 재고수량합 조회

SELECT I.ITEMSELLER, COUNT(*), SUM(ITEMQTY) 
FROM ITEM2 I GROUP BY I.ITEMSELLER;

● 판매자별 COUNT(물품수량), SUM(재고수량합계), 판매자이름, 판매자 나이 조회

SELECT M.USERNAME, M.USERAGE, I1.* FROM MEMBER2 M,(    
    SELECT I.ITEMSELLER, COUNT(*), SUM(I.ITEMQTY)
    FROM ITEM2 I
    GROUP BY I.ITEMSELLER) I1 WHERE I1.ITEMSELLER = M.USERID;

● 재고수량합계가 100이상인 판매자별 물품수량 조회

SELECT I.ITEMSELLER, COUNT(*), SUM(ITEMQTY) FROM ITEM2 I
GROUP BY I.ITEMSELLER
HAVING SUM(I.ITEMQTY)>=100;

● 판매자가 a인 물품의 물품수량, 물품가격평균 조회

SELECT ITEMSELLER, COUNT(*), AVG(I.ITEMPRICE)
FROM ITEM2 I
WHERE ITEMSELLER = 'A' 
GROUP BY ITEMSELLER;

● 판매자별 재고수량이 가장 높은 것 1개씩 조회

SELECT * FROM (
    SELECT 
        I.*, 
        ROW_NUMBER() OVER( PARTITION BY ITEMSELLER ORDER BY ITEMQTY DESC ) QTY
    FROM ITEM2 I
) WHERE QTY=1;

뷰 사용

CREATE OR REPLACE VIEW ITEM2_VIEW AS SELECT I.*, ROW_NUMBER() OVER(
    PARTITION BY ITEMSELLER 
    ORDER BY ITEMQTY DESC) QTY
FROM ITEM2 I;
SELECT * FROM ITEM2_VIEW WHERE QTY =1;

---

● 3개 조인
● 주문내역

SELECT M.*, I.*, O.* FROM MEMBER2 M, ITEM2 I, ORDER2 O 
WHERE M.USERID=O.USERID AND I.ITEMNO=O.ITEMNO;

● 주문내역 + 금액

SELECT M.*, I.*, O.*, O.ORDCNT*I.ITEMPRICE 금액  
FROM MEMBER2 M, ITEM2 I, ORDER2 O 
WHERE M.USERID=O.USERID AND I.ITEMNO=O.ITEMNO;

● 일자별 주문수량, 주문총개수, 물품명, 가격, 재고수량

SELECT TO_CHAR(O.ORDDATE ,'YYYY-MM-DD') 주문일자, O.ITEMNO 물품번호,
COUNT(*) 주문수량, SUM(ORDCNT) 주문개수 
FROM ORDER2 O
GROUP BY( TO_CHAR(O.ORDDATE ,'YYYY-MM-DD'), O.ITEMNO );

● 고객별 주문수량, 주문금액, 물품명, 가격

SELECT O.USERID 고객아이디, O.ITEMNO 물품코드, SUM(O.ORDCNT) 주문수량 
FROM ORDER2 O GROUP BY(O.USERID, O.ITEMNO);