예제 따라가며 쉽게 배우는 오라클 연습문제 3

1. 10번 부서에 대해 급여의 평균 값,최대 값,최소 값,인원 수를 구하여 출력하여라.

SELECT AVG(sal),MAX(sal),MIN(sal),COUNT(empno) FROM emp WHERE deptno = 10;

2. 각 부서별 같은 직무를 갖는 사원의 인원수를 구하여 부서 번호, 직무,인원수를 출력하여라.

SELECT deptno,job,COUNT(empno) FROM emp GROUP BY deptno,job;

3. 사원들의 직무별 평균 급여와 최고급여,최저급여를 평균급여에 대해 오름차순으로 정렬하여라.

SELECT AVG(sal),MAX(sal),MIN(sal) FROM emp GROUP BY job ORDER BY AVG(sal) ASC;

4. EMP와 DEPT테이블을 조인하여 모든 사원에 대해 부서 번호, 부서이름, 사원이름, 급여를 출력하여라.

SELECT e.ename,e.sal,e.deptno,d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ;

5. 이름이 'ALLEN'인 사원의 부서 이름을 출력하여라.

SELECT d.dname FROM  emp e,dept d WHERE e.deptno =d.deptno AND e.ename = (SELECT ename FROM emp WHERE ename = 'ALLEN');

6. 'ALLEN'과 직무가 같은 사원의 이름,부서 이름, 급여, 부서위치를 출력하여라.

SELECT e.ename, d.dname, e.sal,d.loc FROM emp e,dept d WHERE e.deptno =d.deptno AND e.job = (select job from emp where ename ='ALLEN');

7. 전체사원의 평균 급여보다 급여가 많은 사람의 사원 번호,이름, 부서이름,입사일,부서위치,급여를 출력하여라.

select e.empno,e.ename,d.dname,e.hiredate,d.loc,e.sal from emp e, dept d where e.deptno =d.deptno and e.sal > (select avg(sal) from emp);

8. 10번 부서 사원들 중에서 20번 부서의 사원과 같은 직무를 갖는 사원의 사원 번호 ,이름,부서 이름, 입사일, 부서 위치를 출력하여라.

select e.empno,d.dname,e.hiredate,d.loc from emp e,dept d where e.deptno =d.deptno and e.deptno=10 and e.job in (select job from emp where deptno =20);

9. 'ALLEN'의 급여보다 많고 'SCOTT'의 급여보다 적은 사원의 사원번호,이름,부서이름,부서위치,급여를 급여가 많은 순으로 출력하여라.

select e.empno,e.ename,d.dname,d.loc,e.sal from emp e, dept d where e.deptno = d.deptno and sal >(select sal from emp where  ename ='ALLEN') and sal <(select sal from emp where  ename ='SCOTT');

10. 30번 부서의 최고급여보다 급여가 많은 사원의 사원 번호 ,이름,급여를 출력하여라.

select empno,ename,sal from emp where sal > (select max(sal) from emp where deptno ='30');

11. 사원 이름의 두 번째 글자가 'A'인 사원들의 수를 출력하여라.

select count(*) from emp where ename like '_A%';

12. 사원들의 평균 급여,총 급여, 최고급여액,최저 급여액을 구하여라.

select avg(sal),sum(sal),max(sal),min(sal) from emp;

13. 평균급여가  1500이 넘는  직무와 평균 급여를 구하여라.

select job,avg(sal) from emp group by job; 

14.사원들의 이름,부서 이름, 급여를 출력하여라.

select e.ename,d.dname, e.sal from emp e, dept d where e.deptno =d.deptno;

15. 각 부서별 부서 번호(모든 부서에 대해서),부서 이름,부서 위치,사원의 수를 출력하여라.

select d.deptno,d.dname,d.loc,count(*) from emp e, dept d where e.deptno =d.deptno group by d.deptno,d.dname,d.loc

16.모든 사원들의 평균 급여보다 많이 받는 사원들의 사원 번호와 이름을 출력하여라.

select empno,ename from emp where sal > (select avg(sal) from emp);

17.'FORD'와 부서가 같은 사원들의 이름,부서 이름, 직무, 급여를 출력하여라.

select e.ename,d.dname,e.job,e.sal from emp e, dept d where e.deptno =d.deptno  and job = (select job from emp where ename ='FORD'); 

18.부서이름이 'SALES'인 사원들의 평균 급여보다 많고, 부서 이름이 'RESEARCH'인 사원들의 평균 급여보다 적은 사원들의 이름, 부서번호, 급여, 직무를 출력하여라.

select ename,sal,job from emp where sal > (select avg(e.sal) from emp e,dept d where e.deptno =d.deptno and d.dname ='SALES')

and  sal < (select avg(e.sal) from emp e,dept d where e.deptno =d.deptno and d.dname ='RESEARCH');

19.부서별 평균 급여가 1000보다 적은 부서 사원들의 부서 번호를 출력하여라.

select deptno from emp group by deptno having avg(sal) <1000;

20. 직무가 'ANALYST'인 사원보다 급여가 적으면서, 직무가 'ANALYST'가 아닌 사원들의 사원번호,이름, 직무, 급여를 출력하여라.

select deptno,ename,job,sal from emp where sal < (select sal from emp where job ='ANALYST')

and job != 'ANALYST';-- 모름