25일차//[Oracle] SUB QUERY 예제(2)

-- SCOTT

--59) DECODE 또는 CASE WHEN THEN 함수를 사용하여 다음 데이터에 따라 JOB열의 값을 기준으로
--모든 사원의 등급을 표시하시오. 
--(CASE와 DECODE 모두 작성)
    SELECT ename, job,
        CASE job
        WHEN 'PRESIDENT' THEN 'A'
        WHEN' ANALYST' THEN 'B'
        WHEN 'MANAGER' THEN 'C'
        WHEN 'SALESMAN' THEN 'D'
        WHEN 'CLERK' THEN 'E'
        ELSE '기타'
        END as 등급
    FROM emp;



--60) BLAKE와 같은 부서에 있는 사원들의 이름과 입사일을 구하는데 BLAKE는 제외하고 출력하시오.(BLAKE가 여러명일 수 있음)
SELECT ename, hiredate, deptno  
FROM emp
WHERE deptno IN (SELECT deptno  
                FROM emp
                WHERE ename = 'BLAKE') 
                and ename != 'BLAKE' ;



--61) 평균급여보다 많은 급여를 받는 사원들의 사원번호, 이름, 월급을 출력하는데 월급이 높은 사람순으로 출력하시오.
SELECT empno, ename, sal 
FROM emp
WHERE sal > (SELECT avg(sal) 
            FROM emp)
ORDER BY sal DESC;


--62) 10번부서에서 급여를 가장 적게 받는 사원과 동일한 급여를 받는 사원의 이름을 출력하시오.
SELECT ename, sal
FROM emp
WHERE sal = (SELECT MIN(sal)
                FROM emp
                WHERE deptno = 10);

--63) 사원수가 3명이 넘는 부서의 부서명과 사원수를 출력하시오.
SELECT d.dname, COUNT(a.empno)
FROM emp a, dept d
WHERE a.deptno = d.deptno
GROUP BY d.dname
HAVING COUNT(a.empno) > 3;

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

--65) 직속상사(mgr)가 KING인 모든 사원의 이름과 급여를 출력하시오.
SELECT mgr, ename, sal
FROM emp 
WHERE mgr =(SELECT empno
               FROM emp
               WHERE ename = 'KING' ); 


--66) 20번 부서에서 가장 급여를 많이 받는 사원과 동일한 급여를 받는 사원의 이름과 부서명,급여, 급여등급을 출력하시오. --이상함
SELECT a.ename, a.deptno, b.dname, c.grade
FROM emp a, dept b, salgrade c
WHERE a.deptno = b.deptno
        AND sal BETWEEN c.losal AND c.hisal
        AND sal IN (SELECT MAX(sal)
                    FROM emp
                    WHERE deptno = 20);


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

--
--NVL2(컬럼, 컬럼이 NULL이 아닌 경우 값, 컬럼이 NULL인 경우 값)

--2
SELECT  deptno, ename, sal+NVL(comm,0) , NVL(comm,0), NVL2(comm,'o','x')
FROM emp
WHERE sal+NVL(comm,0) > (SELECT AVG(sal) FROM emp);




--68) CHICAGO 지역에서 근무하는 사원의 평균 급여보다 높은 급여를 받는 사원의 이름과 급여, 지역명을 출력하시오.
--지역번호
--평균급여
--비교
SELECT a.ename, a.sal, b.loc
FROM emp a, dept b
WHERE    a.deptno = b.deptno 
        AND
        sal > (SELECT AVG(sal)
                 FROM emp
                    WHERE deptno = (SELECT deptno
                                    FROM dept
                                    WHERE loc = 'CHICAGO' ));


--69) 업무가 SALESMAN인 직원이 2명 이상인 부서의 이름, 근무하는 사원의 이름, 업무를 출력하시오.(컬럼명은 부서명, 사원명, 업무로 출력)
SELECT b.dname as 부서명,
        a.ename as 사원명,
        a.job as 업무
FROM emp a, dept b
WHERE a.deptno = b.deptno
        and a.deptno =  (SELECT deptno
                  FROM emp
                  WHERE job = 'SALESMAN'
                  GROUP BY deptno
                  HAVING count(job)>2 );


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

--71) SMITH의 관리자(mgr)의 이름과 부서명, 근무지역을 출력하시오. 
SELECT a.ename, a.mgr, b.dname, b.loc 
FROM emp a, dept b
WHERE 
        a.deptno = a.deptno 
        AND a.empno = (SELECT mgr
                        FROM emp
                        WHERE ename = 'SMITH');