6.Database 조인

/*
    # 기본키 (Primary Key, PK)
    
     - 한 테이블에서 하나의 행을 유일하게 구분할 수 있는 컬럼 (중복이 되지 않아야함)
     - 한 테이블 당 하나 밖에 설정할 수 없다
     - 기본키가 될 수 있는 컬럼이지만 기본키로 설정되지 않은 컬럼은
        후보(Candidate Key)' 라고 한다
     - 기본키로 설정된 컬럼에는 중복된 값을 넣을 수 없고 (UNIQUE)
       비어있는 값도 허용하지 않는다 (not null)
       
     # 외래키 (Foreign Key, FK)
        - 다른 테이블에서는 기본키이지만 다른데로 간다면 그냥 값들중 하나가 돼버린다.
        - 다른 테이블에서는 기본키 또는 후보키(또는 중복값이 없는 컬럼)
          해당 테이블에서는 중복되는 값인 컬럼
          ex: employees의 department_id는 employees에서는 중복을 허용하는 컬럼이지만
          departments 테이블에서는 기본키로 사용되고있는 컬럼이다.
        - 외래키가 설정된 테이블끼리는 관계가 형성 된다
        - 외래키로 지정된 컬럼에는 반드시 참조하는 컬럼에 이미 존재하는 값만 추가할 수 있따
        -- 임플로이스에 디파트먼트 아이디에는 777을 추가할수없음 왜냐면 디파트먼트에는 777의 값이 없기때문에
*/
/*
    # 1:n 관계
    
     - 하나의 부서에 여러명의 사원이 소속될 수 있다
     - 하나의 제조 회사가 여러개의 제품을 제조한다 // 겔럭시 붙은건 삼성에서만 만듬
    
    # n:n 관계
    
     - 하나의 제품에 여러개의 부품이 조립된다. 하지만 부품은 다른 제품에도 사용될 수 있다. // 삼성에서 티비를 만든다 치면 다른데서도 만들수있음
     - 하나의 강아지에게 여러가지의 매력이 있다..
       하지만 다른 강아지도 같은 매력을 가지고 있을 수 있다.
*/

/*
    # 테이블 JOIN
    
     - 기본키와 외래키를 통해 관계가 형성되어 있는 각 테이블들의 정보를
       종합하여 조회하는 것
       
    # CROSS JOIN
    
        - 조인에 사용되는 테이블들의 모든 행을 조합하여 나올 수 있는 모든 경우를 출력하는 JOIN
        - 그냥 모든 경우를 출력하는 쓸모없는 JOIN 이다
*/
SELECT * FROM employees, departments ORDER BY employee_id; 
SELECT * FROM employees; -- 107 ROWS
SELECT * FROM departments; -- 27 ROWS

SELECT * FROM employees, departments, jobs ORDER BY employee_id;

모든 경우의 수를 곱해줌 모든 사원이 속할수 있는 경우들을 다 출력해준다 CROSS JOIN의 예 이다

/*
     # EQUI JOIN
     
      - 두 테이블 간에 서로 동일한 값을 지닌 컴럼을 이용하여
        CROSS JOIN에서 의미있는 결과만 남기는 JOIN
*/

SELECT * FROM 
    employees, departments 
WHERE 
    employees.department_id = departments.department_id AND first_name LIKE 'A%';

SELECT * FROM      employees, departments  WHERE      employees.department_id = departments.department_id AND first_name LIKE 'A%';      employees테이블과 departments 테이블에 department_id가 같으면서 first_name이 A로 시작하는 사람들을 출력

--연습1: first_name이 H로 시작하는 사원들의 사원번호/이름/부서명을 조회해보세요
SELECT  
    departments.department_id, first_name,  department_name
FROM 
    employees,departments 
WHERE 
    employees.department_id = departments.department_id
AND
    first_name LIKE 'H%';

--연습2: job_title에 stock이 포함되는 사원들의 그룹별 평균 월급을 조회해보세여(그룹명 ,그룹별 평균월급 조회) SELECT*FROM jobs;
SELECT 
    job_title,avg(salary)  --employees.job_id
FROM 
    employees e, jobs j
WHERE 
    j.job_id = e.job_id 
AND 
    job_title LIKE 'Stock%' GROUP BY job_title;

--연습3: 시애틀에서 근무하는 모든 사원의 이름과 직책과 부서명을 출력해보세요 SELECT * FROM locations;
SELECT 
    locations.city, first_name, job_title, department_name 
FROM
    employees, locations, departments,jobs
WHERE 
    employees.DEPARTMENT_id = departments.department_id
AND
    departments.location_id = locations.location_id
AND
    employees.job_id = jobs.job_id
AND 
    locations.city LIKE 'Seattle';

/*
    # SELF JOIN
    
     - 하나의 테이블 내에서 자기 자신과 JOIN하여 원하는 데이터를 얻어내는 조인 방식
     - 외래키가 자기 테이블의 기본키 컬럼을 외래키로 참조하는 경우.
     - ex: employees의 manager_id는 직장 상사의 사원번호를 저장하는 외래키이다.
*/

-- 컬럼명이 백퍼 같기에 구분해줘야한다 
SELECT 
    a.first_name AS 사원이름, b.first_name AS 직장상사 
FROM 
    employees a, employees b 
WHERE 
    a.manager_id = b.employee_id;

--연습1: 직장상사 이름이 Matthew인 사람들의 사번/이름/월급/부서명 을 조회해보세요
SELECT 
    a.employee_id ,a.first_name AS 사원이름, b.first_name AS 직장상사, a.salary AS 월급, d.department_name AS 부서명
FROM 
    employees a, employees b, departments d
WHERE
    b.first_name = 'Matthew'
AND
    a.manager_id = b.employee_id
AND
    a.department_id = d.department_id;

--연습2: Alberto와 동일한 직책을 가진 사원들의 모든 정보를 조회해보세요
SELECT
    a.*
FROM
    employees a, employees b
WHERE
    b.first_name = 'Alberto'
AND
    a.job_id = b.job_id;

/*
    # OOUTER JOIN
    
     - JOIN 조건을 만족하지 못해서 등장하지 못했던 행들을 확인할수 있는 JOIN
     - (+)를 붙인쪽의 컬럼에 null을 추가해서 반대쪽에서 등장하지 못한 행들을 확인한다
*/
SELECT * FROM employees;

SELECT 
    * 
FROM 
    employees e, departments d
WHERE 
    e.department_id = d.department_id(+)
ORDER BY
     e.department_id DESC;
     
    
--연습1: 사원번호/이름/도시를 출력하되 소속된 사원이 없는 도시도 출력해보세요
SELECT 
    employee_id,first_name, department_name ,l.city
FROM
    employees e,locations l, departments d
WHERE
    e.department_id(+) = d.department_id
AND
    d.location_id(+) = l.location_id -- d.로케이션 아이디와 l.로케이션 아이디에 공통된 부분만 가져오는데! l.로케이션 아이디에 d.로케이션 아이디와 공통되지 못한 부분도 null을 붙여서 가져온다
ORDER BY
    employee_id,d.department_id;