오라클 문제 풀이 (Sub 쿼리 활용)

--Scott
--33) 각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달수)를 계산하여
--열 레이블을 MONTHS_WORKED로 지정하시오. 결과는 정수로 반올림하여 표시하고 근무달 수를
--기준으로 오름차순으로 정렬하시오.(MONTHS_BETWEEN 함수 참고)

SELECT
    *
FROM emp;

SELECT ename, round(MONTHS_BETWEEN(SYSDATE , hiredate)) as MONTHS_WORKED
FROM emp
ORDER BY MONTHS_WORKED asc;

--34)emp테이블에서 이름, 업무, 근무연차를 출력하시오.

SELECT ename, job, round(MONTHS_BETWEEN(SYSDATE , hiredate)/ 12)
FROM emp;


--35)emp테이블에서 사원이름, 월급, 월급과 커미션을 더한 값을 컬럼명 실급여라고 해서 출력.
--단, NULL값은 나타나지 않게 작성하시오.
    SELECT ename, sal, sal +  NVL(comm,0) as "실급여"
    FROM emp;

--36)월급과 커미션을 합친 금액이 2,000이상인 급여를 받는 사원의 이름,업무,월급,커미션,고용날짜
--를 출력하시오. 단, 고용날짜는 1980-12-17 형태로 출력하시오.
    SELECT ename, job, sal, nvl(comm, 0), sal +  nvl(comm, 0) as "실급여", TO_CHAR(hiredate, 'YYYY-MM-DD')
    FROM emp
    where sal +  nvl(comm, 0) >= 2000;
     
--    37)DECODE 또는 CASE WHEN THEN 함수를 사용하여 다음 데이터에 따라 JOB열의 값을 기준으로
-- 모든 사원의 등급을 표시하시오.

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

SELECT ename, job, 
case 
    when job = 'PRESIDENT' THEN 'A'
    when job = 'ANALYST' THEN 'B'
    when job = 'MANAGER' THEN 'C'
    when job = 'SALESMAN' THEN 'D'
    when job = 'CLERK' THEN 'E'
    ELSE '0'
    END AS "등급"
    
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, job, sal
FROM emp
where sal >(select avg(sal) from emp)
order by sal;

-- 62) (10번부서에서 급여를 가장 적게 받는 사원)과 동일한 급여를 받는 사원의 이름을 출력하시오.
select empno, sal, ename, deptno
from emp
where sal in (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(*) > 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번 부서에서 가장 급여를 많이 받는 사원)과 동일한 급여를 받는 
-- 사원의 이름과 부서명,급여, 급여등급을 출력하시오.(emp, dept, salgrade)
SELECT * 
FROM emp e , dept d, salgrade s;

SELECT e.ename, d.dname, e.sal, s.grade 
FROM emp e , dept d, salgrade s
where e.deptno = d.deptno and sal BETWEEN s.losal and s.hisal
and sal in (SELECT max(sal) FROM emp where deptno = 20);

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

SELECT *
FROM emp;

select deptno, ename, sal + comm, nvl2(comm,'유(O)', '무(X)') as comm유무
from emp
where sal + comm > (select avg(sal) from emp);

-- 68) (CHICAGO 지역에서 근무하는 사원의 평균 급여)보다 높은 급여를 받는 사원의 이름과 급여,
--    지역명을 출력하시오.
SELECT ename, sal, d.loc
FROM emp e , dept d
where e.deptno = d.deptno and sal > (select avg(e.sal) from emp e , dept d GROUP by d.loc having d.loc = 'CHICAGO' ); 

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

SELECT d.dname, e.ename, e.job
FROM emp e, dept d
where e.deptno = d.deptno and job = 'SALESMAN'
and 
d.dname in (select d.dname from emp e, dept d where e.deptno = d.deptno GROUP by d.dname having count(*) >= 2);

-- 70) (커미션이 없는 사원들) 중 월급이 가장 높은 사원의 이름과 급여등급을 출력하시오.
SELECT e.ename,  s.grade, comm
FROM emp e , salgrade s
where sal BETWEEN s.losal and s.hisal  and sal in (select max(sal) from emp where comm is null);


-- 71) SMITH의 관리자(mgr)의 이름과 부서명, 근무지역을 출력하시오. 
SELECT e.ename, dname, loc
FROM emp e, dept d
where e.deptno = d.deptno and e.empno = (select mgr from emp where ename = 'SMITH');