sub query
Abel보다 급여 많이 받는 사람 구하기
ABel의 급여
SELECT salary FROM employees WHERE last_name='Abel';
SALARY ------ 11000
Abel의 급여인 11000보다 큰사람
SELECT last_name, salary FROM employees WHERE salary>11000;
LAST_NAME SALARY --------- ------ King 24000 Kochhar 17000 De_Haan 17000 Hartstein 13000 Higgins 12000
둘을 합친거
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE last_name = 'Abel');
LAST_NAME SALARY --------- ------ King 24000 Kochhar 17000 De_Haan 17000 Hartstein 13000 Higgins 12000
이런 경우 sub query안의 데이터가 복수가 나올 경우를 주의해.
각 부서에서 최소급여를 받는 사원을 구해보자.
SELECT department_id, MIN(salary) FROM employees GROUP BY department_id;
DEPARTMENT_ID MIN(SALARY)
------------- -----------
NULL 7000
90 17000
20 6000
110 8300
50 2500
80 8600
60 4200
10 4400
SELECT last_name, salary
FROM employees
WHERE salary = ( SELECT MIN(salary)
FROM employees
GROUP BY department_id );
ORA-01427: single-row subquery returns more than one row
>IN
SELECT last_name FROM employees WHERE job_id = 'IT_PROG' or job_id = 'AD_VP'; --IN사용해서 위와 같게 SELECT last_name FROM employees WHERE job_id IN ( 'IT_PROG', 'AD_VP');
LAST_NAME --------- Kochhar De_Haan Hunold Ernst Lorentz
이걸 사용하면 되겠군.
SELECT department_id, last_name, salary
FROM employees
WHERE salary IN ( SELECT MIN(salary)
FROM employees
GROUP BY department_id )
ORDER BY 1;
DEPARTMENT_ID LAST_NAME SALARY
------------- --------- ------
10 Whalen 4400
20 Fay 6000
50 Vargas 2500
60 Lorentz 4200
60 Ernst 6000 --;
80 Taylor 8600
90 De_Haan 17000
90 Kochhar 17000
110 Gietz 8300
NULL Grant 7000
정확히 원하는 바는 아니지..
굳이 구하자면 이정도 될까..
SELECT e.department_id, e.last_name, e.salary FROM employees e JOIN ( SELECT department_id, MIN(salary) as sal FROM employees GROUP BY department_id ) d ON e.department_id = d.department_id AND e.salary = d.sal ORDER BY 1;
DEPARTMENT_ID LAST_NAME SALARY
------------- --------- ------
10 Whalen 4400
20 Fay 6000
50 Vargas 2500
60 Lorentz 4200
80 Taylor 8600
90 Kochhar 17000
90 De_Haan 17000
110 Gietz 8300
암튼 IN에 대해 알아보려 한거였고..
>ALL, ANY
SELECT last_name, salary
FROM employees
WHERE salary > ALL ( SELECT min(salary)
FROM employees
GROUP BY department_id);
LAST_NAME SALARY --------- ------ King 24000
sub query중 최대값인 17000보다 큰걸 찾은거지
ex)IT_PROG보다 많이 받는 사람
ALL
SELECT last_name, salary
FROM employees
WHERE salary > ALL ( SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
LAST_NAME SALARY --------- ------ Zlotkey 10500 Abel 11000 Higgins 12000 Hartstein 13000 Kochhar 17000 De_Haan 17000 King 24000
IT_PROG 중 최고액인 9000보다 큰사람이지..
ANY : or 조건으로 들어가..
SELECT last_name, salary
FROM employees
WHERE salary > ANY ( SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
LAST_NAME SALARY --------- ------ King 24000 Kochhar 17000 De_Haan 17000 Hartstein 13000 Higgins 12000 Abel 11000 Zlotkey 10500 Hunold 9000 Taylor 8600 Gietz 8300 Grant 7000 Ernst 6000 Fay 6000 Mourgos 5800 Whalen 4400
IT_PROG 중 최저액인 4200보다 큰사람이지..
연습문제 몇개.
--ex1. King의 직속부하
SELECT last_name
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE last_name = 'King');
--ex2. 전체사원의 평균 급여보다 많은 급여를 받는 사람들의 last_name, salary
SELECT AVG(salary) FROM employees;
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
--ex3. 이름에 u가 포함된 사원과 같은 부서에서 일하는 사원의 last_name, department_id
SELECT last_name, salary
FROM employees
WHERE department_id IN (
SELECT department_id FROM employees
WHERE last_name like '%u%');
.
.
.
.