과제[1]

2010/05/19

-----------------우선 과제를 위한 생성-----------------------

CREATE TABLE emp
       (empno NUMBER(4) NOT NULL,
        ename VARCHAR2(10),
        job VARCHAR2(9),
        mgr NUMBER(4),
        hiredate date,
        sal NUMBER,
        comm NUMBER,
        deptno NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
         '1980-12-17',  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
         '1981-2-20', 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
         '1981-2-22', 1250,  500, NULL);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
         '1981-4-2',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
         '1981-9-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
         '1981-5-1',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
         '1981-6-9',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
         '1982-12-9', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
         '1981-11-17', 5000, NULL, NULL);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
         '1981-12-8',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
         '1983-1-12', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
         '1981-12-3',  950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
         '1981-12-3',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
         '1982-1-23', 1300, NULL, 10);

CREATE TABLE DEPT
       (deptno NUMBER(2),
        dname VARCHAR2(14),
        loc VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
        (ename VARCHAR2(10),
         job   VARCHAR2(9),
         sal   NUMBER,
         comm  NUMBER);

CREATE TABLE SALGRADE
        (grade NUMBER(1),
         losal NUMBER,
         hisal NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;


-------------------------- 문제-------------------------------


-- 1번
--사원 테이블에서 사원번호가 7369, 7698 번인 사원번호와 이름을
--출력하세요.

SELECT empno
      , ename
FROM emp
WHERE empno IN('7369','7698');

-- 2번
-- 사원 테이블에서 사원번호가 7369, 7698 번인 아닌 사원번호와 이름을 출력하세요

SELECT empno
      , ename
FROM emp
WHERE empno NOT IN('7369','7698');

-- 3번
-- 사원 테이블에서 급여(SAL)가 3000에서 5000사이인 사원 정보를 다 출력하세요

SELECT *
FROM emp
WHERE sal BETWEEN 3000 AND 5000;

-- 4번
-- 사원 테이블에서 성과급(COMM)을 받는 사원의 정보를 모두 출력하세요.

SELECT *
FROM emp
WHERE comm IS NOT NULL AND comm > 0;

-- 5번
-- 사원 테이블에서 고용일자(HIREDATE)가 1981년인 사원의 정보를 모두 출력하세요

SELECT *
FROM emp
WHERE TO_CHAR(hiredate,'YYYY') = 1981;

-- 6번
-- 사원 테이블에서 직업(JOB)이 SALESMAN 중에서 사원번호의 최대값을 출력하세요.

SELECT MAX(empno)
FROM emp
WHERE job IN('SALESMAN')

-- 7번
-- 사원 테이블에서 부서번호(DEPTNO)별 최대월급을 구하세요.
SELECT deptno
      , MAX(sal)
FROM emp
GROUP BY deptno;

-- 8번
-- 사원 테이블에서 직무(JOB)별 평균월급을 구하세요.
-- 단 부서 인원이 1명인 부서는 제외하고 직무에 대한 오름차순으로 나타내세요

SELECT job
      , AVG(sal)
FROM emp
GROUP BY job
HAVING COUNT(*) > 1
ORDER BY job;