[Oracle SQL] SubQuery 서브쿼리

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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126

/*  서브쿼리      : Main Query에 반대되는 개념     : 메인쿼리를 구성하는 소단위 쿼리     : select / insert / delete / update 절에서 모두 사용가능     : 서브쿼리의 결과 집합을 메인 쿼리가 중간 결과값으로 사용     : 서브쿼리 자체는 일반 쿼리와 다를 바 없음  */    select round(avg(salary))   from employees;      select employee_id, first_name, last_name  from employees  where salary < 6462;      /* Where절에서는 집계함수 사용 불가 */    select employee_id, first_name, last_name  from employees  where salary < round(avg(salary));        /* select서브쿼리 문 */    select employee_id, first_name, last_name  from employees  where salary < (select round(avg(salary))                   from employees);        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' );       /* 월급이 가장 적은 사원 */   select emp.first_name, emp.last_name, job.job_title, emp.salary from employees emp, jobs job where emp.salary = (select min(salary) from employees)   and emp.job_id = job.job_id;       /* 월급이 가장 많은 사원 */   select emp.first_name, emp.last_name, job.job_title, emp.salary from employees emp, jobs job where emp.salary = ( select max(salary) from employees)   and emp.job_id = job.job_id;        /* 평균 급여보다 많이 받는 사원들의 명단 조회 */   select emp.first_name, emp.last_name, job.job_title from employees emp, jobs job where emp.salary > (select avg(salary) from employees)   and emp.job_id = job.job_id;       /* any, all */   select salary from employees where department_id = 20;       /* department_id = 20의 최소 연봉보다 많이 받는 사람 모두 출력 */   select employee_id, department_id, salary from employees  where salary > any ( select salary                      from employees                      where department_id = 20 );       select employee_id, department_id, salary from employees  where salary > ( select min(salary)                  from employees                  where department_id = 20 );       /* department_id = 20의 최대 연봉보다 많이 받는 사람 모두 출력 */   select employee_id, department_id, salary from employees  where salary > all ( select salary                      from employees                      where department_id = 20 );       select employee_id, department_id, salary from employees  where salary > ( select max(salary)                  from employees                  where department_id = 20 );       /* department_id = 20의 연봉과 동일하게 받는 사람 모두 출력 */   select employee_id, department_id, salary from employees  where salary in ( select salary                  from employees                  where department_id = 20 );

cs