오라클 문법 연습 (210218)

SELECT ROUND(1234.5678) AS ROUND,
       ROUND(1234.5678, 0) AS ROUND_0,
       ROUND(1234.5678, 1) AS ROUND_1,
       ROUND(1234.5678, 2) AS ROUND_2,
       ROUND(1234.5678, -1) AS ROUND_MINUS1,
       ROUND(1234.5678, -2) AS ROUND_MINUS2
    FROM DUAL;
    
    
SELECT TRUNC(1234.5678) AS TRUNC,
       TRUNC(1234.5678, 0) AS TRUNC_0,
       TRUNC(1234.5678, 1) AS TRUNC_1,
       TRUNC(1234.5678, 2) AS TRUNC_2,
       TRUNC(1234.5678, -1) AS TRUNC_MINUS1,
       TRUNC(1234.5678, -2) AS TRUNC_MINUS2
    FROM DUAL;

SELECT TRUNC(1539.125023, 4) AS TRUNC_EX1,  --1539.1250
       TRUNC(4586.89453, 2) AS TRUNC_EX2,   --4586.89
       TRUNC(2560.48522, -1) AS TRUNC_EX3   --2560
    FROM DUAL;

SELECT CEIL(3.14),  --4
       FLOOR(3.14), --3
       CEIL(-3.14), --(-3)
       FLOOR(-3.14) --(-4)
    FROM DUAL;
    
SELECT MOD(15, 6),
       MOD(10, 2),
       MOD(11, 2)
    FROM DUAL;
    
SELECT SYSDATE AS NOW,
       SYSDATE-1 AS YESTERDAY,
       SYSDATE +1 AS TOMORROW
    FROM DUAL;

SELECT SYSDATE,
       ADD_MONTHS(SYSDATE, 3)
    FROM DUAL;
    
-- 입사 10주년이 되는 사원들 데이터 출력

SELECT EMPNO, ENAME, HIREDATE,
        ADD_MONTHS(HIREDATE, 120) AS WORK10YEARS
    FROM EMP;
    
-- 입사 32년 미만인 사원 데이터 출력

SELECT EMPNO, ENAME, HIREDATE, SYSDATE
    FROM EMP
   WHERE ADD_MONTHS(HIREDATE, 384) > SYSDATE;
   
SELECT SYSDATE,
        ADD_MONTHS(SYSDATE, 6) AS AFTER6MONTHS
    FROM DUAL;
    
SELECT EMPNO, ENAME, HIREDATE, SYSDATE,
       MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,
       MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTHS2,
       TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTHS3
    FROM EMP;
    
SELECT SYSDATE,
        NEXT_DAY(SYSDATE, '월요일'),
        LAST_DAY(SYSDATE)
    FROM DUAL;
    
SELECT EMPNO, ENAME, EMPNO + '500'
    FROM EMP
   WHERE ENAME = 'BLAKE';
   
SELECT 'ABCD' + EMPNO, EMPNO    --문자데이터이므로 덧셈 x
    FROM EMP
   WHERE ENAME = 'BLAKE';
   
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간
    FROM DUAL;
    
SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'MM') AS MM,
       TO_CHAR(SYSDATE, 'MON') AS MON,
       TO_CHAR(SYSDATE, 'MONTH') AS MONTH,
       TO_CHAR(SYSDATE, 'DD') AS DD,
       TO_CHAR(SYSDATE, 'DY') AS DY,
       TO_CHAR(SYSDATE, 'DAY') AS DAY
    FROM DUAL;
    
SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'MM') AS MM,
       TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = KOREAN') AS MON_KOR,
       TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = JAPANESE') AS MON_JPN,
       TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH') AS MON_ENG,
       TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = KOREAN') AS MONTH_KOR,
       TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = JAPANESE') AS MONTH_JPN,
       TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = ENGLISH') AS MONTH_ENG
    FROM DUAL; 

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'MM') AS MM,
    TO_CHAR(SYSDATE, 'DD') AS DD,
    TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = KOREAN') AS DY_KOR,
    TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = JAPANESE') AS DY_JPN,
    TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = ENGLISH') AS DY_EN,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = KOREAN') AS DAY_KOR,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = JAPANESE') AS DAY_JPN,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = ENGLISH') AS DAY_EN
   FROM DUAL;
   
SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'HH24:MI:SS') AS HH24MISS,
    TO_CHAR(SYSDATE, 'HH12:MI:SS AM') AS HHMISS_AM,
    TO_CHAR(SYSDATE, 'HH12:MI:SS P.M.') AS HHMISS_PM
   FROM DUAL;

SELECT SAL,
    TO_CHAR(SAL, '$999,999') AS SAL_$,
    TO_CHAR(SAL, 'L999,999') AS SAL_L,
    TO_CHAR(SAL, '999,999.00') AS SAL_1,
    TO_CHAR(SAL, '000,999,999.00') AS SA_2,
    TO_CHAR(SAL, '000999999.99') AS SAL_3,
    TO_CHAR(SAL, '999,999.00') AS SAL_4
   FROM EMP;
   
 SELECT 1300 - '1500',
        '1300' + 1500
    FROM DUAL;
    
SELECT '1300' - '1500'
    FROM DUAL;
    
SELECT TO_NUMBER('1,300', '999,999') - TO_NUMBER('1,500', '999,999')
    FROM DUAL;
    
SELECT TO_DATE('2018-07-14', 'YYYY-MM-DD') AS TODATE1,
       TO_DATE('20180714', 'YYYY-MM-DD') AS TODATE2
    FROM DUAL;

SELECT *
    FROM EMP
   WHERE HIREDATE > TO_DATE('1981/06/01', 'YYYY/MM/DD');

SELECT TO_DATE('49/12/10', 'YY/MM/DD') AS YY_YEAR_49,
       TO_DATE('49/12/10', 'RR/MM/DD') AS RR_YEAR_49,
       TO_DATE('50/12/10', 'YY/MM/DD') AS YY_YEAR_50,
       TO_DATE('50/12/10', 'RR/MM/DD') AS RR_YEAR_50,
       TO_DATE('51/12/10', 'YY/MM/DD') AS YY_YEAR_51,
       TO_DATE('51/12/10', 'RR/MM/DD') AS RR_YEAR_51
    FROM DUAL;

SELECT *
    FROM EMP
   WHERE HIREDATE > TO_DATE('1980/10/15', 'YYYY/MM/DD');
   
SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM,
    NVL(COMM, 0),
    SAL+NVL(COMM, 0)
   FROM EMP;

SELECT EMPNO, ENAME, COMM,
    NVL2(COMM, 'O', 'X'),
    NVL2(COMM, SAL*12+COMM, SAL*12) AS ANNSAL
   FROM EMP;
   
SELECT EMPNO, ENAME, JOB, SAL,
    DECODE(JOB,
            'MANAGER', SAL*1.1,
            'SALESMAN', SAL*1.05,
            'ANALYST', SAL,
            SAL*1.03) AS UPSAL
  FROM EMP;
  
SELECT EMPNO, ENAME, JOB, SAL,
    CASE JOB
        WHEN 'MANAGER' THEN SAL*1.1
        WHEN 'SALESMAN' THEN SAL*1.05
        WHEN 'ANALYST' THEN SAL
        ELSE SAL*1.03
    END AS UPSAL
   FROM EMP;
   
   
SELECT EMPNO, ENAME, COMM,
    CASE
        WHEN COMM IS NULL THEN '해당사항 없음'
        WHEN COMM = 0 THEN '수당없음'
        WHEN COMM > 0 THEN '수당 : ' || COMM
    END AS COMM_TEXT
   FROM EMP;
   
SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
    FROM EMP;
    
SELECT *
    FROM EMP
   WHERE UPPER(ENAME) = UPPER('scott');
   
SELECT *
    FROM EMP
   WHERE UPPER(ENAME) LIKE UPPER('%scott%');
   
SELECT UPPER(ENAME)
    FROM EMP;
    
SELECT ENAME, LENGTH(ENAME)
    FROM EMP;
    
SELECT ENAME, LENGTH(ENAME)
    FROM EMP
   WHERE LENGTH(ENAME) >= 5;
   
SELECT LENGTH('한글'), LENGTHB('한글')
    FROM DUAL;

SELECT *
    FROM EMP
   WHERE LENGTH(JOB) >= 6;


SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 5)
    FROM EMP;

SELECT SUBSTR(ENAME, 3)
    FROM EMP;

SELECT JOB,
    SUBSTR(JOB, -LENGTH(JOB)),
    SUBSTR(JOB, -LENGTH(JOB), 2),
    SUBSTR(JOB, -3)
   FROM EMP;
   
SELECT INSTR('HELLO, ORACLE', 'L') AS INSTR_1,
       INSTR('HELLO, ORACLE', 'L', 5) AS INSTR_2,
       INSTR('HELLO, ORACLE', 'L', 2, 2) AS INSTR_3
      FROM DUAL;
      
SELECT *
    FROM EMP
   WHERE INSTR(ENAME, 'S') > 0;
   
SELECT *
    FROM EMP
   WHERE ENAME LIKE '%S%';
   
SELECT '010-1234-5678' AS REPLACE_BEFORE,
       REPLACE('010-1234-5678', '-', ' ') AS REPLACE_1,
       REPLACE('010-1234-5678', '-') AS REPLACE_2
      FROM DUAL;

SELECT 'Oracle',
        LPAD('Oracle', 10, '#') AS LPAD_1,
        RPAD('Oracle', 10, '*') AS RPAD_1,
        LPAD('Oracle', 10) AS LPAD_2,
        RPAD('Oracle', 100) AS RPAD_2
       FROM DUAL;

SELECT
    RPAD('971225-', 14, '*') AS RPAD_JMNO,
    RPAD('010-1234-', 13, '*') AS RPAD_PHONE
   FROM DUAL;
   
SELECT CONCAT(EMPNO, ENAME),
       CONCAT(EMPNO, CONCAT(' : ', ENAME))
      FROM EMP
     WHERE ENAME = 'BLAKE';
     
SELECT EMPNO || ENAME,
        EMPNO || ' : ' || ENAME
       FROM EMP;
       
SELECT '[' || TRIM(' _ _Oracle_ _ ') || ']' AS TRIM,
       '[' || TRIM(LEADING FROM ' _ _Oracle_ _ ') || ']' AS TRIM_LEANDING,
       '[' || TRIM(TRAILING FROM ' _ _Oracle_ _ ') || ']' AS TRIM_TRAILING,
       '[' || TRIM(BOTH FROM ' _ _Oracle_ _ ') || ']' AS TRIM_BOTH
      FROM DUAL;
      
SELECT '[' || TRIM('_' FROM '_ _Oracle_ _') || ']' AS TRIM,
       '[' || TRIM(LEADING '_' FROM '_ _Oracle_ _') || ']' AS TRIM_LEADING,
       '[' || TRIM(TRAILING '_' FROM '_ _Oracle_ _') || ']' AS TRIM_TRAILING,
       '[' || TRIM(BOTH '_' FROM '_ _Oracle_ _') || ']' AS TRIM_BOTH
      FROM DUAL;
      
SELECT '[' || TRIM(' _Oracle_ ') || ']' AS TRIM,
       '[' || LTRIM(' _Oacle_ ') || ']' AS LTRIM,
       '[' || LTRIM('<_Oracle_>', '_<') || ']' AS LTRIM_2,
       '[' || LTRIM(' _Oracle_ ') || ']' AS RTRIM,
       '[' || RTRIM('<_Oracle_>', '>_') || ']' AS RTRIM_2
      FROM DUAL;
      
--6.1

SELECT EMPNO
       , RPAD(SUBSTR(EMPNO, 1, 2), 4, '*') AS MASKING_EMPNO
       , ENAME
       , RPAD(SUBSTR(ENAME, 1, 1), LENGTH(ENAME), '*') AS MASKING_ENAME
       FROM EMP
     WHERE LENGTH(ENAME) >= 5 AND LENGTH(ENAME) < 6;
     
-- 6.2
SELECT * FROM EMP;

SELECT EMPNO
       , ENAME
       , SAL
       , TRUNC((SAL / 21.5), 2) AS DAY_PAY
       , ROUND((SAL / 21.5 / 8), 1) AS TIME_PAY
      FROM EMP; 
      
-- 6.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')
          FROM EMP;
          
--6.4
SELECT EMPNO
       , ENAME
       , MGR
       , CASE
            WHEN MGR IS NULL THEN '0000'
            WHEN SUBSTR(MGR, 1, 2) = 75 THEN '5555'
            WHEN SUBSTR(MGR, 1, 2) = 76 THEN '6666'
            WHEN SUBSTR(MGR, 1, 2) = 77 THEN '7777'
            WHEN SUBSTR(MGR, 1, 2) = 78 THEN '8888'
            ELSE TO_CHAR(MGR)
        END AS CHG_MGR
       FROM EMP;
       
SELECT SUM(DISTINCT SAL)
       , SUM(ALL SAL)
       , SUM(SAL)
      FROM EMP;
      
SELECT SUM(SAL)
       , SUM(COMM)
      FROM EMP;
      
SELECT COUNT(*)
    FROM EMP;

SELECT COUNT(*)
    FROM EMP
   WHERE DEPTNO = 30;
 
SELECT COUNT(DISTINCT SAL)
       , COUNT(ALL SAL)
       , COUNT(SAL)
      FROM EMP;
      
SELECT COUNT(COMM)
    FROM EMP;

SELECT COUNT(COMM)
    FROM EMP
   WHERE COMM IS NOT NULL; 

SELECT MAX(SAL)
    FROM EMP
   WHERE DEPTNO = 10;
  
SELECT MIN(SAL)
    FROM EMP
   WHERE DEPTNO = 10;
  
SELECT MAX(HIREDATE)
    FROM EMP
   WHERE DEPTNO = 10;
  
SELECT MIN(HIREDATE)
    FROM EMP
   WHERE DEPTNO = 20;  
  
SELECT AVG(SAL)
    FROM EMP
   WHERE DEPTNO = 30;
  
SELECT AVG(DISTINCT SAL)
    FROM EMP
   WHERE DEPTNO = 30;
  
SELECT AVG(COMM)
    FROM EMP
   WHERE DEPTNO = 30;
  
SELECT AVG(SAL), DEPTNO
    FROM EMP
   GROUP BY DEPTNO;
  
SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
   GROUP BY DEPTNO, JOB
   ORDER BY DEPTNO, JOB;

SELECT AVG(COMM), DEPTNO
    FROM EMP
   GROUP BY DEPTNO;

SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
   GROUP BY DEPTNO, JOB
    HAVING AVG(SAL) >= 2000
   ORDER BY DEPTNO, JOB;
  
SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
   GROUP BY DEPTNO, JOB
    HAVING AVG(SAL) >= 2000
   ORDER BY DEPTNO, JOB;
   
SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
   WHERE SAL <= 3000
  GROUP BY DEPTNO, JOB
    HAVING AVG(SAL) >= 2000
   ORDER BY DEPTNO, JOB;
  
SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
   GROUP BY DEPTNO, JOB
    HAVING AVG(SAL) >= 500
   ORDER BY DEPTNO, JOB;
  
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
    FROM EMP
   GROUP BY DEPTNO, JOB
   ORDER BY DEPTNO, JOB;

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
    FROM EMP
   GROUP BY ROLLUP(DEPTNO, JOB);
   
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
    FROM EMP
   GROUP BY CUBE(DEPTNO, JOB)
  ORDER BY DEPTNO, JOB;
  
SELECT DEPTNO, JOB, COUNT(*)
    FROM EMP
   GROUP BY DEPTNO, ROLLUP(JOB);

SELECT DEPTNO, JOB, COUNT(*)
    FROM EMP
   GROUP BY JOB, ROLLUP(DEPTNO);

SELECT DEPTNO, JOB, COUNT(*)
    FROM EMP
   GROUP BY GROUPING SETS(DEPTNO, JOB)
   ORDER BY DEPTNO, JOB;

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL),
        GROUPING(DEPTNO),
        GROUPING(JOB)
       FROM EMP
      GROUP BY CUBE(DEPTNO, JOB)
      ORDER BY DEPTNO, JOB;
      
SELECT DEPTNO, JOB, MAX(SAL)
    FROM EMP
   GROUP BY DEPTNO, JOB
   ORDER BY DEPTNO, JOB;
   
SELECT *
    FROM(SELECT DEPTNO, JOB, SAL
        FROM EMP)
    PIVOT(MAX(SAL)
          FOR DEPTNO IN (10, 20, 30)
         )
    ORDER BY JOB;
    
SELECT *
    FROM (SELECT JOB, DEPTNO, SAL
            FROM EMP)
    PIVOT(MAX(SAL)
            FOR JOB IN ('CLERK' AS CLERK,
                        'SALESMAN' AS SALESMAN,
                        'PRESIDENT' AS PRESIDENT,
                        'MANAGER' AS MANAGER,
                        'ANALYST' AS ANALYST)
         )
    ORDER BY DEPTNO;
    
SELECT DEPTNO,
    MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
    MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
    MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
    MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
    MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
   FROM EMP
  GROUP BY DEPTNO
  ORDER BY DEPTNO;
  
SELECT *
    FROM(SELECT DEPTNO,
            MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
            MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
            MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
            MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
            MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
           FROM EMP
          GROUP BY DEPTNO
          ORDER BY DEPTNO)
    UNPIVOT(
        SAL FOR JOB IN (CLERK, SALESMAN, PRESIDENT, MANAGER, ANALYST))
    ORDER BY DEPTNO, JOB;
    
--7.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;
     
-- 6.2
SELECT JOB, COUNT(*)
    FROM EMP
   GROUP BY JOB
    HAVING COUNT(*) >= 3;     
   
-- 6.3
SELECT TO_CHAR(HIREDATE, 'YYYY') AS HIREDATE
       , DEPTNO
       , COUNT(*) AS CNT
      FROM EMP
     GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO;
     
SELECT
    NVL2(COMM, 'O', 'X') AS EXIST_COMM
    , COUNT(*) AS CNT
   FROM EMP
  GROUP BY NVL2(COMM, 'O', 'X');

-- 6.5
SELECT DEPTNO
       , TO_CHAR(HIREDATE, 'YYYY') AS HIRE_YEAR
       , COUNT(*) AS CNT
       , MAX(SAL) AS MAX_SAL
       , SUM(SAL) AS SUM_SAL
       , AVG(SAL) AS AVG_SAL
      FROM EMP
     GROUP BY ROLLUP(DEPTNO, TO_CHAR(HIREDATE, 'YYYY'));
     
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, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
       , D.DNAME, D.LOC
      FROM EMP E, DEPT D
     WHERE E.DEPTNO = D.DEPTNO
    ORDER BY EMPNO;
    
SELECT E.EMPNO, D.DNAME
    FROM EMP E, DEPT D
   WHERE E.EMPNO = 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
    AND SAL >= 3000
  ORDER BY D.DEPTNO, E.EMPNO;
  
SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
    FROM EMP E, DEPT D
   WHERE E.DEPTNO = D.DEPTNO
    AND SAL <= 2500
    AND EMPNO <= 9999
   ORDER BY E.EMPNO;
   
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;
     
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(+)
     ORDER BY E1.EMPNO;
    
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
     ORDER BY E1.EMPNO;
     
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
     ORDER BY DEPTNO, E.EMPNO;

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM
       , DEPTNO, D.DNAME, D.LOC
      FROM EMP E JOIN DEPT D USING (DEPTNO)
     WHERE SAL >= 3000
    ORDER BY DEPTNO, E.EMPNO;
    
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM
       , E.DEPTNO
       , D.DNAME, D.LOC
      FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
     WHERE SAL <= 3000
    ORDER BY E.DEPTNO, EMPNO;
    
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_ENAME
      FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
     ORDER BY E1.EMPNO;
     
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM
       , DEPTNO, D.DNAME, D.LOC
      FROM EMP E JOIN DEPT D USING (DEPTNO)
     WHERE SAL >= 3000
        AND E.MGR IS NOT NULL
    ORDER BY DEPTNO, E.EMPNO;

-- 8 
-- SQL-99 이전방식
-- 8.1
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
    FROM EMP E, DEPT D
        WHERE E.DEPTNO = D.DEPTNO
            AND E.SAL > 2000
       ORDER BY D.DEPTNO;
       
-- 8.2
SELECT D.DEPTNO
       , D.DNAME
       , TRUNC(AVG(SAL)) AS AVG_SAL
       , MAX(SAL) AS MAX_SAL
       , MIN(SAL) AS MIN_SAL
       , COUNT(*) AS CNT 
      FROM EMP E, DEPT D
     WHERE E.DEPTNO = D.DEPTNO
    GROUP BY D.DEPTNO, D.DNAME;


SELECT D.DEPTNO, D.DNAME
       , E.EMPNO, E.ENAME, E.JOB, E.SAL
      FROM EMP E, DEPT D
     WHERE E.DEPTNO(+) = D.DEPTNO
    ORDER BY D.DEPTNO, E.EMPNO;
    
SELECT D.DEPTNO, D.DNAME
       , E1.EMPNO, E1.ENAME, E1.MGR, E1.SAL, E1.DEPTNO
       , S.LOSAL, S.HISAL, S.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.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
        AND E1.MGR = E2.EMPNO(+)
    ORDER BY D.DEPTNO, D.DNAME;
    
    
-- 8 
-- SQL-99 이후방식
-- 8.1
SELECT DEPTNO, D.DNAME
       , E.EMPNO, E.ENAME, E.SAL
    FROM EMP E JOIN DEPT D USING (DEPTNO)
   WHERE E.SAL > 2000
  ORDER BY DEPTNO;
      
--8.2
SELECT D.DEPTNO, D.DNAME
       , AVG(SAL) AS AVG_SAL
       , MAX(SAL) AS MAX_SAL
       , MIN(SAL) AS MIN_SAL
       , COUNT(*) AS CNT
      FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
     GROUP BY D.DEPTNO, D.DNAME;
     
--8.3
SELECT D.DEPTNO, D.DNAME
       , E.EMPNO, E.ENAME, E.JOB, E.SAL
      FROM DEPT D LEFT JOIN EMP E ON (D.DEPTNO = E.DEPTNO)
     ORDER BY D.DEPTNO;
     
SELECT D.DEPTNO, D.DNAME
       , E1.EMPNO, E1.ENAME, E1.MGR, E1.SAL, E1.DEPTNO
       , S.LOSAL, S.HISAL, S.GRADE
       , E2.EMPNO AS MGR_EMPNO
       , E2.ENAME AS MGR_ENAME
      FROM DEPT D LEFT JOIN EMP E1
            ON (D.DEPTNO = E1.DEPTNO)
           LEFT OUTER JOIN SALGRADE S
            ON (E1.SAL BETWEEN S.LOSAL AND S.HISAL)
           LEFT OUTER JOIN EMP E2
            ON (E1.MGR = E2.EMPNO)
    ORDER BY D.DEPTNO, E1.EMPNO;