오라클 연봉 구하기 - olakeul yeonbong guhagi

---------------------연습문제----------------------

--1. 연봉이 12000 이상되는 직원들의 LAST_NAME 및 연봉을 조회한다.

SELECT  LAST_NAME

        , SALARY

FROM    EMPLOYEES E

WHERE   SALARY >=12000

;

--2. 사원번호가 176 인 사람의 LAST_NAME 과 부서 번호를 조회한다.

SELECT  LAST_NAME

        ,DEPARTMENT_ID

FROM    EMPLOYEES

WHERE   EMPLOYEE_ID =  176

;

--3. 연봉이 5000 에서 12000의 범위 이외인 사람들의 LAST_NAME 및 연봉을 조회힌다.

SELECT  LAST_NAME

        ,SALARY

FROM    EMPLOYEES

WHERE   NOT (SALARY > 5000

AND     SALARY <12000)

ORDER   BY  SALARY 

;

--4.  2005/02/20 일부터 2005/05/01 사이에 고용된 사원들의 

--    LAST_NAME 사번, 고용일자를 조회한다.

--    고용일자 순으로 정렬한다.

SELECT  LAST_NAME

        ,EMPLOYEE_ID

        ,HIRE_DATE

FROM    EMPLOYEES

WHERE   HIRE_DATE >= TO_DATE('2005-02-20' , 'YYYY-MM-DD')

AND     HIRE_DATE <= TO_DATE('2005-05-01' , 'YYYY-MM-DD')

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

;

--6. 20 번 및 50 번 부서에 근무하며, 연봉이 5000 ~ 12,000 사이인 사원들의 

--   LAST_NAME 및 연봉을 조회한다.

SELECT  LAST_NAME

        ,SALARY

FROM    EMPLOYEES

WHERE   (DEPARTMENT_ID =20

OR      DEPARTMENT_ID =50)

AND     SALARY > 5000

AND     SALARY < 12000

;

--7. 2004년도에 고용된 모든 사람들의 LAST_NAME 및 고용일을 조회한다.

SELECT LAST_NAME

       ,HIRE_DATE

FROM    EMPLOYEES

WHERE   HIRE_DATE >= TO_DATE('2004-01-01' , 'YYYY-MM-DD')

AND     HIRE_DATE <= TO_DATE('2004-12-31' , 'YYYY-MM-DD')

;

--8. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.

SELECT  LAST_NAME

        ,JOB_ID

FROM    EMPLOYEES

WHERE   MANAGER_ID IS NULL

;

--9. 커미션을 버는 모든 사원들의 LAST_ANME, 연봉 및 커미션을 조회한다.

--   연봉 역순, 커미션 역순차로 정렬한다.

SELECT  LAST_NAME

        ,SALARY

        ,COMMISSION_PCT

FROM    EMPLOYEES

WHERE   COMMISSION_PCT IS NOT 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 및 e 글자가 있는 사원들의 LAST_NAME 을 조회힌다.

SELECT  LAST_NAME

FROM    EMPLOYEES

WHERE   LAST_NAME LIKE '%a%'

AND      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.   직업이 AD_PRESS 인 사람은 A 등급을, 

--ST_MAN 인 사람은 B 등급을,

--IT_PROG 인 사람은 C 등급을,

--SA_REP 인 사람은 D 등급을, 

--ST_CLEAK 인 사람은 E 등급을 

--기타는 0 을 부여하여 조회한다.

SELECT  JOB_ID

        ,DECODE(

        JOB_ID  

        ,'AD_PRESS','A'

        ,'ST_MAN','B'

        ,'IT_PROG','C'

        ,'SA_REP','D'

        ,'ST_CLERK','E'

        ,'0'

        ) G

FROM    EMPLOYEES

;

--14. 모든 사원들의 LAST_NAME, 부서 번호 및 부서 이름을 조회한다.

SELECT  LAST_NAME

        ,E.DEPARTMENT_ID

        ,DEPARTMENT_NAME

FROM    EMPLOYEES   E

        ,DEPARTMENTS D

WHERE   E.DEPARTMENT_ID =   D.DEPARTMENT_ID

;

--15. 부서번호 30 내의 모든 직업들을 유일한 포맷으로 조회한다. 

--     90 부서 또한 포함한다.

SELECT DISTINCT JOB_ID

        ,LOCATION_ID

FROM    EMPLOYEES   E 

        ,DEPARTMENTS D  

WHERE  D.DEPARTMENT_ID = E.DEPARTMENT_ID

        AND (D.DEPARTMENT_ID <= 30

        OR D.DEPARTMENT_ID = 90)

;

--16. 커미션을 버는 모든 사람들의 

-- LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.

SELECT  LAST_NAME

        ,DEPARTMENT_NAME

        ,L.LOCATION_ID

        ,CITY

FROM    EMPLOYEES E,

        DEPARTMENTS D,

        LOCATIONS L

WHERE   (D.DEPARTMENT_ID = E.DEPARTMENT_ID

AND     L.LOCATION_ID = D.LOCATION_ID)

AND      COMMISSION_PCT IS NOT NULL

;

--17.  LAST_NAME 이 Davies 인 사람보다 후에 고용된 사원들의 

-- LAST_NAME 및 HIRE_DATE 을 조회한다.

SELECT  E.LAST_NAME

       ,E.HIRE_DATE

FROM    EMPLOYEES E

   ,(

      SELECT *

      FROM  EMPLOYEES

      WHERE LAST_NAME = 'Davies'

      )E1

WHERE E.EMPLOYEE_ID > E1.EMPLOYEE_ID

;

SELECT LAST_NAME

       ,HIRE_DATE

FROM    EMPLOYEES E

WHERE   HIRE_DATE >= (

                      SELECT HIRE_DATE

                      FROM  EMPLOYEES

                      WHERE LAST_NAME = 'Davies'

                      )

;

--18. 자신의 매니저보다 먼저 고용된 사원들의 LAST_NAME 및 고용일을 조회한다

SELECT E.LAST_NAME

       ,E.HIRE_DATE

       ,M.LAST_NAME MANAGER_LAST_NAME

       ,M.HIRE_DATE MANAGER_HIRE_DATE

FROM    EMPLOYEES E     ----부하직원

        ,EMPLOYEES M    ----상사    

WHERE   E.MANAGER_ID = M.EMPLOYEE_ID

AND     E.HIRE_DATE < M.HIRE_DATE

;

--19. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.

SELECT  MAX(SALARY)

    ,MIN(SALARY)       

    ,SUM(SALARY)

    ,ROUND(AVG(SALARY),0)               

FROM    EMPLOYEES

;

--20.  각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다.

SELECT  JOB_ID

    ,MAX(SALARY)

    ,MIN(SALARY)       

    ,SUM(SALARY)

    ,ROUND(AVG(SALARY),0)               

FROM    EMPLOYEES

GROUP   BY  JOB_ID

;     

--21.   동일한 직업을 가진 사원들의 총 수를 조회한다

SELECT  JOB_ID

        ,COUNT(1)

FROM    EMPLOYEES

GROUP   BY   JOB_ID

;

--22.   매니저로 근무하는 사원들의 총 수를 조회한다

SELECT COUNT(DISTINCT MANAGER_ID) MANAGER#

       ,MANAGER_ID

       ,MIN(SALARY)

FROM   EMPLOYEES

GROUP   BY  MANAGER_ID

ORDER   BY  MIN(SALARY) DESC

;

SELECT DISTINCT MANAGER_ID MANAGER#

FROM   EMPLOYEES

;

--23. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.

SELECT MAX(SALARY)-MIN(SALARY) DIFF

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)

FROM    DEPARTMENTS D

        ,EMPLOYEES  E

WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP   BY  DEPARTMENT_NAME

            ,LOCATION_ID

;

--26. 총 사원 수 및 2001, 2002, 2003, 2004 년도 별 고용된 사원들의 총 수를 다음과 같이 조회한다

-- DUMMY TABLE 이라고함 (DUAL)

SELECT    (

            SELECT COUNT(1)

            FROM   EMPLOYEES

           )TOTAL

          ,(

          SELECT    COUNT(1)

          FROM      EMPLOYEES

          WHERE     HIRE_DATE >= TO_DATE('2001-01-01','YYYY-MM-DD')

          AND       HIRE_DATE <= TO_DATE('2001-12-31','YYYY-MM-DD')

          ) "2001"

          ,(

          SELECT    COUNT(1)

          FROM      EMPLOYEES

          WHERE     HIRE_DATE >= TO_DATE('2002-01-01','YYYY-MM-DD')

          AND       HIRE_DATE <= TO_DATE('2002-12-31','YYYY-MM-DD')

          ) "2002"

          ,(

          SELECT    COUNT(1)

          FROM      EMPLOYEES

          WHERE     HIRE_DATE >= TO_DATE('2003-01-01','YYYY-MM-DD')

          AND       HIRE_DATE <= TO_DATE('2003-12-31','YYYY-MM-DD')

          ) "2003"

          ,(

          SELECT    COUNT(1)

          FROM      EMPLOYEES

          WHERE     HIRE_DATE >= TO_DATE('2004-01-01','YYYY-MM-DD')

          AND       HIRE_DATE <= TO_DATE('2004-12-31','YYYY-MM-DD')

          ) "2004"

FROM    DUAL

;

SELECT JOB_ID JOB

        ,(

          SELECT SUM(SALARY)

          FROM   EMPLOYEES E1

          WHERE  E1.DEPARTMENT_ID = 20

          AND    E1.JOB_ID   = E.JOB_ID

          )"DEPT 20"

          ,SUM(SALARY)TOTAL

FROM    EMPLOYEES   E

GROUP   BY E.JOB_ID

;

--27. 다음과 같은 포맷으로 각 부서 별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다.

--28. LAST_NAME 이 Zlotkey 와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 조회한다.

--    결과값에서 Zlotkey 는 제외한다.

SELECT  LAST_NAME

        ,HIRE_DATE

FROM    EMPLOYEES E

WHERE   DEPARTMENT_ID =(

                        SELECT DEPARTMENT_ID

                        FROM    EMPLOYEES

                        WHERE   LAST_NAME = 'Zlotkey'

                        )

AND NOT LAST_NAME='Zlotkey'

;

--29. 회사 전체 평균 연봉보다 더 받는 사원들의 사번 및 LAST_NAME 을 조회한다.

SELECT  EMPLOYEE_ID

        ,LAST_NAME

FROM    EMPLOYEES

WHERE   SALARY > (

                  SELECT  AVG(SALARY)

                  FROM  EMPLOYEES

                  )

;

--30. ** 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%')  

                        )

;   

--31.  위치 ID 가 1700 인 부서에서 근무하는 사원들의 LAST_NAME, 부서 번호 및 JOB_ID 를 조회한다.

SELECT LAST_NAME

       ,D.DEPARTMENT_ID

       ,JOB_ID

FROM   EMPLOYEES   E

       ,DEPARTMENTS D

WHERE  D.DEPARTMENT_ID  =   E.DEPARTMENT_ID

AND    D.LOCATION_ID = 1700

;

--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  D.DEPARTMENT_ID

        ,LAST_NAME

        ,JOB_ID

FROM    EMPLOYEES   E

        ,DEPARTMENTS D

WHERE   E.DEPARTMENT_ID =  D.DEPARTMENT_ID

AND     DEPARTMENT_NAME = 'Executive'

;

--34. 회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이

--   근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다.

SELECT 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

                                              )

                          )

;

-- 35. ST_CLERK 인 직업 ID 를 가진 사원이 없는 부서 ID 를 조회한다.

-- NULL 값은 제외한다. (NOT EXISTS 사용)

SELECT  DISTINCT DEPARTMENT_ID

FROM    EMPLOYEES E

WHERE   NOT EXISTS (

                        SELECT  *

                        FROM    EMPLOYEES E1

                        WHERE   E.DEPARTMENT_ID = E1.DEPARTMENT_ID

                        AND     JOB_ID = 'ST_CLERK'

                   )

AND     DEPARTMENT_ID IS NOT NULL                   

;

SELECT  DISTINCT DEPARTMENT_ID

FROM    EMPLOYEES E

WHERE   DEPARTMENT_ID NOT IN   (

                                SELECT  DEPARTMENT_ID

                                FROM    EMPLOYEES E1

                                WHERE   E1.DEPARTMENT_ID = E.DEPARTMENT_ID

                                AND     E1.JOB_ID = 'ST_CLERK'

                               )

AND     DEPARTMENT_ID IS NOT NULL                   

;

-- 36. 위치한 부서가 없는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용)

SELECT  COUNTRY_ID CO

        , COUNTRY_NAME

FROM    COUNTRIES C

WHERE   C.COUNTRY_ID IN (

                            SELECT  COUNTRY_ID

                            FROM    LOCATIONS L

                            WHERE   NOT EXISTS(

                                                SELECT  *

                                                FROM    DEPARTMENTS D

                                                WHERE   L.LOCATION_ID = D.LOCATION_ID

                                              )

                        )

; 

SELECT  C.COUNTRY_ID CO

        , COUNTRY_NAME

FROM    COUNTRIES C

        , (

            SELECT  DISTINCT COUNTRY_ID

            FROM    LOCATIONS L

            WHERE   NOT EXISTS(

                                SELECT  *

                                FROM    DEPARTMENTS D

                                WHERE   L.LOCATION_ID = D.LOCATION_ID

                              )

          ) L -- 뷰 형식!!!!!

WHERE   C.COUNTRY_ID = L.COUNTRY_ID

; 

SELECT  *

FROM    EMP_DETAILS_VIEW

;

-- 37. 기존의 직업을 여전히 가지고 있는 사원들의 사번 및 JOB_ID 를 조회한다

SELECT  E.EMPLOYEE_ID

        , JOB_ID

FROM    EMPLOYEES E

        , (

            SELECT  EMPLOYEE_ID

            FROM    EMPLOYEES

            MINUS   

            SELECT  EMPLOYEE_ID

            FROM    JOB_HISTORY

          ) E1

WHERE   E.EMPLOYEE_ID = E1.EMPLOYEE_ID          

;

-- 41. 커미션을 버는 사원들의 부서 및 연봉이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다.

SELECT  LAST_NAME       --MULTI_IN 을 쓰는 것

        ,DEPARTMENT_ID

        ,SALARY

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_ID

        ,SALARY

FROM    EMPLOYEES

WHERE   (SALARY, COMMISSION_PCT) IN (

                                        SELECT  SALARY,

                                                COMMISSION_PCT

                                        FROM    EMPLOYEES E

                                                ,DEPARTMENTS D

                                        WHERE    D. DEPARTMENT_ID = E. DEPARTMENT_ID    

                                        AND      D. LOCATION_ID = 2500

                                      )

;

-- 43.  LAST_NAME 이 'Kochhar' 인 사원과 동일한 연봉 및 커미션을 버는 사원들의 

--      LAST_NAME, 부서 번호 및 연봉을 조회한다.

SELECT  LAST_NAME

        ,DEPARTMENT_ID

        ,E. SALARY

FROM    EMPLOYEES E

         ,(

          SELECT    SALARY

                    ,NVL(COMMISSION_PCT,0) COMMISSION_PCT

          FROM      EMPLOYEES

          WHERE     LAST_NAME = 'Kochhar'

          )K

WHERE   E.SALARY = K.SALARY

AND     NVL(E.COMMISSION_PCT,0) = K.COMMISSION_PCT

AND     E. LAST_NAME != 'Kochhar'

;

-- 44. 직업 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

FROM    EMPLOYEES   E

        ,DEPARTMENTS D

        ,LOCATIONS  L

WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID

AND     L.LOCATION_ID = D.LOCATION_ID

AND     CITY LIKE '%T%'                        

;                       

--46. 각 부서별 평균 연봉보다 더 받는 동일부서 근무사원들의 

-- LAST_NAME, 연봉, 부서번호 및 해당 부서의 평균 연봉을 조회한다.

-- 결과는 부서별 연봉을 기준으로 정렬한다.

SELECT  E.LAST_NAME

        , E.SALARY

        , E.DEPARTMENT_ID DEPTNO

        , AVG.SALARY DEPT_AVG

FROM    EMPLOYEES E

        , (

            SELECT  DEPARTMENT_ID

                    , ROUND(AVG(SALARY)) SALARY

            FROM    EMPLOYEES

            GROUP   BY DEPARTMENT_ID        

          ) AVG

WHERE   E.DEPARTMENT_ID = AVG.DEPARTMENT_ID

AND     E.SALARY > AVG.SALARY

ORDER   BY AVG.SALARY

;          

-- 47. NOT EXISTS 연산자를 사용하여 매니저가 아닌 사원 이름을 조회한다.

SELECT  LAST_NAME

        ,EMPLOYEE_ID

FROM    EMPLOYEES E

WHERE   NOT EXISTS(

                    SELECT 1

                    FROM   EMPLOYEES E1

                    WHERE  E1.MANAGER_ID = E.EMPLOYEE_ID

                    )

;                                            

-- 48. 소속부서의 평균 연봉보다 적게 버는 사원들의 last_name 을 조회한다.

SELECT  LAST_NAME

FROM    EMPLOYEES   E

        ,(

           SELECT   AVG(SALARY) SALARY

                    ,DEPARTMENT_ID

           FROM     EMPLOYEES 

           GROUP    BY  DEPARTMENT_ID         

           )E1      

WHERE   E.SALARY > E1.SALARY

AND     E.DEPARTMENT_ID = E1.DEPARTMENT_ID

;