DataBase/oracle
oracle subQuery
HUCHUCHU
2020. 3. 19. 22:47

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
/* 서브쿼리 */
select round(avg(salary)) from employees ;
select round(avg(salary)) from employees
where salary < round(avg(salary)); -- 에러, where절에서는 집계함수 사용불가
-- 서브쿼리를 사용하여 평균 급여보다 적은 급여를 받고있는 사람 출력
select employee_id, first_name, last_name, salary from employees
where salary < (select round( avg(salary)) from employees );
-- null 값인 로케이션 아이디값으로 deparatment 정보 가져오기
select * from departments where location_id in (
select location_id from locations where state_province is null );
select * from departments where location_id in (
select location_id from locations where country_id = 'US');
/* any, all */
-- any 하나조건만 만족하면 됨, all 조건들을 다 만족하면됨
select department_id, employee_id, salary
from employees
where salary > (select salary from employees
where department_id = 20);
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5text-decoration:none">Colored by Color Scripter
|