[코멘토 SQL직무부트캠프] 2차 과제-데이터추출요청서&SQL연습

<과제 목표>

현업이 제출한 데이터 추출 요청서를 확인 후 요구사항의 애매모호성 제거하여 데이터 추출 작업 준비하기.

 

 

상황

내년부터 우리 회사에 직원 복지항목으로 자녀 학자금에 대한 지원이 추가됨.

관련하여 인사팀에서 현황을 파악하고자 관련 데이터 추출을 요청함.

 

---

1. 현업의 요구사항을 모두 파악 후 추출하려는 데이터의 레이아웃을 그려보세요

직원번호

직원성명

사내부부여부

직원의 자녀수

자녀성명

자녀성별

자녀 생년월일

자녀나이

자녀순서

쌍생아 여부

*자녀순서는 몇 번째 자녀인지 의미 

 

데이터는 은퇴한 직원의 정보도 담고 있지만 재직 중인 임직원을 대상으로 하는 복지혜택이기 때문에 재직여부는 필요없을 것 같다.  

 

FEEDBACK 이후

직원번호	직원성명	직원성별	사내부부여부	배우자 성명	자녀성명	자녀성별	자녀생년월일	자녀나이	쌍생아 여부
EMP_C	EMP_C	EMP_C	FAM_REL_C	FAM_REL_C EMP_C	FAM_C	FAM_C	FAM_C	FAM_C	FAM_C
EMP_NO	EMP_NM	GENDER_CD	EMP_REL_NO	EMP_REL_NO, EMP_NM	FAM_NM	GENDER_CD	BIRTH_YMD	BIRTH_YMD	REL_TYPE_CD

 

2열: 테이블

3열: 컬럼

---

2. 애매모호한 부분에 밑줄을 긋고 어떻게 명확히 할 지 수정해 보세요.

직원 복지향상을 위해 내년부터 아동 자녀 학자금을 지원하려 대상자를 확인하려 합니다. 전체 임직원 중 - 기혼의 임직원 중 자녀가 1명 이상의 인 자 - 자녀 나이가 5~7세인자 -배우자가 사내부부인 경우는 남편, 아내 중 한쪽만 가능 - 배우자가 사내부부인 경우 배우자 명을 표시 - 해당자녀의 나이, 성별표시 - 쌍생아의 경우 대표 1명만 표시

 

- 기혼의 임직원 중 자녀가 1명 이상의 인 자

미혼모, 미혼부도 있을 수 있다 생각.

재작자중 자녀코드를 가지고 있고 자녀가 1명이상이면 기혼으로 간주

 

- 자녀 나이가 5~7세인자

나이의 기준이 만나이인지 세는 나이(한국식나이)인지 연나이인지 애매모호함

또한 만나이를 기준으로 삼을 경우 어느 날짜를 기준으로 만나이를 할 것인지도 부정확함

만나이로 할 경우에는 "2022년 01월 01일 기준으로 만5~7세 자녀"로 기준 세우는 것이 명확함.

그리고 나이보다 출생연도를 기준으로 세우는 것도 명확하다고 생각.

예를 들어 2022년 기준으로 한국식 나이 5세~7세에 해당되는 "2016년 01월 01일 ~ 2018년 12월 31일 사이 출생자"

- 해당자녀의 나이, 성별표시

마찬가지로 나이의 기준이 애매모호함.

만나이, 연나이, 세는나이(한국식나이) 기준 명확화 필요

나이, 생년월일, 성별 추출

- 쌍생아의 경우 대표 1명만 표시

쌍생아의 기준: 관계코드가 자녀이면서 생년월일이 같은 경우

쌍생아 대표의 기준이 애매모호함. 

쌍생아의 경우에도 자녀 순서가 나뉘어 있음. 쌍생아 중 첫째를 대표로 표시 

또는 이름으로 오름차순 기준, 상위자로 추출,

 

 

+ 추가사항

- 2명이상의 자녀가 나이기준에 충족될 경우에 혜택 중복 여부 -> 나이가 더 많은 자녀를 대표 1명으로 표시 

- 임직원의 연차기준에 따른 복지혜택 적용 여부 -> 입사연도 상관없이 모든 임직원 복지적용 or 입사한 지 2년이상된 직원부터 복지 적용 등 기준세우기

 

FEEDBACK 이후 추가

배우자가 사내부부인 경우는 남편, 아내 중 한쪽만 가능

사내커플인 경우 부를 대상자에 포함하고 모는 제외

 

 배우자가 사내부부인 경우 배우자 명을 표시

대상자 부 기준으로 모의  직원번호 및 배우자 명을 함께 추출

 

---

3. SQL을 사용하여 문제에 답하세요.

(1) 직원번호 10004141 의 이름은? 

SELECT emp_no, emp_nm
FROM emp_c
WHERE emp_no = 10004141;

결과

(2) 직원번호 10004141 는 몇 명의 가족이 있나요? 

SELECT COUNT(emp_no)
FROM fam_c
WHERE emp_no = 10004141;

결과

(3) 직원번호 10004141 의 소속 부서 코드는? 

SELECT org_cd
FROM emp_c
WHERE emp_no = 10004141;

결과

(4) 직원번호 10004141 의 소속 부서 명은?  

SELECT org_nm
FROM org_c
WHERE org_cd = (
  SELECT org_cd
  FROM emp_c
  WHERE emp_no = 10004141
);

결과

(5) 남자 / 여자 직원이 몇 명인지 한번에 알 수 있는 sql 작성

SELECT gender_cd, COUNT(*)
FROM emp_c
GROUP BY gender_cd
ORDER BY gender_cd

(6) 직원별 자녀가 몇 명인지 한번에 알 수 있는 sql 작성

SELECT emp_no, COUNT(*) AS 자녀수
FROM fam_c
WHERE rel_type_cd = 'A27'
GROUP BY emp_no;

 

결과

(7) 생년월일이 1970년 1월 1일 이전인 직원의 수를 구하는 sql 작성

SELECT COUNT(emp_no)
FROM emp_c
WHERE birth_ymd < 19700101

결과

(8) 현재 재직 중인 직원의 전체 수를 구하는 sql 작성

SELECT COUNT(emp_no)
FROM emp_c
WHERE retire_ymd LIKE '9999%'

SELECT COUNT(emp_no)
FROM emp_c
WHERE retire_ymd = '9999/12/31'

결과