04/18 7일차 SQL 문제, ROWNUM

1981년 5월 31일 이후 입사자 중 커미션(COMM)이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 COMM을 출력하시오

SELECT ename, (CASE
			   WHEN comm IS NULL THEN 500
               WHEN comm = 0 THEN 500
               ELSE comm
               END)
FROM emp
WHERE hiredate > TO_DATE('1981/05/31','YYYY/MM/DD');

SELECT ename, DECODE(NVL(comm,0), 0,500, comm)
FROM emp
WHERE TO_CHAR('hiredate','YYYY/MM/DD') > '1981/05/31';

 

EMP 테이블에서 1980년도 입사자와 그 외에 입사자를 각각 출력하시오 

SELECT (SELECT COUNT(empno)
		FROM emp
        WHERE hiredate BETWEEN '1980/01/01' AND '1980/12/31'), (SELECT COUNT(empno)
        														FROM emp
                                                                WHERE hiredate NOT BETWEEN '1980/01/01' AND '1980/12/31');
FROM emp

 

가장 높은 급여를 받는 사원보다 입사일이 늦은 사원의 이름, 입사일을 출력

SELECT hiredate
FROM emp
WHERE sal = (SELECT max(sal)
			 FROM emp); // 가장 높은 급여를 받는 사원의 입사일 

SELECT ename, hiredate
FROM emp
WHERE hiredate > (SELECT hiredate
				  FROM emp
                  WHERE sal = (SELECT max(sal)
			      FROM emp));

 

FORD 보다 입사일이 늦은 사원 중 급여가 가장 높은 사원의 이름과 급여를 출력

--FORD 보다 입사일이 늦은 사원 중 급여가 가장 높은 사원의 이름과 급여를 출력

SELECT hiredate
FROM emp
WHERE ename = 'FORD';

SELECT MAX(sal)
FROM emp
WHERE hiredate > (SELECT hiredate
				  FROM emp
				  WHERE ename = 'FORD');

SELECT ename, sal
FROM emp
WHERE sal = (SELECT MAX(sal)
			 FROM emp
		     WHERE hiredate > (SELECT hiredate
				  			   FROM emp
				               WHERE ename = 'FORD'));

 

20번 부서의 최고 급여보다 많은 사원의 사원번호,사원명,급여를 출력

SELECT MAX(sal)
FROM emp
WHERE deptno = 20; //20번 부서의 최고 급여

SELECT empno, ename, sal
FROM emp
WHERE sal > (SELECT MAX(sal)
		     FROM emp
	         WHERE deptno = 20);

 

EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력

SELECT MAX(COUNT(empno))
FROM emp
GROUP BY mgr // 가장 많은 사원을 갖는 mgr의 사원수

SELECT mgr
FROM emp
GROUP BY mgr
HAVING COUNT(empno) = (SELECT MAX(COUNT(empno))
					   FROM emp
					   GROUP BY mgr);

 

ROWNUM

급여가 두 번째로 많은 급여를 받는 사원의 이름, 급여 출력

SELECT *
FROM(SELECT ROWNUM as rnum, t.*
FROM(SELECT ename, sal
FROM emp
ORDER BY sal DESC)t)
WHERE rnum = 2;

 

입사일이 두 번째로 빠른 사람의 부서명과 이름, 입사일 출력

SELECT *
FROM(SELECT ROWNUB as rnum, t.*
	 FROM(SELECT dname, ename, hiredate
	 	 FROM emp e, dept d
	 	 WHERE e.deptno = d.deptno
     	 ORDER BY hiredate ASC) t)
WHERE rnum = 2;

 

DALLAS에 위치한 부서에 최대 급여를 받는 사원에서 최소 급여를 받는 사람을 뺀 급여 출력

SELECT MAX(sal) - MIN(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno AND
	  d.loc = 'DALLAS';