서브쿼리
Lex보다 많이 받는 사원 구하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT
SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = ‘Lex’);
SELECT A.FIRST_NAME, B.SALARY
FROM EMPLOYEES A,
(SELECT
SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = ‘Lex’) B
WHERE A.SALARY > B.SALARY;
– 조인으로도 처리 가능
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEES );
–SINGLE LOW SUB QUERY
– 문_ JOB_ID가 ‘IT_PROG’인 직원이 받는 월급보다 많이 받고 있는 직원의 이름과 월급
SELECT FIRST_NAME,
SALARY
FROM EMPLOYEES
WHERE SALARY > ALL (SELECT SALARY
FROM EMPLOYEES
WHERE JOB_ID = ‘IT_PROG’)
– MULTI ROW SUB QUERY
–문) 각 부서별로 최고 월급을 받는 직원의 이름과 직원의 이름과 월급 출력
WHERE을 활용
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE (SALARY,DEPARTMENT_ID) IN (
SELECT MAX(SALARY),DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
);
FROM을 활용
SELECT A.DEPARTMENT_ID, FIRST_NAME, SALARY
FROM EMPLOYEES A,
( SELECT DEPARTMENT_ID,
MAX(SALARY) AS MAX_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
) B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND A.SALARY = B.MAX_SALARY;
CORELATED 활용
SELECT DEPARTMENT_ID, FIRST_NAME, SALARY
FROM EMPLOYEES A
WHERE SALARY = ( SELECT MAX(SALARY)
FROM EMPLOYEES
WHERE A.DEPARTMENT_ID = DEPARTMENT_ID);
– CORELATED SUBQUERY
자기 부서의 평균월급 보다 적은 월급을 받는 사원을 구하라