class 20-21 : SQL

SELECT * FROM EMP ORDER BY ENAME ASC;

-- 연봉 구해보기
SELECT ENAME AS 이름, SAL AS 월급, SAL*12 AS 연봉 FROM EMP;

-- 오름차순, 내림차순
SELECT * FROM EMP ORDER BY DEPTNO ASC, SAL DESC;

-- 하나만 조회
SELECT * FROM EMP WHERE EMPNO = 7566;

-- DEPTNO 30
SELECT * FROM EMP WHERE DEPTNO = 30 AND JOB = 'SALESMAN';
SELECT * FROM EMP WHERE DEPTNO = 30 OR JOB = 'SALESMAN';

-- SAL 3000 이상
SELECT * FROM EMP WHERE SAL >= 3000;

-- SAL 3000이 아닌 사람
SELECT * FROM EMP WHERE SAL NOT IN 3000;
SELECT * FROM EMP WHERE SAL <> 3000;
SELECT * FROM EMP WHERE SAL ^= 3000;
SELECT * FROM EMP WHERE SAL != 3000;

-- JOB 
SELECT * FROM EMP WHERE JOB = 'MANAGER' OR JOB = 'CLERK';
SELECT * FROM EMP WHERE JOB IN ( 'MANAGER', 'CLERK' );
SELECT * FROM EMP WHERE JOB NOT IN ( 'MANAGER', 'CLERK' );
SELECT * FROM EMP WHERE DEPTNO IN (10, 20);

-- 사이의 값
SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;
SELECT * FROM EMP WHERE EMPNO BETWEEN 7000 AND 7600;
SELECT * FROM EMP WHERE ENAME BETWEEN 'A' AND 'D';

-- LIKE 연산자
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
SELECT * FROM EMP WHERE ENAME LIKE '%S%';
SELECT * FROM EMP WHERE ENAME LIKE 'A%';
SELECT * FROM EMP WHERE ENAME LIKE '_L%';
SELECT * FROM EMP WHERE ENAME LIKE '%AM%';
SELECT * FROM EMP WHERE ENAME LIKE '%M%I%';
SELECT * FROM EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%M%';
SELECT * FROM EMP WHERE ENAME LIKE '%ER';

-- 몇 번째 글짜
SELECT * FROM EMP WHERE ENAME LIKE '_L%';
SELECT * FROM EMP WHERE ENAME LIKE '__A%';
SELECT * FROM EMP WHERE ENAME LIKE '_____';

-- NULL CHECK
SELECT * FROM EMP WHERE COMM IS NOT NULL;

-- GROUP BY
SELECT AVG(SAL) AS 평균급여, DEPTNO FROM EMP; -- 오류남
SELECT AVG(SAL) AS 평균급여, DEPTNO FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO, JOB, AVG(SAL) FROM EMP GROUP BY JOB; -- 오류남
SELECT DEPTNO, JOB, AVG(SAL) FROM EMP GROUP BY DEPTNO, JOB;
SELECT ENAME, DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO, ENAME;

-- 1
SELECT * FROM EMP WHERE ENAME LIKE '%S';
-- 2 
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP 
WHERE DEPTNO = 30 AND JOB = 'SALESMAN';
-- 3
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP 
WHERE DEPTNO IN (20, 30) AND SAL > 2000;
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE DEPTNO IN (20, 30) 
MINUS SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP 
WHERE DEPTNO IN (20, 30) AND SAL <= 2000;
-- 4
SELECT * FROM EMP 
WHERE SAL < 2000 OR SAL > 3000;
-- 5
SELECT ENAME, EMPNO, SAL, DEPTNO FROM EMP 
WHERE SAL NOT BETWEEN 1000 AND 2000 AND ENAME LIKE '%E%' AND DEPTNO = 30;
-- 6
SELECT * FROM EMP 
WHERE ENAME NOT LIKE '_L%' 
AND JOB IN ('MANAGER', 'CLERK')
AND MGR IS NOT NULL;

-- UNION / INTERSECT
SELECT * FROM EMP WHERE DEPTNO = 10 
UNION SELECT * FROM EMP WHERE DEPTNO = 10;
SELECT * FROM EMP WHERE DEPTNO = 10 
UNION ALL SELECT * FROM EMP WHERE DEPTNO = 10;
SELECT * FROM EMP INTERSECT
SELECT * FROM EMP WHERE DEPTNO = 10;

-- 오라클 함수
SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME) FROM EMP;
SELECT * FROM EMP;
SELECT ENAME, LENGTH(ENAME) FROM EMP;
SELECT ENAME FROM EMP WHERE LENGTH(ENAME) = 6;
SELECT LENGTH('오라클'), LENGTHB('오라클 SQL') FROM DUAL;

-- DUMMY TABLE 
-- DUAL

-- 글자 잘라내기
SELECT JOB, SUBSTR(JOB, 1, 2) FROM EMP;
SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, -3, 2), SUBSTR(JOB, -3) FROM EMP;
SELECT SUBSTR(JOB, -LENGTH(JOB)) FROM EMP;

-- 문자열의 위치를 찾는 함수
SELECT INSTR('HELLO, ORACLE', 'L') FROM DUAL;
SELECT INSTR('HELLO, ORACLE', 'L', 1, 2) FROM DUAL; -- 1번부터 찾는데 두 번째 L
SELECT INSTR('Oracle databases 11g releases', 'DATABASES') FROM DUAL;
SELECT INSTR('Oracle databases 11g releases', LOWER('DATABASES')) FROM DUAL;
SELECT * FROM EMP WHERE INSTR(ENAME, 'S') > 0;

-- REPLACE
SELECT '010-1111-2222' AS ORIGINAL, 
REPLACE('010-1111-2222', '-', ' ') AS NEW1, 
REPLACE('010-1111-2222', '-') AS NEW2 FROM DUAL;
SELECT '010 1111-2222' AS ORIGINAL, 
REPLACE(REPLACE('010 1111-2222', '-'), ' ') AS NEW1 FROM DUAL;


-- RPAD, LPAD
SELECT 'ORACLE' AS ORACLE,
LPAD('ORACLE', 10, '#') AS LPAD1,
RPAD('ORACLE', 10, '#') AS RPAD1,
LPAD('ORACLE', 10) AS LPAD2,
RPAD('ORACLE', 10) AS RPAD2
FROM DUAL;
SELECT RPAD(SUBSTR(ENAME, 1, 2), LENGTH(ENAME), '*') FROM EMP; 
SELECT RPAD('123456-2', 14, '*') AS JUMIN FROM DUAL;
SELECT RPAD('010-2333-', 13, '*') AS JUMIN FROM DUAL;

-- CONCAT
SELECT CONCAT(EMPNO, ENAME) AS CONCAT,
CONCAT(EMPNO, CONCAT(' : ', ENAME)) AS doubleCONCAT FROM EMP;
SELECT (ENAME || ' : ' || JOB) FROM EMP;

-- TRIM
-- 회원가입이나 로그인할 때 뒤에 스페이스바를 치고 입력해서 공백이 있는 경우 활용
SELECT '[' || TRIM('    ORACLE          ') || ']' FROM DUAL;
SELECT '[' || LTRIM('    ORACLE          ') || ']' FROM DUAL;
SELECT '[' || RTRIM('    ORACLE          ') || ']' FROM DUAL;
SELECT '[' || TRIM(BOTH '_' FROM '__ORACLE__') || ']' FROM DUAL;
SELECT '[' || RTRIM('__ORACLE__', '_') || ']' FROM DUAL;
SELECT '[' || TRIM(TRAILING '_' FROM '_ _ORACLE_ _') || ']' FROM DUAL;
SELECT '!' || REPLACE('     ORACLE        ', ' ', '') || '!' FROM DUAL;

-- 숫자함수
SELECT 1234.59 AS NUM FROM DUAL;
SELECT TRUNC(1234.59) FROM DUAL; --절사
SELECT ROUND(1234.3) FROM DUAL;
SELECT ROUND(1234.59) FROM DUAL;
SELECT ROUND(1234.59, 1) FROM DUAL;
SELECT CEIL(1234.3) FROM DUAL;
SELECT CEIL(1234.59) FROM DUAL;
SELECT FLOOR(1234.3) AS NUM FROM DUAL;
SELECT FLOOR(1234.59) FROM DUAL;
SELECT MOD(11, 3) FROM DUAL;

-- 날짜
SELECT SYSDATE AS TODAY FROM DUAL;
SELECT SYSDATE - 1 AS YESTERDAY FROM DUAL;

-- 1
SELECT EMPNO,
RPAD(SUBSTR(EMPNO, 1, 2), LENGTH(EMPNO), '*') AS MASKING_EMPNO,
ENAME,
RPAD(SUBSTR(ENAME, 1, 1), LENGTH(ENAME), '*')AS MASKING_ENAME
FROM EMP 
WHERE LENGTH(ENAME) = 5;

-- 2 
SELECT EMPNO,
ENAME,
TRUNC(SAL / 21.5, 2) AS DAY_PAY,
ROUND(SAL / (21.5 * 8), 1) AS TIME_PAY
FROM EMP;

-- 3 
SELECT EMPNO,
ENAME, 
HIREDATE,
TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 3), '월요일'), 'YYYY-MM-dd') AS R_JOB, 
NVL(TO_CHAR(COMM), 'N/A') AS COMM
FROM EMP;

SELECT EMPNO,
ENAME, 
HIREDATE,
TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 3), '월요일'), 'YYYY-MM-dd') AS R_JOB, 
DECODE(COMM, NULL, 'n/a', COMM) AS COMM 
FROM EMP;

-- 응용
SELECT 
NEXT_DAY(SYSDATE, '목요일') FROM DUAL;
SELECT 
TO_CHAR(NEXT_DAY(SYSDATE, '목요일'), 'YYYY-MM-dd') || ' 목요일' FROM DUAL;

-- 다중행 함수
SELECT SUM(SAL) AS TOTAL FROM EMP;
SELECT TRUNC(AVG(SAL)) AS AVG_SAL FROM EMP;
SELECT TO_CHAR(AVG(SAL), '$999,999') AS AVG_SAL FROM EMP;
SELECT COUNT(*) FROM EMP;
SELECT COUNT(COMM) FROM EMP;
SELECT COUNT(JOB) FROM EMP;
SELECT COUNT(DISTINCT JOB) FROM EMP;

SELECT SUM(SAL) AS SUM_SAL,
MAX(SAL) AS MAX_SAL, 
TRUNC(AVG(SAL), 2) AS AVG_SAL
FROM EMP;

SELECT AVG(DISTINCT SAL) FROM EMP 
WHERE DEPTNO = 30;

SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = 30;

-- GROUP BY
SELECT * FROM EMP;
SELECT DEPTNO, JOB, AVG(SAL) AS TOTAL FROM EMP GROUP BY JOB, DEPTNO;
SELECT JOB, TO_CHAR(MAX(SAL), '$999,999') AS MAX_SAL FROM EMP GROUP BY JOB;

SELECT JOB,
COUNT(JOB) AS COUNT_JOB,
TO_CHAR(SUM(SAL), '$999,999') AS SUM_SAL,
TO_CHAR(MAX(SAL), '$999,999') AS MAX_SAL, 
TO_CHAR(TRUNC(AVG(SAL)), '$999,999') AS AVG_SAL
FROM EMP GROUP BY JOB;

SELECT DEPTNO,
COUNT(DEPTNO) AS COUNT_DN,
TO_CHAR(SUM(SAL), '$999,999') AS SUM_SAL,
TO_CHAR(MAX(SAL), '$999,999') AS MAX_SAL, 
TO_CHAR(TRUNC(AVG(SAL)), '$999,999') AS AVG_SAL
FROM EMP GROUP BY DEPTNO;

SELECT DEPTNO,
MIN(SAL) AS MIN_SAL FROM EMP GROUP BY DEPTNO;

-- 그룹 바이는 편리하다 (없었으면 유니온 올)
SELECT DEPTNO, TRUNC(AVG(SAL)) AS AVG_SAL 
FROM EMP 
GROUP BY DEPTNO ORDER BY DEPTNO ASC;

SELECT '10' AS DEPTNO, TRUNC(AVG(SAL)) AS AVG_SAL 
FROM EMP WHERE DEPTNO = 10 UNION ALL
SELECT '20' AS DEPTNO, TRUNC(AVG(SAL)) AS AVG_SAL 
FROM EMP WHERE DEPTNO = 20 UNION ALL
SELECT '30' AS DEPTNO, TRUNC(AVG(SAL)) AS AVG_SAL 
FROM EMP WHERE DEPTNO = 30;

SELECT 
DEPTNO, JOB, AVG(SAL) FROM EMP 
GROUP BY DEPTNO, JOB 
ORDER BY DEPTNO, JOB;

SELECT
DEPTNO, JOB, AVG(SAL) FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 1500
ORDER BY DEPTNO, JOB;

-- 순서
-- 1.WHERE 2.GROUP BY 3.HAVING 4.ORDER BY
SELECT * FROM EMP ORDER BY JOB;
-- 1
SELECT 
DEPTNO, TRUNC(AVG(SAL)) AS AVG_SAL, MAX(SAL) AS MAX_SAL,
MIN(SAL) AS MIN_SAL, COUNT(*) AS CNT FROM EMP 
GROUP BY DEPTNO;

-- 2
SELECT JOB, COUNT(*) FROM EMP 
GROUP BY JOB HAVING COUNT(*) >= 3;

-- 3
SELECT 
TO_CHAR(HIREDATE, 'YYYY') AS HIRE_YEAR, 
DEPTNO, COUNT(*) AS CNT
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO
ORDER BY HIRE_YEAR;

-- 4
SELECT NVL2(COMM, 'O', 'X') AS EXIST_COMM, 
COUNT(*) AS CNT FROM EMP GROUP BY NVL2(COMM, 'O', 'X');

SELECT * FROM DEPT;
SELECT * FROM EMP;
SELECT * FROM SALGRADE;

SELECT * FROM DEPT, EMP ORDER BY EMPNO;
SELECT * FROM DEPT, EMP WHERE EMP.DEPTNO = DEPT.DEPTNO ORDER BY EMPNO;

--- JOIN
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMPNO;

SELECT * FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO
ORDER BY EMPNO;

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO 
ORDER BY D.DEPTNO, E.EMPNO;

SELECT E.EMPNO, E.ENAME, E.SAL, E.JOB,
D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND SAL >= 3000;

SELECT * FROM SALGRADE;

-- 비등가조인 (같은 값이 없음)
SELECT * FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

-- 같은 테이블 안에서 엮어내기
SELECT 
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO, 
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;


---------------------
--- 외부조인

-- 왼쪽 외부 조인 / LEFT OUTER JOIN
SELECT 
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_NAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+)
ORDER BY E1.EMPNO;

-- 오른쪽 외부 조인 / RIGHT OUTER JOIN
SELECT 
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_NAME
FROM EMP E1, EMP E2
WHERE E1.MGR(+) = E2.EMPNO
ORDER BY E1.EMPNO;

SELECT COUNT(EMPNO) FROM EMP WHERE MGR IS NULL;
SELECT * FROM EMP;

-- NATURAL JOIN
-- 알아서 값이 같은 열을 찾는다
SELECT  
E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
DEPTNO, D.DNAME, D.LOC
FROM
EMP E NATURAL JOIN DEPT D
WHERE SAL >= 3000
ORDER BY DEPTNO, E.EMPNO;

-- JOIN / USING
-- 값이 같은 열을 명시한다
SELECT 
*
FROM EMP E JOIN DEPT D USING (DEPTNO) 
WHERE SAL >= 3000
ORDER BY DEPTNO, E.EMPNO;

-- JOIN / ON
SELECT * 
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE SAL >= 3000
ORDER BY E.DEPTNO, EMPNO;

SELECT * 
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE SAL <= 3000
ORDER BY E.DEPTNO, EMPNO;

-- 오라클 방식과 디폴트 방식 비교
--- LEFT OUTER JOIN 
SELECT 
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;

SELECT 
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_NAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+)
ORDER BY E1.EMPNO;

--- RIGHT OUTER JOIN 
SELECT 
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1 RIGHT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;

SELECT 
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_NAME
FROM EMP E1, EMP E2
WHERE E1.MGR(+) = E2.EMPNO
ORDER BY E1.EMPNO;

-- 전체 외부 조인
--- 오라클 (+)로는 전체 외부 조인을 사용할 수 없다 
SELECT 
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;


------ 문제

-- 1
SELECT D.DEPTNO, D.DNAME,
E.EMPNO, E.ENAME, E.SAL
FROM DEPT D JOIN EMP E ON D.DEPTNO = E.DEPTNO
WHERE E.SAL >= 2000;

-- 2
SELECT D.DEPTNO, D.DNAME,
TRUNC(AVG(E.SAL)) AS AVG_SAL, 
MAX(E.SAL) AS MAX_SAL, 
MIN(E.SAL) AS MIN_SAL,
COUNT(*) AS CNT 
FROM DEPT D JOIN EMP E ON D.DEPTNO = E.DEPTNO
GROUP BY D.DEPTNO, D.DNAME;

-- 3
SELECT 
D.DEPTNO, D.DNAME,
E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM DEPT D JOIN EMP E
ON D.DEPTNO = E.DEPTNO
ORDER BY D.DEPTNO, E.ENAME;

-- 4
SELECT
D.DEPTNO, D.DNAME, 
E1.EMPNO, E1.ENAME, E1.MGR, E1.SAL, E1.DEPTNO, 
LOSAL, HISAL, GRADE, 
E2.EMPNO AS MGR_EMPNO, 
E2.ENAME AS MGR_ENAME
FROM DEPT D, EMP E1, EMP E2, SALGRADE S
WHERE 
D.DEPTNO = E1.DEPTNO(+) 
AND E1.MGR = E2.EMPNO(+) 
AND E1.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
ORDER BY D.DEPTNO, EMPNO;

SELECT
D.DEPTNO, D.DNAME, E1.EMPNO, E1.ENAME, E1.MGR, E1.SAL,
E1.DEPTNO, LOSAL, HISAL, GRADE, 
E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM
EMP E1 FULL OUTER JOIN EMP E2 ON (E2.EMPNO = E1.MGR)
RIGHT OUTER JOIN DEPT D ON (D.DEPTNO = E1.DEPTNO)
FULL OUTER JOIN SALGRADE S ON (E1.SAL BETWEEN S.LOSAL AND S.HISAL)
ORDER BY D.DEPTNO, EMPNO;

# 서브쿼리
SELECT * FROM EMP;

-- 이름이 JONES보다 월급 많이 받는 사람 알고 싶다.
SELECT SAL FROM EMP
WHERE ENAME = 'JONES';

SELECT * FROM EMP
WHERE SAL > 2975;


-- QUERY안에 QUERY 또 쓰기...
SELECT * FROM EMP
WHERE SAL > (SELECT SAL FROM EMP
WHERE ENAME = 'JONES');

--JONES보다 입사일이 빠른 사람...
SELECT * FROM EMP
WHERE HIREDATE < (SELECT HIREDATE FROM EMP
WHERE ENAME = 'JONES');


-- 이름이 ALLEN인 사원의 추가수당보다 더 많은 추가 수당을 받는 사람 구해 보기...
SELECT COMM FROM EMP
WHERE ENAME = 'ALLEN';

SELECT * FROM EMP 
WHERE COMM > (SELECT COMM FROM EMP
WHERE ENAME = 'ALLEN');

-- 20번 부서에 속한 사원 중 전체 사원의 평균 급여보다 많은 급여를 받는 사원 정보 뽑아보기....

--SELECT AVG(SAL) FROM EMP;
SELECT E.EMPNO,E.ENAME, E.JOB, E.SAL, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO 
      AND D.DEPTNO = 20
      AND E.SAL > (SELECT AVG(SAL) FROM EMP);

-- DEPTNO 20, 30 부서 정보 다 뽑아 보기...
SELECT * FROM EMP
WHERE DEPTNO IN (20,30);

-- 각 부서별 최고 급여를 받는 사람과 동일한 급여를 받는 사람 출력 해보기...
SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;

SELECT * FROM EMP
WHERE SAL IN (2850,3000,5000);

SELECT * FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);  --같은 경우

SELECT * FROM EMP
WHERE SAL <> ANY (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT * FROM EMP
WHERE SAL > ALL (1000,2000,3000); -- 3000보다 큰 경우

SELECT * FROM EMP
WHERE SAL <> ALL (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO); -- 3000보다 큰 경우



--EXISTS
SELECT * FROM EMP
WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO  = 100);  -- 있거나 없거나...

SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND JOB = (SELECT JOB
                FROM EMP
               WHERE ENAME = 'ALLEN'); 


--CH91 연습 문제 1번
SELECT JOB FROM EMP
WHERE ENAME='ALLEN';

SELECT E.JOB ,E.EMPNO, E.ENAME,E.SAL, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND JOB = (SELECT JOB FROM EMP
WHERE ENAME='ALLEN') ;



--CH91 연습 문제 2번
--SELECT AVG(SAL) FROM EMP;
SELECT E.EMPNO, E.ENAME,D.DNAME,E.HIREDATE,D.LOC,E.SAL, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO 
AND E.SAL BETWEEN S.LOSAL AND S.HISAL 
AND E.SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY E.SAL DESC;


-- CH09 연슴문제3번
SELECT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO,D.DNAME,D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 10 
AND JOB NOT IN(SELECT JOB FROM EMP WHERE DEPTNO = 30);

SELECT * FROM DICT;

SELECT * FROM DICTIONARY;

SELECT TABLE_NAME FROM USER_TABLES;

SELECT OWNER,TABLE_NAME FROM ALL_TABLES;

SELECT * FROM DBA_TABLES;

--INDEX 는 검색을 빨리해야할때.... 
SELECT * FROM USER_INDEXES;

-- INDEX만들기..  PRIMARYKEY
CREATE INDEX IDX_EMP_SAL
ON EMP(SAL);

DROP INDEX IDX_EMP_SAL;

--VIEW
CREATE VIEW VW_EMP20 AS 
(SELECT EMPNO, ENAME,JOB,DEPTNO FROM EMP WHERE DEPTNO = 20);


SELECT * FROM USER_VIEWS;

SELECT * FROM VW_EMP20;

DROP VIEW VW_EMP20;

SELECT * FROM EMP;

SELECT ROWNUM,E.*
FROM EMP E;

SELECT ROWNUM, E.*
FROM EMP E
ORDER BY SAL DESC;


-- FROM절에 쓰인 서브쿼리를 INLINE VIEW라고도 한다.
SELECT ROWNUM,E.*
FROM (SELECT * FROM EMP ORDER BY SAL DESC) E
WHERE ROWNUM <= 5;

WITH E AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, E.*
FROM E
WHERE ROWNUM <= 5;

-- SEQUENCE  AUTO-INCREAMENT

CREATE SEQUENCE SEQ_DEPT_SEQUENCE
    INCREMENT BY 10
    START WITH 10
    MAXVALUE 90
    MINVALUE 0
    NOCYCLE
    CACHE 2;


SELECT * FROM USER_SEQUENCES;

CREATE TABLE DEPT_SEQUENCE AS SELECT * FROM DEPT WHERE 1 = 0;

SELECT * FROM DEPT_SEQUENCE;

INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL,'DATABASE','SEOUL');



-- 동의어
CREATE SYNONYM E FOR EMP;

RENAME "E" to EEE;

SELECT * FROM EEE;



--- 연습 문제
CREATE TABLE EMPIDX  AS SELECT * FROM EMP;

CREATE INDEX IDX_EMPIDX_EMPNO ON EMPIDX (EMPNO);

SELECT * FROM USER_INDEXES;

CREATE OR REPLACE VIEW EMPIDX_OVER15 AS (
    SELECT EMPNO,ENAME,JOB,DEPTNO,SAL,NVL2(COMM,'O','X') AS COMM
    FROM EMPIDX
    WHERE SAL > 1500
);

SELECT * FROM EMPIDX_OVER15;

CREATE  TABLE DEPTSEQ AS SELECT * FROM DEPT;

CREATE SEQUENCE SEQ_DEPTSEQ 
INCREMENT BY 1
START WITH 1
MAXVALUE 99
MINVALUE 1
NOCYCLE
NOCACHE;


INSERT INTO DEPTSEQ(DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPTSEQ.NEXTVAL,'DATABASE','SEOUL');


INSERT INTO DEPTSEQ(DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPTSEQ.NEXTVAL,'WEB','BUSAN');

INSERT INTO DEPTSEQ(DEPTNO,DNAME,LOC)
VALUES (SEQ_DEPTSEQ.NEXTVAL,'MOBILE','ILSAN');

SELECT * FROM DEPTSEQ;

-- 데이터 입력시 제약을 걸어서 무조건 입력받게 하기....
CREATE TABLE TABLE_NOTNULL (
    LOGIN_ID     VARCHAR2(20) NOT NULL,
    LOGIN_PWD    VARCHAR2(20) NOT NULL,
    TEL          VARCHAR2(20)
);

CREATE TABLE TABLE_NOTNULL02 (
    LOGIN_ID     VARCHAR2(20) CONSTRAINT TABLE_NOTNULL20_LOGIN_NOT_NULL NOT NULL,
    LOGIN_PWD    VARCHAR2(20) NOT NULL,
    TEL          VARCHAR2(20)
);

DESC TABLE_NOTNULL;

INSERT INTO TABLE_NOTNULL (LOGIN_ID,LOGIN_PWD,TEL)
VALUES ('TEST','1234','010-1111-2222');

INSERT INTO TABLE_NOTNULL (LOGIN_ID,LOGIN_PWD)
VALUES ('TEST2','5678');


UPDATE TABLE_NOTNULL
SET TEL = '010-1111-222';

ALTER TABLE TABLE_NOTNULL 
MODIFY (TEL NOT NULL);

SELECT * FROM TABLE_NOTNULL;

SELECT * FROM USER_CONSTRAINTS;

CREATE TABLE TABLE_UNIQUE (
    LOGIN_ID    VARCHAR2(20) UNIQUE,
    LOGIN_PWD   VARCHAR2(20) NOT NULL,
    TEL         VARCHAR2(20)
);
DROP TABLE TABLE_UNIQUE;
INSERT INTO TABLE_UNIQUE(LOGIN_ID,LOGIN_PWD,TEL)
VALUES (NULL,'1234','1111');

COMMIT;

DESC TABLE_UNIQUE;

SELECT * FROM TABLE_UNIQUE;


CREATE TABLE TABLE_PK (
    LOGIN_ID    VARCHAR2(20)    PRIMARY KEY,
    LOGIN_PWD   VARCHAR2(20)     NOT NULL,
    TEL         VARCHAR2(20)    
);

-- PRIMARY KEY를 제약 조건으로 걸면 UNIQUE, NOT NULL, INDEX도 설정 된다.
INSERT INTO TABLE_PK(LOGIN_ID,LOGIN_PWD,TEL)
VALUES ('JJANG051','1234','1111');

-- FOREIGN KEY  정규화
SELECT * FROM EMP;

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (9999,'HONG','CLERK','7788',TO_DATE('2023-02-28', 'YYYY/MM/DD'),1200, NULL, 50);

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP','DEPT');

SELECT * FROM DEPT;

CREATE TABLE DEPT_FK (
    DEPTNO  NUMBER(2) CONSTRAINT DEPTFK_DEPTNO_PK PRIMARY KEY,
    DNAME VARCHAR2(20),
    LOC VARCHAR2(20)
);


CREATE TABLE EMP_FK  (
    EMPNO NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(10),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK (DEPTNO)
);

-- 비밀번호  8자리 입력 


INSERT INTO DEPT_FK VALUES (10,'AAA','DANGSAN');
INSERT INTO DEPT_FK VALUES (20,'BBB','ILSAN');
INSERT INTO EMP_FK VALUES (2222,'TEST_USER','TEST_JOB',NULL, TO_DATE('2023-02-28','YYYY-MM-DD'),
    3000,NULL,20
);

CREATE TABLE TABLE_CHECK(
    LOGIN_ID     VARCHAR2(20) CONSTRAINT TABLE_CHECK_LOGIN_PK PRIMARY KEY,
    LOGIN_PWD    VARCHAR2(20) CONSTRAINT TABLE_LOGINPWD_CH CHECK ( LENGTH(LOGIN_PWD) > 3 ),
    TEL          VARCHAR2(20)
);

INSERT INTO TABLE_CHECK VALUES ('TEST_ID','1234','1111-2222');



CREATE TABLE TABLE_DEFAULT(
    LOGIN_ID     VARCHAR2(20) CONSTRAINT TABLE_CHECK_LOGIN_PK02 PRIMARY KEY,
    LOGIN_PWD    VARCHAR2(20) CONSTRAINT TABLE_LOGINPWD_CH02 CHECK ( LENGTH(LOGIN_PWD) > 3 ),
    TEL          VARCHAR2(20),
    REGDATE      DATE DEFAULT SYSDATE
);
INSERT INTO TABLE_DEFAULT (LOGIN_ID,LOGIN_PWD,TEL) VALUES ('TEST_ID02','1234','1111-2222');
SELECT * FROM TABLE_DEFAULT;
-- 안이 벅힌다.



CREATE TABLE DEPT_CONST (
  DEPTNO NUMBER(2) CONSTRAINT DEPTCONST_DEPTNO_PK PRIMARY KEY,
  DNAME VARCHAR2(14) CONSTRAINT  DEPTCONST_DEPTNO_UNIQUE UNIQUE , 
  LOC VARCHAR2(20) CONSTRAINT  DEPTCONST_DEPTNO_NN NOT NULL 
);

CREATE TABLE DEPT_CONST02 (
  DEPTNO NUMBER(2)  PRIMARY KEY,
  DNAME VARCHAR2(14)  UNIQUE , 
  LOC VARCHAR2(20)  NOT NULL 
);


CREATE TABLE EMP_CONST(
    EMPNO       NUMBER(4)       CONSTRAINT EMPCONST_EMPNO_PK PRIMARY KEY,
    ENAME       VARCHAR2(10)    CONSTRAINT EMPCONST_ENAME_NN NOT NULL,
    JOB         VARCHAR2(9),
    TEL         VARCHAR2(20) CONSTRAINT EMPCONST_TEL_UNQ UNIQUE,
    HIREDATE    DATE    ,
    SAL         NUMBER(7) CONSTRAINT EMPCONST_SAL_CHK CHECK(SAL BETWEEN 1000 AND 9999),
    COMM        NUMBER(7) ,
    DEPTNO      NUMBER(2) CONSTRAINT EMPCONST_DEPTNO_FK REFERENCES DEPT_CONST (DEPTNO)
);