[KH정보교육원] 24일차_SEQUENCE, DBMS 정리

2021.11.12(금)

🌿 SEQUENCE

• 자동으로 번호를 발생시켜주는 역할을 하는 객체
• 정수값을 자동으로 순차적으로 생성해줌
• 채번 시 사용 → 편리
  e.g. 회원 번호, 사번, 게시글 번호 등
• 생성 구문

CREATE SEQUENCE 시퀀스명 START WITH 시작숫자 -- 생략 가능, 처음 발생시킬 시작값 지정 
INCREMENT BY 증가값 -- 생략 가능, 몇 씩 증가시킬지 결정; DEFAULT = 1 
MAXVALUE -- 생략 가능, 최대값 지정; DEFAULT = 9,999,999,999,999,999,999,999,999,999 
MINVALUE -- 생략 가능, 최소값 지정; DEFAULT = 1 
CYCLE/NOCYCLE -- 생략 가능, 값 순환(MAX 넘어가면 MIN으로 돌아갈지) 여부; DEFAULT = N 
CACHE 바이트크기/NOCACHE -- 생략 가능, 캐시메모리 사용 여부; 기본값은 20BYTES

 

SEQUENCE에서의 CACHE MEMORY: 미리 발생할 값들을 생성해서 저장해두는 공간 → 매번 호출할 때 새로 번호를 생성하는 것은 비효율적이므로, 캐시메모리 공간에 미리 생성된 값들을 가져다 쓰게 해서 속도를 높임

 

접두사 또는 접미사(회사/팀마다 다름, 안 붙일 수도 있고)

• 테이블명: TB_
• 뷰명: VW_
• 시퀀스: SEQ_

• 시퀀스명.CURRVAL: 현재 시퀀스의 값, 마지막으로 성공적으로 발생된 NEXTVAL 값
  시퀀스명.NEXTVAL: 시퀀스 값을 증가시키고 증가된 시퀀스의 값, 기존의 시퀀스 값에서 INCREMENT BY 값만큼 증가된 값, 시퀀스명.CURRVAL + INCREMENT BY 값 → 시퀀스 객체에 NEXTVAL할 때마다 숫자가 나옴

SELECT SEQ_EMPNO.CURRVAL FROM DUAL; -- DUAL = 가상/DUMMY 테이블, 300
SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 305
SELECT * FROM USER_SEQUENCES; -- LAST_NUMBER = 310

SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 310
SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 지정한 MAXVALUE 값(310)을 초과했기 때문에 오류 발생

SELECT SEQ_EMPNO.CURRVAL FROM DUAL; -- 310 = 마지막에 성공적으로 수행된 NEXTVAL 값

• 시퀀스 변경(START WITH(시작값)는 변경 불가)

  ALTER SEQUENCE 시퀀스명
  INCREMENT BY 증가값
  MAXVALUE 최대값
  MINVALUE 최소값
  CYCLE/NOCYCLE
  CACHE 바이트크기/NOCACHE

🌿 문제 풀이

1. 개발팀의 실수로 사원들의 연락처가 유출되었습니다. 모든 사원들의 연락처 뒤 4자리를 '*'로 채우고(연락처가 없는 사람들은 고려하지 않음), 사번, 사원이름, 연락처, 부서명을 조회하는 쿼리문을 작성하시오.

SELECT EMP_ID 사번, EMP_NAME 사원이름, REPLACE(PHONE, SUBSTR(PHONE, -4, 4), '****') 연락처, DEPT_TITLE 부서명
-- SELECT EMP_ID 사번, EMP_NAME 사원이름, RPAD(SUBSTR(PHONE, 1, 7), 11, '*') 연락처, DEPT_TITLE 부서명
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

2. 연락처를 수정하다 보니, 011, 017 번호를 쓰는 직원들을 위해 최신 갤럭시 노트 10+를 회사 복지 차원으로 지급하기로 했다. 연락처가 '011', '017'로 시작하는 직원들의 사번, 사원명, 연락처, 부서명, 직급명을 조회하고, 연락처를 '010'으로 시작하는 번호로 수정하는 쿼리문을 작성하시오.

-- 나의 풀이1)
SELECT EMP_ID 사번, EMP_NAME 사원명, PHONE 기존연락처, REPLACE(PHONE, SUBSTR(PHONE, 1, 3), '010') 새연락처, DEPT_TITLE 부서명, JOB_NAME 직급명, SALARY 기존급여, SALARY - 30000 변경급여
FROM EMP_COPY_TEST2 E, DEPARTMENT D, JOB J
WHERE E.DEPT_CODE = D.DEPT_ID(+)
 AND E.JOB_CODE = J.JOB_CODE(+)
 AND SUBSTR(PHONE, 1, 3) IN ('011', '017');

-- 나의 풀이2)
SELECT EMP_ID 사번, EMP_NAME 사원명, PHONE 기존연락처, REPLACE(PHONE, SUBSTR(PHONE, 1, 3), '010') 새연락처, DEPT_TITLE 부서명, JOB_NAME 직급명, SALARY 기존급여, SALARY - 30000 변경급여
FROM EMP_COPY_TEST2 E, DEPARTMENT D, JOB J
WHERE E.DEPT_CODE = D.DEPT_ID(+)
 AND E.JOB_CODE = J.JOB_CODE(+)
 AND SUBSTR(PHONE, 3, 1) != '0';

-- 강사님 풀이
SELECT EMP_ID 사번, EMP_NAME 사원명, PHONE 연락처, DEPT_TITLE 부서명, JOB_NAME 직급명
FROM EMP_COPY_TEST2
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN JOB USING (JOB_CODE)
WHERE SUBSTR(PHONE, 1, 3) IN ('011', '017');

UPDATE EMPLOYEE
SET PHONE = '0103654485'
WHERE SUBSTR(PHONE, 1, 3) = '011';

UPDATE EMPLOYEE
SET PHONE = '0109964233'
WHERE SUBSTR(PHONE, 1, 3) = '017';

3. 근속연수별 사원들의 연봉통계를 내고자 한다. EMP_COPY_TEST3에서 근속연수별 사원들의 근속연수가 10년 이상인 사원들의 근속연수별 평균급여와 최고급여를 구하여, 근속연수별 내림차순으로 정렬하시오.

-- 나의 풀이1) '중간에 퇴사한 사람의 근속연수 = 퇴사연도 - 입사연도'로 계산
SELECT 근속연수, AVG(SALARY), MAX(SALARY)
FROM (SELECT SALARY, CASE WHEN ENT_YN = 'N' THEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
                          WHEN ENT_YN = 'Y' THEN EXTRACT(YEAR FROM ENT_DATE) - EXTRACT(YEAR FROM HIRE_DATE)
                     END 근속연수
      FROM EMP_COPY_TEST3)
GROUP BY 근속연수
HAVING 근속연수 >= 10
ORDER BY 근속연수 DESC;

-- 강사님 풀이)
SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE),
        AVG(SALARY),
        MAX(SALARY)
FROM EMPLOYEE
GROUP BY EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
HAVING EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) >= 10
ORDER BY 1 DESC;

-- 강사님 풀이 듣고 난 뒤 나의 풀이2) 중간에 퇴사한 사람은 집계에서 제외
SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) 근속연수, AVG(SALARY), MAX(SALARY)
FROM EMPLOYEE
WHERE ENT_YN = 'N' -- 강사님 지도 = 중간에 퇴사한 사람은 HAVING절에서 제외시키기 -> HAVING절에 어떻게 써야할지 모르겠음
GROUP BY (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)) -- GROUP BY절에 들어가는 값에는 별명 못 붙이는 건가?
HAVING EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) >= 10
ORDER BY 1 DESC;

4. 기술지원부의 인원이 부족하여 부서가 없는 직원들을 기술지원부로 데려오기로 결정하였다. 부서가 없는 직원들을 기술지원부로 이동한 후, 기술지원부의 사번, 사원명, 급여, 직급명, 부서명을 조회하시오.

CREATE TABLE EMP_COPY_TEST4
AS (SELECT * FROM EMPLOYEE LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID));

UPDATE EMP_COPY_TEST4 SET DEPT_TITLE = '기술지원부' WHERE DEPT_TITLE IS NULL; -- 4개 행이 업데이트됨

SELECT EMP_ID, EMP_NAME, SALARY, JOB_NAME, DEPT_TITLE
FROM EMP_COPY_TEST4
LEFT JOIN JOB USING (JOB_CODE)
WHERE DEPT_TITLE = '기술지원부';

📗 homework:

-- 2021.11.12(금) 시험 대비 문제 풀기
-- 1번 문제) 매니저 번호 별로 그룹화하여 해당 매니저의 사번, 사원명, 휘하 사원들의 급여합계를 조회하시오.
SELECT NVL(E.MANAGER_ID, '매니저 없음') "매니저 사번", NVL(M.EMP_NAME, '해당사항 없음') "매니저 이름", SUM(E.SALARY) "휘하사원 급여 합계", COUNT(E.EMP_NAME) "휘하사원 수"
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID(+)
GROUP BY E.MANAGER_ID, M.EMP_NAME;

-- 참고: 사원 조회 테이블과 사수 조회 테이블 동등JOIN의 RESULT SET
SELECT *
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID(+);

-- 참고: EMPLOYEE 테이블 전체 컬럼을 MANAGER_ID 순서대로(올림차순) 정렬
SELECT *
FROM EMPLOYEE
ORDER BY MANAGER_ID;

-- 2번 문제) 부서별 근속년수가 가장 오래된 사원을 찾아 해당 사원의 사번, 사원명, 부서명, 직급명, 입사일을 조회하시오.
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN JOB USING (JOB_CODE)
WHERE ENT_YN = 'N'
 AND EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) IN (SELECT MAX(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)) FROM EMPLOYEE GROUP BY DEPT_CODE);

-- 3번 문제) 사내 생일파티가 이벤트! 이번 생일파티는 3,4,5월생들만 진행한다.
-- 이번 생일파티 주인공들의 사번, 사원명, 주민번호, 부서명, 직급명, 근무지명을 조회하시오. 단, 주민번호는 앞자리만 공개한다. 뒷자리는 모두 *처리
SELECT EMP_ID 사번, EMP_NAME 사원명, RPAD(SUBSTR(EMP_NO, 1, 7), 14, '*') 주민번호, DEPT_TITLE 부서명, JOB_NAME 직급명, LOCAL_NAME 근무지명
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN JOB USING (JOB_CODE)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE SUBSTR(EMP_NO, 3, 2) IN ('03', '04', '05')
ORDER BY SUBSTR(EMP_NO, 3, 4); 

-- 4번 문제) 회사에서 직원들의 사기충전을 위해 작은 이벤트! 곧 있을 회사창립 30주년을 기념하여 주민등록번호에 3과 0이 들어가는 직원에게 소정의 선물을 증정하기로했다.
-- 주민등록번호에 3과 0이 들어가는 직원의 정보를 조회하시오.
SELECT *
FROM EMPLOYEE
WHERE EMP_NO LIKE '%3%' AND EMP_NO LIKE '%0%';

-- 5번 문제) 각 사원별 시급을 계산하여 사원번호, 사원이름, 시급을 조회시오.
-- 조건1: 한 달 근무일 수는 25일, 하루 근무시간은 9시간이다.
-- 조건2: 소수점 자리는 없애고 맨 뒷 자리수가 0으로 나타나게끔!
-- 조건3: 시급이 높은 순으로 정렬
SELECT EMP_ID 사원번호, EMP_NAME 사원이름, CONCAT(TO_CHAR(ROUND(NVL(SALARY, 9160 * 25 * 9) / (25 * 9) , -1), '999,999'), '원') 시급
FROM EMPLOYEE
ORDER BY 3 DESC;