12. 실습(20081030)

Oracle 문제3

(1) 부서별 평균 급여가 1000 이상인 부서의 부서번호는?

1. SQL> SELECT deptno
     2  FROM emp
     3  GROUP BY deptno
     4  HAVING avg(sal) >= 1000;
2.  
3.     DEPTNO
   ----------
           10
           20
           30

 

(2) salesman의 부서별 평균 급여가 1500이상인 부서의 부서 번호는?

1. SQL> SELECT deptno, job, avg(sal)
     2  FROM emp
     3  WHERE job='SALESMAN'
     4  GROUP BY deptno, job
     5  HAVING avg(sal) > 1500;
2. no rows selected
3.  
4. SQL> SELECT deptno, job, avg(sal)
     2  FROM emp
     3  GROUP BY deptno, job
     4  HAVING job='SALESMAN' AND avg(sal) > 1500;
5. no rows selected

 

(3) 사번이 7700 이상인 사원들의 직종(job) 별 사원 수는 ?

1. SQL> SELECT job, count(*) "명"
     2  FROM emp
     3  WHERE empno >= 7700
     4  GROUP BY job;
2.  
3. JOB               명
   --------- ----------
   ANALYST            2
   CLERK              3
   MANAGER            1
   PRESIDENT          1
   SALESMAN           1

 

(4) 메니저(mgr)별로 사원 수와  사원들의  봉급 총합은?

1. SQL> SELECT count(*), sum(sal), mgr
     2  FROM emp
     3  GROUP BY mgr;
2.  
3.   COUNT(*)   SUM(SAL)        MGR
   ---------- ---------- ----------
            2       6000       7566
            5       6550       7698
            1       1300       7782
            1       1100       7788
            3       8275       7839
            1        800       7902
            1       5000
4. 7 rows selected.

 

(5) 메니저별 사원의 봉급평균이 높은 순으로 mgr을 출력하시오.

1. SQL> SELECT avg(sal), mgr
     2  FROM emp
     3  GROUP BY mgr
     4  ORDER BY avg(sal) DESC;
2.  
3.   AVG(SAL)        MGR
   ---------- ----------
         5000
         3000       7566
   2758.33333       7839
         1310       7698
         1300       7782
         1100       7788
          800       7902
4. 7 rows selected.

 

(6) 직종별로 봉급이 가장 낮은 사원의 이름을 출력하시오.

1. SQL> SELECT e.ename, e.job, e.sal
     2  FROM emp e,
     3  (SELECT job, MIN(sal) min_sal
     4  FROM emp
     5  GROUP BY job) s
     6  WHERE e.job=s.job AND e.sal=s.min_sal;
2.  
3. ENAME      JOB              SAL
   ---------- --------- ----------
   SMITH      CLERK            800
   WARD       SALESMAN        1250
   MARTIN     SALESMAN        1250
   CLARK      MANAGER         2450
   SCOTT      ANALYST         3000
   KING       PRESIDENT       5000
   FORD       ANALYST         3000
4. 7 rows selected.

 

Oracle 문제4

• emp, dept, salgrade 테이블에 대하여 다음 질의를 수행하시오.

(1) 봉급의 등급이 3인 사원의 사번과 이름은?

1. SQL> SELECT e.empno, e.ename
     2  FROM emp e, salgrade s
     3  WHERE e.sal BETWEEN s.losal AND s.hisal
     4  AND s.grade=3;
2.  
3.      EMPNO ENAME
   ---------- ----------
         7499 ALLEN
         7844 TURNER

 

(2) 현재 사원들이 받는 봉급 중 등급별로 최고 급여는?

1. SQL> SELECT s.grade, MAX(e.sal)
     2  FROM emp e, salgrade s
     3  WHERE e.sal BETWEEN s.losal AND s.hisal
     4  GROUP BY s.grade;
2.  
3.      GRADE MAX(E.SAL)
   ---------- ----------
            1       1100
            2       1300
            3       1600
            4       3000
            5       5000

 

(3) 부서별 봉급 등급별 최고 급여는?

1. SQL> SELECT e.deptno, s.grade, MAX(e.sal)
     2  FROM emp e, salgrade s
     3  WHERE e.sal BETWEEN s.losal AND s.hisal
     4  GROUP BY e.deptno, s.grade;
2.  
3.     DEPTNO      GRADE MAX(E.SAL)
   ---------- ---------- ----------
           10          2       1300
           10          4       2450
           10          5       5000
           20          1       1100
           20          4       3000
           30          1        950
           30          2       1250
           30          3       1600
           30          4       2850
4. 9 rows selected.

 

(4) 사번, 사원이름, 부서번호, 부서이름, 봉급 등급을 출력하시오.

1. SQL> SELECT e.empno, e.ename, d.deptno, d.dname, s.grade
     2  FROM emp e, dept d, salgrade s
     3  WHERE e.deptno = d.deptno
     4  AND e.sal BETWEEN s.losal AND s.hisal;
2.  
3.      EMPNO ENAME          DEPTNO DNAME               GRADE
   ---------- ---------- ---------- -------------- ----------
         7369 SMITH              20 RESEARCH                1
         7900 JAMES              30 SALES                   1
         7876 ADAMS              20 RESEARCH                1
         7521 WARD               30 SALES                   2
         7654 MARTIN             30 SALES                   2
         7934 MILLER             10 ACCOUNTING              2
         7844 TURNER             30 SALES                   3
         7499 ALLEN              30 SALES                   3
         7782 CLARK              10 ACCOUNTING              4
         7698 BLAKE              30 SALES                   4
         7566 JONES              20 RESEARCH                4
         7788 SCOTT              20 RESEARCH                4
         7902 FORD               20 RESEARCH                4
         7839 KING               10 ACCOUNTING              5
4. 14 rows selected.

이 글은 스프링노트에서 작성되었습니다.