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
;