[Oracle DB SQL] scott Schema Sub Query 연습문제

59) DECODE 또는 CASE WHEN THEN 함수를 사용하여 다음 데이터에 따라 JOB열의 값을 기준으로 모든 사원의 등급을 표시하시오.

업무        등급
PRESIDENT  A
ANALYST      B
MANAGER   C
SALESMAN  D
CLERK 　    E
기타　  　 0

 

-- scott

SELECT ename, job,
       DECODE(job,
       'PRESIDENT', 'A',  -- classification( == 분류 )
       'ANALYST',   'B',
       'MANAGER',   'C',
       'SALESMAN',   'F',
       'CLERK',     'E',
       0) as GRADE
FROM emp;


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 '0'
       END  as GRADE
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(*)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname
HAVING COUNT(e.empno) > 3;

 

 

64) (사원번호가 7844인 사원보다 빨리 입사)한 사원의 이름과 입사일을 출력하시오.

 

SELECT ename, hiredate
FROM emp
WHERE hiredate < (SELECT hiredate
                  FROM emp
                  WHERE empno = 7844);

 

 

65) 직속상사(mgr)가 (KING)인 모든 사원의 이름과 급여를 출력하시오.

 

SELECT ename, sal
FROM emp
WHERE mgr = (SELECT empno
             FROM emp
             WHERE ename = 'KING');

 

 

66) 20번 부서에서 가장 급여를 많이 받는 사원과 동일한 급여를 받는 사원의 이름과 부서명,급여, 급여등급을 출력하시오.

 

SELECT ename, d.dname, sal, s.grade
FROM emp e, dept d, salgrade s
WHERE sal = (SELECT MAX(sal)
             FROM emp
             WHERE deptno = 20)
AND e.deptno = d.deptno
AND sal BETWEEN s.losal AND s.hisal;

 

 

67) 총급여(sal+comm)가 평균 급여보다 많은 급여를 받는 사람의 
부서번호, 이름, 총급여, 커미션을 출력하시오.(커미션은 유(O),무(X)로 표시하고 컬럼명은 "comm유무" 출력)

 

SELECT deptno, ename, sal + NVL(comm,0), NVL(comm,0), NVL2(comm, '있음', '없음')
FROM emp
WHERE sal + NVL(comm,0) > (SELECT AVG(sal)
                           FROM emp);

 

 

68) CHICAGO 지역에서 근무하는 사원의 평균 급여보다 높은 급여를 받는 사원의 이름과 급여, 지역명을 출력하시오.

 

SELECT e.ename, sal, dname
FROM emp e, dept d
WHERE sal > (SELECT ROUND(AVG(sal),1)  -- 3. 비교     -- 2. 평균급여
             FROM emp
             WHERE deptno = (SELECT deptno           -- 1. CHICAGO 지역번호
                             FROM dept
                             WHERE  loc = 'CHICAGO'))
AND e.deptno = d.deptno ;

 

 

 

 

69) 업무가 SALESMAN인 직원이 2명 이상인 부서의 이름, 근무하는 사원의 이름, 업무를 출력하시오.(컬럼명은 부서명, 사원명, 업무로 출력)

 

SELECT d.deptno, d.dname, e.ename, e.job
FROM emp e, dept d
WHERE e.deptno IN (SELECT deptno
                   FROM emp
                   WHERE job = 'SALESMAN'
                   GROUP BY deptno
                   HAVING COUNT(job) > 2)
AND e.deptno = d.deptno;                   

 

 

70) 커미션이 없는 사원들 중 월급이 가장 높은 사원의 이름과 급여등급을 출력하시오.

 

SELECT e.ename, s.grade
FROM emp e, salgrade s
WHERE sal = (SELECT MAX(sal)
             FROM emp
             WHERE comm IS NULL OR comm = 0)
AND sal BETWEEN losal AND hisal;

 

 

71) SMITH의 관리자(mgr)의 이름과 부서명, 근무지역을 출력하시오.

 

SELECT empno, ename, dname, loc
FROM emp e, dept d  
WHERE empno = (SELECT mgr
               FROM emp
               WHERE ename = 'SMITH')
AND e.deptno = d.deptno;