15. 고급서브쿼리문(스칼라, 상호관련)

■고급 서브쿼리문

■ 17장. 고급 서브쿼리문(3권 228페이지)

*목차

1. 스칼라 서브쿼리

2. 상호관련 서브쿼리

3. exists 문

4. with 절

 

 

■ 스칼라 서브쿼리

옛날에 배웠음.

select, order by 문에서 서브쿼리 쓰는 거.

 

select-------스칼라 서브쿼리

 from-----inner line view 서브쿼리

 where

 group by-----여기만 서브쿼리 안됨.

having

order by-----스칼라 서브쿼리

 

 

 

 

 

문제503.

사원테이블의 평균월급을 뽑으시오

select round(avg(sal) )

from emp;

 

 

문제504.

사원번호, 이름, 월급, 사원 테이블의 평균월급을

출력하시오

select empno, ename, sal, round(avg(sal) over() )

from emp;

 

 

문제505.

위 결과를 데이터분석함수 이용하지 말고 수행하시오

select empno, ename, sal,

       (select avg(sal)

        from emp)평균월급

 from emp;

 

 

 

문제506.

사원번호, 이름, 월급, 사원 테이블의 토탈월급,

                           사원 테이블의 최대월급,

                           사원 테이블의 최소월급,

                           사원 테이블의 평균월급을

 출력하시오.

 

select empno, ename, sal,

(select sum(Sal) from emp)토탈월급,

(select max(sal) from emp)최대월급,

(select min(sal) from emp)최소월급,

(select round(avg(sal)) from emp)평균월급

from emp;

 

블럭수가 53개. 악성sql 이다.

 

근데 아래와 같이 써도 값이 나온다.

select sum(sal), max(sal), min(sal), round(avg(sal))

from emp;

 

 

그럼 506번처럼 하지 말고 다른방법으로

튜닝해보시오

select empno, ename, sal,

(select sum(sal), max(sal), min(sal), round(avg(sal))

from emp)

from emp;

안됨;;;;;;;;;;;;;;too many values 에러뜸.

>>>>>이게 스칼라 서브쿼리의 특징.

 

※스칼라 서브쿼리의 특징

 스칼라 서브쿼리는 하나의 값만 리턴한다.

 

 

연결연산자를 이용해서 출력하면

select empno, ename, sal,

(select sum(sal)|| max(sal)|| min(sal)|| round(avg(sal))

from emp)

from emp;

 

 

 

문제507.(점심시간 문제)

위의 SQL에 substr 을 사용해서

아래와 같이 결과가 출력되게 하시오!

힌트: inline view 이용

1. 일일이 손으로 세어서

2. length 이용해서

3. Rpad 이용해서

**함수별칭 지정 소문자로 하시오

 >>자꾸 에러뜸

 

              

 

1. 손으로 자리수 세어서.

 select e.empno, e.ename, e.sal,

       substr(a, 1, 5)토탈,

       substr(a, 6, 4)최대,

       substr(a, 10, 3)최소,

       substr(a, 13, 4)평균

  from

   (select sum(sal)||max(sal)||min(sal)||round(avg(sal)) as a

    from emp) ee, emp e;

 

 

2. length 써서.

select e.empno, e.ename, e.sal,

   substr(ee.p,1,su) as 토탈,

  substr(ee.p,su+1,ma) as 최대,

  substr(ee.p,su+ma+1,mi) as 최소,

  substr(ee.p,su+ma+mi+1) as 평균

   from (select sum(sal)||max(sal)||min(sal)||round(avg(sal)) as p,

              length(sum(sal)) as su,

          length(max(sal)) as ma,

          length(min(sal)) as mi,

          length(avg(sal)) as av

            from emp) ee, emp e;

 

 

 

 

 

화면 캡처: 2018-11-05 오후 12:05

 

3. rpad 써서.

select e.empno, e.ename, e.sal,

substr(전체토탈, 1, 10) as 토탈,

substr(전체토탈,11, 10) as 최대,

substr(전체토탈, 21, 10) as 최소,

substr(전체토탈, 31, 10 ) as 평균

from (select

              RPAD(sum(sal),10,'  ')||

              RPAD(max(sal),10,'  ')||

              RPAD(min(sal),10,'  ')||

              RPAD(round(avg(sal)),10,'  ') 전체토탈

                 from emp) EE, emp e;

 

 

 

서브만 돌려보면,

select RPAD(sum(sal),10,'  ')||

              RPAD(max(sal),10,'  ')||

              RPAD(min(sal),10,'  ')||

              RPAD(round(avg(sal)),10,'  ') as T

FROM EMP;

 

 

 

화면 캡처: 2018-11-05 오후 1:57

 

 

블락수가 한자리수로 훅 줄어듬.

책에도 나오는 중요한 튜닝 방법이라 함.

 

 

 

문제508.

직업이 SALESMAN 인 사원들의

이름, 월급, 직업, 직업이 SALESMAN 인 사원들의 최대월급,

                     직업이 SALESMAN 인 사원들의 최소월급,

                     직업이 SALESMAN 인 사원들의 토탈월급,

                     직업이 SALESMAN 인 사원들의 평균월급,

출력하시오.

(rpad 또는 length 로 하시오)

 

SELECT e.ENAME, e.SAL, e.JOB,

  SUBSTR(AA, 1, 10) 최대,

  SUBSTR(AA, 11, 10) 최소,

  SUBSTR(AA, 21, 10) 토탈,

  SUBSTR(AA, 31, 10) 평균

FROM ( SELECT RPAD(MAX(SAL), 10,' ' )||

             RPAD(MIN(SAL), 10,' ' )||

             RPAD(SUM(SAL), 10,' ' )||

             RPAD(ROUND(AVG(SAL)), 10,' ' ) aa

       FROM EMP

       WHERE JOB = 'SALESMAN') EE, emp e

 

이건 왜 안나오지?

>>>>알파벳 쪽 다시 쓰면 나옴. 이유모름;

대문자로 쓰면 에러뜸.

 SELECT e.ENAME, e.SAL, e.JOB,

            SUBSTR(t, 1, ms) 최대,

            SUBSTR(t, ms+1, mi) 최소,

           SUBSTR(t, ms+mi+1, ss) 토탈,

          SUBSTR(t, ms+mi+ss+1) 평균

 FROM ( SELECT

         MAX(SAL)||MIN(SAL)||SUM(SAL)||ROUND(AVG(SAL)) AS t,

               LENGTH(MAX(SAL)) ms,

               LENGTH(MIN(SAL)) mi,

              LENGTH(SUM(SAL)) ss,

              LENGTH(ROUND(AVG(SAL)) ) rs

              FROM EMP

         WHERE JOB = 'SALESMAN') AA, emp e;

 

 

 

■ 상호관련 서브쿼리

 "메인쿼리의 컬럼이 서브쿼리 안으로 들어가게 되는 SQL 을

상호관련 서브쿼리 라고 한다."

"메인쿼리 부터 실행된다"

*서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리이다.

 일반적으로 메인쿼리가 먼저 수행되어 얽혀진 데이터를

 서브쿼리에 조건이 맞는지 확인하고자 할 때 주로 사용된다.*

 

예:

 직업별 인원수가 4명 이상인 직업인 사원들의 이름과 직업을

출력하시오

 

SELECT ENAME, JOB

 FROM EMP M

WHERE 4 <=(SELECT COUNT(*)

                       FROM EMP S

                      WHERE S.JOB = M.JOB);

 

 

 

화면 캡처: 2018-11-05 오후 2:31

 

메인쿼리의 지정 M 이 WHERE 절의 서브쿼리에도 들어가서

작동한다. 메인과 서브가 상호관련 한다해서

'상호관련 서브쿼리' 라고 한다.

 

 

SELECT ENAME, JOB

 FROM EMP M

WHERE 4 <=(SELECT COUNT(*)

                       FROM EMP S

                      WHERE JOB = 'PRESIDENT');

라고 하면

WHERE 4 <= 1 이 되어서 결국 출력 안 한다.

 

PRESIDENT

SALESMAN

CLERK

ANALYST

각각의 직원수가 4명보다 많아야 출력된다는 뜻이다.

 

중요한건

메인쿼리부터 실행된다.

 

※ 상호관련 서브쿼리는 main query 부터 실행이 된다.

메인쿼리의 컬럼이 서브쿼리로 들어간 게 상호관련 서브쿼리다.

 

 

 

문제509.

우리반에서 나이가 같은 나이인 동료학생이

한 명도 없는 학생들의 이름과 나이를 출력하시오

 

select ename, age

from emp2 e2

 where 1= (select count(*) from emp2

                where age= e2.age);