쌍용교육 -SQL 수업 19일차 SUBQUERY-(1. 단일행 서브쿼리,2.다중행 서브쿼리,3.다중열 서브쿼리,4.인라인뷰,5.스칼라 서브쿼리,6.실습문제)

단일행 서브쿼리

SUBQUERY: 다른 하나의 SQL문장의 절에 NESTED된 SELECT문장

단일행 서브쿼리: 오직 한 개의 행(값)을 반환

SELECT job FROM emp WHERE empno =7369;
SELECT empno,ename, job FROM emp WHERE job = 'CLERK';
-> 위의 두개의 문자를 서브쿼리를 사용해서 작성
SELECT empno,ename,job FROM emp
WHERE job = (SELECT JOB FROM emp WHERE empno = 7369);

7698의 급여보다 많은 급여를 받는 사원들의 사원번호,사원이름,급여를 출력하시오
SELECT empno,ename,sal FROM emp
WHERE sal > (SELECT sal FROM emp WHERE empno = 7698);

다중행 서브쿼리

IN연산자 사용

다중 행 서브쿼리 : 하나 이상의 행들 반한하는 서브쿼리

IN 연산자의 사용:다중행에서 동일한 값을 읽어올 떄 

부서별로 가장 급여를 적게 받는 사원들과 동일한 급여를 받은 사원의 정보를 출력하시오.
SELECT empno,ename,sal,  deptno FROM emp
WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);

ANY연산자사용

ANY연산자의 사용(약간 or의 개념과 비슷함)
ANY 연산자는 서브쿼리의 결과값 중 어느 하나의 값이라도 만족이 되면 결과값을 반환

SELECT sal FROM emp WHERE job = 'SALESMAN';
SELECT ename,sal FROM emp WHERE sal > 1250 OR sal >1500 OR sal > 1600; --SALESMAN의 급여보다 더 많이 받는 사람들을 추출
->위 문장을 서브쿼리 형태로 작성
SELECT ename,sal FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE job = 'SALESMAN');
                                                                --1600 or 1500 or 1250 or 1250 을 나타내는 값목록

ALL연선자사용

ALL 연산자의 사용(AND의 개념과 비슷)
서브쿼리의 결과와 모든 값이 일치
SELECT sal FROM emp WHERE deptno = 20;
SELECT empno,ename,sal,deptno FROM emp 
WHERE sal > 800 AND sal > 2975 AND sal >3000;
-> 위 두 쿼리를 서브쿼리로 작성
SELECT empno,ename,sal,deptno FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno =20);

다중열 서브쿼리

다중열 서브쿼리
서브 쿼리의 결과가 두 개 이상의 컬럼으로 반환되어 메인 쿼리에 전달하는 쿼리

SELECT empno,ename,sal,deptno
FROM emp
WHERE (deptno,sal) IN (SELECT deptno,sal FROM emp WHERE deptno = 30);
--        (             ) 소괄호가 없으면 에러가 난다. IN으로 이어지는 각자 deptno와 sal로 이동시켜야되기 때문이고 또, 다중은 소괄호로 묶어야된다.

부서별로 가장 급여를 적게 받는 사원 정보를 출력
SELECT empno,ename,sal,deptno FROM emp
WHERE (deptno, sal) IN (SELECT deptno,MIN(sal) 
                                  FROM emp GROUP BY deptno);
부서별로 가장 급여를 적게 받는 사원과 동일한 급여를 받은 사원 정보를 출력
SELECT empno ,ename,sal,deptno FROM emp
WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);

인라인뷰

인라인뷰: 메인 쿼리의 FROM절을 서브 쿼리로 이용하는 방법 
급여가 20번부서의 평균 급여보다 많은 급여를 받은 사원의 사원번호,이름,부서명 출력
SELECT e.empno,e.ename,d.dname 
FROM( -- table을 써야할 곳에 서브쿼리를 쓰면서 이 자리를 원래 table 처럼 쓸 수 있다.  
            SELECT empno,ename,deptno
                FROM emp WHERE sal > (SELECT AVG(sal) 
                                                FROM emp 
                                                WHERE deptno = 20)) e 
                                                JOIN dept d 
ON e.deptno = d.deptno;

JOIN을 쓴것..?
SELECT e.empno, e.ename,d.dname 
FROM emp e 
JOIN dept d
ON e.deptno = d.deptno 
WHERE sal > (SELECT AVG(sal) 
                    FROM emp 
                     WHERE deptno = 20);

부서별로 총급여를 출력하는데 부서번호,부서명,총급여를 출력하시오.
SELECT deptno,dname,total FROM dept 
JOIN (SELECT deptno,SUM(sal) total FROM emp GROUP BY deptno) --조인뒤에 온 소괄호가 인라인뷰다. 인라인뷰는 FROM절 뒤에 와야됨으로 FROM ~ JOIN 방식으로 진행했기에 문제없음
USING (deptno);

스칼라 서브쿼리

스칼라 서브쿼리
스칼라값은 단일 값을 의미함.
결과값이 단일 행, 단일 열의 스칼라값으로 반환됨.
만약 결과값이 다중 행이거나 다중 열이라면 DBMS는 그 중 어떠한 행,
어떠한 열을 출력해야 하는지 알 수 없어 에러를 출력

SELECT deptno, (SELECT dname FROM dept WHERE deptno = e.deptno), --스칼라 서브쿼리를 쓰면 조인을 안쓰고도 하나의 행열 체계를 유지함.
        SUM(sal) FROM emp e GROUP BY deptno;

실습문제

[실습문제]
1."BLAKE"와 같은 부서에 있는 사원들의 이름과 입사일을 구하는데 "BLAKE"는 제외하고 출력하시오.(BLAKE는 유니크하지 않기 때문에 empno와 같이 쓰는 다중행을 사용해야된다 단일행도 지금은 가능하지만 나중에 다른사람이 입사해서 테이블이 바뀌면 안될 수도 있음)
SELECT e.ename, e.hiredate FROM emp e WHERE e.deptno = (SELECT e.deptno FROM emp e WHERE e.ename = 'BLAKE') AND e.ename != 'BLAKE'; --단일행 (내가쓴답)
SELECT ename,hiredate FROM emp WHERE deptno IN(SELECT deptno FROM emp WHERE ename = 'BLAKE')AND ename != 'BLAKE';--다중행
2.평균급여보다 많은 급여를 받는 사원들의 사원번호, 이름,월급을 출력하는데 월급이 높은 사람 순으로 출력하시오.;
SELECT empno,ename,sal FROM emp 
WHERE sal >(SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;    
3.10번 부서에서 급여를 가장 적게 받는 사원과 동일한 급여를 받는 사원의 이름과 월급을 출력하시오.; (단일열로 명시해야된다)
SELECT ename,sal FROM emp
WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno HAVING deptno = 10);--내가 쓴 답
--강사님답
SELECT ename,sal FROM emp 
WHERE sal = (SELECT MIN(sal) FROM emp WHERE deptno = 10); --단일열
4.(부서별 사원수를 구하고) 사원수가 3명이하의 부서의 부서명과 사원수를 출력하시오.
--내가 쓴답 X 틀림.
SELECT d.dname, COUNT(e.empno) FROM emp e JOIN(SELECT deptno FROM dept d GROUP BY deptno) ON e.deptno = d.deptno ; 
--강사님 답
SELECT a.dname, b.cnt FROM dept a,
(SELECT deptno, COUNT(empno) cnt FROM emp GROUP BY deptno)b 
WHERE a.deptno = b.deptno AND b.cnt <=3;
--강사님 답 2
SELECT d.dname, COUNT(e.empno) cnt
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname
HAVING COUNT(e.empno) <=3;

5.사원번호가 7844인 사원보다 빨리 입사한 사원의 이름과 입사일을 출력하시오.
--내가 쓴답 O
SELECT ename , hiredate 
FROM emp 
WHERE hiredate < (SELECT hiredate 
FROM emp 
WHERE empno = '7844');

6.직속상사(mgr)가 KING 인 모든 사원의 이름과 급여를 출력하시오.(동명이인이 있을 수 있기 때문에  empno를 사용하기위해 다중열을 쓰는게 좋다)
--지피티도움
SELECT e.ename, e.sal 
FROM emp e 
WHERE e.mgr = (SELECT empno FROM emp WHERE ename = 'KING');

--강사님답
SELECT ename,sal FROM emp
WHERE mgr IN (SELECT empno FROM emp WHERE ename = 'KING');

7.20번 부서에서 가장 급여를 많이 받는 사원과 동일한 급여를 받는 사원의 이름과 부서명,급여,급여등급을 출력하시오.
[Oracle]
SELECT e.ename, d.dname,e.sal,s.grade
FROM emp e , dept d ,salgrade s
WHERE e.deptno = d.deptno 
AND e.sal BETWEEN s.losal AND s.hisal
AND e.sal = (SELECT MAX(sal) FROM emp WHERE deptno = '20');
[표준 SQL]
SELECT e.ename, d.dname,e.sal,s.grade
FROM emp e JOIN dept d
ON e.deptno = d.deptno
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal = (SELECT MAX(sal) FROM emp WHERE deptno = '20') ;

8.총급여(sal+comm)가 평균 급여보다 많은 급여를 받은 사람의 부서번호,이름,총급여,커미션을 출력하시오.
    (커미션은 유(O), 무(X)로 표시하고 컬럼명은 "comm유무"로 출력);
    --1번
SELECT deptno,ename,sal+NVL(comm,0) "총급여" , 
CASE WHEN comm IS NOT NULL THEN 'O'
ELSE 'X'
END "comm유무"
FROM emp 
WHERE sal+NVL(comm,0) >(SELECT AVG(sal) FROM emp);
--2번
SELECT deptno,ename,sal+NVL(comm,0) "총급여" , NVL2(comm,'O','X') comm유무
FROM emp
WHERE sal+NVL(comm,0) >(SELECT AVG(sal+NVL(comm,0)) FROM emp);

9.CHICAGO 지역에서 근무하는 사원의 평균 급여보다 높은 급여를 받는 사원의 이름과 급여,지역명을 출력하시오.
--1번
SELECT e.ename,e.sal,d.loc FROM emp e, dept d WHERE e.deptno = d.deptno AND e.sal >
(SELECT AVG(e.sal) FROM emp e, dept d 
WHERE e.deptno = d.deptno AND d.loc = 'CHICAGO');
--2번
SELECT e.ename,e.sal,d.loc FROM emp e, dept d WHERE e.deptno = d.deptno AND e.sal >
(SELECT AVG(sal) FROM emp
WHERE deptno IN (SELECT deptno FRom dept WHERE loc ='CHICAGO')); --IN을 쓰는이유 시카고가 중복될 수 있기 때문에

10.커미션이 없는 사원들 중 월급이 가장 높은 사원의 이름과 급여등급을 출력하시오.
--내가 쓴답
SELECT e.ename, s.grade 
FROM emp e, salgrade s 
WHERE (e.sal BETWEEN s.losal AND s.hisal)
AND e.sal IN (SELECT MAX(sal) FROM emp WHERE comm IS NULL);
--강사님답
SELECT e.ename, s.grade 
FROM emp e, salgrade s 
WHERE e.sal BETWEEN s.losal AND s.hisal
AND e.sal = (SELECT MAX(sal) FROM emp WHERE comm IS NULL);

11.SMITH의 직속 상사(mgr)의 이름과 부서명, 근무지역을 출력하시오.
-- 내가쓴답
SELECT e.ename,d.dname,d.loc 
FROM emp e, dept d ,emp m
WHERE e.deptno = d.deptno
AND e.mgr = m.empno
AND m.empno IN(SELECT e.mgr FROM emp e, emp m WHERE   e.ename = 'SMITH');

--강사님답
SELECT e.ename,d.dname,d.loc 
FROM emp e JOIN dept d
USING(deptno)
WHERE e.empno IN(SELECT mgr FROM emp
                            WHERE ename = 'SMITH');

12.ALLEN 보다 급여를 많이 받는 사람 중에서 입사일이 가장 빠른 사원과 동일한 날짜에 입사한 사원의 이름과 입사일,급여를 출력하시오.
--내 답
SELECT ename, hiredate, sal 
FROM emp 
WHERE  hiredate = (SELECT MIN(hiredate) FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'ALLEN'));
--강사님 답(ALLEN이 또 있을 수 있으니까 다중을 쓰는게 좋음)
SELECT ename, hiredate, sal 
FROM emp
WHERE hiredate = (SELECT MIN(hiredate) FROM emp
                        WHERE sal > ALL (SELECT sal FROM emp 
                        WHERE ename = 'ALLEN'));