[SQL][TIL] Single-row Subquery, Multiple-row Subquery

Q_ 부서별 급여 평균을 계산했을 때, 회사 전체 평균 급여보다 적은 부서는?

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) < (select avg(sal)
                        from emp);

 

 

▶ IN 연산자

select employee_id, last_name, job_id, salary
from employees
where salary in (select salary
                   from employees
                  where job_id = 'IT_PROG');

       

 

---

▶ ANY 연산자

select employee_id, last_name, job_id, salary
from employees
where salary > any (select salary
                   from employees
                  where job_id = 'IT_PROG')
                  order by 1;

select employee_id, last_name, job_id, salary
from employees
where salary >  (select min(salary)
                   from employees
                  where job_id = 'IT_PROG');

 

 

---

▶ ALL 연산자

select employee_id, last_name, job_id, salary
from employees
where salary> all (select salary
                   from employees
                  where job_id = 'IT_PROG')
order by 4;

select employee_id, last_name, job_id, salary
from employees
where salary > 9000
and salary > 6000
and salary > 4800
and salary > 4200
order by 4;

                  

select employee_id, last_name, job_id, salary
from employees
where salary > (select max(salary)
                   from employees
                  where job_id = 'IT_PROG')
order by 4;