예제 따라가며 쉽게 배우는 오라클 연습 문제 p.127 ~ 128

연습문제 sql은 모두 제가 직접 작성한 것이므로 오타, 오답이 존재할 수 있습니다.

궁금하신 점이 추가로 있을 시에는 문의하시면 최대한 답변드리겠습니다.

 

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

 

[답]

SELECT AVG(sal), MAX(sal), MIN(sal), COUNT(ename) "인원수"
FROM emp
WHERE deptno=10;

 

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

 

[답]

select deptno, job, count(ename) "인원수"
from emp
group by deptno, job
order by deptno;

 

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

 

[답]

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

명확한 결과를 확인하기 위해서는 job을 select 문에 넣어주는 것이 좋습니다.

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

 

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

 

[답]

select e.deptno, d.dname, e.ename, e.sal
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='ALLEN';

SELECT dname
FROM dept
WHERE deptno = (SELECT deptno 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 sal > (select avg(sal)
        	from emp);

 

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

 

[답]

select e.empno, e.ename, 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')
ORDER BY sal DESC;

 

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

 

[답]

SELECT empno, ename, sal
FROM emp
WHERE sal > (SELECT MAX(sal)
                FROM emp
                WHERE deptno=30);

 

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

 

[답]

select count(ename)
from emp
where substr(ename, 2, 1) = 'A';

SELECT COUNT(ename)
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
having avg(sal) > 1500;

 

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(e.ename)
from emp e, dept d
where e.deptno(+)=d.deptno
group by d.deptno, d.dname, d.loc;

select d.deptno, d.dname, d.loc, count(e.ename)
from emp e, dept d
where e.deptno(+)=d.deptno
group by d.deptno, d.dname, d.loc
order by d.deptno;

 

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 e.deptno = (select deptno
                                            from emp
                                            where ename='FORD');

 

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

 

[답]

SELECT ename, deptno, 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');
                
안 좋은 예시

1. 서브 쿼리는 서브 쿼리대로 주쿼리는 주쿼리대로 줄 맞춤을 해주지 않으면 가시성이 떨어집니다.

SELECT ename, deptno, 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');

2. 주쿼리 문장에서 필요 없는 join이 들어가서 복잡한 내용이 됩니다.
join이 필요한 상황인지 아닌지를 판단해서 작성할 필요가 있습니다.

SELECT e.ename, e.deptno, e.sal, e.job
FROM emp e, dept d
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;

평균 급여가 1000보다 적은 부서는 존재하지 않기 때문에
'선택된 레코드가 없습니다.'가 출력되는 것이 정상입니다.

 

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

 

[답]

1. 직무가 'ANALYST'인 사원들의 평균 급여보다 적으면서,
직무가 'ANALYST'가 아닌 사원들의 사원번호, 이름, 직무, 급여

select empno, ename, job, sal
from emp
where sal < (select avg(sal)
            from emp
            where job = 'ANALYST')
AND job != 'ANALYST';

2. 직무가 'ANALYST'인 사원의 최소 급여보다 적으면서,
직무가 'ANALYST'가 아닌 사원들의 사원번호, 이름, 직무, 급여

select empno, ename, job, sal
from emp
where sal < (select min(sal)
            from emp
            where job = 'ANALYST')
AND job != 'ANALYST';

이 문제에서는 직무가 'ANALYST'인 직원 2명의 급여는 모두 3000이라 1번과 2번 답 크게 상관이 없습니다.

 

 

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

 

[답]

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

 

+. 10번 부서 사원들의 사원이름과 상사의 이름을 출력하여라.

 

[답]

SELECT e1.ename, e2.ename AS "상사"
FROM emp e1, emp e2
WHERE e1.mgr=e2.empno
AND e1.deptno=10;

 

궁금한 점이 있을 시 덧글 주시면 최대한 답글 드리겠습니다.