08. Oracle 중간연습문제

17. SALES에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무,급여,부서번호 추출

SELECT ename, job, sal, deptno
FROM emp
WHERE job = (SELECT job FROM emp WHERE dname = 'SALES');

where job in (select job from emp natural join dept
where dname='SALES');

select* from emp;

 

 

18. 업무별 평균 월급보다 낮은 사원의 부서번호, 이름, 급여, 부서 평균급여를 추출

SELECT deptno, ename, sal, job
FROM emp
WHERE sal IN (SELECT AVG(sal) FROM emp GROUP BY job); --사원1037.5 영업1400, 사장5000, 매니저 2758.333, 분석가3000

SELECT deptno, ename, sal, (SELECT AVG(sal) FROM emp t WHERE t.deptno=e.deptno) "부서별 평균월급"
FROM emp e
WHERE sal < (SELECT AVG(sal) FROM emp t WHERE t.deptno=e.deptno)
ORDER BY deptno;

* SELECT절에서 새로운 절로 조건 지정하여 계산된 항목 출력 가능
* SELECT절과 부속 절의 테이블 별칭으로 나누기 가능

 

 

19. 사원번호,사원명,부서명,소속부서 인원수,업무,소속 업무 급여평균,급여를 추출

SELECT empno, ename, dname, (SELECT COUNT(*) FROM emp e WHERE e.deptno=d.deptno) "소속부서 인원수", job, (SELECT AVG(sal) FROM emp e WHERE e.deptno=d.deptno) "소속 업무 급여평균", sal
FROM emp e, dept d
WHERE e.deptno = d.deptno;

 

 

20. 사원번호,사원명,부서번호,업무,급여, 자신의 소속 업무 평균급여를 추출

SELECT empno, ename, deptno, job, sal, (SELECT AVG(sal) FROM emp e WHERE e.job = d.job) "소속 업무 평균급여"
FROM emp d;

 

 

21. 최소한 한 명의 부하직원이 있는 관리자의 사원번호,이름,입사일자,급여 추출

SELECT empno, ename, hiredate, sal
FROM emp e
WHERE EXISTS (SELECT 1 FROM emp d WHERE d.mgr=e.empno);

* EXISTS (서브쿼리) : 결과값이 한 건이라도 존재하면 T, 없으면 F를 반환
결과값이 한 건이라도 있으면 쿼리를 더이상 수행하지 않는다.
SELECT 절은 반환이 필요치 않으므로 의미 없는 1 기입

 

 

22. 부하직원이 없는 사원의 사원번호, 이름, 업무, 부서번호 추출

SELECT empno, ename, job, deptno
FROM emp e
WHERE NOT EXISTS (SELECT 1 FROM emp m WHERE m.mgr=e.empno);

* NOT EXISTS (서브쿼리) : 결과값이 존재하지 않으면 T반환

 

 

23. BLAKE의 부하직원의 이름, 업무, 상사번호 추출

SELECT ename, job, mgr
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'BLAKE');

 

 

24. BLAKE와 같은 상사를 가진 사원의 이름,업무, 상사번호 추출

SELECT ename, job, mgr
FROM emp
WHERE mgr = (SELECT mgr FROM emp WHERE ename='BLAKE');