[8/100] 240419

oracle 20문항

--
--1. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하시오.
--DEPTNO   COUNT(*)   SUM(SAL)
------------ ---------- ----------
--        30          6       9400
select deptno, count(*), sum(sal)
from emp
group by deptno
having count(*) >4
;

--2.  EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력하시오.
--    DEPTNO   COUNT(*)
------------ ----------
--        30          6
select deptno, count(*)
from emp
group by deptno
having count(*) =
(select max(count(*))
from emp
group by deptno)
;

--3. EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하시오.
--EMPNO
------------
--7698
select mgr as empno
from emp
group by mgr
having count(mgr) =
(select max(count(mgr)) as mcnt
from emp
group by mgr
)
;

--4. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하시오.
--
--CNT10      CNT20
------------ ----------
--         3          6
select (select count(*) from emp where deptno = 10) as CNT10, (select count(*) from emp where deptno = 30) as CNT30
from dual
;

--5. EMP 테이블에서 사원번호(EMPNO)가 7521인 사원의 직업(JOB)과 같고 사원번호(EMPNO)가 7934인 사원의 급여(SAL)보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하시오.
--
--EMPNO ENAME      JOB              SAL
------------ ---------- --------- ----------
--      7499 ALLEN      SALESMAN        1600
--      7844 TURNER     SALESMAN        1500
select empno, ename, job, sal
from emp
where job = (select job from emp where empno = 7521)
and   sal > (select sal from emp where empno = 7934)
;

--6. 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하시오.
---조건1 : 직업별로 내림차순 정렬
--
--     EMPNO ENAME      JOB       DNAME
------------ ---------- --------- --------------
--      7521 WARD       SALESMAN  SALES
--      7654 MARTIN     SALESMAN  SALES
--      7839 KING       PRESIDENT ACCOUNTING
--      7782 CLARK      MANAGER   ACCOUNTING
--      7369 SMITH      CLERK     RESEARCH
--      7788 SCOTT      ANALYST   RESEARCH
--      7902 FORD       ANALYST   RESEARCH
select e.empno, e.ename, e.job, d.dname
from emp e, dept d
where e.deptno = d.deptno
group by e.empno, e.ename, e.job, d.dname
;

select min(sal)
from emp
group by job
;

--7. 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하시오.
---조건1. 한달 근무일수는 20일, 하루 근무시간은 8시간이다.
---조건2. 시급은 소수 두 번째 자리에서 반올림한다.
---조건3. 부서별로 오름차순 정렬
---조건4. 시급이 많은 순으로 출력
--DEPTNO ENAME              시급
------------ ---------- ----------
--        10    KING         31.3
--        10    CLARK        15.3
--        20    FORD         18.8
--        20    JONES        18.6
--               ~~~ 중략 ~~~
--        30    JAMES         5.9
select deptno, ename, round((sal/160),1) as 시급
from emp
order by deptno asc, 시급 desc

;
--8. 각 사원 별 커미션(COMM)이 0 또는 NULL이고 부서위치가 ‘GO’로 끝나는 사원의 정보를 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하여라.
---조건1. 보너스가 NULL이면 0으로 출력
--
--EMPNO ENAME            COMM     DEPTNO DNAME          LOC
------------ ---------- ---------- ---------- -------------- -------------
--      7698 BLAKE               0         30 SALES          CHICAGO
--      7844 TURNER              0         30 SALES          CHICAGO
--      7900 JAMES               0         30 SALES          CHICAGO
select e.empno, e.ename, nvl(e.comm,0) as comm, e.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno and (comm = 0 or comm is null) and loc like '%GO'
;
--9. 각 부서 별 평균 급여가 2000 이상이면 초과, 그렇지 않으면 미만을 출력하시오.
--
--    DEPTNO 평균급여
------------ ----
--        30 초과
--        20 미만
--        10 미만
select deptno, case when avg(sal) >= 2000 then '초과' else '미만' end
from emp
group by deptno
;

--10. 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하시오.
--
--EMPNO ENAME          DEPTNO HIREDATE
------------ ---------- ---------- ------------------------------
--      7782 CLARK              10 1981-06-09
--      7369 SMITH              20 1980-12-17
--      7499 ALLEN              30 1981-02-20
select e.empno, e.ename, m.deptno, to_char(m.hiredate, 'YYYY-MM-DD')
from emp e, (select deptno, min(hiredate) as hiredate from emp group by deptno) m
where e.deptno = m.deptno and e.hiredate = m.hiredate
order by deptno
;

--11. 1980년~1980년 사이에 입사된 각 부서별 사원수를 부서번호, 부서명, 입사1980, 입사1981, 입사1982로 출력하시오.
--
--    DEPTNO DNAME              입사1980     입사1981     입사1982
------------ -------------- ---------- ---------- ----------
--        10 ACCOUNTING 0          2          1
--        20 RESEARCH 1          2          0
--        30 SALES 0          6          0
select e.deptno, d.dname,
count((select hiredate from emp where hiredate between '1980-01-01' and '1980-12-31'))as 입사1980,
count((select hiredate from emp where hiredate between '1981-01-01' and '1981-12-31'))as 입사1981,
count((select hiredate from emp where hiredate between '1982-01-01' and '1982-12-31'))as 입사1982
from emp e, dept d
where e.deptno = d.deptno
;

select count((select hiredate from emp where hiredate between '1980-01-01' and '1980-12-31'))
from emp
;

--12. 1981년 5월 31일 이후 입사자 중 커미션(COMM)이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 COMM을 출력하시오.
--
--ENAME            COMM
------------ ----------
--MARTIN           1400
--CLARK             500
--SCOTT             500
--KING              500
--TURNER            500
--ADAMS             500
--JAMES             500
--FORD              500
--MILLER            500
select ename, case when comm is null then 500 when 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
------------ -------------- ---------- --------- --------------------------------
--        30 SALES          TURNER     SALESMAN  1981-09-08
--        30 SALES          MARTIN     SALESMAN  1981-09-28
--        30 SALES          JAMES      CLERK      1981-12-03
select e.deptno, d.dname, e.ename, e.job, to_char(e.hiredate, 'YYYY-MM-DD')
from emp e, dept d
where e. deptno = d.deptno and
e.hiredate between '1981-06-01' and '1981-12-31' and
d.dname = 'SALES'
order by hiredate asc
;
--14. 현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하시오.
---조건1. 현재시간 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
---조건1. 한시간후 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
--
--현재시간                한시간후
--------------------- -------------------
--2012-07-31 05:43:45 2012-07-31 06:43:45
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS')as 현재시간, to_char(sysdate+1/24, 'YYYY-MM-DD HH24:MI:SS')as 한시간후
from dual
;

--15. 각 부서별 사원수를 출력하시오.
---조건1. 부서별 사원수가 없더라도 부서번호, 부서명은 출력
---조건2. 부서별 사원수가 0인 경우 ‘없음’ 출력
---조건3. 부서번호 오름차순 정렬
--DEPTNO DNAME          사원수
------------ -------------- ----------------------------------------
--        10 ACCOUNTING 3
--        20 RESEARCH 5
--        30 SALES 6
--        40 OPERATIONS 없음
select e.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno
;

--16.사원 테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하시오.
---조건1. 각 사원의 급여(SAL)는 매니저 급여보다 많거나 같다.
--
--      사원번호 사원명      매니저사원번호   매니저명
------------ ---------- ---------- ----------
--      7902 FORD             7566 JONES
--      7788 SCOTT            7566 JONES
select e.empno as 사원번호, e.ename as 사원명, m.mgr as 매니저번호, m.ename as 매니저명
from emp e, emp m
where e.deptno = m.deptno and e.mgr = m.empno and e.sal >= m.sal
;

--18. 사원명의 첫 글자가 ‘A’이고, 처음과 끝 사이에 ‘LL’이 들어가는 사원의 커미션이 COMM2일때,
--모든 사원의 커미션에 COMM2를 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하시오.
--
--ENAME            COMM      COMM2 COMM+COMM2
------------ ---------- ---------- ----------
--ADAMS               0        300        300
--ALLEN             300        300        600
--BLAKE               0        300        300
--CLARK               0        300        300
--FORD                0        300        300
--JAMES               0        300        300
--JONES               0        300        300
--KING                0        300        300
--MARTIN           1400        300       1700
--MILLER              0        300        300
--SCOTT               0        300        300
--SMITH               0        300        300
--TURNER              0        300        300
--WARD              500        300        800
select ename, nvl(comm,0) as comm, (select comm from emp where ename like 'A%LL%') as comm2,
nvl(comm,0) + (select comm from emp where ename like 'A%LL%') as 합친값
from emp
order by ename;

--19. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.
---조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력
---조건2. 부서번호 오름차순 정렬
---조건3. 입사일 오름차순 정렬
--
--        DEPTNO DNAME               EMPNO ENAME      HIREDATE
------------ -------------- ---------- ---------- ---------
--        10 ACCOUNTING           7782 CLARK      09-6-81
--        10 ACCOUNTING           7839 KING       17-11-81
--        10 ACCOUNTING           7934 MILLER     23-1-82
--        20 RESEARCH             7902 FORD       03-12-81
--        20 RESEARCH             7788 SCOTT      19-4-87
--        20 RESEARCH             7876 ADAMS      23-5-87
--        30 SALES                7844 TURNER     08-9-81
--        30 SALES                7654 MARTIN     28-9-81
--        30 SALES                7900 JAMES      03-12-81
--        40 OPERATIONS
select e.deptno, d.dname, e.empno, e.ename, to_char(e.hiredate, 'DD-MM-YY')
from emp e, dept d
where e.deptno = d.deptno and hiredate > '1981-05-31'
order by deptno, hiredate
;
--20. 입사일로부터 지금까지 근무년수가 40년 미만인 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하시오.
---조건1. 근무년수는 월을 기준으로 버림 (예:30.4년 = 30년, 30.7년=30년)
--
--     EMPNO ENAME   HIREDATE 근무년수
------------ ---------- ----------  ----------
--      7788 SCOTT      1987-04-19     25
--    7876 ADAMS      1987-05-23     25

select empno, ename, to_char(hiredate, 'YYYY-MM-DD'), trunc((sysdate - hiredate)/365) as 근무년수
from emp
where trunc((sysdate - hiredate)/365) < 40
;