[Database] 17장 연습문제5

 

문제

 

1. 각 부서의 최소 급여를 받는 사원의 이름, 급여, 부서번호를 구하시오.

2. 평균 영업사원 급여보다 급여가 적으면서 업무가 영업사원이 아닌 사원들을 표시(사원번호, 이름, 담당 업무, 급여)하시오.

3. 부하직원이 없는 사원의 이름을 표시하시오. (사원 번호가 매니저로 저장되어 있진 않은 사람을 조회해야 함)

4. 부하직원이 있는 사원의 이름을 표시하시오. (사원 번호가 매니저로 저장되어 있는 사람을 조회해야 함)

 

5. 김동혁과 동일한 부서에 속한 사원의 부서코드, 이름, 입사일을 표시하는 질의를 작성하시오.

6. 급여가 평균 급여보다 많은 사원들의 사원 번호와 이름을 표시하되 결과를 급여에 대해서 오름차순으로 정렬하시오.

7. 이름에 “국”이 포함된 사원과 같은 부서에서 일하는 사원의 사원 번호와 이름을 표시하시오.

8. 부서위치가 서울인 사원의 이름과 부서번호 및 담당업무를 표시하시오.

9. 김지완에게 보고하는 사원의 이름과 급여를 표시하시오.

10. 조사부 부서의 사원에 대한 부서번호, 사원이름 및 담당 업무를 표시하시오.

11. 평균 월급보다 많은 급여를 받고 이름에 “우”가 포함된 사원과 같은 부서에서 근무하는 사원의 사원 번호, 이름, 급여를 표시하시오.

12. 평균급여가 가장 적은 업무를 찾으시오.

13. 담당업무가 관리자 인 사원이 소속된 부서와 동일한 부서의 사원을 표시하시오.

 

 

 

1번 문제

 

-- 1. 각 부서의 최소 급여를 받는 사원의 이름, 급여, 부서번호를 구하시오.
select * from emp;
select * from dept;
select e.ename 이름, e.sal 급여, e.deptno 부서번호
    from emp e, 
        (select deptno, min(sal) as sal from emp
            group by deptno) e1
    where e.sal = e1.sal
    order by e.deptno;

 

결과 화면1

 

 

 

2번 문제

 

-- 2. 평균 영업사원 급여보다 급여가 적으면서 업무가 영업사원이 아닌 사원들을 표시(사원번호, 이름, 담당 업무, 급여)하시오.
select * from emp;
select * from dept;

select empno 사원번호, ename 이름, job 담당업무, sal 급여 from emp
    where sal < (select round(avg(sal)) from emp
        where job = '영업사원')
    and job != '영업사원';

select e.empno 사원번호, e.ename 사원명, e.job 담당업무, e.sal 급여
    from emp e,(select avg(sal) sall from emp where job = '영업사원') e1
    where e.sal < e1.sall and  e.job != '영업사원';

 

결과 화면2

 

 

 

3번 문제

 

-- 3. 부하직원이 없는 사원의 이름을 표시하시오. (사원 번호가 매니저로 저장되어 있진 않은 사람을 조회해야 함)
-- deistinct() : 중복 제거
select distinct(ename) 사원명
    from emp
    where ename not in 
        (select e.ename 사원이름 from emp e, emp e1
        where e.empno = e1.mgr);

 

결과 화면3

 

 

 

4번 문제

 

-- 4. 부하직원이 있는 사원의 이름을 표시하시오. (사원 번호가 매니저로 저장되어 있는 사람을 조회해야 함)
select distinct(e.ename) 사원명
    from emp e, emp e1
    where e.empno = e1.mgr;

 

결과 화면4

 

 

 

5번 문제

 

-- 5. 김동혁과 동일한 부서에 속한 사원의 부서코드, 이름, 입사일을 표시하는 질의를 작성하시오.
-- ( 단 김동혁은 제외 )
select * from emp;

select e1.deptno 부서코드, e1.ename 이름, e1.hiredate 입사일
    from emp e, emp e1
    where e.deptno = e1.deptno
    and e.ename = '김동혁'
    and not e1.ename = '김동혁';

 

결과 화면5

 

 

 

6번 문제

 

-- 6. 급여가 평균 급여보다 많은 사원들의 사원 번호와 이름을 표시하되 결과를 급여에 대해서 오름차순으로 정렬하시오.
select empno 사원번호, ename 이름, sal 급여
    from emp
    where sal > (select round(avg(sal), 0) from emp)
    order by sal asc;

 

결과 화면6

 

 

 

7번 문제

 

-- 7. 이름에 “국”이 포함된 사원과 같은 부서에서 일하는 사원의 사원 번호와 이름을 표시하시오.
select * from emp;

select distinct(E1.EMPNO) 사원번호,E1.ENAME 사원명
    from EMP e, EMP E1
    where e.DEPTNO = E1.DEPTNO and
        e.ENAME like '%국%';

 

결과 화면7

 

 

 

8번 문제

 

-- 8. 부서위치가 서울인 사원의 이름과 부서번호 및 담당업무를 표시하시오.
select * from emp;
select * from dept;

select e.ename 사원이름, d.deptno 부서번호, e.job 담당업무 
    from emp e, dept d
    where e.deptno = d.deptno
    and d.loc = '서울';

 

결과 화면8

 

 

 

9번 문제

 

-- 9. 김지완에게 보고하는 사원의 이름과 급여를 표시하시오.
-- 관리자가 김지완
select * from emp;

select e1.ename 사원이름, e1.sal 급여 
    from emp e, emp e1
    where e.empno = e1.mgr 
    and e.ename = '김지완';

 

결과 화면9

 

 

 

10번 문제

 

-- 10. 조사부 부서의 사원에 대한 부서번호, 사원이름 및 담당 업무를 표시하시오.
select * from emp;
select * from dept;
    
select dept.deptno 부서번호, emp.ename 사원명, emp.job 담당업무
    from emp, dept
    where emp.deptno = dept.deptno
    and dept.dname = '조사부';

 

결과 화면10

 

 

 

11번 문제

 

-- 11. 평균 월급보다 많은 급여를 받고 이름에 “우”가 포함된 사원과 같은 부서에서 근무하는 사원의 사원 번호, 이름, 급여를 표시하시오.
select round(avg(sal), 0) from emp;

select e.ename 사원번호, e.ename 이름, e.sal 급여 
    from emp e, (select deptno from emp where ename like '%우%') e1
    where e.sal > (select round(avg(sal), 0) from emp)
    and e1.deptno = e.deptno;

select * from emp;
select * from emp where sal > (select round(avg(sal), 0) from emp);
select * from emp where deptno in (20, 50);
select * from emp where ename like '%우%'; -- 고영우, 박승우
select * from dept; -- 조사부(20), 개발부(50)

 

결과 화면11

 

 

 

12번 문제

 

-- 12. 평균급여가 가장 적은 업무를 찾으시오.
select * from emp;

select job, sal
    from (select job, round(avg(sal)) sal from emp group by job order by sal asc)
    where rownum = 1;

select asd 업무
    from (select job as asd from emp group by job order by avg(sal) asc)
    where rownum=1;

 

결과 화면12

 

 

 

13번 문제

 

-- 13. 담당업무가 관리자 인 사원이 소속된 부서와 동일한 부서의 사원을 표시하시오.
select * from emp;

-- inline view
select distinct(e.ename) 사원명, e.deptno 부서번호, e.job 담당업무 
    from emp e,
    (select deptno from emp where job='관리자')e1
    where e.deptno = e1.deptno;

 

결과 화면13