Oracle/SQL - DB /DATABASE / JOIN

1030수업내용_

 

 # 기본키 (Primary Key, PK)

    - 한 테이블에서 하나의 행을 유일하게 구분할 수 있는 컬럼

    - 각 테이블의 기본키는 하나만 설정할 수 있다..

    - 기본키로 설정된 컬럼에는 중복값이 없어야 한다. (UNIQUE)

    - 기본키로 설정된 컬럼에는 NULL이 없어야 한다. (NOT NULL)

 

    # 후보키 (Candidate Key)

    - 기본키가 될 자격이 있지만 기본키로 설정되지 않은 컬럼

   

  # 외래키 (Foreign Key)

    - 다른 테이블에서는 기본키(또는 후보키)이지만,

      현재 테이블에서는 중복되는 값을 지닌 일반 컬럼인 경우

    - 외래키 컬럼에는 참조하는

      기본키(또는 후보키)컬럼에 없는 값이 존재해서는 안된다.(참조 무결성)

    - 어떤 테이블의 기본키(또는 후보키)가 다른 테이블의 외래키로 설정되었다면

      두 테이블 간에는 1:N(일대다관계) 관계가 형성되었다고 할 수 있다.

      ex> 한 부서에 여러 사원이 소속될 수 있다.

      (부서 테이블의 기본키가 사원테이블의 외래키)

 

 

 

SELECT * FROM employees; -- employees의 department_id는 외래키

SELECT * FROM departments; -- departments의 department_id는 기본키

SELECT * FROM jobs; -- job_id 기본키

 

 

    # 개체 간 관계의 종류

    - 1 : 1 : 같은 테이블 안에 컬럼으로 넣는다

    - 1 : N : 부서 - 사원, 게시글 - 댓글, 회원 - 게시글,

    - N : N : 학원 - 학생, 선생 - 학생, 과목 - 교수, 렌트카 - 대여자

 

    # JOIN

    - 기본키와 외래키로 엮어 관계가 형성되어 있는 두 테이블의 정보를 종합하여 원하는 정보를 만들어 조회하는 것

     

    # CROSS JOIN

    - JOIN에 사용되는 테이블들의 모든 행을 조합하여 나올 수 있는 모든 경우를 출력하는  JOIN

    - 모든 행을 조합한(모든 경우의 수를 보여주는) 쓸모없는 정보

 

SELECT * FROM employees; -- 107rows

SELECT * FROM departments; -- 27rows

SELECT * FROM employees, departments; -- 2889rows (107 * 27), cross join

 

오류 >

SELECT

    employees_id,

    first_name,

    last_name,

    department_id,

    department_name

FROM

    employees,

    departments; --  "column ambiguously defined" 53행, 5열에서 오류 발생

 

 

#  cross join을 하면 두 테이블에 존재하는 모든 컬럼을 조회할 수 있다

    같은 이름의 컬럼이 존재하는 경우 반드시 어느 테이블 소속인지 명확하게 해야 한다. #

 

해결 >

 

SELECT

    employee_id,

    first_name,

    last_name,

    employees.department_id AS "emp_dept_id",

    departments.department_name AS "dept_dept_id"

FROM

    employees,

    departments;

 

 

 

## 테이블명이 길어질 때 테이블 이름에도 별칭을 지정할 수 있다.

   employees emp,  departments dept; ==> 여기의  emp, dept가 별칭이 되는 것

 

 

 EQUI JOIN

    - 두 테이블에서 서로 동일한 값을 지닌 컬럼(주로 기본키, 외래키) 들을,외래키) 이용하여 CROSS JOIN으로부터 의미 있는 데이터들만 걸러내는 JOIN

 

SELECT * FROM employees emp, departments dept

WHERE emp.department_id = dept.department_id

ORDER BY employee_id ASC;

 

SELECT employee_id, first_name, department_name

FROM employees emp, departments dept

WHERE emp.department_id = dept.department_id

ORDER BY employee_id ASC;

 

 

 

-- 연습 1 > 모든 사원들의 사원번호/이름/직책이름을/이름/ 조회해 보세요

==>

 

-- 연습 2 > job_title이 Programmer인 사원들의 사원번호/이름/부서번호/부서명 조회해 보세요

==>

 

-- 연습 3> 커미션을 받는 사원들의 직책이름(job_title)에 어떤 것이 있는지 조회해 보세요

==> 

※오류 : 직책이름 1가지씩만 나와야하는데, 이렇게 많은 정보는 필요가 없다.

DISTINCT 사용을 자꾸 잊어버리는 것 같다…

 

--- >> 답 :

SELECT DISTINCT job_title FROM employees e, jobs j

WHERE e.job_id = j.job_id AND e.commission_pct IS NOT NULL;

 

-- 연습 4> Seattle에서 근무하는 사원들의 이름/월급/부서명/우편번호 조회

==> 

SELECT emp.first_name, emp.salary, dept.department_name, loca.postal_code

FROM locations loca, employees emp, departments dept

WHERE emp.department_id = dept.department_id

AND loca.city = 'Seattle';

-- postalcode 98199로 다 동일하게 나오는 오류

 

 

-- 선생님 해설 > 순서

SELECT * FROM employees e, departments d, locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id; -

 

SELECT * FROM employees e, departments d, locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id

AND city = 'Seattle';

 

 

SELECT first_name, salary, department_name, postal_code

FROM employees e, departments d, locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id

AND city = 'Seattle';

 

 

 

 

 

생각해 보기

 

  1> 사원과 부서처럼 1:N관계로 설정할 수 있는 실제 사례를 하나 생각해 보기

   

    2> 1:N 관계로 설정한 각 개체를 테이블 형태로 설계해 보세요

    - 테이블이름

    - 컬럼명

    - 각 컬럼의 타입

    - 기본키 컬럼

    - 왜래키 컬럼