04/17 6일차 DECODE, CASE WHEN THEN, 올림 내림(ROUND, CEIL, FLOOR, TRUNC)

CNT10 CNT20 CNT30 
-----      -----         ----- 
3            5              6

대쉬보드에서 많이 쓰는 쿼리

SELECT(SELECT COUNT(1) FROM emp WHERE deptno = 10)
	  ,(SELECT COUNT(1) FROM emp WHERE deptno = 20)
      ,(SELECT COUNT(1) FROM emp WHERE deptno = 30)
FROM dual;

 

각 부서별 사원수를 출력하시오.
-조건1. 부서별 사원수가 없더라도 부서번호,
         부서명은 출력
-조건2. 부서별 사원수가 0인 경우 ‘없음’ 출력
-조건3. 부서번호 오름차순 정렬

SELECT d.deptno, d.dname, COUNT(e.deptno)
FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno
GROUP BY deptno, dname
ORDER BY deptno ASC;

 

DECODE : switch문

SELECT deptno, dname
	   ,DECODE(deptno, 10,'십',
       				   20,'이십',
                       30,'삼십',
                       '아니다') as nn
FROM dept

 

-각 부서별 사원수를 출력하시오.
조건1. 부서별 사원수가 없더라도 부서번호,
         부서명은 출력
조건2. 부서별 사원수가 0인 경우 ‘없음’ 출력
조건3. 부서번호 오름차순 정렬

(DECODE 이용하여 풀기)

SELECT d.deptno, d.dname, DECODE(COUNT(e.deptno),0,'없음',COUNT(e.deptno))
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno
GROUP BY d.deptno, d.dname
ORDER BY d.deptno ASC;

 

CASE WHEN 조건문 THEN : if문

SELECT(CASE 
	   WHEN deptno = 10 THEN '십'
       WHEN deptno = 20 THEN '이십'
       WHEN deptno = 30 TEHN '삼십'
       ELSE '아니다'
       END) as nn
FROM dept;

SELECT ename, sal, (CASE
					WHEN sal BETWEEN 1000 AND 2000 THEN '1'
                    WHEN sal BETWEEN 2000 AND 3000 THEN '2'
                    ELSE '99'
                    END) as nn
FROM emp;

 

사원 테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하시오.
조건1. 각 사원의 급여(SAL)는 매니저 급여보다 많거나 같다.

사원 테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하시오.
조건1. 각 사원의 급여(SAL)는 매니저 급여보다 많거나 같다.

SELECT e.empno, e.ename, m.empno, m.ename as mname
FROM emp e, emp m
WHERE e.mgr = m.empno AND
	  e.sal > m.sal;

 

반올림 ROUND(숫자, 보여지고싶은자리수)

SELECT 10.98765, ROUND(10.94, 0), ROUND(10.94, 1) FROM dual;
//10.98765     11     10.9

 

올림 CEIL(숫자) 실수-->정수

SELECT 10.98765, CEIL(10.94), CEIL(10.1) FROM dual;
// 10.98765      11               11

 

내림 FLOOR(숫자) 실수-->정수

SELECT 10.98765, FLOOR(10.94), FLOOR(10.11) FROM dual;
// 10.98765         10             10

 

버림 TRUNC(숫자, 버릴자리수)

SELECT 10.98765, TRUNC(10.94,1), TRUNC(10.11)
// 10.98765         10             10

 

--입사일로부터 지금까지 근무년수가 30년 이상인 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하시오.
---조건1. 근무년수는 월을 기준으로 버림
        --(예:30.4년 = 30년, 30.7년=30년)

 SELECT empno, ename, hiredate, TRUNC((sysdate-TO_DATE(hiredate))/365,0) as 근무년수
 FROM emp
 WHERE TRUNC((sysdate-TO_DATE(hiredate))/365,0) >=30;
 
 --인라인뷰 사용
 SELECT *
 FROM (SELECT empno, ename, hiredate, TRUNC((sysdate-TO_DATE(hiredate))/365,0) as 근무년수
 	   FROM emp)
 WHERE 근무년수 >=30;

 

LIKE

SELECT ename FROM emp
WHERE ename = 'A%'; // A로 시작
WHERE ename = '%A%'; // 이름에 A가 들어감
WHERE ename = '_A%'; // 두번째 글자가 A
WHERE ename = '%A'; // 마지막 글자가 A

 

각 부서 별 평균 급여가 2000이상이면 초과, 그렇지 않으면 미만을 출력하시오

SELECT deptno, (CASE
				WHEN AVG(sal)>=2000 THEN '초과'
                ELSE '미만'
                END) as 평균급여
FROM emp
GROUP BY deptno;

 

각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하시오(WHERE)

SELECT empno, ename, deptno, hiredate
FROM emp
WHERE (deptno,hiredate) IN (SELECT deptno, MIN(hiredate) as hiredate
							FROM emp
                            GROUP BY deptno);
--인라인뷰
SELECT 
FROM emp e,(SELECT deptno, MIN(hiredate) as mdate FROM emp GROUP BY deptno)t
WHERE e.deptno = t.deptno AND
	  e.hiredate = t.mdate;

 

EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하시오

EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하시오
SELECT t.deptno, t.부서인원, SUM(sal)
FROM emp e, (SELECT deptno, COUNT(empno) as 부서인원
			 FROM emp
             GROUP BY deptno)t
WHERE e.deptno = t.deptno AND
	  부서인원 > 4
GROUP BY t.deptno, t.부서인원;

 

emp 테이블에서 가장 많은 사원이 속해 있는 부서번호와 사원 수를 출력하시오

emp 테이블에서 가장 많은 사원이 속해 있는 부서번호와 사원 수를 출력하시오
SELECT deptno, COUNT(empno)
FROM emp
GROUP BY deptno
HAVING COUNT(empno) = (SELECT MAX(COUNT(empno))
					   FROM emp
                       GROUP BY deptno);

 

EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하시오
검증용 (mgr 기준으로 그 밑에 있는 사원(empno) 확인)

--EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하시오
--검증용 (mgr 기준으로 그 밑에 있는 사원(empno) 확인)
SELECT mgr
FROM emp
GROUP BY mgr
HAVING COUNT(empno) = (SELECT MAX(COUNT(empno))
					   FROM emp
                       GROUP BY mgr);