~조인(Join) 실습문제

 

-- 14. 모든 사원들의 LAST_NAME, 부서 번호 및 부서 이름을 조회한다.
SELECT  LAST_NAME
        , E.DEPARTMENT_ID
        , DEPARTMENT_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS
;
 

---

-- 15. 부서번호 30 내의 모든 직업들을 유일한 포맷으로 조회한다. 90 부서 또한 포함한다.
SELECT  DISTINCT E.JOB_ID
        , JOB_TITLE
        , DEPARTMENT_ID
FROM    EMPLOYEES E
        , JOBS J
WHERE     J.JOB_ID = E.JOB_ID
AND     DEPARTMENT_ID <= 30
OR      DEPARTMENT_ID = 90
;

-- 26. 총 사원 수 및 1995, 1996, 1997, 1998 년도 별 고용된 사원들의 총 수를 다음과 같이 조회한다. 
 
SELECT  DISTINCT ( SELECT  COUNT(EMPLOYEE_ID)
          FROM    EMPLOYEES
        ) TOTAL
        , (
            SELECT  COUNT(EMPLOYEE_ID)
            FROM    EMPLOYEES
            WHERE   TO_CHAR(HIRE_DATE, 'YYYY') = '2005'    
          ) COUNT_2005
        , ( SELECT  COUNT(EMPLOYEE_ID)
            FROM    EMPLOYEES
            WHERE   TO_CHAR(HIRE_DATE, 'YYYY') = '2006'
          ) COUNT_2006
        , (
            SELECT  COUNT(EMPLOYEE_ID)
            FROM    EMPLOYEES
            WHERE   TO_CHAR(HIRE_DATE, 'YYYY') = '2007'
          ) COUNT_2007
        , (
            SELECT  COUNT(EMPLOYEE_ID)
            FROM    EMPLOYEES
            WHERE   TO_CHAR(HIRE_DATE, 'YYYY') = '2008'
          ) COUNT_2008
FROM    DUAL
;
 

FROM 절이 쓸모가 없다고 할때는 FROM DUAL을 쓴다.

---

-- 27. 다음과 같은 포맷으로 각 부서 별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다. 
 
SELECT  JOB_ID JOB
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 20
            AND     JOB_ID = E.JOB_ID
         ), 0) "DEPT 20"
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 50
            AND     JOB_ID = E.JOB_ID
         ), 0) "DEPT 50"
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 80
            AND     JOB_ID = E.JOB_ID
         ), 0) "DEPT 80"
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   DEPARTMENT_ID = 90
            AND     JOB_ID = E.JOB_ID
         ), 0) "DEPT 90"
        ,NVL((
            SELECT  SUM(SALARY)
            FROM    EMPLOYEES
            WHERE   JOB_ID = E.JOB_ID
         ), 0) TOTAL
FROM    EMPLOYEES E
GROUP   BY JOB_ID
;

---

-- 28. LAST_NAME 이 Zlotkey 와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 조회한다.
-- 결과값에서 Zlotkey 는 제외한다.
 
SELECT  EMPLOYEE_ID
        , HIRE_DATE
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID = (
                          SELECT  DEPARTMENT_ID
                          FROM    EMPLOYEES
                          WHERE   LAST_NAME = 'Zlotkey'
                        )
AND     LAST_NAME != 'Zlotkey'
;
 

---

 

-- 30. LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다.
-- LAST_NAME에 u가 들어가는 사람만 뽑아내서 뷰로 만든다.
SELECT  DISTINCT 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. 위치 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
FROM    EMPLOYEES
WHERE   MANAGER_ID IN (
                          SELECT  EMPLOYEE_ID
                          FROM    EMPLOYEES
                          WHERE   LAST_NAME = 'King'
                        )
;
 

 

---

-- 33. EXECUTIVE 부서에 근무하는 모든 사원들의 부서 번호, LAST_NAME, JOB_ID 를 조회한다.
 
SELECT  DEPT.DEPARTMENT_ID
        , EMP.LAST_NAME
        , EMP.JOB_ID
FROM    DEPARTMENTS DEPT
        , EMPLOYEES EMP
WHERE   DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID
AND     DEPT.DEPARTMENT_NAME = 'Executive'
;

이 문제는 위처럼 조인을 써도 되지만, IN을 쓸 수도 있다.

---

-- 34. 회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이
-- 근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다.
 
SELECT    DISTINCT EMPLOYEE_ID
          , LAST_NAME
          , SALARY
FROM      (
            /* 회사 전체 평균 연봉보다 더 버는 사원 중에서 
            LAST_NAME 에 u가 있는 사원들의 부서 VIEW*/
                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
;
 

IN을 써서 풀수도 있지만 위처럼 뷰로 바꿔서 푸는것이 가장 효율적이다. 왜? 더 보기 편하니까! 쿼리는 어떻게든 보기 편해야한다.

---

-- 35. ST_CLERK 인 직업 ID 를 가진 사원이 없는 부서 ID 를 조회한다.
- NULL 값은 제외한다. (NOT EXISTS 사용)
 
 
SELECT  DISTINCT DEPARTMENT_ID
FROM    EMPLOYEES E
WHERE   NOT EXISTS (
                      SELECT '1'
                      FROM EMPLOYEES E1
                      WHERE JOB_ID = 'ST_CLERK'
                      AND E1.DEPARTMENT_ID = E.DEPARTMENT_ID
 
                   )
AND     DEPARTMENT_ID IS NOT NULL
;
 

---