힘내보자

https://github.com/neverGiveUpppppp/Comento_SQL_Oracle

    - 추출조건 :  현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출.

    - 추출조건2 : 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에  ‘Y’ 표시 요망.

    - 추출조건3 : 사내부부인 경우도 예외 없이 직원과 자녀를 모두 추출하고, 이 때 상대 배우자의 직원번호를 추출 요망.

   -  추출 요청 컬럼 → 직원번호 / 직원성명 / 배우자성명 (사내부부일 때만) / 배우자직원번호 / 자녀성명 / 자녀성별 / 자녀생년월일 / 쌍생아여부

힌트

 조건 brainstorming
 재직중 : RETIRE 99/12/31 조건
 자녀 생년월일 2012.01.01~2015.12.31 : BETWEEN 20120101 AND 20151231
 쌍둥이의 경우 두명 모두 추출 : FAM_C SELF JOIN FAM_NM을 F1,F2 따로 조회 및 조건에 NM <>추가
쌍둥이 여부 표시 'Y' : 리터럴 작성
 사내부부의 경우, 상대 배우자의 직원번호 추출 : ?
 조회 목록 : EMP_NO, EMP_NM, CASE(SPOUSE_EMP_NO 사내부부일때,일반일때), CHILD_NM, CHILD_GENDER, CHILD_BIRTH_YMD, TWIN_YN
(직원번호 / 직원성명 / 배우자성명 (사내부부일 때만) / 배우자직원번호 / 자녀성명 / 자녀성별 / 자녀생년월일 / 쌍생아여부)

--- 추출조건1
--- 현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출
SELECT *
FROM FAM_C
WHERE REL_TYPE_CD = 'A27'
AND BIRTH_YMD BETWEEN 20120101 AND 20151231
;

--- 추출조건2
--- 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에 'Y' 표시 요망
SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
    JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
AND F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD
;
    
 
 
 
--- 추출조건1 + 추출조건2
--- 현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출
--- 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에 'Y' 표시 요망
SELECT *
FROM FAM_C
WHERE REL_TYPE_CD = 'A27' 
AND BIRTH_YMD BETWEEN 20120101 AND 20151231
;

SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
    JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
AND F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD
;

-- FROM절의 AND조건들을 WHERE절로 돌려서 넣으면 뭐가 다를까?
SELECT F1.EMP_NO 직원번호, F1.FAM_NM 쌍둥이1, F2.FAM_NM 쌍둥이2, 'Y' "쌍둥이 여부"
FROM FAM_C F1
    JOIN FAM_C F2 ON(F1.EMP_NO = F2. EMP_NO)
WHERE F1.FAM_NM <> F2.FAM_NM
AND F1.REL_TYPE_CD = 'A27'
AND F1.REL_TYPE_CD = F2.REL_TYPE_CD
AND F1.BIRTH_YMD = F2.BIRTH_YMD    
;

2) LISTAGG를 활용하여 직원번호가 10001483 인 자녀의 성명이 한줄로 추출 되도록 SQL을 작성해 보세요

HINT : LISTAGG 활용 --> https://gent.tistory.com/328

https://github.com/neverGiveUpppppp/Comento_SQL_Oracle

• 문제1) 직원번호 10004141 의 이름은? 
• 문제2) 직원번호 10004141 는 몇 명의 가족이 있나요? 
• 문제3) 직원번호 10004141 의 소속 부서 코드는? 
• 문제4) 직원번호 10004141 의 소속 부서 명은? 
• 문제5) 남자 / 여자 직원이 몇 명인지 한번에 알 수 있는 sql을 작성하세요.
• 문제6) 직원별 자녀가 몇 명인지 한번에 알 수 있는 sql을 작성하세요. 
• 문제7) 생년월일이 1970년 1월 1일 이전인 직원의 수를 구하는 sql을 작성하세요.
• 문제8) 현재 재직 중인 직원의 전체 수를 구하는 sql을 작성하세요.

--문제1) 직원번호 10004141 의 이름은? 힌트 : emp_c에 있음
--  최ㅇ규
SELECT EMP_NM FROM EMP_C
WHERE EMP_NO = 10004141;
SELECT * FROM EMP_C
WHERE EMP_NO = 10004141;

--문제2) 직원번호 10004141 는 몇 명의 가족이 있나요? 힌트 fam_c
--  5명
SELECT * FROM FAM_C
WHERE EMP_NO = 10004141;

--문제3) 직원번호 10004141 의 소속 부서 코드는? 힌트 emp_c
-- A183500
SELECT ORG_CD FROM EMP_C
WHERE EMP_NO = 10004141;


--문제4) 직원번호 10004141 의 소속 부서 명은?  힌트 org_c
-- 전XX룹
SELECT ORG_NM FROM ORG_C
WHERE ORG_CD = 'A183500';

--문제5) 남자 / 여자 직원이 몇 명인지 한번에 알 수 있는 sql을 작성하세요. (group by gender_cd)
-- 남 1374명 // 여 621명
-- 전체 개수를 세어야하니 COUNT집계 함수 사용해야함

--SELECT COUNT(GENDER_CD) --DECODE(SUBSTR(GENDER_CD,1,1),1,'남','여') AS 성별  
--FROM EMP_C
--GROUP BY GENDER_CD;
--HAVING GENDER_CD = 2; -- 필요없는 조건

SELECT GENDER_CD,COUNT(*)
FROM EMP_C
GROUP BY GENDER_CD;

-- DECODE+ALIAS로 분류 컬럼 추가
SELECT DECODE(SUBSTR(GENDER_CD,1,1),1,'남','여') AS 성별, COUNT(*)
FROM EMP_C
GROUP BY GENDER_CD;





--문제6) 직원별 자녀가 몇 명인지 한번에 알 수 있는 sql을 작성하세요. (rel_type_cd가 A27이면 자녀)
--

SELECT *
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27';    
--WHERE EMP_NO = 10004141;

-- WHERE문에 E.EMP_NO = 10004141 추가로 넣어서 사원 한명의 자녀 목록 확인
SELECT E.EMP_NO, EMP_NM, E.BIRTH_YMD, FAM_NM, REL_TYPE_CD,F.BIRTH_YMD
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'
AND E.EMP_NO = 10004141; -- 10004141	최ㅇ규	19551223	최ㅇ철	A27	19850503

-- 자녀 둘인 걸 확인 했으니 COUNT()로 자녀수 조회ㄱㄱ
SELECT COUNT(E.EMP_NO)
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'
AND E.EMP_NO = 10004141; -- 2

-- 전체 직원의 자녀수 인듯. 직원 별 자녀수가 필요
SELECT COUNT(E.EMP_NO)
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE REL_TYPE_CD = 'A27'; -- 2412

-- 직원 별이니까 GROUP BY 통해 묶고 대상은 EMP_NO이면 될 듯
SELECT E.EMP_NO, COUNT(E.EMP_NO)
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)    
WHERE REL_TYPE_CD = 'A27'
GROUP BY E.EMP_NO; -- 인출행 수 1239

--SELECT E.EMP_NO,E.EMP_NM, COUNT(E.EMP_NO)
--FROM EMP_C E
--    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)    
--WHERE REL_TYPE_CD = 'A27'
--GROUP BY E.EMP_NO; -- 인출행 수 1239 -> 조회할려는 컬럼이 그룹바이로 지정이 안됐기에 에러 발생

-- ANSI 표준 JOIN
SELECT E.EMP_NO, E.EMP_NM, COUNT(E.EMP_NO) "자녀수"
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)    
WHERE REL_TYPE_CD = 'A27'
GROUP BY E.EMP_NO, E.EMP_NM; -- 인출행 수 1239

-- 오라클 전용 JOIN구문
SELECT E.EMP_NO, E.EMP_NM, COUNT(E.EMP_NO) "자녀수"
FROM EMP_C E, FAM_C F
WHERE E.EMP_NO = F.EMP_NO
AND REL_TYPE_CD = 'A27'
GROUP BY E.EMP_NO, E.EMP_NM;  -- 인출행 수 1239