SQL Sub Query 단일행 where 절

= , ! =, <,>,

≥, ≤ 가능

서브쿼리에서 검색한 값 (1가지) —→ 메인쿼리에서 받아서 실행 (이과정이 단일행이다)

서브쿼리가 먼저 실행 된뒤, 메인 쿼리가 실행된다.

예제 : 존스의 월급보다(서브쿼리) 더 많은 월급을 받는 사원들의이름,월급 출력

꼭서브쿼리문을 이용하지 않아도 쿼리를 두번 실행하여 결과값을 출력 할 수 있다.

과정 : 

1. 존스의 월급을 먼저 구한다.→ 나오는 월급(2975)을 보고

2. select sal
   from emp
   where ename = 'JONES';

3. jones 의 월급이 2975 이다. 이 값을 가지고 한번 더 쿼리를 날린다.

4. select ename, sal 
   from emp 
   where sal > 2975;

---

BUT 한번에 결과를 보기 위해 서브 쿼리를 이용한다.

---

select ename, sal
from emp
where sal > (select sal
             from emp
             where ename = 'JONES')
             ;

오라클이 서브 쿼리 먼저 실행 한 후에 메인 쿼리를 실행한다.

---

문제 : SCOTT 과 같은 월급 받는 사원들의 이름, 월급 출력 (SCOTT제외)

 

select ename, sal 
from emp 
where sal = (select sal 
             from emp 
             where ename = 'SCOTT')
      and ename != 'SCOTT';

 

괄호 안만 서브쿼리 이다.

---

문제 : 스미스와 같은 직업을 갖는 사원들의 이름,직업 출력 (스미스 제외)

select ename, job
from emp
where job =( select job
              from emp
              where ename = 'SMITH')
  and ename != 'SMITH';

문제  : 엘렌보다 늦게 입사한 사원들의 이름, 입사일 출력

select ename, hiredate 
from emp 
where hiredate > (select hiredate 
                 from emp 
                 where ename = 'ALLEN');

문제 : 최대 월급을 받는 사원의 이름, 월급 출력

select ename, sal 
from emp
where sal = (select max(sal) 
             from emp);

 

먼저 최대월급을 구하고, 메인쿼리의 검색조건에 넣어준다.

---

문제 : 서울시 물가 데이터를 조회 (PRICE) 서울시에서 가장 비싼 품목의 품목명, 가격, 파는곳 출력

가장 비싼곳 이라고 특정 지어졌지 때문에 먼저 이값을 구하고, 그 값과 같은 컬럼의  품목명, 가격, 파는곳을 구한다. 

select a_name, a_price, m_name 
from price 
where a_price = (select max(a_price) 
                 from price);

문제  : 우리나라 대학중에 대학등록금이 가장 비싼 학교는 어디인가?

select 대학명 , 평균등록금 
from univ 
where 평균등록금 = (select max(평균등록금) 
                 from univ);

문제  : 대학명, 평균등록금, 순위 출력 , 순위는 평균등록금이 높은순서에 대한 순위, 순위는 1~10 위 출력 ( sub query , in line view ) 

select * 
from( select 대학명, to_char(평균등록금,'999,999,999') as 평균등록금,
      rank() over(order by 평균등록금 desc) as 순위
      from univ)
where 순위 between 1 and 10;

문제 : 절도가 가장 많이 발생하는 요일과 순위를 출력하시세요 (crime_day)

1. 먼저 unpivot 문 생성

create or replace view crime_day2
as 
select * 
from crime_day 
unpivot ( 건수 for 요일 
          in (일, 월, 화, 수, 목, 금, 토)
        );

 

→ 요일에 대한 순위를 구해야 하므로 요일이 데이터로 검색 될 수 있도록 컬럼에서

→데이터로 변환시켜주는 unpivot 문 작성한다.

→ 요일에 대한 건수 이므로 문법은 건수 for 요일 in ( 컬럼명인 요일작성)

---

1. sub query (in line view) 이용한 query 작성

select 요일, 순위
from (select 범죄분류, 요일, rank() over(partition by 범죄분류
                                      order by 건수 desc) as 순위
      from crime_day2 ) 
where 범죄분류 = '절도';

 

→ 먼저 결과를 도출해 내는데 필요한 컬럼명을 작성한다.

절도를 뽑아낼 범죄분류, 요일, 순위 (순위는 범죄분류별로 건수가 높은 순으로 정렬해야한다) 해당 데이터는 아까 만든 view 테이블에서 가져왔으니 from 절에 crime_day2 이다.

→ 범죄분류와, 요일, 범죄분류별 건수가 순위별로 도출 되었는데

→ 여기서 요일과, 순위만 출력하면서 절도만 추출해야한다.

---

문제  : 우리반에서 가장 나이가 어린 학생의 이름과 나이와 통신사를 출력

select ename, age, telecom 
from emp14 
where age = (select min(age) 
              from emp14);

문제 :  직업이 세일즈맨인 사원들의 최대월급보다 더많은 월급을 받는 사원들의 이름과 월급과 직업을 사원테이블에서 출력

select ename, sal, job
from emp 
where SAL > (select max(sal) 
             from emp 
             where JOB = 'SALESMAN');

 

직업이 세일즈맨인 사원의 최대월급 1개의 행이 , 메인쿼리로 리턴되는 단일행 서브쿼리문이다.