[210527목] 오라클 자체조인 복습, 자바와 오라클 연동하기

레아Leah 2021. 5. 27. 09:00

오라클 역시 영어독해와 같이 문제? 질문을 끊어서 파악하면 풀기 쉽다.

관계형 데이터의 무결성 유지를 위해 정규화(쪼개고), 원하는 데이터를 가져오기 위해 조인을 한다.

다이어그램(ER 다이어그램): 테이블의 관계를 알려주는 표

테이블에 대한 전체적인 INSIGHT를 알고 있어야 한다. 왜냐하면 FK의 이름이 테이블을 만든 사람에 따라 달라질 수 있기때문에 동일한 데이터를 의미하더라도 FK명이 달라, FK로 인식하기 어려운 경우도 있기 때문이다.

테이블 employees에서는 PK는 EMPLOYEE_ID이고, FK로 JOB_ID, MANAGER_ID, DEPARTMENT_ID가 있다.

[연습문제 풀이]

1번) 업무의 종류가 CLERK인 사람들중에서 근속년수가 12년인 직원들에게 상여금 10%를 지급하는 쿼리문을 작성하세요. (이름, 입사일, 근속년수, 부서명, 업무명, 급여, 특별상여)

오라클에서 substring을 사용하고자 한다면, 자바와 다르게 인덱스가 1부터 시작한다는 사실을 알아야 한다.

select substr(sysdate, 1, 2) from dual;

substr(sysdate, 1, 2) - substr(e.hire_date, 1, 2)

sysdate, hire_date 모두 날짜 테이터이므로 굳이 to_char로 변환할 필요가 없다.

select e.first_name 이름, e.hire_date 입사일, substr(sysdate, 1,2)- substr(e.hire_date,1,2) 근속년수, d.department_name 부서명, j.job_title 직책, e.salary 급여, e.salary*0.1 특별상여
from employees e, departments d, jobs j
where
    e.job_id like '%CLERK%' and
    e.job_id = j.job_id and
    e.department_id = d.department_id and
    substr(sysdate,1,2)-substr(e.hire_date,1,2) >= 12;

3번) 사원번호, 근무기간, 업무명을 조회 예) Smith는 5년 근무한 IT_PROG이다.

★조인하고자 하는 테이블에 FK가 없어 관계가 없다고 하면 그 두 테이블의 관계를 연결해주는 다른 테이블의 PK, FK를 이용해 연결한다.

몇 년 근무했는지 궁금하다면, 몇 일을 근무했는지부터 (마지막날- 첫날)로 구해준다.

근속년수 추출하기

: (js.end_date -js.start_date)/365

: 마지막 근무날짜에서 시작날짜를 빼면 근무한 일수가 나오고 그 값을 365일로 나눠주면 몇 일 근무했는지 알 수 있다.

년을 제외한 일수를 버리기 위해 FLOOR함수 혹은 TRUNC 함수를 사용해준다.

FLOOR -1.5는 -2가 되버림. 왼쪽 방향으로 가장 가까운 정수를 찾는 함수

소수점 이하의 자리를 구하기 위해 5.5-5=0.5를 구할 수 있듯이

((JS.END_DATE - js.start_date)/365 - TRUNC((JS.END_DATE -js.start_date)/365)) 해서 소수점 이하 한 자리수를 구한다.

소수점 이하는 개월 수를 의미한다. (년은 10진법, 개월은 12진법이기 때문에 정확하지 않다.)

지워져서 다시 해서 넣기

select e.first_name ||'은 '|| trunc((js.end_date- js.start_date)/365)||'년 '||
        trunc(((js.end_date- js.start_date)/365 -trunc((js.end_date- js.start_date)/365))*10) || '개월 근무한 '
        ||js.job_id||'이다.'
from
    employees e, job_history js, departments d
where
    e.employee_id = js.employee_id
    and
    js.department_id = d.department_id;

[자체조인 복습]

▶자체조인

: 물리적으로 하나의 테이블 안에 PK, FK를 다 같이 가지고 있는 형태, 하나의 테이블 안에서 조인이 일어나는 형국이다.

컬럼명을 줄 때는 "스페이스 공백이 있는 경우", '데이터가 있는 경우'에는 ' '를 사용한다. ' '는 데이터로 인식하기 때문이다.

EMPLOYEES E1(EMPLOYEE_ID), EMPLOYEES E2(MANAGER_ID)

SELECT
    E1.FIRST_NAME 직원명, E2.FIRST_NAME 매니저명
FROM
    EMPLOYEES E1, EMPLOYEES E2
WHERE
    E1.MANAGER_ID = E2.EMPLOYEE_ID
ORDER BY e1.employee_id;

[데이터 모델링]

▶개념적 모델링

: 어떤 컬럼이 필요한지, 데이터를 수집하는 작업(서술하듯이 이름, 나이, 생년월일 등등)

▶논리적 모델링(정규화→ JOIN → 역정규화)

: 컬럼명을 설정하고 데이터 타입을 선택(팀원들이 이해하기 쉽도록)

: 정규화로 인해 조인이 너무 많아 성능이 낮아진다면 다시 역정규화한다.

: (정규화→ JOIN → 역정규화)이 잘 이루어져있어야 물리적 모델링 시 작업이 편하다.

: Ex) name 문자열(20)

▶물리적 모델링

: 어떤 DBMS를 사용할지 결정해야 물리적 모델링을 시작할 수 있다.

: Ex) name varchar(100)

위의 작업이 끝나면 Application(프로그램)과 DB와 연결한다.