SQL - SUBQUERY

select 문, insert문 , update문, delete문등에서 사용되는 select문
select 문의 select절, from절, where절 등에서 서브쿼리를 사용할수 있다.
서브쿼리는 단독으로 사용 가능하다
select 절에서 사용되는 경우 한행의 결과를 출력하는 문만 가능
where 절에서 사용되는 경우 결과가 하나의 컬럼만 가능하며
in이나 any 절에서는 여러 행의 결과가 가능하지만
=,>,< 등의 연산에서는 하나의 행만 가능

다양한 서브쿼리 사용 예시들

select name, (select sal from emp where city='서울') from emp; -- 에러 : 서브 쿼리가 여러줄
            
select name, (select avg(sal) from emp) from emp; -- 에러 : 서브 쿼리가 여러줄
            
select name, sal - (select avg(sal) from emp) 차이 from emp; -- 에러 : 서브 쿼리가 여러줄
            
select name, sal
from emp
where sal >= (select sal from emp where city='인천');
-- 에러
                
select name, sal, city
from emp
where sal in (select sal from emp where city='인천');
-- 인천 사람들 급여하고 급여가 똑같은 애들 출력
                
-- 서울 사람이 아닌 사람 중 서울 평균(sal)보다 많이 받는사람 (name,sal,city)
select name, sal, city
from emp
where city != '서울' and 
sal > (select avg(sal) from emp where city = '서울');

-- name, sal, 최대급여와의 차이
select name, sal, (select max(sal) from emp) - sal as 차이
from emp; -- 집계 함수는 일반 컬럼과 같이 사용 불가
        
-- 여자 중 sal을 가장 많이 받는사람(name,rrn,sal)
select name, rrn, sal
from emp
where mod(substr(rrn, 8, 1),2) = 0 and
sal = (select max(sal) from emp where mod(substr(rrn, 8, 1),2) = 0);
           
-- sal+bonus가 가장 많은 사람 (name,sal,bonus,sal+bonus)
select name, sal, bonus, sal+bonus pay
from emp
where sal+bonus = (select max(sal+bonus) from emp);
            
-- 부서별 인원수가 가장 많은 부서명과 인원 수
select dept, count(*)
from emp
group by dept;
            
select max(count(*))
from emp
group by dept;
            
select dept, count(*)
from emp
group by dept
having count(*) = (select max(count(*)) from emp group by dept );
            
-- 입사 인원수가 가장 많은 년도 및 인원 수 출력
-- 입사년도별 인원수
select to_char(hiredate, 'YYYY'),count(*)
from emp
group by to_char(hiredate, 'YYYY')
                
-- 입사년도별 인원의 최대값
select max(count(*))
from emp
group by to_char(hiredate, 'YYYY')
                
-- 가장 많은 년도 인원수
select to_char(hiredate, 'YYYY'),count(*)
from emp
group by to_char(hiredate, 'YYYY')
having count(*) = (select max(count(*)) from emp group by to_char(hiredate, 'YYYY'));
                
-- 다른 방법
with tb as (
select to_char(hiredate, 'YYYY')년도 ,count(*) 인원수
from emp
group by to_char(hiredate, 'YYYY')
)
select 년도,인원수
from tb
where 인원수 = ( select max(인원수) from tb );
            
--생일이 동일한 사람이 2명 이상인 경우
-- name, birth
select name, to_date(substr(rrn, 1, 6), 'RRMMDD') birth
from emp
order by to_char(birth, 'MMDD')
            
select substr(rrn, 3, 4), count(*)
from emp
group by substr(rrn, 3, 4);
            
select substr(rrn, 3, 4), count(*)
from emp
group by substr(rrn, 3, 4)
having count(*) >= 2;
            
select substr(rrn, 3, 4), count(*)
from emp
group by substr(rrn, 3, 4)
having count(*) >= 2;
            
select name, to_date(substr(rrn, 1, 6), 'rrmmdd') birth
from emp
where substr(rrn, 3, 4) in (
                select substr(rrn, 3, 4)
                from emp
                group by substr(rrn, 3, 4)
                having count(*) >= 2
            )
order by to_char(birth, 'MMDD');