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 INNERJOIN表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;

●LEFTOUTER JOIN(左:全体＋右:マッチングのみ)
SELECT*FROM表1,表2 WHERE表1.表2.列(+);

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

● ANSI SQL
SELECT*FROM表1 LEFTOUTER 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 T 1 RIGHT OUTER JOIN T 2 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;