Oracle - 연습문제 #4

1. EMP 테이블에서 인원수, 최대급여, 최소급여, 급여의 합을 계산하여 출력하는 SELECT 문장을 작성하여라.

-------------------------------------------------------------------

SQL> select count(*) as "인원", max(sal), min(sal), sum(sal) from emp;

      인원   MAX(SAL)   MIN(SAL)   SUM(SAL)
---------- ---------- ---------- ----------
        14       5000        800      29025

SQL>

2. EMP 테이블에서 각 업무별로 최대 급여, 최소 급여ㅡ 급여의 합을 출력하는 SELECT 문장을 작성하여라.

-------------------------------------------------------------------

SQL> select deptno, max(sal), min(sal), sum(sal)
  2  from emp
  3  group by deptno;

    DEPTNO   MAX(SAL)   MIN(SAL)   SUM(SAL)
---------- ---------- ---------- ----------
        30       2850        950       9400
        20       3000        800      10875
        10       5000       1300       8750

SQL>

3. EMP 테이블에서 업무별 인원수를 구하여 출력하는 SELECT 문장을 작성하여라.

-------------------------------------------------------------------

SQL> select deptno, count(*)
  2  from emp
  3  group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

SQL>

4. EMP 테이블에서 최고 급여와 최소 급여의 차이는 얼마인가 출력하는 SELECT 문장을 작성하여라.

-------------------------------------------------------------------

SQL> select max(sal), min(sal), max(sal)-min(sal)
  2  from emp;

  MAX(SAL)   MIN(SAL) MAX(SAL)-MIN(SAL)
---------- ---------- -----------------
      5000        800              4200

SQL>

5. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

H_YEAR   COUNT(*)     MIN(SAL)     MAX(SAL)        AVG(SAL)     SUM(SAL) -------------------------------------------------------------------------------------------------------------- 80              1                    800               800              800                 800 81             10                   950              5000          2282.5              22825 82              1                  1300              1300             1300               1300 87              2                  1100              3000             2050               4100

-------------------------------------------------------------------

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select trunc(hiredate,'YEAR') as "H_YEAR", count(*), min(sal), max(sal), av
g(sal), sum(sal)
  2  from emp
  3  group by trunc(hiredate,'YEAR')
  4* order by trunc(hiredate,'YEAR')
SQL> /

H_YEAR     COUNT(*)   MIN(SAL)   MAX(SAL)   AVG(SAL)   SUM(SAL)
-------- ---------- ---------- ---------- ---------- ----------
80/01/01          1        800        800        800        800
81/01/01         10        950       5000     2282.5      22825
82/01/01          1       1300       1300       1300       1300
87/01/01          2       1100       3000       2050       4100

SQL>

6. EMP 테이블에서 모든 사원의 이름과 급여 (15자리로 출력 좌축의 빈곳은 "*"로 대치)를 출력하는 SELECT 문장을 기술하시오.

TOTAL    1980    1981    1982    1983    1984    1987 ------------------------------------------------------------------------------        14         1       10          1         0         0         2

-------------------------------------------------------------------

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select count(*) as "total",
  2  sum(decode(to_char(hiredate,'YYYY'),1980,1,0)) as "1980",
  3  sum(decode(to_char(hiredate,'YYYY'),1981,1,0)) as "1981",
  4  sum(decode(to_char(hiredate,'YYYY'),1982,1,0)) as "1982",
  5  sum(decode(to_char(hiredate,'YYYY'),1983,1,0)) as "1983",
  6  sum(decode(to_char(hiredate,'YYYY'),1984,1,0)) as "1984",
  7  sum(decode(to_char(hiredate,'YYYY'),1987,1,0)) as "1987"
  8* from emp
SQL> /

     total       1980       1981       1982       1983       1984       1987
---------- ---------- ---------- ---------- ---------- ---------- ----------
        14          1         10          1          0          0          2

SQL>

7. EMP 테이블에서 모든 사원의 정보를 이름, 업무, 입사일, 입사한 요일을 출력하는  SELECT 문장을 기술하시오.

JOB               Deptno 10  Deptno 20  Deptno 30      Total ------------------------------------------------------------------------------------ ANALYST                                6000                       6000 CLERK                    1300          1900           950       4150 MANAGER               2450          2975         2850       8275 PRESIDENT             5000                                        5000 SALESMAN                                               5600       5600

-------------------------------------------------------------------

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select job,
  2  sum(decode(deptno,10,sal,null)) as "Deptno 10",
  3  sum(decode(deptno,20,sal,null)) as "Deptno 20",
  4  sum(decode(deptno,30,sal,null)) as "Deptno 30",
  5  sum(sal) as "Total"
  6  from emp
  7  group by job
  8* order by job
SQL> /

JOB                 Deptno 10  Deptno 20  Deptno 30      Total
------------------ ---------- ---------- ---------- ----------
ANALYST                             6000                  6000
CLERK                    1300       1900        950       4150
MANAGER                  2450       2975       2850       8275
PRESIDENT                5000                             5000
SALESMAN                                       5600       5600

SQL>

8. EMP 테이블에서 이름의 길이가 6자 이상인 사원의 정보를 이름, 이름의 글자수, 업무를 출력하는 SELECT 문장을 기술하시오.

MGR   JOB                  SUM(SAL) ----------------------------------------------------       7566   ANALYST                6000       7566                                6000       7698   CLERK                      950       7698   SALESMAN              5600       7698                                6550       7782   CLERK                     1300       7782                                1300       7788   CLERK                     1100       7788                                 1100       7839   MANAGER                8275       7839                                 8275        MGR   JOB                  SUM(SAL) ------------------------------------------------------       7902   CLERK                       800       7902                                   800                 PRESIDENT              5000                                               5000                                             29025

-------------------------------------------------------------------

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select mgr,job,sum(sal)
  2  from emp
  3  group by rollup(mgr,job)
  4* order by mgr
SQL> /

       MGR JOB                  SUM(SAL)
---------- ------------------ ----------
      7566 ANALYST                  6000
      7566                          6000
      7698 CLERK                     950
      7698 SALESMAN                 5600
      7698                          6550
      7782 CLERK                    1300
      7782                          1300
      7788 CLERK                    1100
      7788                          1100
      7839 MANAGER                  8275
      7839                          8275

       MGR JOB                  SUM(SAL)
---------- ------------------ ----------
      7902 CLERK                     800
      7902                           800
           PRESIDENT                5000
                                    5000
                                   29025

16 개의 행이 선택되었습니다.

SQL>