Kosta 클라우드 네이티브 애플리케이션 개발 (CNA) 교육 9일차

--1. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하시오.
--DEPTNO      CNT          SAL
------------ ---------- ----------
--   00        00           0000

select e.deptno as 부서번호 
        ,ee.cnt as 인원수 
        ,sum(e.sal) as 급여의합 
from emp e, (select deptno, count(deptno) as cnt
            from emp
            group by deptno
            ) ee
where e.deptno = ee.deptno
and ee.cnt >= 4
group by e.deptno, ee.cnt;

----------------------------------------------------------

--2.  EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력하시오.
--DEPTNO       CNT
------------ ----------
--00            00

select e.deptno, max(ee.cnt) as CNT
from emp e, (
            select deptno, count(deptno) as cnt , row_number() over (order by count(deptno) desc) as rk
            from emp
            group by deptno
            ) ee
where e.deptno = ee.deptno
and ee.rk = 1
group by e.deptno;

----------------------------------------------------------

--3. EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하시오.
--EMPNO
------------
--0000

select empno, ename
from emp e, (select mgr
                , count(1) cnt
                , row_number() over(order by count(1) desc) as rk 
            from emp
            group by mgr
)ee
where ee.rk =1
and empno = ee.mgr;

----------------------------------------------------------

--4. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하시오.
--CNT10      CNT30
---------   -------
--00          00

select (
        select count(1) from emp where deptno = 10 group by deptno) as cnt10
        ,(select count(1) from emp where deptno = 30 group by deptno) as cnt30
from dual;

----------------------------------------------------------


--5. EMP 테이블에서 사원번호(EMPNO)가 7521인 사원의 직업(JOB)과 같고 사원번호(EMPNO)가 7934인 사원의 급여(SAL)보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하시오.
--EMPNO   ENAME   JOB    SAL
-------- ------  -----  -----
--0000 0000 0000   0000  

select e.empno, e.ename, e.job, e.sal
from emp e
            ,(select sal from emp where empno = 7934) esal
            ,(select job from emp where empno = 7521) ejob

where e.sal > esal.sal
and e.job = ejob.job;

----------------------------------------------------------

--6. 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하시오. 
---조건1 : 직업별로 내림차순 정렬
--EMPNO  ENAME   JOB   DNAME         
-------  ------- ----  ------
--0000 0000 0000   0000    

select eee.empno, eee.ename, eee.job, d.dname
from (
        select e.deptno, e.empno, e.ename, ee.job
            from emp e
                ,(select job, min(sal) as msal 
                    from emp
                    group by job) ee
        where e.sal = ee.msal
        ) eee, 
        (
        select deptno, dname 
        from dept
        ) d
        
where eee.deptno = d.deptno
order by job desc;

----------------------------------------------------------

--7. 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하시오.
---조건1. 한달 근무일수는 20일, 하루 근무시간은 8시간이다.
---조건2. 시급은 소수 두 번째 자리에서 반올림한다.
---조건3. 부서별로 오름차순 정렬
---조건4. 시급이 많은 순으로 출력
--DEPTNO  ENAME   TSAL
-------- ------- -----
--0000 0000  0000 

select deptno, ename, round(sal/20/8,1) as tsal
from emp
order by deptno asc, tsal desc;

----------------------------------------------------------


--8. 각 사원 별 커미션(COMM)이 0 또는 NULL이고 부서위치가 ‘GO’로 끝나는 사원의 정보를 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하여라. 
---조건1. 보너스가 NULL이면 0으로 출력
--EMPNO   ENAME   COMM   DEPTNO   DNAME   LOC          
--------  -----  ------ -------  ------ -----
--0000 0000 0000   0000      0000   0000

select e.empno, e.ename, nvl(e.comm,0) as comm, d.deptno, d.dname, d.loc
from emp e, (
                select deptno, dname, loc
                from dept
                where  substr(loc,-2,2) = 'GO'
                ) d
where e.deptno = d.deptno
and (e.comm is null or e.comm = 0)
;

----------------------------------------------------------

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

select deptno
        ,case when avg(sal) > 2000 then '초과' else '미만' end as SAL
from emp
group by deptno
order by deptno;

----------------------------------------------------------


--10. 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하시오.
--EMPNO  ENAME  DEPTNO  HIREDATE 
-------  -----  ------  ---------
--0000   0000   0000     0000 

select e.empno, e.ename, e.deptno, e.hiredate
from emp e, (
                select min(hiredate) as hiredate
                from emp
                group by deptno
                ) ee
where e.hiredate = ee.hiredate
order by deptno asc;

----------------------------------------------------------


--11. 1980년~1980년 사이에 입사된 각 부서별 사원수를 부서번호, 부서명, CNT1980, CNT1981, CNT1982로 출력하시오. 
--DEPTNO  DNAME  CNT1980   CNT1981    CNT1982
--------  ------ --------  --------  --------
--0000    0000    0000       0000       0000

select e.deptno, d.dname        
            ,sum(decode(to_char(e.hiredate, 'YYYY'), '1980', 1, 0)) as CNT1980
            ,sum(decode(to_char(e.hiredate, 'YYYY'), '1981', 1, 0)) as CNT1981
            ,sum(decode(to_char(e.hiredate, 'YYYY'), '1982', 1, 0)) as CNT1982   
from emp e, dept d
where e.deptno = d.deptno
group by e.deptno, d.dname;

----------------------------------------------------------


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

select ename
        ,case when comm is null or comm = 0 then 500 else comm end as comm
from emp
where hiredate > '1981-05-31';

----------------------------------------------------------


--13. 1981년 6월 1일 ~ 1981년 12월 31일 입사자 중 부서명(DNAME)이 SALES인 사원의 부서번호, 사원명, 직업, 입사일을 출력하시오.
---조건1. 입사일 오름차순 정렬
--DEPTNO  DNAME   ENAME    JOB     HIREDATE 
--------  ------  ------  ------  ---------
--0000    0000     0000     0000    0000

select d.deptno, d.dname, e.ename, e.job, e.hiredate
from 
    (select ename, job, deptno, hiredate from emp where hiredate between '1981-06-01' and '1981-12-31') e
    ,(select deptno, dname from dept) d
where e.deptno = d.deptno;

----------------------------------------------------------

--14. 현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하시오.
---조건1. 현재시간 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
---조건1. 한시간후 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
--현재시간                한시간후               
--------------------- -------------------
--0000                   0000

select sysdate as 현재시간, sysdate + (interval '1' hour) as 한시간후 from dual;

----------------------------------------------------------


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

select d.deptno, d.dname, nvl(e.cnt,'0')
from dept d, (
                select deptno, count(1) cnt
                from emp
                group by deptno) e
where d.deptno = e.deptno(+) 
order by deptno asc;

----------------------------------------------------------

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

--사원번호   사원명    매니저번호   매니저명      
---------   ------  ---------   ------
--0000      0000     0000         0000

select e.empno as 사원번호
        , e.ename as 사원명
        , m.mgr as 매니저번호
        , m.ename as 매니저명
from emp e, emp m
where e.mgr = m.empno
and m.sal <= e.sal;

----------------------------------------------------------

--18. 사원명의 첫 글자가 ‘A’이고, 처음과 끝 사이에 ‘LL’이 들어가는 사원의 커미션이 COMM2일때, 
--모든 사원의 커미션에 COMM2를 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하시오.
--
--ENAME   COMM     COMM2   COMM+COMM2
--------  ------  ------- -----------
--0000    0000     0000     0000

select comm as comm2
from emp
where ename like 'A%' and ename like '%LL%';



select e.ename, e.comm ,c.comm2, nvl(e.comm + c.comm2,0) as "COMM+COMM"
from emp e,(
            select comm as comm2
            from emp
            where ename like 'A%' and ename like '%LL%'
) c;

----------------------------------------------------------

--19. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.
---조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력
---조건2. 부서번호 오름차순 정렬
---조건3. 입사일 오름차순 정렬
--
--DEPTNO  DNAME   EMPNO   ENAME   HIREDATE 
--------  ------  -----  ------- ---------
--0000    0000     0000     0000   0000

select d.deptno, d.dname, e.empno, e.ename, e.hiredate       
from dept d, (
                select deptno, empno, ename, hiredate
                from emp
                where hiredate >'1981-05-31') e
where d.deptno = e.deptno
order by d.deptno asc, e.hiredate asc;

----------------------------------------------------------

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

select empno, ename, hiredate, trunc((sysdate - hiredate)/365)  as 근무년수 
from emp
where trunc((sysdate - hiredate)/365) < 40
order by 근무년수 asc;