[1] 데이터 분석 SQL 실습 - 조인에서 1:M의 개념, 조인과 Where절 & With절

엑셀 잠깐 보류하고 SQL 실습 ~

---

오늘의 실습 내용 요약
1. 조인에서 1:M의 개념
2. 조인 실습 - 다섯 가지 예제와 where절, order by, with절의 활용

 

먼저 실습환경 구축 - PostgreSQL + DBeaver 설치

• Set as default : 특정 스키마를 default로 설정하면 테이블 조회 시 스키마 입력 안 해도 됨 

---

SQL 조인 시 데이터 집합 레벨의 변화 1,2

조인 Join

• RDBMS (관계형 DB) 에서 테이블을 연결하는 가장 기본적인 기능
• 서로 "동등한" 동격인 테이블을 가져오는 기능 (정보를 양쪽에서 모두 가져옴)
  • 1:M 조인 시 결과 집합은 M집합의 레벨을 그대로 유지 
    • 1이란, '유일한, 고유한' 값이라는 의미 (이 값이 PK가 되면 식별자가 되는 것) EX. 사원 번호
    • M은 여러 건이 있다는 의미가 아니라, 개별 값들이 '중복되어서 있다' 는 의미 EX. 부서 이름

EX. 상품 주문 테이블 (사용자 ID, 주문 ID, 상품 ID가 있는 표) & 상품 테이블 (상품ID, 상품 명) 조인 시

 

• 상품 주문 테이블에 있는 주문ID+상품ID는 중복된 값이므로 M에 해당
• 상품 테이블에 있는 상품 ID에 따른 상품명 정보는 고유한 값이므로 1에 해당
• 조인 결과는 M집합의 레벨이 그대로 가져온 상태에서 1이 추가된 형태
• 즉, 1:1 조인 결과는 1이고, 1:M 조인 결과의 집합은 M이 되는 것 (후에 M:N 조인을 다룰 예정)

EX. 고객 테이블 (고객ID와 고객명) & 고객 연락처 테이블 (고객ID, 구분[전화,주소,직장...], 연락처[010-..,경기도,서울]) 조인 시

• 중복된 값들인 고객 연락처 테이블이 M, 고유값인 고객 테이블이 1 
• 여기서도 M집합의 레벨을 그대로 가져와 고객 연락처 테이블 레벨을 기준으로 고객 테이블이 추가된 형태가 됨

---

조인 실습

• hr 스키마 우클릭 - '다이어그램 보기' 기능으로 테이블 간의 구조 확인 가능 ( ERD 그려줌, DBeaver 기능)
• 주어진 테이블 종류
  • 직원 emp
  • 부서 dept
  • 과거 급여 이력 emp_salary_hist
  • 과거 소속 부서 이력 emp_dept_hist 
• 소속 부서가 바뀌었을 경우, 부서 이력 테이블에 기록되는, 쌓이는 구조
• 쿼리 입력 시, select만 써놓고 조회 대상 데이터는 마지막에 입력하면 편하다.

1. 직원 정보 + 속한 부서명 가져오기

 

쿼리

 

• 직원 emp 테이블과 부서 dept 테이블 조인하기
• alias로 a,b 사용
• 결과는 M(중복 값)에 해당하는 emp 테이블을 기준으로 dept 테이블이 우측에 붙여진 형태

---

2. job이 Salesman에 해당하는 직원 정보 + 부서명 가져오기

 

쿼리

• 위와 완전히 동일하게 입력 + 끝에 where절로 조건만 추가

---

3. 부서명 sales와 research 소속 직원들의 정보 + 과거 급여 정보 추출

 

 

쿼리

 

• from + join  시 < 스키마.테이블 alias > 
• 2 번 join 하기
  • 1:m으로 dept 테이블 : emp 테이블 조인되어 emp테이블 레벨 가져왔다가, 
  • => 한번 더 1:m 조인, emp 테이블 : emp_salary_hist 이번엔 hist 레벨로 가져와진 것
• where절에서 두 개 이상 조건인 경우,   in ( ) 형태로 입력

---

4. 3번 + 과거 급여 중 1983년 이전 데이터는 제외하고 불러오기

쿼리

결과

• 위 쿼리와 동일하게 입력하되, where 절에 and로 조건 추가
  • emp_salary_hist 의 fromdate 날짜가 1983년 이후인 데이터만 출력하기, to_date ( '시작날짜', '날짜형식' ) 사용
  • order by의 경우 열의 번호를 입력해도 되고, alias를 입력해도 된다.

---

5. sales와 research 소속 직원 별 과거~현재 모든 급여 평균 구하기

쿼리

• with절 활용

WITH 임시테이블명 AS ( SUB QUERY문 (SELECT절) ) SELECT 컬럼, [컬럼, ...] FROM 임시테이블명

• with절 안에 입력한 select ~ from ~ join ~ wher ~ order by 부분은 서브 쿼리 (4번 답과 동일한 쿼리)
• 그리고 with ~ as ()를 빠져나와 조회하고 싶은 데이터를 집계함수 & group by로 입력해주면 된다.
  • '소속 직원 별 평균' => 소속 직원 테이블인 empno가 group by 대상

---

5. 직원명 smith의 과거 소속 부서 정보 구하기

과거 소속 부서 테이블

쿼리

결과

• 총 3가지 테이블 조인
  • 직원명 smith의 - empno 
  • 과거 소속 부서 - emp_dept_hist
  • 부서 정보 - deptno
• 현재도 일하고 있기 때문에 마지막 todate는 9999-12-31으로 표시됨

---