SQL - JOIN, SUB QUERY, INLINE TABLE, IN VIEW

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (

SELECT DEPARTMENT_ID

FROM EMPLOYEES

WHERE FIRST_NAME = 'Steven'

)

AND FIRST_NAME != 'Steven' -- 여러개일때는 IN 사용

;

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, D.DEPARTMENT_NAME

FROM EMPLOYEES E,

DEPARTMENTS D

WHERE SALARY > (

SELECT ROUND( AVG(SALARY), 0 )

FROM EMPLOYEES

)

ORDER BY SALARY

;

SELECT E.*

FROM DEPARTMENTS D

, LOCATIONS L

, EMPLOYEES E

WHERE L.CITY = 'Seattle'

AND E.DEPARTMENT_ID = D.DEPARTMENT_ID

AND D.LOCATION_ID = L.LOCATION_ID

;

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (

SELECT DEPARTMENT_ID

FROM DEPARTMENTS

WHERE LOCATION_ID = (

SELECT LOCATION_ID

FROM LOCATIONS

WHERE CITY = 'Seattle'

)

)

;

-- 평균 급여가 많은 부서를 조회

-- 집계함수 있으면 GROUP BY 사용

-- INLINE-VIEW - TABLE의 결과를 TABLE로 쓴다.

-- INLINE-TABLE - TABLE에 그대로 SUB QUERY쓰면 INLINE TABLE

SELECT * -- MAX(SALARY)

FROM (

SELECT *

FROM ( -- INLINE VIEW

SELECT DEPARTMENT_ID

, AVG(SALARY) AVG_SALARY

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

)

ORDER BY AVG_SALARY DESC

)

WHERE ROWNUM = 1 -- 1번 불러오기, ORDER BY가 INLINE VIEW에 있어서 ROWNUM 사용 가능

;

-- PAGENATION

SELECT D.DEPARTMENT_ID, AVG(E.SALARY)

FROM EMPLOYEES E,

DEPARTMENTS D

WHERE SALARY < (

SELECT AVG(SALARY)

FROM EMPLOYEES

)

GROUP BY DEPARTMENT_ID

;

-- 부서별 평균 연봉이 회사 전체 평균연봉보다

-- 적은 부서명과 평균연봉을 조회하라.

SELECT D.DEPARTMENT_NAME

, AVG_D.AVG_SALARY

FROM (

SELECT DEPARTMENT_ID

, AVG(SALARY) AVG_SALARY

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

) AVG_D

, DEPARTMENTS D

WHERE AVG_D.DEPARTMENT_ID = D.DEPARTMENT_ID

AND AVG_SALARY < (

SELECT AVG(SALARY)

FROM EMPLOYEES

)

;

-- 부서별 평균 연봉이 회사 전체 평균연봉보다

-- 적은 부서명과 평균연봉을 조회하라.

SELECT AVG_D.DEPARTMENT_NAME

, AVG_D.AVG_SALARY

FROM (

SELECT D.DEPARTMENT_NAME

, AVG(SALARY) AVG_SALARY

FROM EMPLOYEES E

, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP BY D.DEPARTMENT_NAME

) AVG_D

WHERE AVG_SALARY >= (

SELECT AVG(SALARY)

FROM EMPLOYEES

)

;

-- 부서별 평균 연봉이 회사 전체 평균연봉보다

-- 적은 부서명과 평균연봉을 조회하라.

SELECT D.DEPARTMENT_NAME

, AVG(SALARY) AVG_SALARY

FROM EMPLOYEES E

, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP BY D.DEPARTMENT_NAME

HAVING AVG(SALARY) < (

SELECT AVG(SALARY)

FROM EMPLOYEES

)

;

-- 직무별 평균 연봉이 회사 전체 평균 연봉과 크거나 같은

-- 직무를 가진 사원들의 모든 정보 출력

SELECT *

FROM EMPLOYEES E

, (

SELECT JOB_ID

, AVG(SALARY) AVG_SALARY

FROM EMPLOYEES

GROUP BY JOB_ID

HAVING AVG(SALARY) >= (

SELECT AVG(SALARY)

FROM EMPLOYEES

)

) JOB_AVG

WHERE JOB_AVG.JOB_ID = E.JOB_ID

;

-- 단 한명의 사원도 없는 도시명 구하기

SELECT CITY

FROM LOCATIONS

WHERE LOCATION_ID NOT IN (

SELECT DISTINCT LOCATION_ID -- 중복제거

FROM EMPLOYEES E

, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID -- JOIN

)

;

-- 같은 조건, SUB QUERY

SELECT CITY

FROM LOCATIONS

WHERE LOCATION_ID IN (

SELECT LOCATION_ID

FROM LOCATIONS

MINUS -- 아래조건을 뺀다

SELECT DISTINCT LOCATION_ID

FROM EMPLOYEES E

, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

)

;

-- 부서의 사원수가 5명 미만인 부서의 부서별 평균 연봉을 구해

-- 평균 연봉이 가장 높은 부서의 부서명을 조회

SELECT DEPARTMENT_NAME

FROM DEPARTMENTS D

, (

SELECT DEPARTMENT_ID , AVG(SALARY) AVG_SALARY

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING COUNT(DEPARTMENT_ID) < 5

ORDER BY AVG_SALARY DESC

) AVG_D

WHERE D.DEPARTMENT_ID = AVG_D.DEPARTMENT_ID

AND ROWNUM = 1

;

-- 동일조건

FROM ( SELECT D.DEPARTMENT_NAME , AVG(SALARY) AVG FROM EMPLOYEES E , DEPARTMENTS D WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID GROUP BY D.DEPARTMENT_NAME HAVING COUNT(1) < 5 ORDER BY AVG DESC ) WHERE ROWNUM = 1 ;