sql중급 / subquery ( select절)

ㅁ복습

1. select 문의 6가지절 (select, from, where, group by, having, order by)

2. 함수의 종류 3가지 (단일행, 복수행, 데이터분석함수)

3. 조인문(오라클 조인문법, 1999 ANSI 조인문법)

- 오라클 조인문법 : equi join, non equi join, outer join, self join

- 1999 ANSI 조인문법 : on절을 사용한 조인, using절을 사용한 조인, natural join, left/right/full outer join, cross join

4. 집합연산자 4가지 (union all, union, intersect, minus)

5. 서브쿼리의 종류 3가지 (단일 행 서브쿼리, 다중 행 서브쿼리, 다중 컬럼 서브쿼리) 

 

ㅁ 서브쿼리를 사용할 수 있는 절 

select 서브쿼리 가능 (scalar subquery)

from 서브쿼리 가능 (in line view)

where 서브쿼리 가능

group by  불가

having 서브쿼리 가능

order by 서브쿼리 가능 (scalar subquery)

 

 

077 서브 쿼리 사용하기 7(SELECT절의 서브 쿼리)

scalar subquery 확장된 서브쿼리라고 불림

-- 사원 테이블의 최대월급을 출력하시오
select max(sal)
 from emp
 
-- 사원이름, 월급, 사원테이블의 최대월급을 출력하시오
select ename, sal, ( select max(sal)
                       from emp )
 from emp;

 

문제370. 사원이름, 월급, 사원테이블의 평균월급을 출력하시오

-- 튜닝전 
select ename, sal, ( select round(avg(sal))
                       from emp )
 from emp;
 -- emp 테이블을 두 번 select 함 
 
 --튜닝 후 : emp테이블을 한 번만 select
 select ename, sal, avg(sal) over ()
 from emp;
-- avg(sal) over ()라고 작성하면 사원테이블 전체의 평균월급이 출력됨 = over ()를 통해  함수가 확장된 것

문제371. 사원이름, 월급, 사원 테이블 전체의 최대월급, 최소월급, 토탈월급을 출력하시오

select ename, sal, max(sal) over () as max,
                   min(sal) over () as min,
                   sum(sal) over () as sum
 from emp;

/* sql튜닝 전후 성능 확인하는 법 (dos창)
set autot on 입력

db block gets
consistent gets
physical reads 
의 토탈값이 작아야 성능이 좋은 것 */

문제372. 부서번호가 20번인 사원들의 토탈월급을 출력하시오

select sum(sal)
 from emp
 where deptno = 20;

문제373. 사원이름, 월급, 부서번호가 20번인 사원들의 토탈월급을 출력하시오

select ename, sal, ( select sum(sal) 
                     from emp
                     where deptno = 20)
 from emp;
 
 /* select절의 서브쿼리로만 작성할 수 있는 sql임.
 서브쿼리의 where절 때문에 데이터분석함수를 써서 불러올 수 없음 */

문제374. 사원이름, 월급, 부서번호가 20번인 사원들의 토탈월급, 부서번호가 20번인 사원들의 최대월급, 부서번호가 20번인 사원들의 최소월급을 출력하시오

select ename, sal, ( select sum(sal) 
                     from emp
                     where deptno = 20) 토탈,
                     ( select max(sal) 
                     from emp
                     where deptno = 20) 최대,
                     ( select min(sal) 
                     from emp
                     where deptno = 20) 최소
 from emp;
 -- SQL의 성능이 느려지는 대표적인 예시 : 테이블이 4번이나 select 됨

문제375. 부서번호가 20번인 사원들의 토탈월급, 최대월급, 최소월급을 출력하시오

select sum(sal), max(sal), min(sal)
 from emp
 where deptno = 20;

문제376. 사원이름, 월급, 부서번호가 20번인 사원들의 토탈월급, 부서번호가 20번인 사원들의 최대월급, 부서번호가 20번인 사원들의 최소월급을 출력하는데 문제374번의SQL을 이용해서 작성하시오.

>튜닝전 SQL은 emp테이블을 4번이나 select하는 문장인데, 튜닝후 SQL은 emp테이블을 2번만 select하는 문장임

select ename, sal, (select sum(sal), max(sal), min(sal)
                     from emp
                     where deptno = 20)
 from emp;
/* 에러
 select절의 서브쿼리의 특징? 값을 하나만 출력할 수 있음 */
 
select ename, sal, (select sum(sal)|| max(sal)|| min(sal)
                     from emp
                     where deptno = 20)
 from emp;
/* 위와같이 연결 연산자로 연결을 하면 값이 하나가 되어 오류없이 출력할 수 있음
 하지만, 토탈값, 최대값, 최소값을 항상 일정하게 잘라내야하는 문제가 생김 */

select ename, sal,(select rpad(sum(sal),10,' ')|| rpad(max(sal),10,' ')||rpad(min(sal),10,' ')
                     from emp
                     where deptno = 20) as totalsal
 from emp;
-- lpad/rpad를 사용해서 일정하게 공백을 만들어줌

select ename,sal, substr(totalsal,1,10) as sum, substr(totalsal,11,10) as max ,substr(totalsal,21,10) as min                                                    
from ( select ename, sal,(select rpad(sum(sal),10,' ')|| rpad(max(sal),10,' ')||rpad(min(sal),10,' ')
                     from emp
                     where deptno = 20) as totalsal 
       from emp);

-- 다른 사람이 쓴 코드
select ename, sal, s, m1, m2
 from emp natural join (select sum(sal) as s, max(sal) as m1, min(sal) as m2
                           from emp
                           where deptno = 20) ;
/* emp테이블이 14건이고 natural join 다음에 나오는 인라인뷰의 select 문장의 결과가 1건이므로
조인조건없이 조인해서 14x1건이 출력됨 */


--
select ename, sal, substr(집계,1,10) 토탈월급, substr(집계,11,10) 최대월급, substr(집계,21,10) 최소월급
    from (
          select ename, sal, ( select sum(sal) || lpad(max(sal),10,' ') || lpad(min(sal),10,' ')
                                 from emp
                                 where deptno = 20 ) 집계
            from emp
          );
          
--
select ename, sal, sum(decode(deptno,20,sal,null)) over() 토탈월급,
                   max(decode(deptno,20,sal,null)) over() 최대월급,
                   min(decode(deptno,20,sal,null)) over() 최소월급
 from emp;

문제378. 우리반 테이블에서 이름, 나이, 통신사가 kt인 학생들의 토탈나이, 통신사가 kt인 학생들의 최대나이, 통신사가 kt인 학생들의 최소나이를 출력하시오

--natural join 사용
select ename, age, sum, max, min
 from emp14, (select sum(age) as sum, max(age) as max, min(age) as min
               from emp14
               where telecom = 'kt');

 

문제379. (복습문제) 직업이 SALESMAN이고, 월급이 1000~2000사이인 사원들의 이름, 월급, 직업을 출력하는데 월급이 높은 사원부터 출력하시오

select ename, sal, job
 from emp
 where job = 'SALESMAN' and sal between 1000 and 2000
 order by sal desc;

문제380. (복습문제) 직업을 출력하고 decode를 이용해서 직업이 PRESIDENT인 사원은 null, 나머지는 월급이 출력되게 하세요

select job, decode(job, 'PRESIDENT',null,sal)
 from emp;

문제381. (복습문제) 위의 결과를 다시 출력하는데 출력되는 월급이 높은 사원부터 출력하시오

select job, decode(job, 'PRESIDENT',null,sal)
 from emp
 order by 2 desc;
 
 /* 950이 맨 위로 출력되는 이유는 decode함수의 버그 때문 
 3번째 인자값의 데이터 타입에 의해 네번째 인자갑의 데이터타입이 결정됨 */
 
 select job, decode(job, 'PRESIDENT',to_number(null),sal)
 from emp
 order by 2 desc nulls last;