직무부트캠프 3주차 심화 과제, 4주차 과제

à★★★ 배우자 직원번호 완성하세요!! :

 SP_EMP.EMP_NO AS 배우자직원번호,

à ★★★ CASE WHEN을 사용하여 쌍둥이 여부를 표시해보세요. :

CASE

        WHEN TWIN.TWIN1 IS NOT NULL AND TWIN.TWIN2 IS NOT NULL THEN 'Y'

        ELSE 'N'

    END AS 쌍둥이여부

à★★★ 현재 재직자만 추출 되도록 조건을 넣으세요. :

 AND EMP.RETIRE_YMD = '99991231’

à★★★ 2012년 1월 1일 부터 2015년 12월 31일 생년월일 조건을 넣으세요:

AND FAM.BIRTH_YMD BETWEEN '20120101' AND '20151231'; -- 2012년 1월 1일부터 2015년 12월 31일 생년월일 조건

 

1. 임직원의 모든 자녀의 순서를 구하는 sql을 작성하세요. Ex ) 사번 / 자녀성명 / 생년월일 / 첫째

•SELECT

    EMP.EMP_NO AS 사번,

    FAM.FAM_NM AS 자녀성명,

    FAM.BIRTH_YMD AS 자녀생년월일,

    ROW_NUMBER() OVER (PARTITION BY EMP.EMP_NO ORDER BY FAM.BIRTH_YMD) AS 첫째

FROM

    EMP_C EMP

LEFT JOIN

    FAM_C FAM ON EMP.EMP_NO = FAM.EMP_NO

WHERE

    FAM.REL_TYPE_CD = 'A27'

ORDER BY

    EMP.EMP_NO, FAM.BIRTH_YMD;

 

 

2. fam_rel_c테이블의 emp_rel_no에는 직원번호가 있는데, emp_c테이블의 emp_no에는 존재하지 않은 임직원 번호를 구하세요.

SELECT frc.emp_rel_no

FROM fam_rel_c frc

LEFT JOIN emp_c ec ON frc.emp_rel_no = ec.emp_no

WHERE frc.emp_rel_no IS NOT NULL

AND ec.emp_no IS NULL;

 

 

3. 조직원장 org_c 테이블에서 super_org_cd와 org_cd의 관계를 파악하고, 데이터에 이상한 점이 없는지 확인해보세요.

•Super_org_cd와 org_cd 는 대다일 관계를 가진다

•Super_org_cd는 부모 조직 코드이고 org_cd는 자식 조직 코드를 나타낸다.

 

 

 

4. 현재 재직중인 임직원을 조직의 상위조직과 관리조직을 추출할수 있는 sql을 작성하세요. Ex) 직원번호 / 조직번호 / 조직명 / 상위조직명 / 관리조직명

SELECT DISTINCT FR.emp_rel_no AS 존재하지_않는_임직원_번호

FROM fam_rel_c FR

LEFT JOIN emp_c E ON FR.emp_rel_no = E.emp_no

WHERE E.emp_no IS NULL;

 

5. 관리조직별 현재 재직중인 직원과 자녀 유무를 판단할 수 있는 sql을 작성하세요. Ex ) 조직코드 / 조직명 / 직원번호 / 성명 / 자녀유무

 

SELECT

    O.ORG_CD AS 조직코드,

    O.ORG_NM AS 조직명,

    E.EMP_NO AS 직원번호,

    E.EMP_NM AS 성명,

    CASE

        WHEN F.EMP_NO IS NOT NULL THEN 'Y'

        ELSE 'N'

    END AS 자녀유무

FROM

    EMP_C E

JOIN

    ORG_C O ON E.ORG_CD = O.ORG_CD

LEFT JOIN

    FAM_C F ON E.EMP_NO = F.EMP_NO AND F.REL_TYPE_CD = 'A27'

WHERE

    E.RETIRE_YMD = '99991231’

AND O.MGR_ORG_CD IS NOT NULL;

•

 

 

/*
    - 추출조건 :  현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출.
    - 추출조건2 : 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에  ‘Y’ 표시 요망.
    - 추출조건3 : 사내부부인 경우도 예외 없이 직원과 자녀를 모두 추출하고, 이 때 상대 배우자의 직원번호를 추출 요망.
   -  추출 요청 컬럼  직원번호 / 직원성명 / 배우자성명 (사내부부일 때만) / 배우자직원번호 / 자녀성명 / 자녀성별 / 자녀생년월일 / 쌍생아여부
*/

select 
 * from 
 emp_c emp
 inner join fam_c fam
 on emp.emp_no = fam.emp_no
 and fam.rel_type_cd = 'A27'
 where sysdate between hire_ymd and retire_ymd;

 

 

/*
    - 추출조건 :  현재 재직중인 직원의 생년월일이 2012년 1월 1일 부터 2015년 12월 31일 인 자녀 모두추출.
    - 추출조건2 : 쌍둥이의 경우 두명 모두 추출하고, 쌍둥이 여부에  ‘Y’ 표시 요망.
    - 추출조건3 : 사내부부인 경우도 예외 없이 직원과 자녀를 모두 추출하고, 이 때 상대 배우자의 직원번호를 추출 요망.
   -  추출 요청 컬럼  직원번호 / 직원성명 / 배우자성명 (사내부부일 때만) / 배우자직원번호 / 자녀성명 / 자녀성별 / 자녀생년월일 / 쌍생아여부
*/

--1
select 
 * from 
 emp_c emp
 inner join fam_c fam
 on emp.emp_no = fam.emp_no
 and fam.rel_type_cd = 'A27'
 where sysdate between hire_ymd and retire_ymd
 and fam.birth_ymd between '20120101' and '20151231';
 

--2
select 
 fam1.emp_no
 ,fam1.fam_nm
  from
 fam_c fam1
 inner join fam_c fam2
 on fam1.emp_no = fam2.emp_no
 and fam1.fam_nm <> fam2.fam_nm
 and fam1.birth_ymd = fam2.birth_ymd
 and fam1.rel_type_Cd = 'A27'
 and fam2.rel_type_cd = 'A27'
 and fam1.rel_type_cd = fam2.rel_type_cd
 ;
 
 --3
 select 
  * from 
  emp_c emp
  left outer join
  fam_rel_c famr
  on emp.emp_no = famr.emp_no
  and famr.rel_type_cd in ('A02','A18')
  ;
 

 

 

select 
 emp.emp_no as 직원번호
 ,emp.emp_nm as 직원성명
 ,famr.emp_rel_no as 배우자직원번호
 ,(select emp_nm from emp_c where emp_no = famr.emp_rel_no)     as 배우자성명
 ,fam.fam_nm as 자녀성명
 ,decode(fam.gender_Cd,'1','남자','2','여자')  as 자녀성별
 ,fam.birth_ymd as 자녀생년월일
 ,case when twin.fam_nm is not null then 'Y' else 'N' end as 쌍생아여부
 from 
 emp_c emp
 
 inner join fam_c fam
 on emp.emp_no = fam.emp_no
 and fam.rel_type_cd = 'A27'
 
 left outer join
  fam_rel_c famr
  on emp.emp_no = famr.emp_no
  and famr.rel_type_cd in ('A02','A18')
  
 left outer join
 (
     select 
     fam1.emp_no
     ,fam1.fam_nm
      from
     fam_c fam1
     inner join fam_c fam2
     on fam1.emp_no = fam2.emp_no
     and fam1.fam_nm <> fam2.fam_nm
     and fam1.birth_ymd = fam2.birth_ymd
     and fam1.rel_type_Cd = 'A27'
     and fam2.rel_type_cd = 'A27'
     and fam1.rel_type_cd = fam2.rel_type_cd
 ) twin
 on emp.emp_no = twin.emp_no
 and fam.fam_nm = twin.fam_nm
 
 where sysdate between hire_ymd and retire_ymd
 and fam.birth_ymd between '20120101' and '20151231'
 ;

 

 

 

select
일자
,부서코드
,org_nm as 부서명
,영업실적
from
        (
                select 
                sh.sales_date as 일자
                ,emp.org_cd as 부서코드
                ,sum(sales_qty * sale_price) as 영업실적
                from sales_h sh
                inner join emp_c emp
                on sh.emp_no = emp.emp_no
                -- where sh.emp_no  = '11500877'
                group by sh.sales_date , emp.org_cd
        ) gip
inner join org_c org
on gip.부서코드 = org.org_cd
;

 

select 
 * 
 from
        (
                select 
                 a.emp_no
                ,a.fam_nm
                ,rank() over (partition by a.emp_no order by a.fam_nm) as rn
                from fam_c a
                inner join fam_c b
                on a.emp_no = b.emp_No
                and a.fam_nm <> b.fam_nm
                and a.birth_Ymd = b.birth_ymd
                and a.rel_Type_Cd = b.rel_Type_Cd
                and a.rel_Type_Cd = 'A27'
                and a.emp_no = '10007088'
        ) twin
where rn = 1-- 첫째
;

 

 

select 
 * 
 from
        (
                select 
                 a.emp_no
                ,a.fam_nm
                /*순위함수를 사용하여 이름순으로 첫째, 둘째를 정한다 */
                ,rank() over (partition by a.emp_no order by a.fam_nm) as rn
                /*순위함수를 사용하여 이름순으로 첫째, 둘째를 정한다 */
                from fam_c a
                inner join fam_c b
                on a.emp_no = b.emp_No
                /*쌍둥이의 조건 이름이 다르고 생년월일이 같다.*/
                and a.fam_nm <> b.fam_nm 
                and a.birth_Ymd = b.birth_ymd
                /*쌍둥이의 조건 이름이 다르고 생년월일이 같다.*/
                and a.rel_Type_Cd = b.rel_Type_Cd
                and a.rel_Type_Cd = 'A27'
                and a.emp_no = '10007088'
        ) twin
where rn = 1 -- 첫째의 조건 1

 

 

--1. select 데이터를 가져온다.
SELECT -- 가져와라 
 * -- 모두 (모든컬럼)
 FROM EMP_C; -- 컨트롤 키 + 엔터

SELECT -- 가져와라 
 EMP_NM -- 한개만 컬럼명을 적어준다.
 FROM EMP_C; -- 컨트롤 키 + 엔터
 
 SELECT -- 가져와라 
  EMP_NM
 ,EMP_NO
 FROM EMP_C; -- 컨트롤 키 + 엔터
 
SELECT -- 가져와라 
  TMP.EMP_NM -- 테이블명.컬럼명
 ,TMP.EMP_NO 
 FROM EMP_C TMP ; -- 테이블에 이름을 붙여준다.

 

 

 

--2. WHERE 조건
SELECT
* 
FROM EMP_C EMP
WHERE BIRTH_YMD >= '19500101'; -- = , <> , > , < , >= , <=

SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD = '1'; --남자

SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD <> '1'; --여자

SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD <> '1'
AND BIRTH_YMD >= '19500101';  -- 여러조건을 만족하는 데이틑 찾을 때 AND를 사용

 

 

SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD <> '1'
AND BIRTH_YMD >= '19500101'
ORDER BY  EMP_NM ASC ; --오름정렬

SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD <> '1'
AND BIRTH_YMD >= '19500101'
ORDER BY  EMP_NM DESC ; --내림정렬

SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD <> '1'
AND BIRTH_YMD >= '19500101'
ORDER BY  EMP_NM, BIRTH_YMD DESC ; --내림정렬

 

 

--3. 정렬
SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD <> '1'
AND BIRTH_YMD >= '19500101'
ORDER BY  EMP_NM ASC ; --오름정렬

SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD <> '1'
AND BIRTH_YMD >= '19500101'
ORDER BY  EMP_NM DESC ; --내림정렬

SELECT
* 
FROM EMP_C EMP
WHERE GENDER_CD <> '1'
AND BIRTH_YMD >= '19500101'
ORDER BY  EMP_NM, BIRTH_YMD DESC ; --내림정렬

 

 

--4. LIKE
SELECT 
*
FROM EMP_C
WHERE EMP_NM LIKE '이%';

SELECT 
*
FROM EMP_C
WHERE EMP_NM NOT LIKE '이%';

SELECT 
*
FROM EMP_C
WHERE EMP_NM LIKE '%이';

SELECT 
*
FROM EMP_C
WHERE EMP_NM LIKE '%이%';

 

 

 

--5. IN , NOT IN
SELECT 
 * FROM
 EMP_C
 WHERE EMP_NO  IN ('11507952','11510199','10004553');
 
 SELECT 
 * FROM
 EMP_C
 WHERE EMP_NO = '11507952';
 
 SELECT 
 * FROM
 EMP_C
 WHERE EMP_NO  NOT IN ('11507952','11510199');
 
 SELECT 
 * FROM
 EMP_C
 WHERE EMP_NO  IN ('11507952','11510199','10004553')
 AND EMP_NM LIKE '김%';

 

 

 

 

-- 6. GROUP BY ~별, 직원별 자녀수를 구하는 sql을 작성해보세요.

SELECT
 EMP_NO
,COUNT(*)
 FROM FAM_C
 WHERE REL_TYPE_CD = 'A27' -- 자녀는 A27
 AND EMP_NO = '10007088'
 GROUP BY EMP_NO ; -- GROUP BY 뒤에 있는 컬럼명을 그대로 SELECT 쪽에 넣어준다.
 
-- 그룹바이 , COUNT(*) , MAX,MIN,AVG,SUM 같이 함께 쓰인다.

-- 임직원중에서 남자와 여자 중에서 가장 생일이 빠른 사람은?
SELECT
 GENDER_CD
,MIN(BIRTH_YMD)
FROM 
EMP_C
GROUP BY GENDER_CD;

SELECT
 GENDER_CD
,MAX(BIRTH_YMD)
FROM 
EMP_C
GROUP BY GENDER_CD;

 

 

-- 7. DECODE, CASE WHEN

SELECT
 EMP_NO
 ,GENDER_CD  AS 성별코드
 ,DECODE(GENDER_CD,'1','남자','2','여자','모름') AS 성별
 FROM EMP_C;
 
 SELECT
 EMP_NO
 ,GENDER_CD  AS 성별코드
 , BIRTH_YMD
 ,DECODE(GENDER_CD,'1','남자','2','여자','모름') AS 성별
 ,CASE WHEN GENDER_CD = '1' AND BIRTH_YMD <= '19500101' THEN '늙은남자'
       WHEN GENDER_CD = '1' AND BIRTH_YMD > '19500101' THEN '젊은남자'
       WHEN GENDER_CD = '2' THEN '여자'
       ELSE '모름' END AS 성별2
 FROM EMP_C;
 
 SELECT 
  ROWNUM --순번
  ,EMP.*
  FROM EMP_C EMP;

--RANK 동순위는 동일하게 처리하고, 뛰어 넘는다.
 SELECT 
  RANK() OVER (ORDER BY EMP_NM ASC) AS RN -- RANK를 순위를 선정하는데, EMP_NM의 오름차순으로
  ,EMP.*
  FROM EMP_C EMP;

--DENSE_RANK --> 동순위를 뛰어 넘기지 않는다.
 SELECT 
  DENSE_RANK() OVER (ORDER BY EMP_NM ASC) AS RN -- RANK를 순위를 선정하는데, EMP_NM의 오름차순으로
  ,EMP.*
  FROM EMP_C EMP;

 

 

SELECT * FROM EMP_C WHERE EMP_NO = '11510796'
UNION ALL -- 합치는데, 중복이 제거 되지 않는다.
SELECT * FROM EMP_C WHERE EMP_NO = '11503691'
;

SELECT * FROM EMP_C WHERE EMP_NO = '11510796'
UNION  -- 합치는데, 중복이 제거된다.
SELECT * FROM EMP_C WHERE EMP_NO = '11503691'
;

SELECT * FROM EMP_C WHERE EMP_NO = '11503691'
UNION ALL -- 합치는데, 중복이 제거 되지 않는다.
SELECT * FROM EMP_C WHERE EMP_NO = '11503691'
;

SELECT * FROM EMP_C WHERE EMP_NO = '11503691'
UNION  -- 합치는데, 중복이 제거된다.
SELECT * FROM EMP_C WHERE EMP_NO = '11503691'
;

SELECT * FROM EMP_C WHERE EMP_NO IN ( '11503691' , '11510796');

 

 

 

SELECT 
EMP_NO
,COUNT(*)
 FROM FAM_C
WHERE REL_TYPE_CD = 'A27'
GROUP BY EMP_NO
HAVING COUNT(*) < 3;

 

 

 

 

-- JOIN
-- 테이블과 테이블을 합친다.
-- 1) 기준 테이블
-- 2) 대상 테이블
-- 3) 키를 합친다.

-- INNER JOIN / LEFT OUTER JOIN

-- 임직원번호 / 임직원성명 / 임직원가족이름
-- EMP_C (EMP_NO ) / EMP_C (EMP_NM ) / FAM_C (FAM_NM )
SELECT
EMP.EMP_NO AS 임직원번호
,EMP.EMP_NM  AS 직원성명
,FAM.FAM_NM AS 직원가족성명
FROM
EMP_C EMP
INNER JOIN -- 교집
FAM_C FAM
ON EMP.EMP_NO = FAM.EMP_NO --JOIN KEY
WHERE EMP.EMP_NO  IN ('10001452','11505155')
;

SELECT
EMP.EMP_NO AS 임직원번호
,EMP.EMP_NM  AS 직원성명
,FAM.FAM_NM AS 직원가족성명
FROM
EMP_C EMP
LEFT OUTER JOIN 
FAM_C FAM
ON EMP.EMP_NO = FAM.EMP_NO --JOIN KEY
WHERE EMP.EMP_NO  IN ('10001452','11505155')

 

 

 

/*
문제1) 직원번호 10004141 의 이름은?
문제2) 직원번호 10004141 는 몇 명의 가족이 있나요?
문제3) 직원번호 10004141 의 소속 부서 코드는?
문제4) 직원번호 10004141 의 소속 부서 명은? 
*/

SELECT 
EMP_NM FROM EMP_C
WHERE EMP_NO = '10004141';

SELECT 
COUNT(*) FROM FAM_C
WHERE EMP_NO = '10004141';

SELECT 
ORG_CD FROM EMP_C
WHERE EMP_NO = '10004141';

SELECT 
ORG_NM FROM ORG_C
WHERE ORG_CD = 'A183500';

SELECT 
ORG_NM FROM ORG_C
WHERE ORG_CD  = (
                    SELECT 
                    ORG_CD FROM EMP_C
                    WHERE EMP_NO = '10004141'
                 );
;

 

 

SELECT
     EMP.EMP_NO AS 임직원번호
    ,EMP.EMP_NM  AS 직원성명
    ,FAM.FAM_NM AS 직원가족성명
FROM
    EMP_C EMP
LEFT OUTER JOIN 
    FAM_C FAM
ON 
    EMP.EMP_NO = FAM.EMP_NO --JOIN KEY
WHERE 
    EMP.EMP_NO  IN ('10001452','11505155')
;