국비 지원 개발자 과정_Day29

<시험1-데이터베이스 구현 문제해결시나리오>

1-1. 일반 사용자 계정이 아닌 sys as sysdba 계정으로 접속해야한다.

1-2. 아이디를 만들때의 sql문이 틀렸다.

2-1. 사용자는 만들었지만 접속 권한을 주지는 않았다.

 

sqlplus “/as sysdba”;

create user SAMPLE identified by 1234;

grant connect, resource to SAMPLE;

connect: DB연결 권한

resource: 객체 생성, 변경, 제거, 데이터 입력, 수정, 조회, 삭제 권한

 

 

1. AND연산자가 OR연산자보가 우선순위가 높기에 ()를 넣어서 우선연산처리를 해야한다.

2. 연봉이 300만원 이상이기에 >를 >=로 바꿔야한다.

3. 남자라는 조건을 넣어야한다.

4. _가 들어간 문자를 검색하기위해선 ESCAPE를 사용해야한다.

5. BONUS가 있어야하기에 IS NOT NULL로 바꿔줘야한다.

SELECT EMP_NAME, EMP_NO, DEPT_CODE, SALARY
  FROM EMPLOYEE
  WHERE (DEPT_CODE = 'D9' OR DEPT_CODE = 'D6')
AND SALARY >= 3000000 
AND SUBSTR (EMP_NO, 8, 1) = 1
AND EMAIL LIKE '___#_%' ESCAPE '#'
AND BONUS IS NOT NULL;

 

 

NULL은 비교연산자를 사용할 수 없기에 IS NULL 혹은 IS NOT NULL로 비교해야한다.

SELECT * FROM EMPLOYEE
  WHERE BONUS IS NULL
AND MANAGER_ID IS NOT NULL;

 

 

<시험2-데이터베이스 구현 서술형(신)>

DBMS에서 사용하는 DML 중 INSERT 문에 대해 서술

INSERT는 테이블에 데이터(행)을 추가할 때 사용하기 위한 SQL문이다.

 

오라클에서 테이블 컬럼의 자료형을 CHAR(100 CHAR)이라고 선언했을 때 의미

CHAR 데이터 타입은 고정된 문자열을 저장하는데 사용하기에 고정된 길이를 지정해줘한다.

예시에서는 100CHAR로 선언했고, 고정된 길이인 100CHAR(100개의 글자)보다 작은 길이의 문자를 넣어도 남은 부분을 공백으로 채운다.

 

NVL함수에 대해 설명하고 EMP테이블에 BONUS컬럼에 NULL값이 있을 때 BONUS컬럼의 NULL값을 0으로 만들어 출력

NVL함수는 값이 NULL인 경우 지정값을 출력하고, NULL이 아니면 원래 값을 그대로 출력한다.

SELECT NVL(BONUS, 0)
FROM EMP;

 

NOT NULL 제약조건

NOT NULL 제약조건을 명시하게되면 해당 컬럼에는 반드시 데이터값을 입력해야하고 NULL을 허용하지 않는다.

반드시 입력 또는 수정해야 할 컬럼에 NOT NULL을 명시해서 제약조건을 추가하는 것이 좋다.

 

CHAR 자료형과 VARCHAR2 자료형의 차이점

CHAR의 최대 길이는 2000byte이고 고정길이 문자열 데이터 타입이다.

항상 지정된 길이로 저장되기에 고정길이보다 짧은 문자열을 저장하더라도 나머지 공간을 공백으로 채운 뒤 저장된다.

VARCHAR2의 최대 길이는 4000byte이고, 가변길이 문자열 데이터 타입이다.

가변길이 문자열이기에 주어진 데이터 값만큼 저장된다.

 

EMP 테이블에서 사원의 이름(EMP_NAME), 입사일(HIRE_DATE), 근무 개월수를 조회하는 SELECT문

SELECT EMP_NAME AS "사원 이름",
HIRE_DATE AS "입사일",
MONTHS_BETWEEN (SYSDATE, HIRE_DATE) AS "근무 개월 수"
FROM EMP;

 

테이블을 생성 시 부여할 수 있는 제약조건 중에서 PRIMARY KEY, UNIQUE, NOT NULL의 정의와 제약조건 지정방식(테이블,컬럼)

PRIMARY KEY

NULL과 중복된 값을 허용하지 않는다.

CREATE TABLE 테이블명 (
컬럼명 데이터타입, CONSTRAINT 제약조건명 PRIMARY KEY (컬럼명));

UNIQUE

테이블 내에서 해당 컬럼 값은 항상 유일한 값을 가진다.

CREATE TABLE 테이블명 (
컬럼명, 데이터타입, CONSTRAINT 제약조건명 UNIQUE (컬럼명));

NOT NULL

해당 컬럼 값으로 NULL을 허용하지 않는다.

CREATE TABLE 테이블명 (
컬럼명 데이터타입 NOT NULL);

 

직급코드가 J7인 직원은 급여의 8%를 인상하고, 직급코드가 J6인 직원은 급여의 7%를 인상하고, 직급코드가 J5인 직원은 급여의 5%를 인상한다. 그 외 직급의 직원은 3%만 인상

직원 테이블(EMP)에서 직원명(EMPNAME), 직급코드(JOBCODE), 급여(SALARY), 인상급여(위 조건)를 조회

(단, DECODE를 이용해서 출력하시오.)

SELECT EMPNAME AS "직원명",
JOBCODE AS "직급코드",
SALARY AS "급여",
DECODE (JOBCODE,
'J7', SALARY * 1.08,
'J6', SALARY * 1.07,
'J5', SALARY * 1.05,
SALARY * 1.03)
AS "인상급여"
FROM EMP;

 

---

 

/* Formatted on 2023/01/05 오후 4:32:15 (QP5 v5.215.12089.38647) */
--/*+ */는 힌트문이라고 한다
--힌트문은 개발자가 옵티마이저에게 실행에 대한 요청이나 생각을 전달할 수 있는 문잡
--만일 힌트물에 오타나 오류가 있더라도 에러가 발생하지는 않음, 다만 무시할 뿐
--옵티마이저에게 내 생각을 전달할 때 사용한다고 생각하면 됨
--아래에서 사원번호 최대값을 찾아야하는데 다행히 empno가 pk이기에 인덱스가 제공되고
--order by를 쓰지 않더라도 정렬이 일어남
--SELECT ename FROM emp는 정렬이 되지 않는 것이 그 이유

--인덱스가 있는 경우에는 인덱스 이름이 있는데 이것을 이용해서 힌트문으로 옵티마이저에게
--개발자의 실행계획이나 의도를 전달항 수 있는 것

--인덱스의 경우 디폴트가 오름차순으로 정렬되어 있는데
--힌트문을 이용해서 인덱스 access시에 내림차순으로 읽어올 것을 요청하는 것
--그러나 무시될 수도 있다는 것이 함정
--옵티마이저의 동작 원리에는
--rule base 옵티마이저 모드와 -> 15규칙 순서에 따라 실행계획을 가져가는 방식
--        : 수동카메라, 개발자가 본인의 의도대로 조작이 가능함
--cost base 옵티마이저 모드가 있음 -> 데이터의 분포도와 통계자료를 바탕으로 실행계획을 가져가는 방식
--        : 데이터의 분포도가 최신인가가 중요함 -> 자동카메라, 효과적인 실행계획을 가져갈 확률이 높다

SELECT empno FROM emp;

SELECT ename FROM emp;

SELECT /*+index_desc(emp pk_emp) */
      empno FROM emp;

SELECT /*+index_desc(emp pk_emp) */
      empno
  FROM emp
 WHERE ROWNUM = 1;

SELECT /*+index_desc(emp pk_emp) */
      empno, (empno + 1)
  FROM emp
 WHERE ROWNUM = 1;

  SELECT empno
    FROM emp
ORDER BY empno DESC;

SELECT empno
  FROM (  SELECT empno
            FROM emp
        ORDER BY empno DESC)
 WHERE ROWNUM = 1;

SELECT empno + 1
  FROM (  SELECT empno
            FROM emp
        ORDER BY empno DESC)
 WHERE ROWNUM = 1;

SELECT /*+all_rows*/
      empno, ename, dname
  FROM emp, dept
 WHERE emp.deptno = dept.deptno;

--회원과 상품의 관계형태는 n:n이다
SELECT * FROM t_giftmem;

SELECT * FROM t_giftpoint;

--문제1
--영화 티켓을 받을 수 있는 사람의 명단과 현재 가지고 있는 포인트, 영화 티켓의 포인트
--그리고 그 티켓을 사용한 후 남은 예상 포인트를 출력하시오.
SELECT mem.name_vc AS "회원명",
       mem.point_nu AS "보유포인트",
       poi.point_nu AS "영화티켓 포인트",
       TO_CHAR (mem.point_nu - poi.point_nu, '999,999') || '점'
          AS "잔여포인트"
  FROM (SELECT point_nu
          FROM t_giftpoint
         WHERE name_vc = '영화티켓') poi,
       t_giftmem mem
 WHERE mem.point_nu >= poi.point_nu;

SELECT mem.name_vc AS "회원명",
       mem.point_nu AS "보유포인트",
       poi.point_nu AS "영화티켓 포인트",
       TO_CHAR (mem.point_nu - poi.point_nu, '999,999') || '점'
          AS "잔여포인트"
  FROM t_giftpoint poi, t_giftmem mem
 WHERE mem.point_nu >= poi.point_nu AND poi.name_vc = '영화티켓';

--문제2
--김유신씨가 보유하고 있는 마일리지 포인트로 얻을 수 있는 상품 중 가장 포인트가 높은 것은
--무엇인가?
SELECT poi.name_vc, mem.name_vc, poi.point_nu
  FROM t_giftpoint poi, t_giftmem mem
 WHERE mem.name_vc = '김유신' AND poi.point_nu <= mem.point_nu;

SELECT name_vc, point_nu
  FROM t_giftpoint poi
 WHERE poi.point_nu =
          (SELECT MAX (poi.point_nu)
             FROM t_giftpoint poi, t_giftmem mem
            WHERE mem.name_vc = '김현식' AND poi.point_nu <= mem.point_nu);

 

그룹함수(복수행함수)

하나 이상의 행을 그룹화하여 그룹별로 결과를 출력하는 함수

COUNT, MAX, MIN, SUM, AVG 등이 있음

그룹함수는 전체범위 처리를 함

 

전체범위처리

사용자가 요구하는 데이터에대한 전체 블록의 액세스가 모두 끝난 후 데이터를 반환하는 방식

GROUP BY나 ORDER BY는 특정한 범위의 데이터를 추출하기위해 정렬 후 계산 또는 정렬 결과를 리턴한다.

속도가 느림

 

부분범위처리

조건에 맞는 데이터를 추출함과 동시에 사용자에게 데이터를 리턴하는 방식

2차 가공이 필요없이 데이터 운반단위에 데이터가 차는대로 사용자에게 리턴

속도가 빠름

 

ROWID

테이블에서 행의 위치를 정하는 논리적인 주소값

DB전체에서 중복되지 않는 유일한 값으로 테이블에 새로운 행이 삽입되면 테이블 내부에서 의사 컬럼 형태로 자동적으로 생성됨

테이블의 특정 레코드를 랜덤하게 접근하기 위해서 주로 사용

총 18글자로 데이터파일 번호, 블록 번호, 테이블 레코드의 물리적 위치정보 등을 담고있음

 

ROWID 예시 AAAArs AAD AAAAUa AAA

데이터 오브젝트 번호(6자리)

데이터베이스 세그먼트를 식별하기 위해 사용되는 데이터 오브젝트 번호

 

상대적 파일 번호 (3자리)

로우가 속한 데이터파일 번호, 테이블스페이스 내에서의 상대적인 파일 번호

블록번호 (6자리)

해당 로우가 저장된 데이터 블록 번호, 테이블스페이스가 아닌 데이터파일 내에서의 상대적 번호

블록 내의 행 번호 (3자리)

블록 내에서 각 로우에 붙여진 일련번호로서, 0부터 시작