HR 계정 SQL 50문제

-- 1. 연봉이 12000 이상되는 직원들의 LAST_NAME 및 연봉을 조회한다.
SELECT  LAST_NAME
        , SALARY
FROM    EMPLOYEES
WHERE   SALARY >= 12000
;
 
-- 2. 사원번호가 176 인 사람의 LAST_NAME 과 부서 번호를 조회한다.
SELECT  LAST_NAME
        , DEPARTMENT_ID
FROM    EMPLOYEES
WHERE   SALARY >= 12000
;
 
-- 3. 연봉이 5000 에서 12000의 범위 이외인 사람들의 LAST_NAME 및 연봉을 조회힌다.
SELECT  LAST_NAME
        , SALARY
FROM    EMPLOYEES
WHERE   SALARY < 5000
OR      SALARY > 12000
;
 
-- 4. 1998/02/20 일부터 1998/05/01 사이에 고용된 사원들의 LAST_NAME 사번, 고용일자를 조회한다.
--    고용일자 순으로 정렬한다.
SELECT  LAST_NAME
        , EMPLOYEE_ID
        , HIRE_DATE
FROM    EMPLOYEES
WHERE   HIRE_DATE >= '1998/02/20'
AND     HIRE_DATE <= '1998/05/01'
;
 
SELECT  LAST_NAME
        , EMPLOYEE_ID
        , HIRE_DATE
FROM    EMPLOYEES
WHERE   HIRE_DATE
BETWEEN TO_DATE('2002/02/20')
AND     TO_DATE('2005/05/01')
ORDER   BY HIRE_DATE
;
 
-- 5. 20 번 및 50 번 부서에서 근무하는 모든 사원들의 LAST_NAME 및 부서 번호를 알파벳순으로 조회한다.
SELECT  LAST_NAME
        , DEPARTMENT_ID
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN ( 20, 50 )
ORDER   BY LAST_NAME ASC
;
 
-- 6. 20 번 및 50 번 부서에 근무하며, 연봉이 5000 ~ 12,000 사이인 사원들의 LAST_NAME 및 연봉을 조회한다.
SELECT  LAST_NAME
        , SALARY
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN ( 20, 50 )
AND     SALARY >= 5000
AND     SALARY <= 12000
;
 
-- 7. 1994년도에 고용된 모든 사람들의 LAST_NAME 및 고용일을 조회한다.
SELECT  LAST_NAME
        , HIRE_DATE
FROM    EMPLOYEES
WHERE   HIRE_DATE LIKE '1994%'
;
 
-- 8-1. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.
SELECT  LAST_NAME
        , JOB_ID
        , MANAGER_ID
FROM    EMPLOYEES
WHERE   MANAGER_ID is null
OR      MANAGER_ID = ''
;
 
-- 8-2. 매니저가 있는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.
SELECT  LAST_NAME
        , JOB_ID
        , MANAGER_ID
FROM    EMPLOYEES
WHERE   MANAGER_ID is not null
OR      MANAGER_ID != ''
;
 
-- 9. 커미션을 버는 모든 사원들의 LAST_ANME, 연봉 및 커미션을 조회한다.
--    연봉 역순, 커미션 역순차로 정렬한다.
SELECT  LAST_NAME
        , SALARY
        , COMMISSION_PCT
FROM    EMPLOYEES
WHERE   NOT (COMMISSION_PCT is null)
ORDER   BY SALARY DESC,
        COMMISSION_PCT DESC
;
 
-- 10. LAST_NAME 의 네번째 글자가 a 인 사원들의 LAST_NAME 을 조회한다.
SELECT  LAST_NAME
FROM    EMPLOYEES
WHERE   LAST_NAME LIKE '___a%'
;
 
-- 11. LAST_NAME 에 a 및(OR) e 글자가 있는 사원들의 LAST_NAME 을 조회한다.
SELECT  LAST_NAME
FROM    EMPLOYEES
WHERE   LAST_NAME LIKE '%a%'
OR      LAST_NAME LIKE '%e%'
;
 
-- 12. 연봉이 2,500, 3,500, 7000 이 아니며 직업이 SA_REP 이나 ST_CLERK 인 사람들을 조회한다.
SELECT  LAST_NAME
        , JOB_ID
        , SALARY
FROM    EMPLOYEES
WHERE   SALARY NOT IN ( 2500, 3500, 7000 )
AND     JOB_ID IN ( 'SA_REP', 'ST_CLERK' )
;
 
-- 13-1.  직업이 AD_PRESS 인 사람은 A 등급을, ST_MAN 인 사람은 B 등급을, IT_PROG 인 사람은 C 등급을,
--          SA_REP 인 사람은 D 등급을, ST_CLERK 인 사람은 E 등급을 기타는 0 을 부여하여 조회한다.
SELECT  EMPLOYEE_ID
        , FIRST_NAME
        , LAST_NAME
        , DECODE(JOB_ID
                , 'AD_PRESS', 'A'
                , 'ST_MAN', 'B'
                , 'IT_PROG', 'C'
                , 'SA_REP', 'D'
                , 'ST_CLERK', 'E'
                , '0') JOB_GRADE
FROM    EMPLOYEES
;
 
-- 13-2
SELECT  *
FROM    (
            SELECT  EMPLOYEE_ID
                    , FIRST_NAME
                    , LAST_NAME
                    , DECODE(JOB_ID
                            , 'AD_PRESS', 'A'
                            , 'ST_MAN', 'B'
                            , 'IT_PROG', 'C'
                            , 'SA_REP', 'D'
                            , 'ST_CLERK', 'E'
                            , '0') JOB_GRADE
            FROM    EMPLOYEES
        )
WHERE   JOB_GRADE = 'B'
;
 
-- 13-3
SELECT  EMPLOYEE_ID
        , FIRST_NAME
        , LAST_NAME
        , CASE JOB_ID
            WHEN 'AD_PRESS' THEN
                'A'
            WHEN 'ST_MAN' THEN
                'B'
            WHEN 'IT_PROG' THEN
                'C'
            WHEN 'SA_REP' THEN
                'D'
            WHEN 'ST_CLERK' THEN
                'E'
            ELSE '0'
        END GRADE
FROM    EMPLOYEES
;
 
-- 13-4
SELECT  EMPLOYEE_ID
        , FIRST_NAME
        , LAST_NAME
        , CASE
            WHEN JOB_ID = 'IT_PROG' THEN
                'C'
            ELSE '0'
        END GRADE
FROM    EMPLOYEES
;
 
-- 13-5
SELECT  ROWNUM
        , EMPLOYEE_ID
        , FIRST_NAME
        , LAST_NAME
        , CASE (SELECT 98 FROM DUAL)
            WHEN 100 THEN
                (SELECT SYSDATE - 1 FROM DUAL)
            ELSE (SELECT SYSDATE - 1 FROM DUAL)
        END GRADE
        , CASE (SELECT 98 FROM DUAL)
            WHEN 100 THEN
                SYSDATE - 2
            ELSE SYSDATE - 2
        END GRADE
        , SYSDATE
FROM    EMPLOYEES
;
 
-- 14. 모든 사원들의 LAST_NAME, 부서 이름 및 부서 번호을 조회한다.
SELECT  EMPLOYEE_ID
        , LAST_NAME
        , DEPARTMENT_NAME
        , D.DEPARTMENT_ID
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
;
 
-- 15. 부서번호 30 내의 모든 직업들을 유일한 포맷으로 조회한다. 90 부서 또한 포함한다.
SELECT  DISTINCT JOB_ID
        , D.DEPARTMENT_ID
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     D.DEPARTMENT_ID IN ( 30, 90 )
ORDER   BY JOB_ID
;
 
-- 16-1. 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.
SELECT  LAST_NAME
        , DEPARTMENT_NAME
        , L.LOCATION_ID
        , CITY
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , LOCATIONS L
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄
AND     D.LOCATION_ID = L.LOCATION_ID     -- 보라줄
;
 
-- 16-2. 시애틀에 사는 사람 중 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.
-- 좋지 못한 방법이다.
SELECT  LAST_NAME
        , DEPARTMENT_NAME
        , L.LOCATION_ID
        , CITY
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , LOCATIONS L
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄
AND     D.LOCATION_ID = L.LOCATION_ID     -- 보라줄
AND     L.CITY = "Seattle"
;
 
-- 16-3. 시애틀에 사는 사람 중 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.
-- 좋은 방법 - PK로 검색
SELECT  LAST_NAME
        , DEPARTMENT_NAME
        , L.LOCATION_ID
        , CITY
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , LOCATIONS L
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄
AND     D.LOCATION_ID = L.LOCATION_ID     -- 보라줄
AND     L.LOCATION_ID = (
                          SELECT  LOCATION_ID
                          FROM    LOCATIONS
                          WHERE   CITY = 'Seattle'
                        )
;
 
-- 17. LAST_NAME 이 DAVIES 인 사람보다 후에 고용된 사원들의 LAST_NAME 및 HIRE_DATE 을 조회한다.
SELECT  LAST_NAME
        , HIRE_DATE
FROM    EMPLOYEES
WHERE   HIRE_DATE >=  (
                        SELECT  HIRE_DATE
                        FROM    EMPLOYEES
                        WHERE   LAST_NAME = 'Davies'
                      )
ORDER   BY HIRE_DATE
;
 
-- 18. 자신의 매니저보다 먼저 고용된 사원들의 LAST_NAME 및 고용일을 조회한다.
SELECT  EMP.LAST_NAME
        , EMP.EMPLOYEE_ID
        , EMP.HIRE_DATE
FROM    EMPLOYEES EMP
        , EMPLOYEES MGR
WHERE   EMP.MANAGER_ID = MGR.EMPLOYEE_ID
AND     EMP.HIRE_DATE < MGR.HIRE_DATE
ORDER   BY EMP.LAST_NAME
;
 
-- 19. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
SELECT  MAX(SALARY)
        , MIN(SALARY)
        , SUM(SALARY)
        , ROUND (AVG(SALARY))
FROM    EMPLOYEES
;
 
-- 20. 각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.
SELECT  JOB_ID
        , MAX(SALARY) MAX
        , MIN(SALARY) MIN
        , SUM(SALARY) SUM
        , ROUND (AVG(SALARY)) AVG
FROM    EMPLOYEES
GROUP   BY JOB_ID
ORDER   BY JOB_ID
;
 
-- 21. 동일한 직업을 가진 사원들의 총 수를 조회한다.
SELECT  JOB_ID
        , COUNT(EMPLOYEE_ID)
FROM    EMPLOYEES
 
GROUP   BY JOB_ID
ORDER   BY JOB_ID
;
 
-- 22-1. 매니저로 근무하는 사원들의 총 수를 조회한다.
SELECT  COUNT(EMPLOYEE_ID)
FROM    EMPLOYEES
WHERE   EMPLOYEE_ID IN  (
                          SELECT  DISTINCT MANAGER_ID
                          FROM    EMPLOYEES
                          WHERE   MANAGER_ID IS NOT NULL
                        )
;
 
-- 22-2. 매니저로 근무하는 사원들의 총 수를 조회한다.
SELECT  COUNT(DISTINCT MANAGER_ID)
FROM    EMPLOYEES 
;
 
-- 23. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.
SELECT  MAX(SALARY) - MIN(SALARY)
FROM    EMPLOYEES
;
 
-- 24. 매니저의 사번 및 그 매니저 밑 사원들 중 최소 연봉을 받는 사원의 연봉을 조회한다.
--     매니저가 없는 사람들은 제외한다.
--     최소 연봉이 6000 미만인 경우는 제외한다.
--     연봉 기준 역순으로 조회한다.
SELECT  MANAGER_ID
        , MIN(SALARY)
FROM    EMPLOYEES
WHERE   MANAGER_ID IS NOT NULL
GROUP   BY MANAGER_ID
HAVING  MIN(SALARY) >= 6000
ORDER   BY MIN(SALARY) DESC
;
 
 
-- 25. 부서 명, 위치 ID, 각 부서 별 사원 총 수, 각 부서 별 평균 연봉을 조회한다.
--     평균 연봉은 소수점 2 자리까지만 표현한다.
SELECT  DEPARTMENT_NAME
        , LOCATION_ID
        , COUNT(EMPLOYEE_ID)
        , ROUND(AVG(SALARY), 2) AVG_SALARY
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP   BY DEPARTMENT_NAME
        , LOCATION_ID
ORDER   BY LOCATION_ID
;
 
-- 26-1. 총 사원 수 및 1995, 1996, 1997, 1998 년도 별 고용된 사원들의 총 수를 다음과 같이 조회한다.
SELECT  DISTINCT(
            SELECT COUNT(EMPLOYEE_ID)
            FROM   EMPLOYEES
          ) AS CountAll
        , (
            SELECT COUNT(EMPLOYEE_ID)
            FROM   EMPLOYEES
            WHERE  HIRE_DATE LIKE '2001%'
          ) AS Count2001
        , (
            SELECT COUNT(EMPLOYEE_ID)
            FROM   EMPLOYEES
            WHERE  HIRE_DATE LIKE '2003%'
          ) AS Count2003
        , (
            SELECT COUNT(EMPLOYEE_ID)
            FROM   EMPLOYEES
            WHERE  HIRE_DATE LIKE '2005%'
          ) AS Count2005
        , (
            SELECT COUNT(EMPLOYEE_ID)
            FROM   EMPLOYEES
            WHERE  HIRE_DATE LIKE '2006%'
          ) AS Count2006
FROM    EMPLOYEES
;
 
-- 27-1.  각 부서별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다. 
SELECT  DEPARTMENT_ID
        , JOB_ID
        , SUM(SALARY)
        , (
        SELECT  SUM(SALARY)
                FROM    EMPLOYEES E1
                WHERE   E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
        ) SumAll
FROM    EMPLOYEES E2
GROUP   BY DEPARTMENT_ID
        , JOB_ID
ORDER   BY DEPARTMENT_ID
        , JOB_ID
;
 
-- 27-2.  다음과 같은 포멧으로 각 부서별 각 직업 별 연봉 총 합 및
--        각 부서별 연봉 총 합을 조회한다. 
SELECT  DISTINCT JOB_ID
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 10
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT10"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 20
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT20"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 30
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT30"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 40
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT40"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 50  
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT50"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 60  
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT60"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 70  
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT70"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 80
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT80"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 90  
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT90"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 100  
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT100"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 110  
            AND     JOB_ID = E.JOB_ID
          ) AS "DEPT110"
        , (
            SELECT  NVL ( SUM(SALARY), 0 )
            FROM    EMPLOYEES
            WHERE   JOB_ID = E.JOB_ID
          ) AS "TOTAL"
FROM    EMPLOYEES E
GROUP   BY JOB_ID
;
 
-- 27-3.  다음과 같은 포멧으로 각 부서별 각 직업 별 연봉 총 합 및
--        각 부서별 연봉 총 합을 조회한다. 
SELECT  JOB_ID
        , NVL ( SUM(DECODE (DEPARTMENT_ID, 20, SALARY)), 0 ) "DEPT 20"
        , NVL ( SUM(DECODE (DEPARTMENT_ID, 50, SALARY)), 0 ) "DEPT 50"
        , NVL ( SUM(DECODE (DEPARTMENT_ID, 80, SALARY)), 0 ) "DEPT 80"
        , NVL ( SUM(DECODE (DEPARTMENT_ID, 90, SALARY)), 0 ) "DEPT 90"
        , NVL ( SUM(SALARY), 0 ) "TOTAL"
FROM    EMPLOYEES
GROUP   BY JOB_ID;
 
-- 28. LAST_NAME 이 Zlotkey 와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 조회한다.
--     결과값에서 Zlotkey 는 제외한다.
SELECT  EMPLOYEE_ID
        , HIRE_DATE
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN  (
                            SELECT  DEPARTMENT_ID
                            FROM    EMPLOYEES
                            WHERE   LAST_NAME = 'Zlotkey'
                          )
AND     LAST_NAME != 'Zlotkey'
;
 
-- 29. 회사 전체 평균 연봉보다 더 받는 사원들의 사번 및 LAST_NAME 을 조회한다.
SELECT  EMPLOYEE_ID
        , LAST_NAME
FROM    EMPLOYEES
WHERE   SALARY >  (
                    SELECT  AVG(SALARY)
                    FROM    EMPLOYEES
                  )
;
 
-- 30-1. LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다.
SELECT  EMPLOYEE_ID
        , LAST_NAME
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN  (
                            SELECT  DEPARTMENT_ID
                            FROM    EMPLOYEES
                            WHERE   LAST_NAME LIKE '%u%'
                          )
;
 
-- 30-2. (VIEW 이용) LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다.
SELECT  DISTINCT E.EMPLOYEE_ID
        , LAST_NAME
FROM    (
          SELECT  DEPARTMENT_ID
          FROM    EMPLOYEES
          WHERE   LAST_NAME LIKE '%u%'
        ) U_NAME
        , EMPLOYEES E
WHERE   U_NAME.DEPARTMENT_ID = E.DEPARTMENT_ID
;
 
-- 31-1. 위치 ID 가 1700 인 부서에서 근무하는 사원들의 LAST_NAME, 부서 번호 및 JOB_ID 를 조회한다.
SELECT  LAST_NAME
        , D.DEPARTMENT_ID
        , JOB_ID
        , D.LOCATION_ID
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     D.LOCATION_ID = 1700
ORDER   BY D.DEPARTMENT_ID
        , JOB_ID
        , LAST_NAME
;
 
-- 31-2. (VIEW 이용)위치 ID 가 1700 인 부서에서 근무하는 사원들의 LAST_NAME, 부서 번호 및 JOB_ID 를 조회한다.
SELECT  LAST_NAME
        , DEPARTMENT_ID
        , JOB_ID
FROM    (
          SELECT  EMPLOYEE_ID
          FROM    EMPLOYEES E
                  , DEPARTMENTS D
          WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
          AND     D.LOCATION_ID = 1700
        ) EMP1700
        , EMPLOYEES E
WHERE   EMP1700.EMPLOYEE_ID = E.EMPLOYEE_ID
;
 
-- 32. King 을 매니저로 두고 있는 모든 사원들의 LAST_NAME 및 연봉을 조회한다.
SELECT  LAST_NAME
        , SALARY
        , MANAGER_ID
FROM    EMPLOYEES
WHERE   MANAGER_ID IN (
                        SELECT EMPLOYEE_ID
                        FROM   EMPLOYEES
                        WHERE  LAST_NAME = 'King'
                      )
;
 
-- 33. EXECUTIVE 부서에 근무하는 모든 사원들의 부서 번호, LAST_NAME, JOB_ID 를 조회한다.
SELECT  D.DEPARTMENT_ID
        , DEPARTMENT_NAME
        , JOB_ID
        , LAST_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     DEPARTMENT_NAME = 'Executive'
ORDER   BY JOB_ID
        , LAST_NAME
;
 
-- 34-1. 회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이
--       근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다.
SELECT  DISTINCT EMPLOYEE_ID
        , LAST_NAME
        , SALARY
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID IN  (
                          SELECT  DEPARTMENT_ID
                          FROM    EMPLOYEES
                          WHERE   LAST_NAME LIKE '%u%'
                          AND     SALARY >= (
                                              SELECT  AVG(SALARY)
                                              FROM    EMPLOYEES
                                            )
                          )
;
 
-- 34-2. (VIEW 이용)회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이
--       근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다.
SELECT  DISTINCT EMPLOYEE_ID
        , LAST_NAME
        , SALARY
FROM    (
        /* 그 사람들의 부서와 원래 EMPLOYEES 테이블과 조인 */
        SELECT  DEPARTMENT_ID
        FROM    (
                /* 평균 연봉보다 많이 버는 사원들 */
                SELECT  EMPLOYEE_ID
                FROM    EMPLOYEES
                WHERE   SALARY >    (
                                    SELECT  AVG(SALARY)
                                    FROM    EMPLOYEES
                                    )
                ) EMP2
        , EMPLOYEES E
        WHERE   EMP2.EMPLOYEE_ID = E.EMPLOYEE_ID
        AND     E.LAST_NAME LIKE '%u%'
        ) DEPT
        , EMPLOYEES E
WHERE   DEPT.DEPARTMENT_ID = E.DEPARTMENT_ID
;
 
-- 35-1. ST_CLERK 인 직업 ID 를 가진 사원이 없는 부서 ID 를 조회한다.
--       NULL 값은 제외한다. (NOT EXISTS 사용)
SELECT  DISTINCT DEPARTMENT_ID
FROM    EMPLOYEES E
WHERE   NOT EXISTS  (
                    /* 부서 하나씩 검사하며 ST_CLERK라는 직업이
                        있으면 '1' 반환
                        없으면 NULL 반환*/
                    SELECT  ''
                    FROM    EMPLOYEES E1
                    WHERE   JOB_ID = 'ST_CLERK'
                    AND     E1.DEPARTMENT_ID = E.DEPARTMENT_ID
                )
AND     DEPARTMENT_ID IS NOT NULL
ORDER   BY DEPARTMENT_ID
;
 
-- 35-2. ST_CLERK 인 직업 ID 를 가진 사원이 있는 부서 ID 를 조회한다.
--       NULL 값은 제외한다. (NOT EXISTS 사용)
SELECT  DISTINCT DEPARTMENT_ID
FROM    EMPLOYEES E
WHERE   NOT EXISTS  (
                        SELECT  '1'
                        FROM    EMPLOYEES E2
                        WHERE   E.EMPLOYEE_ID = E2.EMPLOYEE_ID
                        AND     E2.JOB_ID != 'ST_CLERK'
                    )
ORDER   BY DEPARTMENT_ID
;
 
-- 36-1. 위치한 부서가 없는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용)
SELECT  COUNTRY_ID
        , COUNTRY_NAME
FROM    COUNTRIES C
WHERE   NOT EXISTS  (
                        /*  */
                        SELECT  '1'
                        FROM    LOCATIONS L
                                , DEPARTMENTS D
                        WHERE   C.COUNTRY_ID = L.COUNTRY_ID
                        AND     L.LOCATION_ID IN (D.LOCATION_ID)
                    )
ORDER   BY COUNTRY_ID
;
 
-- 36-2. 위치한 부서가 없는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용)
SELECT  COUNTRY_ID
        , COUNTRY_NAME
FROM    COUNTRIES C
WHERE   NOT EXISTS  (
                        SELECT  '1'
                        FROM    LOCATIONS L
                                , DEPARTMENTS D
                        WHERE   L.COUNTRY_ID = C.COUNTRY_ID
                        AND     L.LOCATION_ID NOT IN (D.LOCATION_ID)
                    )
ORDER   BY COUNTRY_ID
;
 
-- 36-3. 위치한 부서가 있는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용)
SELECT  COUNTRY_ID
        , COUNTRY_NAME
FROM    COUNTRIES C
WHERE   EXISTS  (
                        SELECT  '1'
                        FROM    LOCATIONS L2
                                , DEPARTMENTS D2
                        WHERE   C.COUNTRY_ID = L2.COUNTRY_ID
                        AND     L2.LOCATION_ID = D2.LOCATION_ID
                    )
;
 
-- 37. 기존의 직업을 여전히 가지고 있는 사원들의 사번 및 JOB_ID 를 조회한다.
SELECT  E.EMPLOYEE_ID
        , E.JOB_ID
FROM    EMPLOYEES E
WHERE   E.EMPLOYEE_ID NOT IN    (
                                SELECT  EMPLOYEE_ID
                                FROM    JOB_HISTORY J
                                WHERE   E.EMPLOYEE_ID = J.EMPLOYEE_ID
                                )
;
 
-- 41. 커미션을 버는 사원들의 부서와 연봉이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT  LAST_NAME
        , DEPARTMENT_ID
        , SALARY
        , COMMISSION_PCT
FROM    EMPLOYEES
WHERE   (DEPARTMENT_ID, SALARY) IN  (
                                    SELECT  DEPARTMENT_ID
                                            , SALARY
                                    FROM    EMPLOYEES
                                    WHERE   COMMISSION_PCT IS NOT NULL
                                    )
;
 
-- 42. 위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT  LAST_NAME
        , DEPARTMENT_NAME
        , SALARY
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     (SALARY, NVL(COMMISSION_PCT,0)) IN  (
                                              SELECT  SALARY
                                                      , NVL(COMMISSION_PCT, 0)
                                              FROM    EMPLOYEES E2
                                                      , DEPARTMENTS D2
                                              WHERE   E2.DEPARTMENT_ID = D2.DEPARTMENT_ID
                                              AND     LOCATION_ID = 1700
                                              AND     E2.EMPLOYEE_ID != E.EMPLOYEE_ID
                                            )
;
 
-- 42-2. (VIEW)위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT  LAST_NAME
        , E2.DEPARTMENT_ID
        , E2.SALARY
FROM    (
        /* 연봉, COMMISSION */
        SELECT  SALARY
                , NVL(COMMISSION_PCT,0)
                /* LOCATION 1700 */
        FROM    (
                SELECT  DEPARTMENT_ID
                FROM    DEPARTMENTS
                WHERE   LOACTION_ID = 1700
                ) D3
                , EMPLOYEES E2
        WHERE   E2.DEPARTMENT_ID = D3.DEPARTMENT_ID
        ) SC
        , EMPLOYEES E
;
 
-- 42-2. (VIEW 강사님)위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT  DISTINCT LAST_NAME
        , DEPARTMENT_ID
        , E.SALARY
FROM    (
        /* 연봉, COMMISSION */
        SELECT  SALARY
                , NVL(COMMISSION_PCT,0) COMMISSION_PCT
                /* LOCATION 1700 */
        FROM    EMPLOYEES E
                , DEPARTMENTS D
        WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
        AND     D.LOCATION_ID = 1700
        ) SAL1700
        , EMPLOYEES E
WHERE   SAL1700.SALARY = E.SALARY
AND     SAL1700.COMMISSION_PCT = NVL(E.COMMISSION_PCT, 0)
;
 
-- 43. LAST_NAME 이 'Kochhar' 인 사원과 동일한 연봉 및 커미션을 버는 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.
SELECT  LAST_NAME
        , DEPARTMENT_ID
        , SALARY
        , COMMISSION_PCT
FROM    EMPLOYEES
WHERE   (SALARY, NVL(COMMISSION_PCT,0)) IN  (
                                              SELECT  SALARY
                                                      , NVL(COMMISSION_PCT, 0)
                                              FROM    EMPLOYEES E2
                                              WHERE   LAST_NAME = 'Kochhar'
                                            )
AND     LAST_NAME != 'Kochhar'
;
 
-- 44-1. 직업 ID 가 SA_MAN 인 사원들이 받을 수 있는 최대 연봉 보다 높게 받는 사원들의 LAST_NAME, JOB_ID 및 연봉을 조회한다.
SELECT  LAST_NAME
        , JOB_ID
        , SALARY
FROM    EMPLOYEES
WHERE   SALARY >  (
                    SELECT  MAX_SALARY
                    FROM    JOBS
                    WHERE   JOB_ID = 'SA_MAN'
                  )
;
 
-- 44-2. 직업 ID 가 SA_MAN 인 사원들의 최대 연봉 보다 높게 받는 사원들의 LAST_NAME, JOB_ID 및 연봉을 조회한다.
SELECT  LAST_NAME
        , JOB_ID
        , SALARY
FROM    EMPLOYEES
WHERE   SALARY > (
                    SELECT  MAX(SALARY)
                    FROM    EMPLOYEES
                    WHERE   JOB_ID = 'SA_MAN'
                  )
;
 
-- 45. 도시 이름이 T 로 시작하는 지역에 사는 사원들의 사번, LAST_NAME 및 부서 번호를 조회한다.
SELECT  EMPLOYEE_ID
        , LAST_NAME
        , D.DEPARTMENT_ID
        , CITY
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , LOCATIONS L
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     D.LOCATION_ID = L.LOCATION_ID
AND     CITY LIKE 'T%'
;
 
-- 46.  각 부서별 평균 연봉보다 더 받는 동일부서 근무사원들의
--      LAST_NAME, 연봉, 부서번호 및 해당 부서의 평균 연봉을 조회한다.
--      결과는 부서별 연봉을 기준으로 정렬한다.
SELECT  E.LAST_NAME
        , E.SALARY
        , E.DEPARTMENT_ID
        , V.DEP_AVG
FROM    EMPLOYEES E
        ,(
            SELECT  DEPARTMENT_ID
                    , ROUND( AVG(SALARY), 0 ) DEP_AVG
            FROM    EMPLOYEES
            GROUP   BY DEPARTMENT_ID
         ) V
WHERE   E.DEPARTMENT_ID = V.DEPARTMENT_ID
AND     E.SALARY > V.DEP_AVG
ORDER   BY V.DEP_AVG
;
 
SELECT  LAST_NAME
        , SALARY
        , DEPARTMENT_ID
 
-- 47. NOT EXISTS 연산자를 사용하여 매니저가 아닌 사원 이름을 조회한다.
SELECT  FIRST_NAME
        , EMPLOYEE_ID
        , MANAGER_ID
FROM    EMPLOYEES E
WHERE   NOT EXISTS  (
                        SELECT  '1'
                        FROM    EMPLOYEES E2
                        WHERE   E.EMPLOYEE_ID = E2.MANAGER_ID
                    )
ORDER   BY EMPLOYEE_ID
;
 
-- 48. 소속부서의 평균 연봉보다 적게 버는 사원들의 last_name 을 조회한다.
SELECT  LAST_NAME
        , SALARY
        , E.DEPARTMENT_ID
        , V.AVG_SALARY
FROM    EMPLOYEES E
        ,(
            SELECT  DEPARTMENT_ID
                    , ROUND(AVG(SALARY), 0) AVG_SALARY
            FROM    EMPLOYEES
            GROUP   BY DEPARTMENT_ID
        ) V
WHERE   E.DEPARTMENT_ID = V.DEPARTMENT_ID
AND     E.SALARY < V.AVG_SALARY
ORDER   BY V.AVG_SALARY
        , E.SALARY
;
 
-- 49. 각 사원 별 소속부서에서 자신보다 늦게 고용되었으나 보다 많은 연봉을 받는 사원이 존재하는 모든 사원들의 last_name 을 조회한다.
/* LASTNAME 출력 */
SELECT  DISTINCT E.FIRST_NAME
        || E.LAST_NAME
        , E.DEPARTMENT_ID
FROM    EMPLOYEES E
        , EMPLOYEES E2
WHERE   E.DEPARTMENT_ID = E2.DEPARTMENT_ID
AND     E.HIRE_DATE < E2.HIRE_DATE
AND     E.SALARY < E2.SALARY
;
 
-- LPAD, RPAD
SELECT  LPAD('111', 6, '0')
        , LPAD('1', 6, '0')
        , RPAD('111', 6, '0')
        , RPAD('1', 6, '0')
FROM DUAL
;
 
-- || 컬럼들을 붙여서 출력할 때
SELECT  LPAD('111', 6, '0')
        || '--++PLUS'
        || LPAD('1', 6, '0')
        || RPAD('111', 6, '0')
        || RPAD('1', 6, '0') CONCATENATION
FROM DUAL
;
 

출처:  http://cocomo.tistory.com/117