SQL Study2 - SELECT

-- 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 DEPARTMENT_NAME

FROM DEPARTMENTS

ORDER BY DEPARTMENT_NAME DESC

;

-- 8. 연봉이 7000 이상인 사원들의 모든 정보를 연봉순(오름차순)으로 정렬하라.

SELECT *

FROM EMPLOYEES

WHERE SALARY > 7000

ORDER BY SALARY ASC

;

-- 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 EMPLOYEE_ID

, FIRST_NAME

, LAST_NAME

, DEPARTMENT_ID

FROM EMPLOYEES

WHERE HIRE_DATE = TO_DATE('2007/06/21', 'YYYY/MM/DD')

;

-- 12. 2006년에 입사한 사원의 사원번호와 입사일을 조회하라.

SELECT EMPLOYEE_ID

, HIRE_DATE

FROM EMPLOYEES

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

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

;

-- 13. 이름(FIRST_NAME)의 길이가 5글자 이상인 사원을 조회하라.

SELECT *

FROM EMPLOYEES

WHERE FIRST_NAME LIKE '_____%'

;

-- 14. 부서번호별 사원수를 조회하라. (부서번호 오름차순 정렬)

SELECT DEPARTMENT_ID

, COUNT(1)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID

;

-- 15. 직무 아이디별 평균 연봉을 조회하라. (직무 아이디 내림차순 정렬)

SELECT JOB_ID

, AVG(SALARY)

FROM EMPLOYEES

GROUP BY JOB_ID

ORDER BY JOB_ID DESC

;

-- 16. 상사가 있는 사원들의 모든 정보를 조회하라.

SELECT *

FROM EMPLOYEES

WHERE MANAGER_ID IS NOT NULL

;

-- 17. 상사가 없는 사원들의 모든 정보를 조회하라.

SELECT *

FROM EMPLOYEES

WHERE MANAGER_ID IS NULL

;

-- 18. 모든 사원들의 사원번호, 이름(FIRST_NAME, LAST_NAME), 부서번호 그리고 부서명을 조회하라.

SELECT E.EMPLOYEE_ID

, E.FIRST_NAME

, E.LAST_NAME

, D.DEPARTMENT_ID

, D.DEPARTMENT_NAME

FROM EMPLOYEES E

, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

;

-- 19. 모든 부서의 부서명과 도시명을 조회하라.

SELECT D.DEPARTMENT_ID

, 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 D.DEPARTMENT_ID = E.DEPARTMENT_ID

AND J.JOB_ID = E.JOB_ID

;

-- 21. 모든 사원들의 사원번호, 부서명, 직무명, 도시명을 조회하라.

SELECT EMPLOYEE_ID

, DEPARTMENT_NAME

, JOB_TITLE

, 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

WHERE DEPARTMENT_ID IN (10,20,30)

;

-- 23. 6인 미만의 사원이 근무하는 부서의 이름을 조회하라.

SELECT DEPARTMENT_NAME

FROM DEPARTMENTS

WHERE DEPARTMENT_ID IN (

SELECT DEPARTMENT_ID

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING COUNT(1) < 6

)

;

-- 24. 4인 미만의 사원이 근무하는 부서의 평균 연봉과 부서명을 조회하라.

SELECT D.DEPARTMENT_NAME

, AVG(SALARY)

FROM DEPARTMENTS D

, EMPLOYEES E

WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

GROUP BY DEPARTMENT_NAME

HAVING COUNT(1)<4

;

-- 25. IT 부서의 연봉 총합을 조회하라.

SELECT D.DEPARTMENT_NAME

, SUM(SALARY) SUM_SALARY

FROM DEPARTMENTS D

, EMPLOYEES E

WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

AND D.DEPARTMENT_NAME = 'IT'

GROUP BY DEPARTMENT_NAME

;

-- 26. 대륙명(REGIONS)별 도시의 개수를 조회하라.

SELECT R.REGION_NAME

, COUNT(1)

FROM COUNTRIES C

, LOCATIONS L

, REGIONS R

WHERE C.COUNTRY_ID = L.COUNTRY_ID

AND C.REGION_ID = R.REGION_ID

GROUP BY R.REGION_NAME

;

SELECT R.REGION_NAME

, C.CNT

FROM (

SELECT COUNT(1) CNT

, C.REGION_ID

FROM COUNTRIES C

, LOCATIONS L

WHERE C.COUNTRY_ID = L.COUNTRY_ID

GROUP BY C.REGION_ID

) C

, REGIONS R

WHERE C.REGION_ID = R.REGION_ID

;

SELECT R.REGION_NAME

, COUNT(L.LOCATION_ID)

FROM REGIONS R

, COUNTRIES C

, LOCATIONS L

WHERE R.REGION_ID = C.REGION_ID

AND C.COUNTRY_ID = L.COUNTRY_ID

GROUP BY R.REGION_NAME

;

-- 27. 도시명 별 부서의 개수를 조회하라.

SELECT CITY

, COUNT(1) NUM_OF_DEPARTMENT

FROM LOCATIONS L

, DEPARTMENTS D

WHERE L.LOCATION_ID = D.LOCATION_ID

GROUP BY CITY

;

-- 28. 부서가 존재하지 않는 도시를 조회하라.

SELECT CITY

FROM LOCATIONS

WHERE LOCATION_ID NOT IN (

SELECT L.LOCATION_ID

FROM DEPARTMENTS D

, LOCATIONS L

WHERE D.LOCATION_ID = L.LOCATION_ID

)

;

-- 29. 사원이 존재하지 않는 국가를 조회하라.

SELECT COUNTRY_NAME

FROM COUNTRIES

WHERE COUNTRY_ID NOT IN (

SELECT DISTINCT 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 E.*

, D.DEPARTMENT_NAME

FROM EMPLOYEES E

, DEPARTMENTS D

WHERE E.EMPLOYEE_ID = D.MANAGER_ID

;

-- 33. 다른 사원의 상사인 사원의 모든 정보를 조회하라.

SELECT DISTINCT MANAGER.*

FROM EMPLOYEES MANAGER

, EMPLOYEES E

WHERE MANAGER.EMPLOYEE_ID = E.MANAGER_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 E.JOB_ID = J.JOB_ID

AND D.LOCATION_ID = L.LOCATION_ID

AND L.COUNTRY_ID = C.COUNTRY_ID

AND C.REGION_ID = R.REGION_ID

AND FIRST_NAME LIKE 'C%s'

;

-- 35. 직무를 전환한 이력이 없는 사원의 모든 정보를 조회하라.

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID NOT IN (

SELECT EMPLOYEE_ID

FROM JOB_HISTORY

)

;

-- 36. 150번 사원보다 늦게 입사한 사원 중 150번 사원보다 더 많은 연봉을 받는 사원을 조회하라.

SELECT E.*

FROM EMPLOYEES E

, (

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID = 150

) ID_150

WHERE E.HIRE_DATE > ID_150.HIRE_DATE

AND E.SALARY > ID_150.SALARY

;

-- 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번 사원의 모든 상사들의

-- 이름(FIRST_NAME)과 부서명을 조회하라.

SELECT LEVEL

, E.FIRST_NAME

, D.DEPARTMENT_NAME

, E.EMPLOYEE_ID

, E.MANAGER_ID

FROM EMPLOYEES E

, DEPARTMENTS D

WHERE D.DEPARTMENT_ID = E.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(L.LOCATION_ID) LO_COUNT

FROM EMPLOYEES E

, DEPARTMENTS D

, LOCATIONS L

WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

AND L.LOCATION_ID = D.LOCATION_ID

GROUP BY L.CITY

ORDER BY LO_COUNT DESC

) A

WHERE ROWNUM = 1

;

SELECT L.LOCATION_ID

, COUNT(L.LOCATION_ID) LO_COUNT

FROM EMPLOYEES E

, DEPARTMENTS D

, LOCATIONS L

WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

AND L.LOCATION_ID = D.LOCATION_ID

GROUP BY L.LOCATION_ID

ORDER BY LO_COUNT DESC

;

-- 41. 직무의 종류가 가장 많은 부서의 이름을 조회하라.

SELECT DEPARTMENT_NAME

FROM (

SELECT DEPARTMENT_NAME

, COUNT( DISTINCT JOB_ID) JO

FROM DEPARTMENTS D

, EMPLOYEES E

WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

GROUP BY DEPARTMENT_NAME

ORDER BY JO DESC

)

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

GROUP BY D.DEPARTMENT_NAME

, E.JOB_ID

)

GROUP BY DEPARTMENT_NAME

ORDER BY CNT DESC

)

WHERE ROWNUM = 1

;

-- 42. 담당 직무의 최대 연봉을 받고 있는 사원들의 모든 정보를 조회하라.

SELECT *

FROM EMPLOYEES E

, JOBS J

WHERE E.JOB_ID = J.JOB_ID

AND J.MAX_SALARY = E.SALARY

;

SELECT *

FROM EMPLOYEES E

, (

SELECT JOB_ID

, MAX(SALARY) MAX_SAL

FROM EMPLOYEES

GROUP BY JOB_ID

) M_E

WHERE E.JOB_ID = M_E.JOB_ID

AND E.SALARY = M_E.MAX_SAL

;

-- 43. 담당 직무의 최소 연봉을 받고 있는 사원들의 모든 정보를 조회하라.

SELECT *

FROM EMPLOYEES E

, JOBS J

WHERE E.JOB_ID = J.JOB_ID

AND E.SALARY = J.MIN_SALARY

;

-- 44. 부서와 직무별 최대연봉, 최소연봉, 사원수를 조회하라.

SELECT D.DEPARTMENT_ID

,J.JOB_ID

,MAX(SALARY)

,MIN(SALARY)

,COUNT(1)

FROM DEPARTMENTS D

,EMPLOYEES E

,JOBS J

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

AND J.JOB_ID = E.JOB_ID

GROUP BY D.DEPARTMENT_ID

,J.JOB_ID

;

-- 45. 사원수가 가장 많은 도시에서 근무하는 모든 사원들의 연봉 총합을 조회하라.

SELECT L.CITY

, SUM(SALARY) SUM_SALARY

FROM EMPLOYEES E

, DEPARTMENTS D

, LOCATIONS L

, (

SELECT *

FROM (

SELECT L.CITY

, COUNT(1) CNT

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 CNT DESC

)

WHERE ROWNUM = 1

) L_COUNT

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

AND L.LOCATION_ID = D.LOCATION_ID

AND L.CITY = L_COUNT.CITY

GROUP BY L.CITY

;

-- 46. 사원수가 가장 많은 도시에서 근무하는 모든 사원들의 부서별 및 직무별 평균 연봉을 조회하라.

-- 47. 입사일이 가장 오래된 사원을 조회하라.

-- 48. 입사일이 가장 최근인 사원을 조회하라.

-- 49. 가장 최근에 입사한 사원과 가장 오래전에 입사한 사원의 일차를 계산해 조회하라.

-- 50. 가장 최근에 입사한 사원과 가장 오래전에 입사한 사원의 시간차를 계산해 조회하라.