Oracle SQL 20 문제 & 정답풀이

-- 1. 부서명과 사원명을 출력하는 뷰 DNAME_ENAME_VU를 작성하시오.
    CREATE OR REPLACE VIEW dname_ename_vu
    ("부서명", "사원명")
    AS
    SELECT d.DNAME, e.ENAME
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO;

-- 2. 이름, 업무, 급여, 부서명, 위치를 포함하는 EMP_DEPT_NAME 이라는 VIEW를 생성하시오.
    CREATE OR REPLACE VIEW emp_dept_name
     ("이름", "업무", "급여", "부서명", "위치")
     AS
     SELECT e.ename, e.job, e.sal, d.DNAME, d.loc
     FROM EMP e, DEPT d
     WHERE e.DEPTNO = d.DEPTNO;

-- 3. EMP 테이블에서 사번, 사원명, 입사일을 입사일이 늦은 사원 5명을 출력하시오.
      SELECT ROWNUM AS "늦게 입사한 5명", empno, ename, hiredate
      FROM (SELECT empno, ename, hiredate
      FROM EMP
      ORDER BY hiredate DESC) aaa
      WHERE ROWNUM <= 5;

-- 4. 다음의 시나리오를 완성하는 SQL 문을 작성하시오.
-- 1) dept 테이블에서 부서번호가 20번인 부서의 이름을
      'DEVELOPMENT'로 변경하시오.
      UPDATE dept
      SET dname = 'DEVELOPMENT'
      WHERE deptno = 20;

-- 2) 50번 부서, 부서이름은 'MARKETTING', 위치는 'PUSAN'인
--     레코드를 입력하시오.
     INSERT INTO DEPT(deptno, dname, loc)
      VALUES (50, 'MARKETTING', 'PUSAN');

-- 3) SAVEPOINT를 지정하시오. 단 SAVEPOINT 이름은 SP0715 입니다.
      SAVEPOINT sp0715;

-- 4) 부서번호가 50번인 레코드를 삭제하시오.
      DELETE FROM dept
      WHERE deptno = 50;

-- 5) 3)번에 지정된 SAVEPOINT로 ROLLBACK하시오.
      ROLLBACK TO sp0715;

-- 5. 다음의 시나리오를 완성하는 SQL 문을 작성하시오.
--1) emp 및 dept table을 이용하여 사번, 사원이름, 업무,
-- 부서이름, 위치를 갖는 emp_dept 테이블을 생성하시오.
   CREATE TABLE emp_dept
   ("사번", "사원이름", "업무", "부서이름", "위치")
   AS
   SELECT e.EMPNO, e.ENAME, e.JOB, d.DNAME, d.LOC
   FROM EMP e, DEPT d
   WHERE e.DEPTNO = d.DEPTNO;

-- 2) 위에서 생성한 emp_dept 테이블에 empno를
   PRIMARY KEY 로 설정하는 SQL문을 작성하시오.
   ALTER TABLE emp_dept
   ADD CONSTRAINT emp_dept_empno_pk PRIMARY KEY(empno);

-- 6. ‘JAMES’가 속해있는 부서의 모든 사람의 사원번호, 이름,
-- 입사일, 급여를 출력하라.
    SELECT empno, ename, hiredate, sal
    FROM EMP
    WHERE deptno = (SELECT deptno
                              FROM EMP
                              WHERE ename = UPPER('james'));

-- 7. 급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호,이름,
-- 급여를 출력하라.
    SELECT empno, ename, sal
    FROM EMP
    WHERE sal > (SELECT MIN(sal)
                         FROM EMP
                         WHERE deptno = 30);

-- 8. comm을 받는 모든 사원의 이름, 부서 이름 및 위치를 표시하는
-- 질의를 작성하시오.
    SELECT e.ename, d.dname, d.loc
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO AND e.comm IS NOT NULL;

-- 9. 업무별, 부서별로 그룹하여 결과를 부서번호, 업무, 인원수,
-- 급여의 평균, 급여의 합을 구하여 출력하시오.
    SELECT deptno, job, COUNT(*), FLOOR(AVG(sal)), SUM(sal)
    FROM emp
    GROUP BY job, deptno

-- 10. 각 부서별 평균 월급, 전체 월급, 최고 월급, 최저 월급을
-- 구하여 평균 월급이 많은 순으로 출력하시오.
    SELECT FLOOR(AVG(sal)), SUM(sal), MAX(sal), MIN(sal)
    FROM EMP
    GROUP BY deptno
    ORDER BY FLOOR(AVG(sal)) DESC;

-- 11.  job이 CLERK이면서 급여가 $1100 이상인 사원의 사번,
-- 이름, 직위, 급여를 출력하시오.
    SELECT empno, ename, job, sal
    FROM EMP
    WHERE job = UPPER('clerk') AND sal >= 1100;

-- 12. 초기값 1, 증가값 1, 최소값 1, 최대값 9999인 TEST_SEQ 를
-- 생성하시오. 단, 시퀀스 번호는 순환되지 않으며,
-- 시퀀스 번호 추출시 한번에 10개씩 메모리에 캐시 되도록 한다.
	CREATE SEQUENCE test_seq
    	START WITH 1
        INCREMENT BY 1
        MINVALUE 1
        MAXVALUE 9999
        NOCYCLE
        CACHE 10;

-- 13. EMP 테이블의 외래키인 부서코드 칼럼에 인덱스를 작성하시오.
-- 단 인덱스의 이름은 I_EMP_DEPTNO 이다.
    CREATE INDEX I_emp_deptno
    ON emp(deptno);

-- 14. 부서(DEPT) 테이블에 대한 시노님 D 를 생성하시오.
    CREATE [PUBLIC] SYNONYM D
    FOR SCOTT.DEPT;

-- 15. 부서 10에서 부서 30의 사원과 같은 업무를 맡고 있는
-- 사원의 이름과 업무를 출력하는 SELECT 문을 작성하시오.
	SELECT ename, job
    FROM EMP
    WHERE deptno = 10 AND
          job IN (SELECT job FROM EMP
                  WHERE deptno = 30);

16. 부서 위치가 DALLAS인 모든 종업원에 대해 이름, 업무,
급여를 출력하는 SELECT 문을 작성하시오.
	SELECT ename, job, sal
    FROM EMP
    WHERE deptno = (SELECT deptno
                               FROM DEPT
                               WHERE loc = UPPER('dallas'));

-- 17. 다음의 일련의 명령어를 입력하시오.
-- 1) 아래의 구조를 만족하는 TEST_DEMO 테이블을 생성하시오.
    --    열이름 :           id          irum          userid          salary
    --    데이타유형 : NUMBER    VARCHAR2   VARCHAR2  NUMBER
    --    길이 :           4          10          30          10,2
	CREATE TABLE test_demo
    (
    	id	number(4),
        irum	VARCHAR2(10),
        userid	VARCHAR2(30),
        salary	NUMBER(10,2)
    );

-- 2) Data Dictionary에서 확인하시오.
   SELECT table_name FROM user_tables
    WHERE table_name = UPPER('test_demo');

-- 3)생성한  TEST_DEMO 테이블에 아래의 값을 입력하시오.
--      id          irum                 userid                 salary
--      1           SCOTT              sscott               10,000.00
--      2           FORD                fford                 13,000.00
--      3           PATEL               ppatel               33,000.00
--      4           REPORT            rreport              23,500.00
--      5           GOOD               ggood               44,450.00

	INSERT INTO test_dept
        VALUES (1,'SCOTT','sscott', 10000.00);


-- 4)테이블에서 id가 3번인 사람의 급여를 65,000.00으로 갱신하시오.
	UPDATE test_dept
        SET sal = 65000.00
        WHERE id = 3;

-- 5)이름이 FORD인 사원을 제명하시오.
	DELETE FROM test_dept
        WHERE irum = UPPER('ford');

-- 6)급여가 15,000 이하인 사람의 급여를 15,000으로 변경하시오.
	UPDATE test_dept
        SET sal = 15000
        WHERE sal <= 15000;

-- 7)테이블을 삭제하시오.
    DROP TABLE test_dept;

-- 18. EMP 테이블의 ename 컬럼에 UNIQUE 제약조건을 추가하시오.
-- 추가한 후 user_constaints 에서 확인하시오.
    ALTER TABLE EMP
    ADD CONSTRAINT emp_ename_uk UNIQUE(ename);

    SELECT table_name, constraint_name, constraint_type, status
    FROM user_constraints
    WHERE table_name = UPPER('emp');

-- 19. 다음의 시나리오를 완성하시오.
-- 1)EMP 테이블에서 PRIMARY KEY를 비활성화하는 SQL문을 작성하시오.
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name = UPPER('emp');

    ALTER TABLE EMP
    DISABLE CONSTRAINT PK_EMP;

   2)위 문제 17에서 생성한 TEST_DEMO 테이블의
   ID를 PRIMARY KEY 로 설정하는 SQL문을 작성하시오.
   	ALTER TABLE test_demp
    ADD CONSTRAINT test_demp_id_pk PRIMARY KEY(id);

   3)위 문제 17에서 생성한 TEST_DEMO 테이블에 다음과 같은 컬럼을 추가하시오.
--열이름 : job
-- 데이타유형 : 가변길이 문자
-- 길이 : 10
   	ALTER TABLE test_demo
    add (job	VARCHAR2(10));

-- 4)위 문제 17에서 생성한 TEST_DEMO 테이블의 irum 컬럼이
   사이즈를 20바이트로 늘리는 SQL 문을 작성하시오.
    ALTER TABLE test_demo
    MODIFY (irum VARCHAR2(20));

-- 5)위 문제 17에서 생성한 TEST_DEMO 테이블의 이름을
    TEST_SAMPLE 로 변경하시오.
    RENAME test_demo TO test_sample;

-- 20. 이름이 SCOTT 또는 MILLER인 사원의 사번, 이름,
-- 관리자 사원번호, 부서번호, 부서이름, 부서위치를 출력하시오.

    SELECT e.EMPNO, e.ename, e.MGR, e.DEPTNO, d.DNAME, d.LOC
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO AND e.ename IN ('SCOTT', 'MILLER')