SQL활용 평가

SQL(Structured Query Language)의 종류

데이터 정의(DDL), 데이터 조작(DML), 데이터 제어(DCL), 트랜잭션 제어어(TCL)

데이터 제어어(Data Control Language)

사용자에게 권한 생성 혹은 권한 삭제 명령어.
CREATE : 계정 생성
DROP : 계정 삭제
GRANT : 권한 생성
REVOKE : 권한 삭제

데이터 정의어(Data Definition Language)

데이터의 구조를 정의하기 위한 테이블 생성, 수정, 삭제 명령어.
CREATE : 테이블 생성
DROP : 테이블 삭제
ALTER : 테이블 수정
TRUNCATE : 테이블에 있는 모든 데이터 삭제
RENAME : 테이블명 변경

데이터 조작어(Data Manipulation Language)

데이터 추가, 조회, 수정 및 삭제를 위한 명령어.
-- commit 또는 rollback
SELECT : 데이터 조회
INSERT : 데이터 입력
UPDATE : 데이터 수정
DELETE : 데이터 삭제

트랜잭션 제어어(Transaction Control Language)

COMMIT : 적용하기
ROLLBACK : 되돌리기
SAVEPOINT : 저장점을 정의

---

** SELECT은 결과화면과 SQL문 작성하고, 나머지는 SQL문만 작성해서 제출합니다.

1. 사용자(guest03)를 생성하고 접속, 리소스, DBA 권한을 설정하시오. (암호: 1234)
   ** 직접 실행하지 않고 SQL 문만 작성합니다.

CREATE USER GUEST03 IDENTIFIED BY "1234";
GRANT CONNECT, RESOURCE, DBA TO GUEST03;

2. dept03(부서) 테이블을 다음과 같이 생성하시오. ( 제약조건 없음 )
   -- deptno (부서번호), 숫자
   -- dname (부서명) 가변 길이(7자) 문자

CREATE TABLE dept03 (
    dept NUMBER,
    dname VARCHAR2(7)
);

3. dept03(부서) 테이블에 area(지역)을 가변길이문자 20으로 추가하시오.

ALTER TABLE dept03 ADD area VARCHAR2(20);

4. dept03(부서) 테이블에 dname(부서명) not null과 문자 길이를 20으로 설정하시오.

ALTER TABLE dept03 MODIFY dname VARCHAR2(20);
ALTER TABLE dept03 MODIFY dname NOT NULL;

5. dept03(부서) 테이블에 deptno(부서번호)에 기본키 제약조건을 추가하시오.

ALTER TABLE dept03 ADD CONSTRAINT PK_DEPT03_NO PRIMARY KEY(deptno);

6. dept03(부서) 테이블에 dname(부서번호)에 고유값 제약조건을 설정하시오.

ALTER TABLE dept03 ADD CONSTRAINT UQ_DEPT01_DNAME UNIQUE(dname);

7. emp03(사원) 테이블을 다음과 같이 생성하시오.
   -- name (사원이름) 가변길이(10자) 문자
   -- empno (사원코드) 숫자, 기본키
   -- deptno (부서코드) 숫자, not null 제약 조건
   -- pempno (상사코드) 숫자
   -- pay (급여) 숫자, not null 제약 조건
   -- regdate (등록일) timestamp
   -- position (직급) 가변길이(10자) 문자, “사원, 대리, 과정, 차장, 부장” 값만 가능

CREATE TABLE emp03(
    empno NUMBER PRIMARY KEY,
    name VARCHAR2(10),
    deptno NUMBER NOT NULL, 
    pempno NUMBER,
    pay NUMBER NOT NULL,
    position VARCHAR(10),
    regdate TIMESTAMP,
    CONSTRAINT CK_EMP03_POSITION CHECK ( position IN ('사원','대리','과장', '차장','부장') )
);

8. emp03(사원) 테이블의 deptno(부서코드)에 dept03(부서) 테이블의 deptno(부서코드) 칼럼을 참조하는 외래키 제약 조건을 설정하시오.

ALTER TABLE EMP03 ADD CONSTRAINT FK_EMP03_DEPTNO FOREIGN KEY(deptno) REFERENCES DEPT03(deptno);

9. 시작 값 1001, 증가 값 1인 시퀀스를 생성하시오.
   ( 시퀀스명 : SEQ_EMP03_EMPNO )

CREATE SEQUENCE SEQ_EMP03_EMPNO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;

10. dept03(부서) 테이블과 emp03(사원) 테이블에 위의 예시와 같이 자료를 추가하시오.
    단, emp03(사원) 테이블의 empno(사원번호)는 위에 생성 한 시퀀스를 사용한다.

INSERT INTO DEPT03 ( DEPTNO, DNAME, AREA) VALUES (101,'영업부', '');
INSERT INTO DEPT03 ( DEPTNO, DNAME, AREA) VALUES (102,'총무부', '');
INSERT INTO DEPT03 ( DEPTNO, DNAME, AREA) VALUES (103,'기획부', '');
INSERT INTO DEPT03 ( DEPTNO, DNAME, AREA) VALUES (104,'홍보부', '');
COMMIT;

INSERT INTO EMP03(EMPNO, NAME, DEPTNO, PEMPNO, PAY, POSITION, REGDATE) VALUES (SEQ_EMP03_EMPNO.NEXTVAL, '가가가', 101, 1001, 200, '부장', CURRENT_DATE);
INSERT INTO EMP03(EMPNO, NAME, DEPTNO, PEMPNO, PAY, POSITION, REGDATE) VALUES (SEQ_EMP03_EMPNO.NEXTVAL, '나나나', 104, 1001, 200, '차장', CURRENT_DATE);
INSERT INTO EMP03(EMPNO, NAME, DEPTNO, PEMPNO, PAY, POSITION, REGDATE) VALUES (SEQ_EMP03_EMPNO.NEXTVAL, '다다다', 103, 1001, 200, '과장', CURRENT_DATE);
INSERT INTO EMP03(EMPNO, NAME, DEPTNO, PEMPNO, PAY, POSITION, REGDATE) VALUES (SEQ_EMP03_EMPNO.NEXTVAL, '라라라', 102, 1001, 200, '대리', CURRENT_DATE);
INSERT INTO EMP03(EMPNO, NAME, DEPTNO, PEMPNO, PAY, POSITION, REGDATE) VALUES (SEQ_EMP03_EMPNO.NEXTVAL, '마마마', 104, 1001, 200, '사원', CURRENT_DATE);
COMMIT;

11. dept03(부서) 테이블 area(지역)을 영업부는 “서울”, 나머지 “부산”으로 변경하시오.

UPDATE DEPT03 SET AREA =
    CASE 
        WHEN (DEPTNO=101) THEN '서울'
        ELSE '부산'
    END;
COMMIT;

12. emp03(사원) 테이블에서 deptno(부서번호)가 “101”인 직원의 데이터를 삭제하시오.
    ** 직접 실행하지 않고 SQL 문만 작성합니다.

DELETE FROM EMP03 WHERE DEPTNO=101;
COMMIT; 또는 ROLLBACK;

13. emp03(사원) 테이블에서 영업부 직원과 총무부 직원의 name(사원이름), position(직급)을 empno(사원코드)순으로 오름차순 조회하시오.

SELECT NAME 사원이름, POSITION 직급 FROM EMP03 WHERE DEPTNO IN (101, 102) ORDER BY EMPNO ASC;

14. emp03(사원) 테이블의 empno(사원코드), name(사원이름), regdate(등록일)의 날짜 포맷을 “년월일”만 표시되도록 모두 조회하시오.

SELECT E.EMPNO 사원코드, E.NAME 사원이름, TO_CHAR(E.REGDATE, 'YYYY-MM-DD')날짜 FROM EMP03 E

15. emp03(사원) 테이블에서 name(이름), pay(급여), tax(세금)을 조회하시오.
    단, 세금은 급여가 0-200이면 5%, 201-300이면 10%, 301-400 이면 15%,
    나머지는 20%로 지정한다. (CASE 사용)

SELECT  NAME 이름, PAY 급여, 
    CASE 
        WHEN (PAY >=0 AND PAY<=200) THEN PAY*0.05 
        WHEN (PAY >=201 AND PAY<=300) THEN PAY*0.1 
        WHEN (PAY >=201 AND PAY<=300) THEN PAY*0.15
        ELSE PAY*0.2
    END 세금 
FROM EMP03;

16. emp03(사원) 테이블에서 부서별 평균 급여과 인원수를 조회하시오.
    ( 부서번호, 급여 평균, 인원수 출력 )

SELECT DEPTNO, AVG(PAY) 평균급여, COUNT(*) 인원수 FROM EMP03 GROUP BY DEPTNO;

17. emp03(사원) 테이블에서 부서별 급여 평균이 300 이상인 사원에 대해 dept03(부서) 테이블과 조인하여 조회하시오. ( 부서번호, 부서명, 평균 급여 출력 )

SELECT D.DEPTNO 부서번호, DNAME 부서명 , AP 평균급여  FROM DEPT03 D,
( SELECT DEPTNO, AVG(PAY) AP FROM EMP03 GROUP BY DEPTNO HAVING AVG(PAY) > 300) E 
WHERE D.DEPTNO=E.DEPTNO; 

18. emp03(사원) 테이블에서 name(사원이름)에 인덱스를 설정하시오.
    ( 인덱스명 : INDEX_EMP03 )

CREATE INDEX INDEX_EMP03 ON emp03(name);

19. emp03(사원) 테이블과 dept03(부서) 테이블을 조인하여 empno(사원번호), name(사원이름), dname(부서명), pay(급여)를 조회하는 뷰(VIEW)를 생성하시오.
    ( 뷰명 : EMP03_VIEW )

CREATE OR REPLACE VIEW EMP03_VIEW AS 
SELECT E.EMPNO 사원번호, E.NAME 사원이름, D.DNAME 부서명, E.PAY 급여 
FROM EMP03 E, DEPT03 D WHERE E.DEPTNO=D.DEPTNO;

20. SEQ_EMP03_EMPNO 시퀀스의 다음 번호를 리턴 하는 PL/SQL 함수를 작성하시오.
    ( 함수명 : FUNC_GET_SEQ )

CREATE OR REPLACE FUNCTION FUNC_GET_SEQ RETURN NUMBER
IS
    V_SEQ NUMBER := 0;
BEGIN
    SELECT SEQ_EMP03_EMPNO.NEXTVAL INTO V_SEQ FROM DUAL;
    RETURN V_SEQ;
END;
/

-- 확인
SELECT FUNC_GET_REQ FROM DUAL;