DB쿼리 실습 (2일차 실습문제)

-- 1. DEPARTMENTS테이블에서 모든 부서 정보를 조회하세요.
SELECT  *
FROM    DEPARTMENTS
;
-- 2. DEPARTMENTS테이블에서 부서명 (DEPARTMENT_NAME)이 Sales인 부서의 모든정보를 조회하세요.
SELECT  *
FROM    DEPARTMENTS
WHERE   DEPARTMENT_NAME = 'Sales'
;
-- 3. DEPARTMENTS테이블에서 부서명이 IT ,Shipping,FInance 인부서 번호 (DEPARTMENT_ID)를 조회하세요.
SELECT  DEPARTMENT_ID
FROM    DEPARTMENTS
WHERE   DEPARTMENT_NAME IN ('IT','Shipping','Finance')
;
-- 4. DEPARTMENTS테이블에서 부서명중 't'가 포함되어 있는 부서번호 (id) 부서명 (name)을 조회하세요.
SELECT  DEPARTMENT_ID
        , DEPARTMENT_NAME
FROM    DEPARTMENTS
WHERE   DEPARTMENT_NAME LIKE '%t%'
;
-- 5. DEPARTMENTS 테이블에서 부서명중 't' , 'a' , 'u' , 'e' 중 하나라도 포함되어 있는 부서의 모든 정보를 조회하세요.
SELECT  *
FROM    DEPARTMENTS
WHERE   DEPARTMENT_NAME LIKE '%t%'
OR      DEPARTMENT_NAME LIKE '%a%'
OR      DEPARTMENT_NAME LIKE '%u%'
OR      DEPARTMENT_NAME LIKE '%e%'
;
-- 6. EMPLOYEES 테이블에서 부서번호별 사원의 수를 조회하세요.
SELECT  DEPARTMENT_ID
        , COUNT(1)
FROM    EMPLOYEES
GROUP   BY DEPARTMENT_ID
;
-- 7. EMPLOYEES 테이블에서 60번 부서의 직무별 사원수를 조회하세요,
SELECT  JOB_ID
        , COUNT(1)
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID = 60
GROUP   BY JOB_ID
;
 
-- 8. 부서변호와 직무별 급여의 평균을 구하시오.
SELECT  DEPARTMENT_ID
        , JOB_ID
        , ROUND (AVG(SALARY) ,2)
FROM    EMPLOYEES
GROUP   BY DEPARTMENT_ID -- 집계함수를 제외하고 모두 넣는다.
        , JOB_ID
HAVING  AVG(SALARY) >= 5000 --평균급여가 5000이상인 것만 가지고 와라
ORDER   BY DEPARTMENT_ID ASC  
        , JOB_ID ASC
;
-- 부서번호별 사원 의 수를 조회하세요. 단, 사원수가 1명인 부서는 제외합니다.
SELECT  DEPARTMENT_ID
       , COUNT(1)
FROM    EMPLOYEES
GROUP   BY DEPARTMENT_ID
HAVING  COUNT(1) > 1
;
-- 10 .FIRST_NAME 이 Bruce 인 사람이 근무하는 부서의 모든 사람들을 조회하세요.
SELECT  *
FROM    EMPLOYEES
WHERE   EMPLOYEE_ID IN (    --브루스가 여러명일수 있기 때문에
                            SELECT  EMPLOYEE_ID
                            FROM    EMPLOYEES
                            WHERE   FIRST_NAME = 'Bruce'
                       )
;
-- 11. 입사일이 2001-01-13 인 사람과 같은 직무의 사람들을 모두 조회하세요.
SELECT  *
FROM    EMPLOYEES
WHERE  JOB_ID IN (
                        SELECT  JOB_ID
                        FROM    EMPLOYEES
                        WHERE   HIRE_DATE = TO_DATE('2001-01-13', 'YYYY-MM-DD')
                    )
;
-- 12. FI_ACCOUNT 직무의 평균연봉보다 많이 버는 사람들을 조회하세요.
SELECT  *
FROM    EMPLOYEES
WHERE   SALARY > (
                        SELECT  AVG(SALARY)
                        FROM    EMPLOYEES
                        WHERE   JOB_ID = 'FI_ACCOUNT'
                 )
;
-- 13. COMMISSION을 받는 사람들의 평균연봉보다 많이 버는 사람들을 조회하세요.
SELECT  *
FROM    EMPLOYEES
WHERE   SALARY > (
                        SELECT  AVG(SALARY)
                        FROM    EMPLOYEES
                        WHERE   COMMISSION_PCT IS NOT NULL
                    )
;
-- 14. 151번 사원보다 연봉이 크거나 같은 사람들을 조회하세요. 단, 151번 사원은 결과에서 제외합니다. 
SELECT  *
FROM    EMPLOYEES
WHERE   SALARY >= (
                        SELECT  SALARY
                        FROM    EMPLOYEES
                        WHERE   EMPLOYEE_ID = 151
                    )
AND     EMPLOYEE_ID != 151  -- 단, 조건
;
 
-- 151번의 EMAIL을 조회하세요.
SELECT  EMAIL
FROM    EMPLOYEES
WHERE   EMPLOYEE_ID = 151
;
-- 15. Sales 부서에서 일하는 모든 사원들의 EMPLOYEE_ID, FIRST_NAME,SALARY,DEPARTMENT_NAME을 조회하세요. 34
SELECT  EMPLOYEE_ID
        , FIRST_NAME
        , SALARY
        , DEPARTMENT_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     D.DEPARTMENT_NAME = 'Sales'
;
-- 16. Sales 부서에서 일하지 않는 모든 사원들의 EMPLOYEE_ID , FIRST_NAME , SALARY, 
-- DEPARTMENT_NAME을 조회하세요. 72/73
SELECT  EMPLOYEE_ID
        , FIRST_NAME
        , SALARY
        , DEPARTMENT_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     D.DEPARTMENT_NAME != 'Sales'
;
-- 17. Roma 도시에서 일하는 모든 사람들의 EMPLOYEE_ID, DEPARTMENT_NAME을 조회하세요 0
SELECT  EMPLOYEE_ID
        , DEPARTMENT_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , LOCATIONS L
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     L.LOCATION_ID =  D.LOCATION_ID
AND     L.CITY = 'Roma'
;
-- 18. Tokyo 시에서 일하지 않는 모든 사원들의 수를 조회하세요. 106/107
SELECT  COUNT(1)
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , LOCATIONS L
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     L.LOCATION_ID =  D.LOCATION_ID
AND     L.CITY != 'Tokyo'
;
-- 19. 30번 부서에서 일하는 모든 사원들의 EMPLOYEE_ID , DEPARTMENT_NAME , CITY , COUNTRY_NAME 을 조회하세요. 6
SELECT  EMPLOYEE_ID
        , DEPARTMENT_NAME
        , CITY
        , COUNTRY_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , LOCATIONS L
        , COUNTRIES C
WHERE   L.COUNTRY_ID = C.COUNTRY_ID
AND     L.LOCATION_ID = D.LOCATION_ID
AND     D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND     D.DEPARTMENT_ID = 30
;
-- 20. Americas Region에서 일하는 모든 사원들의 EMPLOYEE_ID, FIRST_NAME , COUNTRY_NAME  , CITY , DEPARTMENT_NAME ,
-- JOB_TITLE을 조회하세요. 70
SELECT  EMPLOYEE_ID
        , FIRST_NAME
        , COUNTRY_NAME
        , CITY
        , DEPARTMENT_NAME
        , JOB_TITLE
FROM    JOBS JB
        , EMPLOYEES E
        , DEPARTMENTS D
        , COUNTRIES C
        , LOCATIONS L
        , REGIONS R
WHERE   R.REGION_ID = C.REGION_ID
AND     C.COUNTRY_ID = L.COUNTRY_ID
AND     L.LOCATION_ID = D.LOCATION_ID
AND     D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND     E.JOB_ID = JB.JOB_ID
AND     R.REGION_NAME = 'Americas'
;