sql중급 / subquery(단일 행)

071 서브 쿼리 사용하기 1(단일행 서브쿼리)

 

ㅁSQL의 종류

1. Query : select문의 6가지 절, 함수, 조인, 집합연산자, 서브쿼리

2. DML : insert, update, delete, merge

3. DDL : create, alter, drop, truncate, rename

4. DCL : grant, revoke

5. TCL : commit, rollback, savepoint

 

ㅁ서브쿼리의 종류 3가지

1. 단일행 서브쿼리

2. 다중행 서브쿼리

3. 다중컬럼 서브쿼리

 

ㅁ서브쿼리(subquery)를 알아야 하는 이유 > 다음의 질문에 답하기 위함

" Jones의 월급보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오"

-- 쿼리를 2번 쓸 경우
select sal
 from emp
 where ename = 'JONES';
 
select ename, sal
 from emp
 where sal > 2975;
 
 
-- 서브쿼리를 이용해서 한번에 결과를 출력하는 방법
select ename, sal
 from emp
 where sal > ( select sal
               from emp
               where ename = 'JONES');
 -- 위의 sql에서 괄호안에 해당하는 부분이 subquery, 나머지는 전부 main query

 

문제326. SCOTT과 같은 월급을 받는 사원들의 이름, 월급을 출력하시오

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

문제327. 위의 결과에서 SCOTT은 제외하고 출력하시오

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

문제328. SMITH와 같은 직업을 갖는 사원들의 이름과 직업을 출력하는데 SMITH는 제외하고 출력하시오

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

문제329. ALLEN보다 늦게 입사한 사원들의 이름과 입사일을 출력하시오

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

문데330. 최대월급을 받는 사원의 이름과 월급을 출력하시오

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

문제331. 서울시 물가 데이터에서 서울시에서 가장 비싼 품목명과 가격, 파는 곳을 출력하시오

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

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

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

문제333. 대학명, 평균등록금, 순위를 출력하는데 순위는 평균등록금이 높은순으로 1~10위까지만 출력하시오

select *
from (
       select 대학명, 평균등록금, rank() over (order by 평균등록금 desc) 순위
        from univ
      )
 where 순위 between 1 and 10;
 
 
select 대학명, 평균등록금, rank() over (order by 평균등록금 desc) 순위
 from univ
 order by 순위 asc fetch first 10 rows only;

 

문제334. (오늘의 마지막 문제) 절도가 가장 많이 발생하는 요일과 순위를 출력하시오 

테이블명 : crime_day

공공데이터포털에서 '범죄발생요일'로 다운받은 데이터로 sql을 작성하시오 > unpivot으로 

-- 처음 쓴 코드
select 범죄분류, 요일, 건수, rank() over (order by 건수 desc) 순위
from (
       select *
        from crime_day
        unpivot ( 건수 for 요일 in (일, 월, 화, 수, 목, 금, 토) )
     )
 where 범죄분류 = '절도';
/* 이렇게 작성할 경우 순위를 나열하여 출력할 순 있지만, "가장 많이 발생하는 순위" 하나만 출력할 수 없음 
   > where절에 순위 별칭을 쓸 수 없으므로 또 다시 서브쿼리를 작성해야함
   > order by 순위 fetch first 1 rows only; 를 추가로 입력하면 하나만 출력할 수 있긴 함 */


-- 다시 쓴 코드
select *
from (
       select 요일, rank() over (order by 건수 desc) 순위
        from crime_day 
        unpivot ( 건수 for 요일 in (일, 월, 화, 수, 목, 금, 토) )
        where 범죄분류 = '절도'
     )
where 순위 = 1;