SQL 50문제 실습하기

 
-- 1. 모든 사원의 이름(FIRST_NAME, LAST_NAME)을 조회하라.
 
SELECT  FIRST_NAME
        , LAST_NAME
FROM    EMPLOYEES
;
 
 
-- 2. 모든 사원의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES
;
 
-- 3. 모든 도시 명을 조회하라.
 
SELECT  CITY
FROM    LOCATIONS
;
 
-- 4. 이름(FIRST_NAME)이 M 으로 시작하는 사원의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES
WHERE   FIRST_NAME LIKE 'M%'
;
 
-- 5. 이름(FIRST_NAME)의 두 번째 글자가 'a'인 사원의 이름(FIRST_NAME)과 연봉을 조회하라.
 
SELECT  FIRST_NAME
        , SALARY
FROM    EMPLOYEES
WHERE   FIRST_NAME LIKE '_a%'
;
 
-- 6. 도시 명을 오름차순 정렬하라.
 
SELECT  CITY
FROM    LOCATIONS
ORDER   BY  CITY;
 
-- 7. 부서 명을 내림차순 정렬하라.
 
SELECT  D.DEPARTMENT_NAME
FROM    DEPARTMENTS D
ORDER   BY D.DEPARTMENT_NAME DESC
;
 
 
-- 8. 연봉이 7000 이상인 사원들의 모든 정보를 연봉순(오름차순)으로 정렬하라.
 
SELECT  *
FROM    EMPLOYEES E
WHERE   E.SALARY >= 7000
ORDER   BY E.SALARY;
 
-- 9. 인센티브(COMMISSION_PCT)를 받지 않는 사원들의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES
WHERE   COMMISSION_PCT IS NULL
;
 
 
-- 10. 인센티브(COMMISSION_PCT)를 받는 사원들의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES
WHERE   COMMISSION_PCT IS NOT NULL
;
 
-- 11. 2007년 06월 21일에 입사한 사원의 사원번호, 이름(FIRST_NAME, LAST_NAME) 그리고 부서번호를 조회하라.
 
SELECT  E.FIRST_NAME
        , E.LAST_NAME  
        , E.DEPARTMENT_ID
FROM    EMPLOYEES E
WHERE   E.HIRE_DATE = TO_DATE ('2007/06/21' , 'YYYY/MM/DD')
;
 
 
-- 12. 2006년에 입사한 사원의 사원번호와 입사일을 조회하라.
 
SELECT  E.EMPLOYEE_ID
        , E.HIRE_DATE
FROM    EMPLOYEES E
WHERE   E.HIRE_DATE >= TO_DATE ( '2006/01/01', 'YYYY/MM/DD' )
AND     E.HIRE_DATE <= TO_DATE ( '2006/12/31', 'YYYY/MM/DD' )
;
 
-- 13. 이름(FIRST_NAME)의 길이가 5글자 이상인 사원을 조회하라.
 
SELECT  E.FIRST_NAME
FROM    EMPLOYEES E
WHERE   E.FIRST_NAME LIKE '_____%'
;
 
-- 14. 부서번호별 사원수를 조회하라. (부서번호 오름차순 정렬)
 
SELECT  E.DEPARTMENT_ID
        ,COUNT(1)
FROM    EMPLOYEES E
GROUP   BY  E.DEPARTMENT_ID
ORDER   BY  E.DEPARTMENT_ID DESC
;
 
-- 15. 직무 아이디별 평균 연봉을 조회하라. (직무 아이디 내림차순 정렬)
 
SELECT  E.JOB_ID
        , AVG(E.SALARY)
FROM    EMPLOYEES E
GROUP   BY  E.JOB_ID
ORDER   BY  E.JOB_ID DESC
;
 
-- 16. 상사가 있는 사원들의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES E
WHERE   E.MANAGER_ID IS NOT NULL
;
 
 
-- 17. 상사가 없는 사원들의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES E
WHERE   E.MANAGER_ID IS NULL
;
 
-- 18. 모든 사원들의 사원번호, 이름(FIRST_NAME, LAST_NAME), 부서번호 그리고 부서명을 조회하라.
 
SELECT  E.EMPLOYEE_ID
        , E.FIRST_NAME
        , E.LAST_NAME
        , E.DEPARTMENT_ID
        , D.DEPARTMENT_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
;
 
 
-- 19. 모든 부서의 부서명과 도시명을 조회하라.
    
SELECT  D.DEPARTMENT_NAME
        , L.CITY
FROM    DEPARTMENTS D
        , LOCATIONS L
WHERE   D.LOCATION_ID = L.LOCATION_ID
;
 
-- 20. 모든 사원들의 사원번호, 부서명, 직무명을 조회하라.
 
SELECT  E.EMPLOYEE_ID
        , D.DEPARTMENT_NAME
        , J.JOB_TITLE
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , JOBS J
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     E.JOB_ID = J.JOB_ID
;
 
-- 21. 모든 사원들의 사원번호, 부서명, 직무명, 도시명을 조회하라.
 
SELECT  E.EMPLOYEE_ID
        , D.DEPARTMENT_NAME
        , J.JOB_TITLE
        , L.CITY
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , JOBS J
        , LOCATIONS L
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     E.JOB_ID = J.JOB_ID
AND     D.LOCATION_ID = L.LOCATION_ID
;
 
        
 
 
-- 22. 10번, 20번, 30번 부서에서 근무하는 사원들의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES E
WHERE   E.DEPARTMENT_ID IN ( 10, 20, 30 )
;
 
 
-- 23. 6인 미만의 사원이 근무하는 부서의 이름을 조회하라.
 
SELECT  D.DEPARTMENT_NAME
FROM    DEPARTMENTS D
WHERE   D.DEPARTMENT_ID IN (
                                SELECT  E.DEPARTMENT_ID
                                FROM    EMPLOYEES E
                                GROUP   BY  E.DEPARTMENT_ID
                                HAVING  COUNT(1) < 6
                            )
                            ;
 
-- 24. 4인 미만의 사원이 근무하는 부서의 평균 연봉과 부서명을 조회하라.
 
SELECT  AVG(SALARY)
        , D.DEPARTMENT_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP   BY  D.DEPARTMENT_NAME
HAVING  COUNT(1) < 4
;
 
-- 25. IT 부서의 연봉 총합을 조회하라.
 
SELECT  AVG(SALARY)
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     D.DEPARTMENT_NAME = 'IT'
GROUP   BY  E.DEPARTMENT_ID
;
 
-- 26. 대륙명(REGIONS)별 도시의 개수를 조회하라.
 
SELECT  R.REGION_NAME
        , COUNT(1)
FROM    COUNTRIES C
        , REGIONS R
        , LOCATIONS L
WHERE   L.COUNTRY_ID = C.COUNTRY_ID
AND     R.REGION_ID = C.REGION_ID
GROUP   BY R.REGION_NAME
;
 
 
--강사님
SELECT  R.REGION_NAME
        , CE.CNT
FROM    (
            SELECT  REGION_ID
                    , COUNT(1) CNT
            FROM    COUNTRIES C
                    , LOCATIONS L
            WHERE   C.COUNTRY_ID = L.COUNTRY_ID
            GROUP   BY REGION_ID
        ) CE
        , REGIONS R
WHERE   CE.REGION_ID = R.REGION_ID
;
 
 
 
-- 27. 도시명 별 부서의 개수를 조회하라.
 
SELECT  L.CITY
        , COUNT(1)
FROM    LOCATIONS L
        , DEPARTMENTS D
WHERE   L.LOCATION_ID = D.LOCATION_ID
GROUP   BY L.CITY
;
 
 
-- 28. 부서가 존재하지 않는 도시를 조회하라.
 
SELECT  L.CITY
FROM    LOCATIONS L
WHERE   L.LOCATION_ID NOT IN (
                                SELECT  D.LOCATION_ID
                                FROM    DEPARTMENTS D
                             )
;
 
 
 
-- 29. 사원이 존재하지 않는 국가를 조회하라.
 
SELECT  COUNTRY_NAME
FROM    COUNTRIES 
WHERE   COUNTRY_ID NOT IN (
                                SELECT  L.COUNTRY_ID
                                FROM    LOCATIONS L
                                        , DEPARTMENTS D
                                        , EMPLOYEES E
                                WHERE   L.LOCATION_ID = D.LOCATION_ID
                                AND     D.DEPARTMENT_ID = E.DEPARTMENT_ID
                            )
;
 
-- 30. 150번 사원보다 빨리 입사한 사원 중 가장 최신에 입사한 사원의 모든 정보를 조회하라.
 
SELECT  *
FROM    (
            SELECT  *
            FROM    EMPLOYEES 
            WHERE   HIRE_DATE < (
                                    SELECT  HIRE_DATE
                                    FROM    EMPLOYEES
                                    WHERE   EMPLOYEE_ID = 150
                                )
            ORDER   BY  HIRE_DATE DESC
        ) 
WHERE   ROWNUM = 1
;
 
-- 31. 직무를 전환한 이력이 있는 사원의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES
WHERE   EMPLOYEE_ID IN(
                            SELECT EMPLOYEE_ID
                            FROM    JOB_HISTORY
                        )     
;
 
-- 32. 부서장으로 있는 사원의 모든 정보와 부서명을 조회하라.
 
SELECT  *
FROM    EMPLOYEES E
WHERE   E.DEPARTMENT_ID IN (
                                SELECT  D.MANAGER_ID
                                FROM    DEPARTMENTS D
                           )
;
 
-- 33. 다른 사원의 상사인 사원의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES
WHERE   EMPLOYEE_ID IN (
                            SELECT  MANAGER_ID
                            FROM    EMPLOYEES
                        )
ORDER   BY EMPLOYEE_ID
;
 
SELECT  DISTINCT MANAGER.*
FROM    EMPLOYEES MANAGER
        , EMPLOYEES E
WHERE   MANAGER.EMPLOYEE_ID = E.MANAGER_ID
ORDER   BY MANAGER.EMPLOYEE_ID
;
 
-- 34. 이름(FIRST_NAME)이 C로 시작하고 s로 끝나는 사원의 부서명, 직무명, 도시명, 국가명, 대륙명을 조회하라.
 
SELECT  E.FIRST_NAME
        , D.DEPARTMENT_NAME
        , J.JOB_TITLE
        , L.CITY
        , C.COUNTRY_NAME
        , R.REGION_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
        , JOBS J
        , LOCATIONS L
        , COUNTRIES C
        , REGIONS R
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     J.JOB_ID = E.JOB_ID
AND     D.LOCATION_ID = L.LOCATION_ID
AND     L.COUNTRY_ID = C.COUNTRY_ID
AND     C.REGION_ID = R.REGION_ID
AND     E.FIRST_NAME LIKE 'C%s'
;
 
-- 35. 직무를 전환한 이력이 없는 사원의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES
WHERE   EMPLOYEE_ID NOT IN (
                                SELECT  EMPLOYEE_ID
                                FROM    JOB_HISTORY
                            )
;
 
-- 36. 150번 사원보다 늦게 입사한 사원 중 150번 사원보다 더 많은 연봉을 받는 사원을 조회하라.
SELECT  EM.*
FROM    (
            SELECT  SALARY
                    , HIRE_DATE
            FROM    EMPLOYEES
            WHERE   EMPLOYEE_ID = 150
        ) ID_150
        , EMPLOYEES EM
WHERE   EM.SALARY > ID_150.SALARY
AND     EM.HIRE_DATE > ID_150.HIRE_DATE
;
 
 
-- 37. 자신의 상사가 자신 보다 늦게 입사한 사원의 모든 정보를 조회하라.
--여려움
 
SELECT  E.*
FROM    EMPLOYEES E
        , EMPLOYEES MANAGER
WHERE   E.MANAGER_ID = MANAGER.EMPLOYEE_ID
AND     E.HIRE_DATE < MANAGER.HIRE_DATE
;
 
 
 
-- 38. 100번 사원을 직속 상사로 두고 있는 사원들의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES
WHERE   MANAGER_ID = 100
;
 
-- 39. 100번 사원을 상사로 두고 있는 모든 사원들의 모든 정보를 조회하라.
-- 어려움
 
SELECT  LEVEL
        , E.*
FROM    EMPLOYEES E
START   WITH E.MANAGER_ID = 100
CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID
;
 
-- 112번 사원의 상사들을 모두 조회해라
 
SELECT  LEVEL
        , E.*
FROM    EMPLOYEES E
START   WITH E.EMPLOYEE_ID = 112
CONNECT BY PRIOR E.MANAGER_ID = E.EMPLOYEE_ID
;
 
--150번 사원의 모든 상사들의 이름과 부서명을 조회하라
 
SELECT  LEVEL
        , E.FIRST_NAME
        , D.DEPARTMENT_NAME
FROM    EMPLOYEES E
        , DEPARTMENTS D
WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
START   WITH E.EMPLOYEE_ID = 150
CONNECT BY PRIOR E.MANAGER_ID = E.EMPLOYEE_ID
;
 
 
-- 40. 사원수가 가장 많은 도시의 이름을 조회하라.
 
SELECT  A.CITY_NAME
FROM    (
            SELECT  L.CITY CITY_NAME
                    , COUNT(1)
            FROM    EMPLOYEES E
                    , DEPARTMENTS D
                    , LOCATIONS L
            WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
            AND     L.LOCATION_ID = D.LOCATION_ID
            GROUP   BY L.CITY
            ORDER   BY COUNT(1) DESC
        ) A
WHERE   ROWNUM = 1
;
 
-- 41. 직무의 종류가 가장 많은 부서의 이름을 조회하라.
 
 
SELECT  D.DEPARTMENT_NAME
FROM    (
            SELECT  E.DEPARTMENT_ID
                    , COUNT(1)
            FROM    EMPLOYEES E
                    , JOBS L
            WHERE   E.JOB_ID = L.JOB_ID
            GROUP   BY E.DEPARTMENT_ID
            ORDER   BY COUNT(1) DESC
        )A
        , DEPARTMENTS D
WHERE   A.DEPARTMENT_ID = D.DEPARTMENT_ID
AND     ROWNUM = 1
;
 
--강사님
SELECT  DEPARTMENT_NAME
FROM    (
            SELECT  DEPARTMENT_NAME
                    , COUNT(1) CNT
            FROM    (
                        SELECT  D.DEPARTMENT_NAME
                                , E.JOB_ID
                        FROM    EMPLOYEES E
                                , DEPARTMENTS D
                        WHERE   E.DEPARTMENT_ID = E.DEPARTMENT_ID
                        GROUP   BY  D.DEPARTMENT_NAME
                                , E.JOB_ID
                    )
            GROUP   BY DEPARTMENT_NAME
            ORDER   BY CNT DESC
        )
WHERE   ROWNUM = 1
;
 
-- 42. 담당 직무의 최대 연봉을 받고 있는 사원들의 모든 정보를 조회하라.
 
SELECT  *
FROM    (
            SELECT  JOB_ID
                    , MAX(SALARY) MAX_SALARY
            FROM    EMPLOYEES
            GROUP   BY  JOB_ID
        ) A
        , EMPLOYEES E
WHERE   E.JOB_ID = A.JOB_ID
AND     E.SALARY = A.MAX_SALARY
;
 
SELECT  *
FROM    EMPLOYEES E
        , JOBS J
WHERE   E.JOB_ID = J.JOB_ID
AND     J.MAX_SALARY = E.SALARY
;
 
-- 43. 담당 직무의 최소 연봉을 받고 있는 사원들의 모든 정보를 조회하라.
 
SELECT  *
FROM    EMPLOYEES E
        , JOBS J
WHERE   E.JOB_ID = J.JOB_ID
AND     J.MIN_SALARY = E.SALARY
;
 
SELECT  *
FROM    (
            SELECT  E.JOB_ID
                    , MIN(E.SALARY) MIN_SALARY
            FROM    EMPLOYEES E
            GROUP   BY E.JOB_ID
        ) MIN_TABLE
        , EMPLOYEES EM
WHERE   EM.JOB_ID = MIN_TABLE.JOB_ID
AND     EM.SALARY = MIN_TABLE.MIN_SALARY
;
-- 44. 부서와 직무별 최대연봉, 최소연봉, 사원수를 조회하라.
 
SELECT  MAX(E.SALARY)
        , MIN(E.SALARY)
        , COUNT(1)
FROM    JOBS J
        , EMPLOYEES E
        , DEPARTMENTS D
WHERE   J.JOB_ID = E.JOB_ID
AND     E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP   BY  J.JOB_ID
        , D.DEPARTMENT_ID
;
 
-- 45. 사원수가 가장 많은 도시에서 근무하는 모든 사원들의 연봉 총합을 조회하라.
 
SELECT  LO.CITY
        , SUM(EM.SALARY)
FROM    EMPLOYEES EM
        , (
            SELECT  *
            FROM    (
                        SELECT  L.CITY
                                , COUNT(1)
                        FROM    EMPLOYEES E
                                , DEPARTMENTS D
                                , LOCATIONS L
                        WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
                        AND     L.LOCATION_ID = D.LOCATION_ID
                        GROUP   BY L.CITY
                        ORDER   BY COUNT(1) DESC
                    )
            WHERE   ROWNUM = 1
        ) CITY_CNT
        , DEPARTMENTS DE
        , LOCATIONS LO
WHERE   EM.DEPARTMENT_ID = DE.DEPARTMENT_ID
AND     DE.LOCATION_ID = LO.LOCATION_ID
AND     LO.CITY = CITY_CNT.CITY
GROUP   BY LO.CITY
;
 
-- 46. 사원수가 가장 많은 도시에서 근무하는 모든 사원들의 부서별 및 직무별 평균 연봉을 조회하라.
 
SELECT  DE.DEPARTMENT_NAME
        , JO.JOB_TITLE
        , AVG(EM.SALARY)
FROM    EMPLOYEES EM
        , DEPARTMENTS DE
        , JOBS JO
        , LOCATIONS LO
        , (
            SELECT  *
            FROM    (
                        SELECT  L.CITY
                                , COUNT(1)
                        FROM    EMPLOYEES E
                                , DEPARTMENTS D
                                , LOCATIONS L
                        WHERE   E.DEPARTMENT_ID = D.DEPARTMENT_ID
                        AND     D.LOCATION_ID = L.LOCATION_ID
                        GROUP   BY L.CITY
                        ORDER   BY COUNT(1) DESC
                    )
            WHERE   ROWNUM = 1
          ) CNT
WHERE   EM.DEPARTMENT_ID = DE.DEPARTMENT_ID
AND     DE.LOCATION_ID = LO.LOCATION_ID
AND     JO.JOB_ID = EM.JOB_ID
AND     CNT.CITY = LO.CITY
GROUP   BY DE.DEPARTMENT_NAME
        , JO.JOB_TITLE
;
 
-- 47. 입사일이 가장 오래된 사원을 조회하라.
 
SELECT  *
FROM    EMPLOYEES EM
WHERE   EM.HIRE_DATE =  (
                            SELECT  *
                            FROM    (
                                        SELECT  HIRE_DATE
                                        FROM    EMPLOYEES E
                                        ORDER   BY HIRE_DATE
                                    )
                            WHERE   ROWNUM = 1
                        )
;
 
SELECT  *
FROM    EMPLOYEES
WHERE   HIRE_DATE = (
                        SELECT  MIN(HIRE_DATE)
                        FROM    EMPLOYEES
                     )
;
 
-- 48. 입사일이 가장 최근인 사원을 조회하라.
 
 
SELECT  *
FROM    EMPLOYEES   
WHERE   HIRE_DATE = (
                        SELECT  MAX(HIRE_DATE)
                        FROM    EMPLOYEES
                    )
;
 
SELECT  *
FROM    EMPLOYEES EM
WHERE   EM.HIRE_DATE =  (
                            SELECT  *
                            FROM    (
                                        SELECT  HIRE_DATE
                                        FROM    EMPLOYEES E
                                        ORDER   BY HIRE_DATE DESC
                                    )
                            WHERE   ROWNUM = 1
                        )
;
 
-- 49. 가장 최근에 입사한 사원과 가장 오래전에 입사한 사원의 일차를 계산해 조회하라.
 
SELECT  MAX(HIRE_DATE) - MIN(HIRE_DATE)
FROM    EMPLOYEES
;
 
-- 50. 가장 최근에 입사한 사원과 가장 오래전에 입사한 사원의 시간차를 계산해 조회하라.
 
SELECT  (MAX(HIRE_DATE) - MIN(HIRE_DATE)) * 24
FROM    EMPLOYEES
;
 
 
 
 
 
 
--현재 날짜 구하기
SELECT  SYSDATE 오늘
        , SYSDATE - 1 하루전
        , SYSDATE + 1 하루후
        , ADD_MONTHS(SYSDATE,1) 한달후
        , ADD_MONTHS(SYSDATE,-1) 한달전
        , ADD_MONTHS(SYSDATE, 12) 일년후 
        , ADD_MONTHS(SYSDATE, -12) 일년전
        , SYSDATE - (1 / 24) 한시간전
        , SYSDATE - (2 / 24) 두시간전
        , SYSDATE + (1 / 24) 한시간뒤
        , SYSDATE + (2 / 24) 한시간뒤
        , SYSDATE - (10 / 24 / 60) 십분전
        , SYSDATE - (10 / 24 / 60 / 60) 십초전
FROM    DUAL
;