OracleSql.sql

/* 사원 테이블 생성 */
CREATE TABLE TN_MEM
   (
     MEM_ID VARCHAR2(10)    /* 사원ID */
     , CMPY_ID VARCHAR2(10) /* 회사ID */
     , MEM_NM VARCHAR2(50)  /* 사원명 */
     , SEX VARCHAR2(1)      /* 성별 */
     , AGE NUMBER           /* 나이 */
     , JOIN_YMD VARCHAR2(8) /* 입사일 */
     , PART_ID VARCHAR2(10) /* 부서ID */
     , POS_ID VARCHAR2(10)  /* 직급ID */
     , REG_DATE DATE        /* 등록일 */
   )
;

/* 부서 테이블 생성 */
CREATE TABLE TN_PART
   (
     PART_ID VARCHAR2(10)   /* 부서ID */
     , PART_NM VARCHAR2(50) /* 부서명 */
     , PART_MEM_ID VARCHAR2(10) /* 부서장ID (사원ID) */
   )
;

/* 직급 테이블 생성 */
CREATE TABLE TN_POS
   (
     POS_ID VARCHAR2(10)   /* 직급ID */
     , POS_NM VARCHAR2(50) /* 직급명 */
   )
;

/* 연봉 테이블 생성 */
CREATE TABLE TN_SAL
   (
     MEM_ID VARCHAR2(10)  /* 사원ID */
     , SAL_YM VARCHAR2(4) /* 연봉계약년도 */
     , SAL_MONEY NUMBER   /* 연봉금액 (만원) */
   )
;

/* 사원 테이블 PK 설정 */
ALTER TABLE TN_MEM ADD CONSTRAINT TN_MEM_PK PRIMARY KEY (MEM_ID);

/* 부서 테이블 PK 설정 */
ALTER TABLE TN_PART ADD CONSTRAINT TN_PART_PK PRIMARY KEY (PART_ID);

/* 직급 테이블 PK 설정 */
ALTER TABLE TN_POS ADD CONSTRAINT TN_POS_PK PRIMARY KEY (POS_ID);


/* 사원 테이블 데이터 삽입 */
INSERT INTO TN_MEM VALUES ('M001', 'C01', '조말생', '1', 30, '20230101', 'B01', 'G01', TO_DATE('20180827', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M002', 'C01', '김희열', '2', 25, '20240201', 'B01', 'G02', TO_DATE('20230329', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M003', 'C02', '구파발', '1', 40, '20230501', 'B02', 'G03', TO_DATE('20140122', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M004', 'C02', '마파람', '2', 25, '20210401', 'B01', 'G01', TO_DATE('20141213', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M005', 'C03', '이기영', '1', 45, '20231201', 'B02', 'G05', TO_DATE('20240116', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M006', 'C03', '황혜찬', '1', 35, '20240115', 'B03', 'G02', TO_DATE('20211210', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M007', 'C04', '남혜연', '2', 28, '20240310', 'B01', 'G01', TO_DATE('20210915', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M008', 'C04', '조희영', '1', 50, '20220701', 'B02', 'G03', TO_DATE('20130826', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M009', 'C05', '임시영', '2', 30, '20250301', 'B03', 'G02', TO_DATE('20220413', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M010', 'C05', '기파랑', '1', 29, '20241111', 'B01', 'G05', TO_DATE('20101028', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M011', 'C06', '유찬수', '2', 33, '20220215', 'B02', 'G01', TO_DATE('20220331', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M012', 'C06', '박명수', '1', 41, '20230909', 'B03', 'G04', TO_DATE('20190922', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M013', 'C07', '김아영', '2', 38, '20200101', 'B02', 'G03', TO_DATE('20250312', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M014', 'C07', '유재필', '1', 27, '20231025', 'B01', 'G02', TO_DATE('20191229', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M015', 'C08', '기호영', '2', 32, '20251231', 'B03', 'G05', TO_DATE('20241130', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M016', 'C08', '박지훈', '1', 37, '20240215', 'B03', 'G06', TO_DATE('20160108', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M017', 'C09', '송은지', '2', 24, '20230525', 'B01', 'G04', TO_DATE('20241211', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M018', 'C09', '전태준', '1', 42, '20221111', 'B02', 'G02', TO_DATE('20240323', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M019', 'C10', '최현우', '1', 31, '20230520', 'B03', 'G03', TO_DATE('20190510', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M020', 'C10', '정세영', '2', 26, '20240210', 'B01', 'G01', TO_DATE('20210101', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M021', 'C11', '이수진', '1', 34, '20231015', 'B02', 'G02', TO_DATE('20180630', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M022', 'C11', '한지혜', '2', 29, '20230801', 'B01', 'G05', TO_DATE('20181220', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M023', 'C12', '김태훈', '1', 39, '20221125', 'B02', 'G04', TO_DATE('20170315', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M024', 'C12', '박소영', '2', 33, '20241210', 'B03', 'G06', TO_DATE('20231101', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M025', 'C13', '장민호', '1', 41, '20250701', 'B01', 'G02', TO_DATE('20150909', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M026', 'C13', '신미정', '2', 28, '20230215', 'B01', 'G01', TO_DATE('20210115', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M027', 'C14', '이하늘', '1', 36, '20240620', 'B02', 'G03', TO_DATE('20180310', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M028', 'C14', '최민수', '2', 43, '20230518', 'B03', 'G04', TO_DATE('20170825', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M029', 'C15', '황지우', '1', 30, '20240505', 'B01', 'G06', TO_DATE('20211212', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M030', 'C15', '김도현', '2', 32, '20231111', 'B02', 'G05', TO_DATE('20201122', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M031', 'C16', '강백호', '1', 28, '20230815', 'B01', 'G01', TO_DATE('20230815', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M032', 'C16', '채소연', '2', 24, '20240101', 'B02', 'G02', TO_DATE('20240101', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M033', 'C17', '서태웅', '1', 27, '20230320', 'B03', 'G03', TO_DATE('20230320', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M034', 'C17', '강철수', '1', 32, '20120515', 'B01', 'G01', TO_DATE('20120515', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M035', 'C01', '이영희', '2', 29, '20151103', 'B02', 'G02', TO_DATE('20151105', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M036', 'C02', '박준형', '1', 45, '20100228', 'B03', 'G03', TO_DATE('20100302', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M037', 'C03', '최지우', '2', 31, '20170719', 'B01', 'G04', TO_DATE('20170719', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M038', 'C04', '윤도현', '1', 38, '20130912', 'B02', 'G05', TO_DATE('20130915', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M039', 'C05', '한소희', '2', 27, '20190401', 'B03', 'G01', TO_DATE('20190401', 'YYYYMMDD'));
INSERT INTO TN_MEM VALUES ('M040', 'C06', '정우성', '1', 40, '20111010', 'B01', 'G02', TO_DATE('20111012', 'YYYYMMDD'));
COMMIT;

/* 부서 테이블 데이터 삽입 */
INSERT INTO TN_PART VALUES ('B01', '인사부', 'M002');
INSERT INTO TN_PART VALUES ('B02', '장무부', 'M003');
INSERT INTO TN_PART VALUES ('B03', '전산부', 'M005');
INSERT INTO TN_PART VALUES ('B04', '기획부', 'M007');
INSERT INTO TN_PART VALUES ('B05', '영업부', 'M009');
INSERT INTO TN_PART VALUES ('B06', '회계부', 'M011');
INSERT INTO TN_PART VALUES ('B07', '홍보부', 'M013');
INSERT INTO TN_PART VALUES ('B08', '연구개발부', 'M015');
COMMIT;

/* 직급 테이블 데이터 삽입 */
INSERT INTO TN_POS VALUES ('G01', '사원');
INSERT INTO TN_POS VALUES ('G02', '대리');
INSERT INTO TN_POS VALUES ('G03', '과장');
INSERT INTO TN_POS VALUES ('G04', '차장');
INSERT INTO TN_POS VALUES ('G05', '부장');
INSERT INTO TN_POS VALUES ('G06', '이사');
COMMIT;

/* 연봉 테이블 데이터 삽입 */
INSERT INTO TN_SAL VALUES ('M001', '2023', 3000);
INSERT INTO TN_SAL VALUES ('M001', '2024', 3500);
INSERT INTO TN_SAL VALUES ('M002', '2024', 3100);
INSERT INTO TN_SAL VALUES ('M003', '2023', 3000);
INSERT INTO TN_SAL VALUES ('M004', '2024', 3200);
INSERT INTO TN_SAL VALUES ('M005', '2023', 4000);
INSERT INTO TN_SAL VALUES ('M005', '2024', 4500);
INSERT INTO TN_SAL VALUES ('M006', '2024', 3800);
INSERT INTO TN_SAL VALUES ('M007', '2023', 3100);
INSERT INTO TN_SAL VALUES ('M007', '2024', 3400);
INSERT INTO TN_SAL VALUES ('M008', '2023', 5000);
INSERT INTO TN_SAL VALUES ('M009', '2024', 3700);
INSERT INTO TN_SAL VALUES ('M010', '2023', 3300);
INSERT INTO TN_SAL VALUES ('M011', '2024', 3600);
INSERT INTO TN_SAL VALUES ('M012', '2023', 4200);
INSERT INTO TN_SAL VALUES ('M013', '2023', 3900);
INSERT INTO TN_SAL VALUES ('M014', '2024', 4100);
INSERT INTO TN_SAL VALUES ('M015', '2023', 4200);
INSERT INTO TN_SAL VALUES ('M016', '2024', 4300);
INSERT INTO TN_SAL VALUES ('M017', '2023', 3500);
INSERT INTO TN_SAL VALUES ('M018', '2024', 4700);
INSERT INTO TN_SAL VALUES ('M019', '2023', 3100);
INSERT INTO TN_SAL VALUES ('M019', '2024', 3600);
INSERT INTO TN_SAL VALUES ('M020', '2024', 3900);
INSERT INTO TN_SAL VALUES ('M021', '2023', 4200);
INSERT INTO TN_SAL VALUES ('M022', '2024', 3500);
INSERT INTO TN_SAL VALUES ('M023', '2023', 4700);
INSERT INTO TN_SAL VALUES ('M024', '2024', 4400);
INSERT INTO TN_SAL VALUES ('M025', '2023', 5000);
INSERT INTO TN_SAL VALUES ('M026', '2024', 4100);
INSERT INTO TN_SAL VALUES ('M027', '2023', 4500);
INSERT INTO TN_SAL VALUES ('M028', '2024', 4600);
INSERT INTO TN_SAL VALUES ('M029', '2023', 4300);
INSERT INTO TN_SAL VALUES ('M029', '2024', 4800);
INSERT INTO TN_SAL VALUES ('M030', '2023', 5200);
INSERT INTO TN_SAL VALUES ('M030', '2024', 5500);
COMMIT;

/* 프로젝트 테이블 생성 */
CREATE TABLE TN_PRJT
   (
     PRJT_ID VARCHAR2(10) CONSTRAINT PK_TN_PRJT PRIMARY KEY /* 프로젝트ID */
     , PRJT_NM VARCHAR2(100)      /* 프로젝트명 */
     , STR_YMD VARCHAR2(8)        /* 프로젝트시작일 */
     , END_YMD VARCHAR2(8)        /* 프로젝트종료일 */
     , ORDERER_NM VARCHAR2(100)   /* 발주처명 */
     , ORDER_AMT NUMBER           /* 발주금액 */
     , SUCBD_AMT NUMBER           /* 낙찰금액 */
     , SUCBD_CMPY_ID VARCHAR2(10) /* 낙찰회사ID */
   )
;
/* 컬럼에 코멘트 추가 */
COMMENT ON COLUMN TN_PRJT.PRJT_ID IS '프로젝트 ID';
COMMENT ON COLUMN TN_PRJT.PRJT_NM IS '프로젝트명';
COMMENT ON COLUMN TN_PRJT.STR_YMD IS '프로젝트 시작일';
COMMENT ON COLUMN TN_PRJT.END_YMD IS '프로젝트 종료일';
COMMENT ON COLUMN TN_PRJT.ORDERER_NM IS '발주처명';
COMMENT ON COLUMN TN_PRJT.ORDER_AMT IS '발주 금액';
COMMENT ON COLUMN TN_PRJT.SUCBD_AMT IS '낙찰 금액';
COMMENT ON COLUMN TN_PRJT.SUCBD_CMPY_ID IS '낙찰 회사 ID';

/* SI업체 테이블 생성 */
CREATE TABLE TN_CMPY
   (
     CMPY_ID VARCHAR2(10) CONSTRAINT PK_TN_CMPY PRIMARY KEY /* 회사ID */
     , CMPY_NM VARCHAR2(100)                                /* 회사명 */
     , STR_YMD VARCHAR2(8)                                  /* 창업일 */
   )
;

/* 컬럼에 코멘트 추가 */
COMMENT ON COLUMN TN_CMPY.CMPY_ID IS '회사 ID';
COMMENT ON COLUMN TN_CMPY.CMPY_NM IS '회사명';
COMMENT ON COLUMN TN_CMPY.STR_YMD IS '창업일';

/* 프로젝트참여사 테이블 생성 */
CREATE TABLE TN_PRJC
   (
     PRJT_ID VARCHAR2(10)   /* 프로젝트ID */
     , CMPY_ID VARCHAR2(10) /* 프로젝트참여회사ID */
     , CMPY_YN VARCHAR2(1)  /* 프로젝트참여사메인여부(Y/N) */
   )
;

/* 컬럼에 코멘트 추가 */
COMMENT ON COLUMN TN_PRJC.PRJT_ID IS '프로젝트 ID';
COMMENT ON COLUMN TN_PRJC.CMPY_ID IS '프로젝트 참여 회사 ID';
COMMENT ON COLUMN TN_PRJC.CMPY_YN IS '프로젝트 참여사 메인 여부 (Y/N)';

/* 프로젝트인력 테이블 생성 */
CREATE TABLE TN_PRJM
   (
     PRJT_ID VARCHAR2(10)     /* 프로젝트ID */
     , MEM_ID VARCHAR2(10)    /* 프로젝트참여인력ID(TN_MEM.MEM_ID) */
     , CMPY_ID VARCHAR2(10)   /* 프로젝트참여회사ID(TN_PRJC.CMPY_ID) */
     , STR_YMD VARCHAR2(8)    /* 프로젝트참여시작일 */
     , END_YMD VARCHAR2(8)    /* 프로젝트참여종료일 */
     , PRJT_JOB VARCHAR2(100) /* 프로젝트참여업무 */
   )
;

/* 컬럼에 코멘트 추가 */
COMMENT ON COLUMN TN_PRJM.PRJT_ID IS '프로젝트 ID';
COMMENT ON COLUMN TN_PRJM.MEM_ID IS '프로젝트 참여 인력 ID';
COMMENT ON COLUMN TN_PRJM.CMPY_ID IS '프로젝트 참여 회사 ID';
COMMENT ON COLUMN TN_PRJM.STR_YMD IS '프로젝트 참여 시작일';
COMMENT ON COLUMN TN_PRJM.END_YMD IS '프로젝트 참여 종료일';
COMMENT ON COLUMN TN_PRJM.PRJT_JOB IS '프로젝트 참여 업무';

/* 프로젝트산출물 테이블 생성 */
CREATE TABLE TN_PRJS
   (
     PRJT_ID VARCHAR2(10)    /* 프로젝트ID */
     , DOC_ID VARCHAR2(10)   /* 프로젝트산출물ID */
     , DOC_NM VARCHAR2(50)   /* 프로젝트산출물명 */
     , DOC_TP VARCHAR2(10)   /* 산출물유형(보고서, 분석, 설계, 소스, 테스트) */
     , FIL_NM VARCHAR2(100)  /* 산출물파일명 */
     , FIL_TP VARCHAR2(10)   /* 파일확장자명(JS, HTML, HWP, PPT 등) */
     , WRT_ID VARCHAR2(10)   /* 작성자ID */
     , WRT_DT DATE           /* 작성일시 */
   )
;

/* TN_PRJT(프로젝트) 테이블 데이터 삽입 */
INSERT INTO TN_PRJT VALUES ('P01', '통계 시스템 고도화 프로젝트', '20220101', '20221231', '통계청', 6000000000, 5800000000, 'C03');
INSERT INTO TN_PRJT VALUES ('P02', '교육 데이터 플랫폼 구축', '20210501', '20230501', '교육부', 4500000000, 4300000000, 'C08');
INSERT INTO TN_PRJT VALUES ('P03', '스마트 도로 관리 시스템', '20220715', '20230714', '국토부', 3000000000, 2900000000, 'C01');
INSERT INTO TN_PRJT VALUES ('P04', '산업 데이터 통합 플랫폼', '20210901', '20220901', '산업부', 2000000000, 1900000000, 'C04');
INSERT INTO TN_PRJT VALUES ('P05', '공공 데이터 통합 관리 시스템', '20120301', '20121231', '행안부', 7000000000, 6800000000, 'C05');
INSERT INTO TN_PRJT VALUES ('P06', '스마트 시티 구축 프로젝트', '20230301', '20240301', '국토부', 5500000000, 5300000000, 'C03');
INSERT INTO TN_PRJT VALUES ('P07', '환경 모니터링 시스템 개발', '20150515', '20170515', '환경부', 4000000000, 3900000000, 'C09');
INSERT INTO TN_PRJT VALUES ('P08', '의료 데이터 분석 플랫폼', '20200101', '20211231', '보건복지부', 5000000000, 4800000000, 'C06');
INSERT INTO TN_PRJT VALUES ('P09', '첨단 농업 기술 지원 시스템', '20180115', '20191215', '농림부', 4500000000, 4400000000, 'C02');
INSERT INTO TN_PRJT VALUES ('P10', '국방 통합 정보 관리 시스템', '20240401', '20260401', '국방부', 8000000000, 7700000000, 'C10');
INSERT INTO TN_PRJT VALUES ('P11', '문화재 디지털 보존 프로젝트', '20170320', '20190320', '문화재청', 3500000000, 3400000000, 'C05');
INSERT INTO TN_PRJT VALUES ('P12', '교통 빅데이터 플랫폼 구축', '20190801', '20200801', '교통부', 6000000000, 5800000000, 'C12');
INSERT INTO TN_PRJT VALUES ('P13', '산림 보호 디지털 관리 시스템', '20210801', '20231231', '산림청', 3000000000, 2900000000, 'C13');
INSERT INTO TN_PRJT VALUES ('P14', '해양 데이터 분석 및 관리 플랫폼', '20201015', '20221015', '해양수산부', 4000000000, 3900000000, 'C14');
INSERT INTO TN_PRJT VALUES ('P15', '스마트 에너지 관리 시스템 구축', '20230301', '20250301', '산업통상자원부', 7500000000, 7300000000, 'C08');
INSERT INTO TN_PRJT VALUES ('P16', 'AI 기반 통계 예측 시스템', '20230101', '20231231', '통계청', 6200000000, 6000000000, 'C01');
INSERT INTO TN_PRJT VALUES ('P17', '스마트 교육 콘텐츠 플랫폼', '20210501', '20240501', '교육부', 4600000000, 4500000000, 'C02');
INSERT INTO TN_PRJT VALUES ('P18', '첨단 도로 관리 솔루션 개발', '20220715', '20240714', '국토부', 3100000000, 3000000000, 'C06');
INSERT INTO TN_PRJT VALUES ('P19', '산업 IoT 데이터 통합 플랫폼', '20210901', '20230901', '산업부', 2500000000, 2400000000, 'C04');
INSERT INTO TN_PRJT VALUES ('P20', '공공 데이터 보안 강화 시스템', '20220301', '20230301', '행안부', 7500000000, 7300000000, 'C10');
INSERT INTO TN_PRJT VALUES ('P21', '스마트 환경 모니터링 시스템', '20180515', '20200515', '환경부', 4200000000, 4100000000, 'C09');
INSERT INTO TN_PRJT VALUES ('P22', '차세대 의료 빅데이터 플랫폼', '20190101', '20210101', '보건복지부', 5200000000, 5000000000, 'C08');
INSERT INTO TN_PRJT VALUES ('P23', '스마트 농업 기술 지원 시스템', '20220115', '20240115', '농림부', 4800000000, 4700000000, 'C02');
INSERT INTO TN_PRJT VALUES ('P24', '국방 네트워크 보안 시스템', '20240401', '20260401', '국방부', 8500000000, 8300000000, 'C11');
INSERT INTO TN_PRJT VALUES ('P25', '디지털 문화유산 아카이브', '20210320', '20230320', '문화재청', 3700000000, 3600000000, 'C05');
INSERT INTO TN_PRJT VALUES ('P26', '가상현실 기반 교육 시스템', '20230101', '20231231', '교육부', 4800000000, 4700000000, 'C99');
INSERT INTO TN_PRJT VALUES ('P27', 'AI 교통 제어 시스템', '20240501', '20230430', '교통부', 7000000000, 6800000000, 'C12');

COMMIT;

/* TN_CMPY(SI업체) 테이블 데이터 삽입 */
INSERT INTO TN_CMPY VALUES ('C01', '(주)인아아이티', '20130301');
INSERT INTO TN_CMPY VALUES ('C02', '(주)스마트솔루션', '20150120');
INSERT INTO TN_CMPY VALUES ('C03', '(주)클라우드테크', '20170515');
INSERT INTO TN_CMPY VALUES ('C04', '(주)데이터사이언스', '20180910');
INSERT INTO TN_CMPY VALUES ('C05', '(주)오픈테크놀로지', '20080512');
INSERT INTO TN_CMPY VALUES ('C06', '(주)아이오티랩', '20210425');
INSERT INTO TN_CMPY VALUES ('C07', '(주)빅데이터인사이트', '20111011');
INSERT INTO TN_CMPY VALUES ('C08', '(주)헬스케어솔루션', '20091115');
INSERT INTO TN_CMPY VALUES ('C09', '(주)스마트팜시스템', '20200830');
INSERT INTO TN_CMPY VALUES ('C10', '(주)시큐리티네트웍스', '20130518');
INSERT INTO TN_CMPY VALUES ('C11', '(주)디지털문화유산', '20210601');
INSERT INTO TN_CMPY VALUES ('C12', '(주)교통시스템랩', '20020414');
INSERT INTO TN_CMPY VALUES ('C13', '(주)산림디지털', '20140322');
INSERT INTO TN_CMPY VALUES ('C14', '(주)해양데이터솔루션', '20200909');
INSERT INTO TN_CMPY VALUES ('C15', '(주)에너지스마트', '20240201');
INSERT INTO TN_CMPY VALUES ('C16', '(주)지능형로보틱스', '20231210');
INSERT INTO TN_CMPY VALUES ('C17', '(주)차세대클라우드', '20230815');

COMMIT;

/* TN_PRJC(프로젝트 참여사) 테이블 데이터 삽입 */
INSERT INTO TN_PRJC VALUES ('P01', 'C03', 'Y');  /* 프로젝트 P01은 회사 C03이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P02', 'C08', 'N');  /* 프로젝트 P02는 회사 C08이 메인 참여사가 아님 (N) */
INSERT INTO TN_PRJC VALUES ('P03', 'C01', 'Y');  /* 프로젝트 P03은 회사 C01이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P04', 'C04', 'N');  /* 프로젝트 P04는 회사 C04가 메인 참여사가 아님 (N) */
INSERT INTO TN_PRJC VALUES ('P05', 'C05', 'Y');  /* 프로젝트 P05는 회사 C05가 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P06', 'C03', 'Y');  /* 프로젝트 P06은 회사 C03이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P07', 'C09', 'Y');  /* 프로젝트 P07은 회사 C09가 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P08', 'C06', 'N');  /* 프로젝트 P08은 회사 C06이 메인 참여사가 아님 (N) */
INSERT INTO TN_PRJC VALUES ('P09', 'C02', 'Y');  /* 프로젝트 P09는 회사 C02가 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P10', 'C10', 'Y');  /* 프로젝트 P10은 회사 C10이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P11', 'C05', 'N');  /* 프로젝트 P11은 회사 C05가 메인 참여사가 아님 (N) */
INSERT INTO TN_PRJC VALUES ('P12', 'C12', 'Y');  /* 프로젝트 P12는 회사 C12가 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P13', 'C13', 'Y');  /* 프로젝트 P13은 회사 C13이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P14', 'C14', 'N');  /* 프로젝트 P14는 회사 C14가 메인 참여사가 아님 (N) */
INSERT INTO TN_PRJC VALUES ('P15', 'C08', 'Y');  /* 프로젝트 P15는 회사 C08이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P16', 'C01', 'N');  /* 프로젝트 P16은 회사 C01이 메인 참여사가 아님 (N) */
INSERT INTO TN_PRJC VALUES ('P17', 'C02', 'Y');  /* 프로젝트 P17은 회사 C02가 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P18', 'C06', 'Y');  /* 프로젝트 P18은 회사 C06이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P19', 'C04', 'Y');  /* 프로젝트 P19는 회사 C04가 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P20', 'C10', 'N');  /* 프로젝트 P20은 회사 C10이 메인 참여사가 아님 (N) */
INSERT INTO TN_PRJC VALUES ('P21', 'C09', 'Y');  /* 프로젝트 P21은 회사 C09가 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P22', 'C08', 'Y');  /* 프로젝트 P22는 회사 C08이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P23', 'C02', 'N');  /* 프로젝트 P23은 회사 C02가 메인 참여사가 아님 (N) */
INSERT INTO TN_PRJC VALUES ('P24', 'C11', 'Y');  /* 프로젝트 P24는 회사 C11이 메인 참여사 (Y) */
INSERT INTO TN_PRJC VALUES ('P25', 'C05', 'Y');  /* 프로젝트 P25는 회사 C05가 메인 참여사 (Y) */

COMMIT;

/* TN_PRJM(프로젝트 인력) 테이블 데이터 삽입 */
INSERT INTO TN_PRJM VALUES ('P01', 'M003', 'C03', '20220101', '20221231', 'PL');
INSERT INTO TN_PRJM VALUES ('P01', 'M020', 'C03', '20220101', '20221231', 'PM');
INSERT INTO TN_PRJM VALUES ('P01', 'M021', 'C03', '20220101', '20221231', '개발자');
INSERT INTO TN_PRJM VALUES ('P01', 'M022', 'C03', '20220101', '20221231', '디자이너');
INSERT INTO TN_PRJM VALUES ('P01', 'M023', 'C03', '20220101', '20221231', 'QA 엔지니어');
INSERT INTO TN_PRJM VALUES ('P02', 'M007', 'C02', '20210501', '20230501', 'PM');
INSERT INTO TN_PRJM VALUES ('P03', 'M012', 'C03', '20220715', '20230714', 'QA 매니저');
INSERT INTO TN_PRJM VALUES ('P04', 'M004', 'C04', '20210901', '20220901', '디자이너');
INSERT INTO TN_PRJM VALUES ('P05', 'M009', 'C05', '20120301', '20121231', '개발자');
INSERT INTO TN_PRJM VALUES ('P06', 'M002', 'C03', '20230301', '20240301', 'DBA');
INSERT INTO TN_PRJM VALUES ('P07', 'M014', 'C09', '20150515', '20170515', '데이터 엔지니어');
INSERT INTO TN_PRJM VALUES ('P08', 'M010', 'C06', '20200101', '20211231', 'QA 엔지니어');
INSERT INTO TN_PRJM VALUES ('P09', 'M001', 'C02', '20180115', '20191215', '퍼블리셔');
INSERT INTO TN_PRJM VALUES ('P10', 'M013', 'C10', '20240401', '20260401', '보안 엔지니어');
INSERT INTO TN_PRJM VALUES ('P11', 'M015', 'C05', '20170320', '20190320', '개발자');
INSERT INTO TN_PRJM VALUES ('P12', 'M005', 'C12', '20190801', '20200801', '데이터 엔지니어');
INSERT INTO TN_PRJM VALUES ('P13', 'M011', 'C13', '20210801', '20231231', '디자이너');
INSERT INTO TN_PRJM VALUES ('P14', 'M006', 'C14', '20201015', '20221015', 'PM');
INSERT INTO TN_PRJM VALUES ('P15', 'M008', 'C08', '20230301', '20250301', '개발자');
INSERT INTO TN_PRJM VALUES ('P16', 'M004', 'C01', '20230101', '20231231', 'QA 매니저');
INSERT INTO TN_PRJM VALUES ('P17', 'M009', 'C02', '20210501', '20240501', '퍼블리셔');
INSERT INTO TN_PRJM VALUES ('P18', 'M003', 'C06', '20220715', '20240714', 'DBA');
INSERT INTO TN_PRJM VALUES ('P19', 'M014', 'C04', '20210901', '20230901', '개발자');
INSERT INTO TN_PRJM VALUES ('P20', 'M002', 'C10', '20220301', '20230301', '개발자');
INSERT INTO TN_PRJM VALUES ('P21', 'M011', 'C09', '20180515', '20200515', '디자이너');
INSERT INTO TN_PRJM VALUES ('P22', 'M007', 'C08', '20190101', '20210101', 'PL');
INSERT INTO TN_PRJM VALUES ('P22', 'M026', 'C08', '20190101', '20210101', 'PM');
INSERT INTO TN_PRJM VALUES ('P22', 'M027', 'C08', '20190101', '20210101', '개발자');
INSERT INTO TN_PRJM VALUES ('P22', 'M028', 'C08', '20190101', '20210101', '개발자');
INSERT INTO TN_PRJM VALUES ('P22', 'M029', 'C08', '20190101', '20210101', '디자이너');
INSERT INTO TN_PRJM VALUES ('P23', 'M005', 'C02', '20220115', '20240115', '개발자');
INSERT INTO TN_PRJM VALUES ('P24', 'M013', 'C11', '20240401', '20260401', '보안 엔지니어');
INSERT INTO TN_PRJM VALUES ('P25', 'M015', 'C05', '20210320', '20230320', '개발자');

COMMIT;

/* TN_PRJS(프로젝트 산출물) 테이블 데이터 삽입 */
INSERT INTO TN_PRJS VALUES ('P01', 'D0001', '요구사항정의서', 'ANL', '요구사항정의서_V1.0.hwp', 'HWP', 'M003', '20220515');
INSERT INTO TN_PRJS VALUES ('P02', 'D0002', '기능명세서', 'DSN', '기능명세서_V1.1.docx', 'DOCX', 'M007', '20220110');
INSERT INTO TN_PRJS VALUES ('P03', 'D0003', '소스코드', 'SRC', '프로그램코드.zip', 'ZIP', 'M012', '20221101');
INSERT INTO TN_PRJS VALUES ('P04', 'D0004', '테스트케이스', 'TST', '테스트케이스.xlsx', 'XLSX', 'M004', '20211120');
INSERT INTO TN_PRJS VALUES ('P05', 'D0005', '프로젝트보고서', 'PRT', '프로젝트보고서_V1.0.pdf', 'PDF', 'M009', '20121115');
INSERT INTO TN_PRJS VALUES ('P06', 'D0006', '데이터베이스설계서', 'DSN', 'DB설계서_V2.0.docx', 'DOCX', 'M002', '20230705');
INSERT INTO TN_PRJS VALUES ('P07', 'D0007', '데이터분석보고서', 'ANL', '데이터분석보고서.pdf', 'PDF', 'M014', '20160630');
INSERT INTO TN_PRJS VALUES ('P08', 'D0008', 'QA테스트보고서', 'TST', 'QA테스트결과.xlsx', 'XLSX', 'M010', '20201120');
INSERT INTO TN_PRJS VALUES ('P09', 'D0009', 'UI디자인명세서', 'DSN', 'UI디자인명세서.hwp', 'HWP', 'M001', '20190110');
INSERT INTO TN_PRJS VALUES ('P10', 'D0010', '보안테스트보고서', 'TST', '보안테스트보고서.pdf', 'PDF', 'M013', '20250225');
INSERT INTO TN_PRJS VALUES ('P11', 'D0011', 'PM보고서', 'PRT', 'PM보고서_V3.0.docx', 'DOCX', 'M015', '20181101');
INSERT INTO TN_PRJS VALUES ('P12', 'D0012', '데이터엔지니어링문서', 'ANL', '데이터엔지니어링.hwp', 'HWP', 'M005', '20190915');
INSERT INTO TN_PRJS VALUES ('P13', 'D0013', '디자인가이드라인', 'DSN', '디자인가이드.pdf', 'PDF', 'M011', '20220310');
INSERT INTO TN_PRJS VALUES ('P14', 'D0014', 'PM계획서', 'PRT', 'PM계획서.docx', 'DOCX', 'M006', '20210420');
INSERT INTO TN_PRJS VALUES ('P15', 'D0015', '개발코드', 'SRC', '개발코드.zip', 'ZIP', 'M008', '20240215');
INSERT INTO TN_PRJS VALUES ('P16', 'D0016', 'QA매니저보고서', 'PRT', 'QA매니저보고서_V1.0.docx', 'DOCX', 'M004', '20230720');
INSERT INTO TN_PRJS VALUES ('P17', 'D0017', '퍼블리싱결과물', 'DSN', '퍼블리싱결과물.hwp', 'HWP', 'M009', '20220415');
INSERT INTO TN_PRJS VALUES ('P18', 'D0018', 'DB설계도', 'DSN', 'DB설계도.pdf', 'PDF', 'M003', '20230930');
INSERT INTO TN_PRJS VALUES ('P19', 'D0019', '코드구현보고서', 'SRC', '코드구현보고서.zip', 'ZIP', 'M014', '20220205');
INSERT INTO TN_PRJS VALUES ('P20', 'D0020', '개발자테스트보고서', 'TST', '개발자테스트결과.pdf', 'PDF', 'M002', '20230210');
INSERT INTO TN_PRJS VALUES ('P21', 'D0021', '디자인샘플', 'DSN', '디자인샘플.docx', 'DOCX', 'M011', '20190501');
INSERT INTO TN_PRJS VALUES ('P22', 'D0022', 'PL결과보고서', 'PRT', 'PL결과보고서.hwp', 'HWP', 'M007', '20200510');
INSERT INTO TN_PRJS VALUES ('P23', 'D0023', '소스코드패키지', 'SRC', '소스코드패키지.zip', 'ZIP', 'M005', '20231015');
INSERT INTO TN_PRJS VALUES ('P24', 'D0024', '보안결과보고서', 'TST', '보안결과보고서.xlsx', 'XLSX', 'M013', '20250505');
INSERT INTO TN_PRJS VALUES ('P25', 'D0025', '기술보고서', 'PRT', '기술보고서.pdf', 'PDF', 'M015', '20220330');
INSERT INTO TN_PRJS VALUES ('P26', 'D0026', '아키텍처보고서', 'TRT', '아키텍처보고서.pdf', 'TTF', 'M022', '20240330');

COMMIT;

UPDATE TN_PRJS
SET FIL_TP = 'HTML'
WHERE FIL_TP = 'PDF'
;

COMMIT;

SELECT *
  FROM TN_MEM
;

SELECT *
  FROM TN_PART
;

SELECT *
  FROM TN_SAL
;

SELECT *
  FROM TN_POS
;

SELECT *
  FROM TN_CMPY 
;

SELECT *
  FROM TN_PRJC
;

SELECT *
  FROM TN_PRJM
;

SELECT *
  FROM TN_PRJS
;

SELECT *
  FROM TN_PRJT
;

SELECT COUNT(DISTINCT A.MEM_ID) AS CNT1
  FROM TN_MEM A
;

SELECT COUNT(*) AS CNT1
     , COUNT(DISTINCT A.MEM_ID) AS CNT2  
  FROM TN_MEM A
  LEFT OUTER JOIN TN_PRJM B
         ON A.MEM_ID = B.MEM_ID
;

SELECT *
  FROM TN_MEM
;

SELECT *
  FROM TN_PRJM
;

/* 2-3 프로젝트 참여한 사원들의 프로젝트 참여 횟수를 구하시오 */
SELECT A.MEM_ID
     , A.MEM_NM
     , COUNT(B.PRJT_ID) AS PRJT_CNT
  FROM TN_MEM A
 INNER JOIN TN_PRJM B
         ON A.MEM_ID = B.MEM_ID
 GROUP BY A.MEM_ID
        , A.MEM_NM
;

/* 2-4 전체 사원의 프로젝트 참여 횟수를 구하시오, 프로젝트 참여한 적 없는 사원은 0으로 표시하시오 */
SELECT A.MEM_ID
     , A.MEM_NM
     , CASE WHEN COUNT(B.PRJT_ID) = 0 THEN 0
            WHEN COUNT(B.PRJT_ID) <> 0 THEN COUNT(B.PRJT_ID) 
        END AS PRJT_CNT
  FROM TN_MEM A
  LEFT OUTER JOIN TN_PRJM B
               ON A.MEM_ID = B.MEM_ID
 GROUP BY A.MEM_ID
        , A.MEM_NM
 ORDER BY 1 
;

/* 
 2-5 한번이라도 프로젝트에 참여한 사원의, 직급별 프로젝트 참여 횟수를 구하시오. 
 1 : N ( TN_POS : TN_PRJM )
 
*/
SELECT A.POS_ID
     , A.POS_NM
     , COUNT(PRJT_ID) AS PRJT_CNT
  FROM TN_POS A
 INNER JOIN TN_MEM B
         ON A.POS_ID = B.POS_ID
 INNER JOIN TN_PRJM C
         ON B.MEM_ID = C.MEM_ID
 GROUP BY A.POS_ID
        , A.POS_NM
;

/*
    EXISTS문 활용
*/
SELECT A.POS_ID
     , A.POS_NM
     , COUNT(PRJT_ID) AS PRJT_CNT
  FROM TN_POS A
 INNER JOIN TN_MEM B
         ON A.POS_ID = B.POS_ID
 WHERE EXISTS (
    SELECT 1
      FROM TN_PRJM C
    WHERE C.MEM_ID = B.MEM_ID
 )
 INNER JOIN TN_PRJM C
         ON B.MEM_ID = C.MEM_ID
 GROUP BY A.POS_ID
        , A.POS_NM
;

SELECT A.POS_ID
     , A.POS_NM
     , COUNT(C.PRJT_ID) AS PRJT_CNT
  FROM TN_POS A
 INNER JOIN TN_MEM B
         ON A.POS_ID = B.POS_ID
 INNER JOIN TN_PRJM C
         ON B.MEM_ID = C.MEM_ID
 WHERE EXISTS (
    SELECT 1
      FROM TN_PRJM C2
     WHERE B.MEM_ID = C2.MEM_ID
 )
 GROUP BY A.POS_ID
     , A.POS_NM
;


SELECT *
  FROM TN_POS
;

SELECT COUNT(*)
  FROM TN_MEM A
 INNER JOIN TN_PRJM B
         ON A.MEM_ID = B.MEM_ID
;

SELECT *
  FROM TN_PRJM
;

/*
 2-6 '전체 프로젝트'와, '전체 IT업체'와의 프로젝트 참여 이력을 출력하시오
 프로젝트ID, 프로젝트명, 참여여부, 회사ID, 회사명
 + 모든 프로젝트와 모든 회사??가 출력되게...
*/
SELECT A.PRJT_ID
     , B.PRJT_NM
     , A.CMPY_YN
     , C.CMPY_ID
     , C.CMPY_NM
  FROM TN_PRJC A
  LEFT OUTER JOIN TN_PRJT B
              ON A.PRJT_ID = B.PRJT_ID
  LEFT OUTER JOIN TN_CMPY C
         ON A.CMPY_ID = C.CMPY_ID
;

SELECT A.PRJT_ID
     , B.PRJT_NM
     , A.CMPY_YN
     , C.CMPY_ID
     , C.CMPY_NM
  FROM TN_PRJC A 
  LEFT OUTER JOIN TN_PRJT B
               ON A.PRJT_ID = B.PRJT_ID
  FULL OUTER JOIN TN_CMPY C
               ON A.CMPY_ID = C.CMPY_ID
;

/*
 5-1
 모든 프로젝트의 낙찰회사명(스칼라 서브쿼리)과 프로젝트별 참여인원수(인라인 서브쿼리)를 출력하시오
*/
SELECT A.PRJT_ID
     , A.PRJT_NM
     , A.SUCBD_CMPY_ID
     , (
        SELECT B.CMPY_NM
          FROM TN_CMPY B
          
         WHERE B.CMPY_ID = A.SUCBD_CMPY_ID
       ) AS SUCBD_CMPY_NM
     , C.MEM_CNT
  FROM TN_PRJT A
 INNER JOIN (
             SELECT A.PRJT_ID
                  , COUNT(B.MEM_ID) AS MEM_CNT
               FROM TN_PRJT A
              INNER JOIN TN_PRJM B
                      ON A.PRJT_ID = B.PRJT_ID
              GROUP BY A.PRJT_ID
            ) C
          ON A.PRJT_ID = C.PRJT_ID
;

/*
 5-2
 프로젝트 참여인원수가 5명 이상인 프로젝트 출력(중첩 서브 쿼리)
*/
SELECT *
  FROM TN_PRJT
 WHERE PRJT_ID IN (
                   SELECT PRJT_ID
                     FROM TN_PRJM
                    GROUP BY PRJT_ID
                    HAVING COUNT(MEM_ID) > 4
                  )
;

/*
 5-3
 프로젝트 참여사 중 창업이 2020년 이후인 프로젝트를 출력(중첩 서브 쿼리)
*/
SELECT *
  FROM TN_PRJC
 WHERE CMPY_ID IN (
                   SELECT CMPY_ID
                     FROM TN_CMPY
                    WHERE STR_YMD > '20200101'
 ) 
;

/*
 7-1
 모든 프로젝트의 년도별 참여인원에 대한 직급별 인원수를 출력
*/

SELECT *
  FROM TN_PRJT
;

SELECT *
  FROM TN_PRJM
;

SELECT *
  FROM TN_MEM
;

SELECT *
  FROM TN_POS
;

WITH CALENDAR AS (
SELECT LEVEL + 2009 AS YEAR
  FROM DUAL
 CONNECT BY LEVEL <= 21
)
SELECT B.YEAR
     , A.PRJT_ID
     , A.PRJT_NM
     , SUBSTR(A.STR_YMD, 1, 4) AS PRJT_STR_YMD
     , SUBSTR(A.END_YMD, 1, 4) AS PRJT_END_YMD
     , COUNT(C.MEM_ID) AS PRJT_MEM_CNT
     , COUNT(CASE WHEN E.POS_NM = '사원' THEN 1 END) AS 사원
     , COUNT(CASE WHEN E.POS_NM = '대리' THEN 1 END) AS 대리
     , COUNT(CASE WHEN E.POS_NM = '과장' THEN 1 END) AS 과장
     , COUNT(CASE WHEN E.POS_NM = '차장' THEN 1 END) AS 차장
     , COUNT(CASE WHEN E.POS_NM = '부장' THEN 1 END) AS 부장
     , COUNT(CASE WHEN E.POS_NM = '이사' THEN 1 END) AS 이사
  FROM TN_PRJT A
 INNER JOIN CALENDAR B
         ON B.YEAR BETWEEN SUBSTR(A.STR_YMD, 1, 4) AND SUBSTR(A.END_YMD, 1, 4) 
  LEFT OUTER JOIN TN_PRJM C
               ON A.PRJT_ID = C.PRJT_ID 
               AND B.YEAR BETWEEN SUBSTR(C.STR_YMD, 1, 4) AND SUBSTR(C.END_YMD,1 ,4)
  INNER JOIN TN_MEM D
          ON C.MEM_ID = D.MEM_ID
  INNER JOIN TN_POS E
          ON D.POS_ID = E.POS_ID 
 GROUP BY B.YEAR
     , A.PRJT_ID
     , A.PRJT_NM
     , SUBSTR(A.STR_YMD, 1, 4)
     , SUBSTR(A.END_YMD, 1, 4)
 ORDER BY 1, 2
;

--SQL 실전연습

/*
 8-1 사원의 나이 기준 오름차순으로 전체 사원 출력
*/
SELECT *
  FROM TN_MEM
 ORDER BY 4
;

/*
 8-2 사원의 나이 기준 내림차순에서 11번째에서 20번까지의 순위
*/
SELECT *
  FROM (
        SELECT MEM_ID
             , MEM_NM
             , AGE
             , DENSE_RANK() OVER(ORDER BY AGE DESC) AS RANK
          FROM TN_MEM
       ) A
 WHERE A.RANK BETWEEN 11 AND 20
;

/*
 8-3 사원의 입사일 기준 오름차순에서 한 페이지 당 10개씩 게시판에 출력할 때, 마지막 페이지 출력
 마지막 페이지 = RUNM : 31, 32, 33
 COUNT / 10 
*/
SELECT *
  FROM (
        SELECT MEM_ID
             , MEM_NM
             , AGE
             , ROW_NUMBER() OVER(ORDER BY JOIN_YMD) AS RNUM
             , COUNT(*) OVER() AS TOTAL_CNT
          FROM TN_MEM      
       )
 WHERE RNUM > (CEIL(TOTAL_CNT/10.0)-1)*10
;

/*
 8-4 현재 연도 기준으로 최근 3년간 프로젝트 시작일 기준으로 최근 프로젝트 우선으로 출력
  현재 년도 SUBSTR(SYSDATE, 1, 4) AS YY
  , 최근 3년간 SUBSTR(SYSDATE, 1, 4) BETWEEN AND -- NUMBER, DATE
  , 문자를 기반으로 할 때 BETWEEN AND는, 길이가 일정하지 않을 경우 '문자형 숫자'를 비교할 때, '사전 순'(ASCII 코드 순)이기 때문에 문제가 생긴다.
  
  2  < 10 (숫자 기준 O, 문자 기준 X)
  10 < 2 (숫자 기준 X, 문자 기준 O)
  , ASCII코드 문자 기준으로 '1' = 49, '2' = 50 이기 때문에
  
  정확히 3년 전을 구하기 위해
  ADD_MONTHS ( DATE, INTEGER ) : DATE와 정수 '월'을 더한 날짜를 반환한다.
*/
SELECT ADD_MONTHS(SYSDATE, -36) FROM DUAL
;

SELECT *
  FROM TN_PRJT
 WHERE STR_YMD BETWEEN 20220509 AND 20250509
 ORDER BY 3 DESC
;

/*
 8-5 전체 프로젝트의 발주금액을 한글 금액으로 출력, 발주금액(한글) 내림차순으로 출력
 CASE WHEN A컬럼 THEN A결과 END
 || : '문자열' 연결 연산자
 TRIM : '문자열'의 공백 또는 특정 문자 제거
 TRUNC(NUMBER) : '숫자'에서 소수점 이하 버림
 MOD(N1, N2) : N1을 N2로 나눈 나머지
*/
SELECT PRJT_ID
     , PRJT_NM
     , TRIM(
            CASE 
                WHEN ORDER_AMT >= 100000000 THEN TRUNC(ORDER_AMT/100000000)||'억' 
                ELSE ''
            END ||
            CASE 
                WHEN MOD(ORDER_AMT, 100000000) >= 10000000 THEN TRUNC(MOD(ORDER_AMT, 100000000)/10000000)||'천'
                ELSE ''
            END ||
            CASE
                WHEN MOD(ORDER_AMT, 10000000) >= 1000000 THEN TRUNC(MOD(ORDER_AMT, 10000000)/1000000)||'백만원'
                ELSE ''
            END
           ) AS "발주금액(한글)"
  FROM TN_PRJT
;

/*
 8-6 연도별 프로젝트 건수
*/
SELECT SUBSTR(STR_YMD, 1, 4) AS YYYY
     , COUNT(PRJT_ID) AS PRJT_CNT
  FROM TN_PRJT
 GROUP BY SUBSTR(STR_YMD, 1, 4)
 ORDER BY 2 DESC
;

/*
 8-8 프로젝트 낙찰회사별, 연도별 낙찰총금액이 50억이상인 회사를
 연도 오름차순, 낙찰금액 내림차순으로
*/
SELECT SUBSTR(B.STR_YMD, 1, 4) AS YYYY
     , A.PRJT_ID
     , A.CMPY_ID
     , CASE WHEN SUM(B.SUCBD_AMT)>=5000000000 THEN SUM(B.SUCBD_AMT) ELSE 0 END AS SUCBD_AMT_ALL
  FROM TN_PRJC A
 INNER JOIN TN_PRJT B
         ON A.PRJT_ID = B.PRJT_ID
 WHERE CMPY_YN = 'Y'
 GROUP BY SUBSTR(B.STR_YMD, 1, 4)
     , A.PRJT_ID
     , A.CMPY_ID
 ORDER BY 1, 4 DESC
;

/*
 8-7 프로젝트 낙찰회사별, 연도별 낙찰총금액 출력
 연도 오름차순, 낙찰금액 내림차순
 
 TO_CHAR(A.SUCBD_AMT , 'FM999,999,999,999') 사용 => 낙찰금액 내림차순에서 문자 순 정렬이 되어버린다.
 구글 검색 REGEXP_REPLACE 사용 :  REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(A.SUCBD_AMT)), '(\d{3})','\1,')), '^,','')
*/
SELECT A.SUCBD_CMPY_ID
     , SUBSTR(A.STR_YMD, 1, 4) AS YYYY
     , REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(A.SUCBD_AMT)), '(\d{3})','\1,')), '^,','') AS SUM_AMOUNT
  FROM TN_PRJT A
 INNER JOIN TN_PRJC B
         ON A.SUCBD_CMPY_ID = B.CMPY_ID
 GROUP BY A.SUCBD_CMPY_ID
        , SUBSTR(A.STR_YMD, 1, 4)
        , REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(A.SUCBD_AMT)), '(\d{3})','\1,')), '^,','')
 ORDER BY 2, 3 DESC
;

/*
 8-9 연도별 전체 프로젝트 건수와 낙찰한 회사수를 출력하시오
 낙찰한 회사수는 동일한 회사가 중복집계되지 않도록 계산하시오
*/
SELECT SUBSTR(A.STR_YMD, 1, 4) AS 연도
     , COUNT(A.PRJT_ID) AS 전체프로젝트건수
     , COUNT(DISTINCT A.SUCBD_CMPY_ID) AS 낙찰한회사수
  FROM TN_PRJT A
 GROUP BY SUBSTR(A.STR_YMD, 1, 4)
 ORDER BY 1
;

/*
 8-10 연도별, 낙찰회사별, 총발주금액 대비 총낙찰금액의 비율을 소수점 2자리까지 반올림해서 출력
 계산식 : 낙찰금액비중 = ROUND((총낙찰금액/총발주금액)*100, 2)
*/
SELECT SUBSTR(A.STR_YMD, 1, 4) AS YYYY
     , A.SUCBD_CMPY_ID AS 낙찰회사ID
     , SUM(A.ORDER_AMT) AS 총발주금액
     , SUM(A.SUCBD_AMT) AS 총낙찰금액
     , ROUND(SUM(A.SUCBD_AMT)/SUM(A.ORDER_AMT)*100, 2) AS 낙찰금액비중
  FROM TN_PRJT A
 GROUP BY SUBSTR(A.STR_YMD, 1, 4)
     , A.SUCBD_CMPY_ID
 ORDER BY 2
;

/*
 8-11 전체 프로젝트의 발주금액과 전체 발주금액 순위 출력
 , 동일 순서는 허용하되, 순차적 순서로 표시하시오 => DENSE_RANK()
*/
SELECT *
  FROM TN_PRJT
;

SELECT A.PRJT_ID
     , A.PRJT_NM
     , A.ORDER_AMT
     , DENSE_RANK() OVER(ORDER BY A.ORDER_AMT DESC) AS 발주금액순위
  FROM TN_PRJT A
;

/*
 8-12
 순위는 동일 연도 내에서 연도발주금액합계 대비 '비중 순위'를 구하되
 순위가 동일한 경우는 낙찰금액 오름차순 기준으로 순위를 표시하라
 
 프로젝트 기간이 1년 이상 기간에 걸쳐있는 경우
 , 프로젝트 개월수가 12개월 이하이면 프로젝트 시작일 연도
 , 12개월 이상이면 프로젝트종료일 연도 기준으로 비중을 구해야 한다.
 
 , DENSE_RANK() OVER(PARTITION BY SUCBD_AMT ORDER BY SUM(B.ORDER_AMT)) AS 순위
 , ROUND(B.ORDER_AMT / SUM(B.ORDER_AMT) OVER (PARTITION BY SUBSTR(B.STR_YMD, 1, 4)), 4) AS 연도내비중
*/
SELECT *
  FROM TN_PRJT
;

SELECT B.PRJT_ID
     , ROUND(SUM(B.ORDER_AMT)/B.ORDER_AMT, 2) AS 연도발주금액합계
  FROM TN_PRJT B
 GROUP BY B.PRJT_ID
;

SELECT A.PRJT_ID AS 프로젝트ID
     , A.PRJT_NM AS 프로젝트명
     , A.ORDER_AMT AS 발주금액
     , A.SUCBD_AMT AS 낙찰금액
     , B.연도
     , B.연도별발주금액비중
     , DENSE_RANK() OVER(ORDER BY B.연도별발주금액비중 DESC, A.SUCBD_AMT ASC) AS 순위
  FROM TN_PRJT A
 INNER JOIN (
             SELECT SUBSTR(B.STR_YMD,1 ,4) AS 연도
                  , PRJT_ID
                  , ROUND(B.ORDER_AMT / SUM(B.ORDER_AMT) OVER (PARTITION BY SUBSTR(B.STR_YMD, 1, 4)), 2) AS 연도별발주금액비중
               FROM TN_PRJT B
            ) B
         ON A.PRJT_ID = B.PRJT_ID
;

SELECT TRUNC(MONTHS_BETWEEN(END_YMD, STR_YMD))
  FROM TN_PRJT
;

SELECT CASE WHEN TRUNC(MONTHS_BETWEEN(END_YMD, STR_YMD)) <= 12 THEN SUBSTR(STR_YMD, 1, 4)
            WHEN TRUNC(MONTHS_BETWEEN(END_YMD, STR_YMD)) >= 12 THEN SUBSTR(END_YMD, 1, 4)
        END AS 연도
  FROM TN_PRJT
;

SELECT A.PRJT_ID AS 프로젝트ID
     , A.PRJT_NM AS 프로젝트명
     , A.ORDER_AMT AS 발주금액
     , A.SUCBD_AMT AS 낙찰금액
     , B.연도
     , B.연도별발주금액비중
     , DENSE_RANK() OVER(ORDER BY B.연도별발주금액비중 DESC, A.SUCBD_AMT ASC) AS 순위
  FROM TN_PRJT A
 INNER JOIN (
             SELECT CASE WHEN TRUNC(MONTHS_BETWEEN(END_YMD, STR_YMD)) <= 12 THEN SUBSTR(STR_YMD, 1, 4)
                         WHEN TRUNC(MONTHS_BETWEEN(END_YMD, STR_YMD)) >= 12 THEN SUBSTR(END_YMD, 1, 4)
                     END AS 연도
                  , PRJT_ID
                  , ROUND(B.ORDER_AMT / SUM(B.ORDER_AMT) OVER (PARTITION BY SUBSTR(B.STR_YMD, 1, 4)), 2) AS 연도별발주금액비중
               FROM TN_PRJT B
            ) B
         ON A.PRJT_ID = B.PRJT_ID
;

/*
 8-13 전체 프로젝트의 연도별 총 낙찰금액 대비...각 낙찰회사별 낙찰금액비중(소수점 2자리 출력)
*/
SELECT *
  FROM TN_PRJT
;

SELECT A.PRJT_ID
     , A.PRJT_NM
     , A.SUCBD_CMPY_ID
     , A.SUCBD_AMT
     , B.기준연도
     , B.연도별총낙찰총액
     , B.낙찰금액비중
  FROM TN_PRJT A
 INNER JOIN (
             SELECT PRJT_ID
                  , SUBSTR(STR_YMD, 1, 4) AS 기준연도
                  , SUM(SUCBD_AMT) OVER(PARTITION BY SUBSTR(STR_YMD, 1, 4)) AS 연도별총낙찰총액
                  , ROUND(SUCBD_AMT/SUM(SUCBD_AMT) OVER(PARTITION BY SUBSTR(STR_YMD, 1, 4)), 2) AS 낙찰금액비중
               FROM TN_PRJT 
            ) B
         ON A.PRJT_ID = B.PRJT_ID
 ORDER BY 5
;

/*
 8-14 전체 프로젝트에서, 연도 내 발주금액과 낙찰금액의 차이 금액이, 내림차순으로 3위까지만 출력
 
 연도는 프로젝트 시작일 기준
 금액차이 순위가 동일할 경우, 낙찰금액 높은 순, 그래도 동일한 순위면 프로젝트ID 오름차순
 
*/
SELECT *
  FROM (
        SELECT SUBSTR(A.STR_YMD, 1, 4) AS 연도
             , DENSE_RANK() OVER(PARTITION BY SUBSTR(A.STR_YMD, 1, 4) ORDER BY B.AMT_DIFF DESC, A.SUCBD_AMT, A.PRJT_ID) AS 금액차이순위
             , A.PRJT_ID
             , A.PRJT_NM
             , A.SUCBD_AMT
          FROM TN_PRJT A
         INNER JOIN (
                     SELECT PRJT_ID
                          , ORDER_AMT - SUCBD_AMT AS AMT_DIFF 
                       FROM TN_PRJT
                    ) B
                 ON A.PRJT_ID = B.PRJT_ID
       ) A
 WHERE A.금액차이순위 <= 3
;

SELECT *
  FROM (
        SELECT SUBSTR(STR_YMD, 1, 4) AS 연도
             , DENSE_RANK() OVER(PARTITION BY SUBSTR(STR_YMD, 1, 4) ORDER BY ORDER_AMT - SUCBD_AMT DESC, SUCBD_AMT, PRJT_ID) AS 금액차이순위
             , PRJT_ID
             , PRJT_NM
             , SUCBD_AMT
          FROM TN_PRJT
       ) A
 WHERE A.금액차이순위 <= 3
;

/*
 8-15 전체 프로젝트 산출물을 산출물 유형별 전체건수, 확장자별로 건수가 많은 순위부터 표시하시오
*/

--정답
SELECT T2.DOC_TP AS 산출물유형코드
     , MAX(DECODE(T2.DOC_TP,'PRT','보고서','ANL','분석문서','DSN','설계문서','SRC','소스','TST','테스트문서', 'TRT', '아키텍처문서')) AS 산출물유형코드명
     , MAX(T2.CNT) AS 전체건수
     , MAX(DECODE(T3.RNK, 1,'('||T3.FIL_TP||')'||T3.CNT)) AS 확장자1순위
     , MAX(DECODE(T3.RNK, 2,'('||T3.FIL_TP||')'||T3.CNT)) AS 확장자2순위
     , MAX(DECODE(T3.RNK, 3,'('||T3.FIL_TP||')'||T3.CNT)) AS 확장자3순위
     , MAX(DECODE(T3.RNK, 4,'('||T3.FIL_TP||')'||T3.CNT)) AS 확장자4순위
     , MAX(DECODE(T3.RNK, 5,'('||T3.FIL_TP||')'||T3.CNT)) AS 확장자5순위
  FROM ( SELECT DOC_TP
              , COUNT(*) AS CNT
           FROM TN_PRJS
          WHERE 1=1
          GROUP BY DOC_TP
       ) T2 -- 산출물유형별_건수
     , ( SELECT DOC_TP
              , FIL_TP
              , COUNT(*) AS CNT
              , ROW_NUMBER() OVER(PARTITION BY DOC_TP ORDER BY COUNT(*) DESC) AS RNK
           FROM TN_PRJS
          WHERE 1=1
          GROUP BY DOC_TP
                 , FIL_TP
       ) T3 -- 산출물유형별_파일확장자별_건수
 WHERE 1=1
  AND T2.DOC_TP = T3.DOC_TP(+)
 GROUP BY T2.DOC_TP
 ORDER BY DECODE(T2.DOC_TP,'PRT',1,'ANL',2,'DSN',3,'SRC',4,'TST',5,6)
;

--------------------------------------------------------------------------------------------------------------------------------------------------- 2025.12.23
/*
 8-2. 사원의 나이 기준 내림차순에서, 11번째에서 20번까지의 순위의 사원 출력
*/
SELECT *
  FROM (
        SELECT A.CMPY_ID
             , A.MEM_ID
             , A.MEM_NM
             , A.SEX
             , A.AGE
             , A.JOIN_YMD
             , A.PART_ID
             , A.POS_ID
             , ROW_NUMBER() OVER(ORDER BY A.AGE DESC) AS SN
          FROM TN_MEM A /* TN_사원, PKEY : CMPY_ID, MEM_ID */
        )
 WHERE SN BETWEEN 11 AND 20
;

/*
 8-3. 사원의 입사일 기준 오름차순에서, 한 페이지당 10개씩 게시판에 출력할 때, 마지막 페이지 출력.
 마지막 페이지...?
 --> 오라클 11g 기준 ROWNUM 관련 명령어 있는지 확인 : X
 --> ROUND(총 카운트/10, 0)은, 10개씩 표시할 때 마지막 페이지 X
 --> TRUNC(SN/10)=3 으로 마지막 페이지 구함, 하지만 총 카운트가 늘어날 때도 마지막 페이지일 수 있을 방법은?
 
 --> WHERE CEIL(SN/10)=(SELECT CEIL(TOT_CNT/10) FROM TMP_DATA) : 무조건 올림을 수행해서 10, 20, 30 .. 정수가 빠진다.
 
 --> WHERE TRUNC(SN/10)=(SELECT TRUNC(TOT_CNT/10) FROM TMP_DATA) : WITH문으로 총 카운트 올리거나
 --> COUNT(*) OVER() 쓰거나
*/
WITH TMP_DATA AS (
 SELECT COUNT(*) AS TOT_CNT
   FROM TN_MEM
)
SELECT *
  FROM (
        SELECT A.CMPY_ID
             , A.MEM_ID
             , A.MEM_NM
             , A.SEX
             , A.AGE
             , NVL(A.JOIN_YMD, '99991231') AS JOIN_YMD
             , A.PART_ID
             , A.POS_ID
             , ROW_NUMBER() OVER(ORDER BY NVL(A.JOIN_YMD, '99991231') ASC) AS SN
          FROM TN_MEM A
       )
 WHERE TRUNC(SN/10)=(SELECT TRUNC(TOT_CNT/10) FROM TMP_DATA)
;

-- COUNT(*) OVER 사용하면
SELECT CMPY_ID
     , MEM_ID
     , MEM_NM
     , SEX
     , AGE
     , JOIN_YMD
     , PART_ID
     , POS_ID
     , SN
  FROM (
        SELECT A.CMPY_ID
             , A.MEM_ID
             , A.MEM_NM
             , A.SEX
             , A.AGE
             , NVL(A.JOIN_YMD, '99991231') AS JOIN_YMD
             , A.PART_ID
             , A.POS_ID
             , ROW_NUMBER() OVER(ORDER BY NVL(A.JOIN_YMD, '99991231') ASC) AS SN
             , COUNT(*) OVER() AS ALL_CNT
          FROM TN_MEM A
       )
 WHERE TRUNC(SN/10)=TRUNC(ALL_CNT/10) 
;

/*
 8-4. 현재 연도 기준으로, 최근 3년간 프로젝트 시작일 기준으로 최근 프로젝트 기준으로 출력하시오
 
 --> 기준이 2개...? 그리고 둘다 날짜,,,? 일단 PARTITION BY 먼저 시작
 --> 최근 3년...?
*/
SELECT A.PRJT_ID
     , A.PRJT_NM
     , TO_CHAR(SYSDATE, 'YYYY') AS PR_YEAR
     , A.STR_YMD
     , ROW_NUMBER() OVER(ORDER BY TO_CHAR(SYSDATE, 'YYYY') DESC, TO_DATE(A.STR_YMD)-(INTERVAL '3' YEAR) DESC) AS SN
  FROM TN_PRJT A
;

--상무님 정답이랑 비교
SELECT T1.PRJT_ID AS "프로젝트ID"
     , T1.PRJT_NM AS "프로젝트명"
     , T1.STR_YMD AS "프로젝트시작일"
     , ROW_NUMBER() OVER(ORDER BY T1.STR_YMD DESC) AS RNK
  FROM TN_PRJT T1
 WHERE 1=1
   --AND SUBSTR(T1.STR_YMD,1,4) BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-12*(3-1)),'YYYY') AND TO_CHAR(SYSDATE,'YYYY')
   AND T1.STR_YMD >= TO_CHAR(ADD_MONTHS(SYSDATE,-12*(3-1)),'YYYY')||'0101' -- 3년전 1월1일보다 크고
   AND T1.STR_YMD <= TO_CHAR(SYSDATE,'YYYY')||'1231' -- 최근년도 12월 31일보다 작고
 ORDER BY RNK
;

--> 상무님 정답이랑 비교 후
--TO_DATE(A.STR_YMD)-(INTERVAL '3' YEAR)
SELECT A.PRJT_ID
     , A.PRJT_NM
     , TO_CHAR(SYSDATE, 'YYYY') AS PR_YEAR
     , A.STR_YMD
     , ROW_NUMBER() OVER(ORDER BY A.STR_YMD DESC) AS SN
  FROM TN_PRJT A
 WHERE TO_DATE(A.STR_YMD) >= ADD_MONTHS(SYSDATE, -36)  
   AND TO_DATE(A.STR_YMD) <= SYSDATE
; 

/*
 8-5. 전체 프로젝트의 발주금액을, 아래와 같이 한글 금액으로 출력하고, 발주금액(한글) 내림차순으로 출력하시오
 
 1. TO_CHAR(A.ORDER_AMT, 'FM999,999,999,999') : 천 단위로 , 찍기

 2.(구글) 
 REVERSE(TO_CHAR(A.ORDER_AMT)) : 숫자를 문자로 바꾼 뒤, 앞뒤를 뒤집는다.
 , REGEXP_REPLACE('(\d{3})', '\1,') : 뒤집힌 숫자에서 숫자 3개를 찾아서 그 뒤에 콤마(,)를 붙인다.
 , 다시 REVERSE()
 , REGEXP_REPLACE(...값, '^,', '') : 문자열의 시작이 콤마(,)인 경우를 찾아서 ''으로 치환한다.
 
 3. 6천5백만원, 1억3천8백만원 등의 표기...?
 뷰 테이블 생성, 숫자 + 단위 컬럼으로 
*/

CREATE TABLE AMT_UNIT (
  UNIT_VAL      NUMBER
  , UNIT_NAME   VARCHAR(20)
  , PIC_EXPR_OR NUMBER
)
;

INSERT INTO AMT_UNIT VALUES (10, '십', 1);
INSERT INTO AMT_UNIT VALUES (100, '백', 2);
INSERT INTO AMT_UNIT VALUES (1000, '천', 3);
INSERT INTO AMT_UNIT VALUES (10000, '만', 4);
INSERT INTO AMT_UNIT VALUES (100000, '십만', 5);
INSERT INTO AMT_UNIT VALUES (1000000, '백만', 6);
INSERT INTO AMT_UNIT VALUES (10000000, '천만', 7);
INSERT INTO AMT_UNIT VALUES (100000000, '억', 8);
INSERT INTO AMT_UNIT VALUES (1000000000, '십억', 9);
INSERT INTO AMT_UNIT VALUES (10000000000, '백억', 10);
INSERT INTO AMT_UNIT VALUES (100000000000, '천억', 11);
INSERT INTO AMT_UNIT VALUES (1000000000000, '조', 12);

SELECT A.PRJT_ID
     , A.PRJT_NM
     , A.STR_YMD
     , A.END_YMD
     , A.ORDERER_NM
     , REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(A.ORDER_AMT)), '(\d{3})', '\1,')), '^,', '') AS ORDER_AMT
     , REGEXP_REPLACE(REVERSE(REGEXP_REPLACE(REVERSE(TO_CHAR(A.SUCBD_AMT)), '(\d{3})', '\1,')), '^,', '') AS SUCBD_AMT
     , SUCBD_CMPY_ID
  FROM TN_PRJT A
;

--> 상무님 답으로... 나중에 복습
--6000000000
--60
--0(600)
--0(6000)
SELECT A.PRJT_ID
     , A.PRJT_NM
     , NVL(SUBSTR(A.ORDER_AMT, 1, LENGTH(A.ORDER_AMT)-8), 0)||'억'|| 
       NVL(SUBSTR(A.ORDER_AMT, (LENGTH(A.ORDER_AMT)-7), 1), 0)||'천'||
       NVL(SUBSTR(A.ORDER_AMT, (LENGTH(A.ORDER_AMT)-6), 1), 0)||'백만원' AS ORDER_AMT
     , SUBSTR(A.ORDER_AMT, 1, LENGTH(A.ORDER_AMT)-8)||'억'||
       SUBSTR(A.ORDER_AMT, (LENGTH(A.ORDER_AMT)-7), 1)||'천'||
       SUBSTR(A.ORDER_AMT, (LENGTH(A.ORDER_AMT)-6), 1)||'백만원' AS ORDER_AMT_NULL
     , A.ORDER_AMT AS "발주금액"
     , SUBSTR(A.ORDER_AMT, 1, (LENGTH(A.ORDER_AMT)-8)) AS "억"
     , SUBSTR(A.ORDER_AMT, (LENGTH(A.ORDER_AMT)-7), 1) AS "천만"
     , SUBSTR(A.ORDER_AMT, (LENGTH(A.ORDER_AMT)-6), 1) AS "백만"
  FROM TN_PRJT A
 ORDER BY 3 DESC
;

/*
 8-6. 연도별 프로젝트 건수를 출력하시오, 프로젝트건수 내림차순으로 정렬하시오.
 '연도별'의 기준이 시작일자냐 종료일자냐 
*/
WITH TMP_STAND AS (
SELECT PRJT_ID
     , EXTRACT(YEAR FROM TO_DATE(STR_YMD)) AS STR_STAND
     , EXTRACT(YEAR FROM TO_DATE(END_YMD)) AS END_STAND
  FROM TN_PRJT
), STR_COUNT AS (
SELECT COUNT(A.PRJT_ID) AS STR_PRJT_CNT
     , A.STR_STAND
  FROM TMP_STAND A
     , (
        SELECT PRJT_ID
             , PRJT_NM
             , EXTRACT(YEAR FROM TO_DATE(SUBSTR(STR_YMD, 1, 4),'YYYY')) AS STR_YEAR
             , EXTRACT(YEAR FROM TO_DATE(SUBSTR(END_YMD, 1, 4),'YYYY')) AS END_YEAR
             , ORDERER_NM
             , SUCBD_AMT
             , SUCBD_CMPY_ID
          FROM TN_PRJT
       ) B
 WHERE A.STR_STAND=B.STR_YEAR
 GROUP BY A.STR_STAND
), END_COUNT AS (
SELECT COUNT(A.PRJT_ID) AS END_PRJT_CNT
     , A.END_STAND
  FROM TMP_STAND A
     , (
        SELECT PRJT_ID
             , PRJT_NM
             , EXTRACT(YEAR FROM TO_DATE(SUBSTR(STR_YMD, 1, 4),'YYYY')) AS STR_YEAR
             , EXTRACT(YEAR FROM TO_DATE(SUBSTR(END_YMD, 1, 4),'YYYY')) AS END_YEAR
             , ORDERER_NM
             , SUCBD_AMT
             , SUCBD_CMPY_ID
          FROM TN_PRJT
       ) B
 WHERE A.END_STAND=B.END_YEAR
 GROUP BY A.END_STAND
)
SELECT A.PRJT_ID
     , B.STR_STAND
     , B.STR_PRJT_CNT
     , C.END_STAND
     , C.END_PRJT_CNT
  FROM TMP_STAND A
     , STR_COUNT B
     , END_COUNT C
 WHERE A.STR_STAND=B.STR_STAND(+)
   AND A.END_STAND=C.END_STAND(+)
 ORDER BY 1
;

--카운트 뻥튀기... 다시!
WITH STR_COUNT AS (
    -- 1. 시작일 기준 연도별 카운트
    SELECT EXTRACT(YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD')) AS YYYY
         , COUNT(*) AS STR_CNT
      FROM TN_PRJT
    -- WHERE STR_YMD IS NOT NULL
     GROUP BY EXTRACT(YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD'))
), 
END_COUNT AS (
    -- 2. 종료일 기준 연도별 카운트
    SELECT EXTRACT(YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD')) AS YYYY
         , COUNT(*) AS END_CNT
      FROM TN_PRJT
    -- WHERE END_YMD IS NOT NULL
     GROUP BY EXTRACT(YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD'))
)
-- 3. 두 결과를 연도 기준으로 합침 (FULL OUTER JOIN)
-- FULL OUTER JOIN : 양쪽 테이블의 모든 데이터를 하나도 빠뜨리지 않고 합치고 싶을 때
SELECT NVL(A.YYYY, B.YYYY)  AS STAND_YEAR    -- 연도 (A가 없으면 B연도 사용)
     , NVL(A.STR_CNT, 0)    AS STR_PRJT_CNT  -- 시작 건수 (없으면 0)
     , NVL(B.END_CNT, 0)    AS END_PRJT_CNT  -- 종료 건수 (없으면 0)
  FROM STR_COUNT A
       FULL OUTER JOIN END_COUNT B ON A.YYYY = B.YYYY
 ORDER BY 1
;

/*
 8-7. 프로젝트 낙찰회사별,연도별 낙찰총금액을 출력하시오. 연도 오름차순, 낙찰금액 내림차순으로 정렬하시오.
 연도별 낙찰총금액 -> STR_YMD, END_YMD
*/
WITH STR_COUNT AS (
    -- 1. 시작일 기준 연도별 카운트
    SELECT EXTRACT(YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD')) AS YYYY
         , SUCBD_CMPY_ID
         , TO_CHAR(SUM(SUCBD_AMT), 'FM999,999,999,999') AS SRT_SUCBD_AMT
      FROM TN_PRJT
    -- WHERE STR_YMD IS NOT NULL
     GROUP BY EXTRACT(YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD'))
         , SUCBD_CMPY_ID
     ORDER BY 1
), 
END_COUNT AS (
    -- 2. 종료일 기준 연도별 카운트
    SELECT EXTRACT(YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD')) AS YYYY
         , SUCBD_CMPY_ID
         , TO_CHAR(SUM(SUCBD_AMT), 'FM999,999,999,999') AS END_SUCBD_AMT
      FROM TN_PRJT
    -- WHERE STR_YMD IS NOT NULL
     GROUP BY EXTRACT(YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD'))
         , SUCBD_CMPY_ID
     ORDER BY 1
)
-- 3. 두 결과를 연도 기준으로 합침 (FULL OUTER JOIN)
-- FULL OUTER JOIN : 양쪽 테이블의 모든 데이터를 하나도 빠뜨리지 않고 합치고 싶을 때
SELECT A.YYYY AS STR_STAND_YEAR
     , B.YYYY AS END_STAND_YEAR 
     , NVL(A.SUCBD_CMPY_ID, B.SUCBD_CMPY_ID) AS SUCBD_CMPY_ID 
     , NVL(A.SRT_SUCBD_AMT, 0) AS SRT_SUCBD_AMT  -- 시작 건수 (없으면 0)
     , NVL(B.END_SUCBD_AMT, 0) AS END_SUCBD_AMT  -- 종료 건수 (없으면 0)
  FROM STR_COUNT A
  FULL OUTER JOIN END_COUNT B 
    ON A.YYYY = B.YYYY 
   AND A.SUCBD_CMPY_ID = B.SUCBD_CMPY_ID
 ORDER BY 1, 2
;

/*
 8-8. "프로젝트 낙찰회사별, 연도별" 낙찰총금액이 5억 이상인 회사를 연도 오름차순, 낙찰금액 내림차순으로 정렬하시오. 
*/
WITH STR_STAND AS (
 SELECT EXTRACT (YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD')) AS YYYY
      , SUCBD_CMPY_ID
      , SUM(SUCBD_AMT) AS STR_SUCBD_AMT
   FROM TN_PRJT
  GROUP BY EXTRACT (YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD'))
      , SUCBD_CMPY_ID
  ORDER BY 1
), END_STAND AS (
 SELECT EXTRACT (YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD')) AS YYYY
      , SUCBD_CMPY_ID
      , SUM(SUCBD_AMT) AS END_SUCBD_AMT
   FROM TN_PRJT
  GROUP BY EXTRACT (YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD'))
      , SUCBD_CMPY_ID
  ORDER BY 1
)
SELECT NVL(A.YYYY, 0) AS STR_YEAR
     , NVL(B.YYYY, 0) AS END_YEAR
     , NVL(A.SUCBD_CMPY_ID, B.SUCBD_CMPY_ID) AS SUCBD_CMPY_ID 
     , TO_CHAR(NVL(A.STR_SUCBD_AMT, 0), 'FM999,999,999,999') AS STR_SUCBD_AMT  -- 시작 건수 (없으면 0)
     , TO_CHAR(NVL(B.END_SUCBD_AMT, 0), 'FM999,999,999,999') AS END_SUCBD_AMT  -- 종료 건수 (없으면 0)
  FROM STR_STAND A 
  FULL OUTER JOIN END_STAND B
    ON A.YYYY=B.YYYY
   AND A.SUCBD_CMPY_ID=B.SUCBD_CMPY_ID
 WHERE (A.STR_SUCBD_AMT>=500000000
    OR  B.END_SUCBD_AMT>=500000000)
;

/*
 8-9. 연도별 전체 프로젝트 건수, 낙찰한 회사수를 출력하시오
*/
WITH STR_STAND AS (
  SELECT EXTRACT(YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD')) AS YYYY
       , COUNT(PRJT_ID) AS PRJT_CNT
       , COUNT(SUCBD_CMPY_ID) AS SUCBD_CMPY_ID
    FROM TN_PRJT
   GROUP BY EXTRACT(YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD'))
   ORDER BY 1
), END_STAND AS (
  SELECT EXTRACT(YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD')) AS YYYY
       , COUNT(PRJT_ID) AS PRJT_CNT
       , COUNT(SUCBD_CMPY_ID) AS SUCBD_CMPY_ID
    FROM TN_PRJT
   GROUP BY EXTRACT(YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD'))
   ORDER BY 1
)
SELECT A.YYYY AS "시작년도"
     , A.PRJT_CNT AS "시작기준 전체건수"
     , A.SUCBD_CMPY_ID AS "시작기준 낙찰회사수"
     , B.YYYY AS "종료년도"
     , B.PRJT_CNT AS "종료기준 전체건수"
     , B.SUCBD_CMPY_ID AS "종료기준 낙찰회사수"
  FROM STR_STAND A
  FULL OUTER JOIN END_STAND B
    ON A.YYYY=B.YYYY
 ORDER BY 1, 4
;

/*
 8-10. 연도별 낙찰회사를 총 발주금액(ORDER_AMT)대비 총 낙찰금액(SUCBD_AMT)의 비율을 소수점 2자리까지 반올림하여 출력하시오.
 연도, 낙찰회사ID, 총 발주금액, 총 낙찰금액, 낙찰금액비중
*/
-- 프로젝트 시작연도 기준
WITH STR_STAND AS (
SELECT EXTRACT(YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD')) AS YYYY
     , SUCBD_CMPY_ID
     , SUM(ORDER_AMT) AS ORDER_AMT
     , SUM(SUCBD_AMT) AS SUCBD_AMT
     , ROUND(SUM(SUCBD_AMT)/SUM(ORDER_AMT)*100, 2) AS RATIO_AMT
  FROM TN_PRJT
 GROUP BY EXTRACT(YEAR FROM TO_DATE(STR_YMD, 'YYYYMMDD'))
     , SUCBD_CMPY_ID
), END_STAND AS (
SELECT EXTRACT(YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD')) AS YYYY
     , SUCBD_CMPY_ID
     , SUM(ORDER_AMT) AS ORDER_AMT
     , SUM(SUCBD_AMT) AS SUCBD_AMT
     , ROUND(SUM(SUCBD_AMT)/SUM(ORDER_AMT)*100, 2) AS RATIO_AMT
  FROM TN_PRJT
 GROUP BY EXTRACT(YEAR FROM TO_DATE(END_YMD, 'YYYYMMDD'))
     , SUCBD_CMPY_ID
)
SELECT *
  FROM STR_STAND A
  FULL OUTER JOIN END_STAND B
    ON A.YYYY=B.YYYY
   AND A.SUCBD_CMPY_ID=B.SUCBD_CMPY_ID
 ORDER BY 1, 6
;

/*
 8-11. 전체 프로젝트의 발주금액과, 전체 발주금액 순위를 출력하시오. 동일 순위는 허용하되 순차적 순위로 표시.
*/
SELECT A.PRJT_ID
     , A.PRJT_NM
     , A.ORDER_AMT AS "발주금액"
     , DENSE_RANK() OVER(ORDER BY A.ORDER_AMT) AS "발주금액순위"
  FROM TN_PRJT A
;

/*
 8-12. 전체 프로젝트의 발주금액과, 연도별 발주금액합계 대비 해당프로젝트의 발주금액 비중 순위 출력
*/
WITH TMP_OBJT AS (
 SELECT A.PRJT_ID
      , A.PRJT_NM
      , A.ORDER_AMT
      , A.SUCBD_AMT
      , EXTRACT(YEAR FROM TO_DATE(A.STR_YMD)) AS YYYY
      , SUM(A.ORDER_AMT) OVER(PARTITION BY EXTRACT(YEAR FROM TO_DATE(A.STR_YMD))) AS YYYY_SUM
   FROM TN_PRJT A
), TMP_RATIO AS (
SELECT A.PRJT_ID
     , A.PRJT_NM
     , A.ORDER_AMT
     , A.SUCBD_AMT
     , B.YYYY
     , B.YYYY_SUM
     , ROUND(RATIO_TO_REPORT(A.ORDER_AMT) OVER(PARTITION BY B.YYYY)*100, 2) AS YYYY_RATIO
  FROM TN_PRJT A
     , TMP_OBJT B
 WHERE A.PRJT_ID=B.PRJT_ID(+)
)
SELECT A.PRJT_ID
     , A.PRJT_NM
     , A.ORDER_AMT
     , A.SUCBD_AMT
     , B.YYYY
     , B.YYYY_SUM
     , B.YYYY_RATIO
     , DENSE_RANK() OVER(ORDER BY B.YYYY_RATIO, A.SUCBD_AMT) AS YYYY_RATIO_SN
  FROM TN_PRJT A
     , TMP_RATIO B
 WHERE A.PRJT_ID=B.PRJT_ID(+)
;

/*
 8-13. 전체 프로젝트의 연도별 총 낙찰금액 대비, 각 낙찰회사별 낙찰금액비중을 출력하시오.
*/
SELECT SUBSTR(A.STR_YMD, 0, 4) AS YYYY
     , A.SUCBD_CMPY_ID
     , ROUND(RATIO_TO_REPORT(SUM(A.SUCBD_AMT)) OVER(PARTITION BY SUBSTR(A.STR_YMD, 0, 4))*100, 2) AS YY_RATIO
  FROM TN_PRJT A
 GROUP BY SUBSTR(A.STR_YMD, 0, 4) 
     , A.SUCBD_CMPY_ID
 ORDER BY 1
;

/*
 8-14. 전체 프로젝트의 연도 내에서 발주금액과, 낙찰금액의 차이 금액을 내림차순으로 3위까지
*/
-- 기준 : TN_PRJT A
-- 전체 프로젝트의 연도 내에서, 즉 (END_YMD랑 STR_YMD)사이에서, 연도는 프로젝트 시작일 기준으로 포함시킨다
SELECT *
  FROM (
        SELECT B.YYYY
             , ROW_NUMBER() OVER(ORDER BY B.MINUS_AM DESC, B.SUCBD_AMT, B.PRJT_ID) AS SN
             , A.PRJT_ID
             , A.PRJT_NM
          FROM TN_PRJT A
             , (
                SELECT SUBSTR(STR_YMD, 0, 4) AS YYYY
                     , PRJT_ID
                     , PRJT_NM
                     , ORDER_AMT
                     , SUCBD_AMT
                     , (ORDER_AMT - SUCBD_AMT) AS MINUS_AM
                  FROM TN_PRJT A
               ) B
         WHERE SUBSTR(A.STR_YMD, 0, 4)=B.YYYY
           AND A.PRJT_ID=B.PRJT_ID
       )
 WHERE SN<=3
;

--상무님
SELECT *
  FROM (
        SELECT B.YYYY
             , DENSE_RANK() OVER(PARTITION BY B.YYYY ORDER BY B.MINUS_AM DESC, B.SUCBD_AMT, B.PRJT_ID) AS SN
             , A.PRJT_ID
             , A.PRJT_NM
          FROM TN_PRJT A
             , (
                SELECT SUBSTR(STR_YMD, 0, 4) AS YYYY
                     , PRJT_ID
                     , PRJT_NM
                     , ORDER_AMT
                     , SUCBD_AMT
                     , (ORDER_AMT - SUCBD_AMT) AS MINUS_AM
                  FROM TN_PRJT A
               ) B
         WHERE SUBSTR(A.STR_YMD, 0, 4)=B.YYYY
           AND A.PRJT_ID=B.PRJT_ID
       )
 WHERE SN<=3
;

/*
 8-15. 전체 프로젝트 산출물을, 산출물유형별 전체건수와, 확장자별로 건수가 많은 순위부터 표시
*/
SELECT A.DOC_TP AS 산출물유형코드
     , MAX(DECODE(A.DOC_TP, '요구사항정의서', '기능명세서', '소스코드', '테스트케이스', '프로젝트보고서')) AS 산출물유형명
     , MAX(A.DOC_CNT) AS 전체건수
     , MAX(DECODE(B.SN, 1, '('||B.FIL_TP||')'||B.FIL_CNT)) AS 확장자1순위
     , MAX(DECODE(B.SN, 2, '('||B.FIL_TP||')'||B.FIL_CNT)) AS 확장자2순위
     , MAX(DECODE(B.SN, 3, '('||B.FIL_TP||')'||B.FIL_CNT)) AS 확장자3순위
     , MAX(DECODE(B.SN, 4, '('||B.FIL_TP||')'||B.FIL_CNT)) AS 확장자4순위
     , MAX(DECODE(B.SN, 5, '('||B.FIL_TP||')'||B.FIL_CNT)) AS 확장자5순위
  FROM (
        SELECT A.DOC_TP
             , COUNT(1) AS DOC_CNT
          FROM TN_PRJS A
         GROUP BY A.DOC_TP
       ) A
     , (
        SELECT A.DOC_TP
             , A.FIL_TP
             , COUNT(A.FIL_TP) AS FIL_CNT
             , ROW_NUMBER() OVER(PARTITION BY A.DOC_TP ORDER BY COUNT(A.FIL_TP)) AS SN
          FROM TN_PRJS A
         GROUP BY A.DOC_TP
             , A.FIL_TP
       ) B
 WHERE A.DOC_TP=B.DOC_TP
 GROUP BY A.DOC_TP
;

SELECT *
  FROM TN_PRJT
;

SELECT *
  FROM TN_PRJS
;

--상무님
SELECT T2.DOC_TP AS "산출물유형코드"
     , MAX(DECODE(T2.DOC_TP,'RPT','보고서','ANL','분석문서','DSN','설계문서','SRC','소스','TST','테스트문서')) AS "산출물유형코드명"
     , MAX(T2.CNT) AS "전체건수"
     , MAX(DECODE(T3.RNK, 1,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자1순위"
     , MIN(DECODE(T3.RNK, 1,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자1순위"     
     , MAX(DECODE(T3.RNK, 2,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자2순위"
     , MAX(DECODE(T3.RNK, 3,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자3순위"
     , MAX(DECODE(T3.RNK, 4,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자4순위"
     , MAX(DECODE(T3.RNK, 5,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자5순위"
     , MAX(DECODE(T3.RNK, 6,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자6순위"
     , MAX(DECODE(T3.RNK, 7,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자7순위"
     , MAX(DECODE(T3.RNK, 8,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자8순위"
     , MAX(DECODE(T3.RNK, 9,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자9순위"
     , MAX(DECODE(T3.RNK,10,'('||T3.FIL_TP||')'||T3.CNT)) AS "확장자10순위"
  FROM
     (
      SELECT T1.DOC_TP
           , COUNT(*) AS CNT
        FROM TN_PRJS T1
       WHERE 1=1
       GROUP BY T1.DOC_TP
     ) T2 -- 산출물유형별_건수
     ,
     (
      SELECT T1.DOC_TP
           , T1.FIL_TP
           , COUNT(*) AS CNT
           , ROW_NUMBER() OVER(PARTITION BY T1.DOC_TP ORDER BY COUNT(*) DESC) AS RNK
        FROM TN_PRJS T1
       WHERE 1=1
       GROUP BY T1.DOC_TP
              , T1.FIL_TP
     ) T3 -- 산출물유형별_파일확장자별_건수
 WHERE 1=1
   AND T3.DOC_TP(+) = T2.DOC_TP
 GROUP BY T2.DOC_TP
 ORDER BY DECODE(T2.DOC_TP,'RPT',1,'ANL',2,'DSN',3,'SRC',4,'TST',5,6)
; -- 정렬도 다양한 함수를 활용할 수 있다.

/*
 8-16. 부서별, 직급별, 성별 사원수를 출력하시오.
*/
SELECT MAX(B.PART_NM||'('||B.PART_ID||')') AS "부서명(부서ID)"
     , MAX(C.POS_NM||'('||C.POS_ID||')') AS "직급명(직급ID)"
     , COUNT(DECODE(A.SEX, 1, A.MEM_ID)) AS "남자_사원수"
     , COUNT(DECODE(A.SEX, 2, A.MEM_ID)) AS "여자 사원수"
  FROM TN_MEM A
  LEFT OUTER JOIN TN_PART B
    ON A.PART_ID=B.PART_ID
  LEFT OUTER JOIN TN_POS C
    ON A.POS_ID=C.POS_ID
 GROUP BY B.PART_ID
     , C.POS_ID
;

/*
 8-17. 사원별, 연도별 투입된 프로젝트 참여건수와, 총 낙찰금액 대비 사원의 연봉금액 비중을 출력하시오
*/
SELECT A.MEM_ID
     , (SELECT MEM_NM FROM TN_MEM WHERE MEM_ID=A.MEM_ID) AS 사원명
     , SUBSTR(A.STR_YMD, 0, 4) AS 연도
     , COUNT(A.PRJT_ID) AS 프로젝트참여건수
     , SUM(B.SUCBD_AMT) AS 총낙찰금액
     , SUM(C.SAL_MONEY) AS 총연봉금액
     , ROUND(((SUM(C.SAL_MONEY))*10000/NULLIF(SUM(B.SUCBD_AMT), 0))*100, 2) AS 연봉비중 
  FROM TN_PRJM A
  LEFT OUTER JOIN TN_PRJT B
    ON A.PRJT_ID=B.PRJT_ID
  LEFT OUTER JOIN TN_SAL C
    ON A.MEM_ID=C.MEM_ID
 GROUP BY A.MEM_ID
     , SUBSTR(A.STR_YMD, 0, 4)
;

--상무님 : 반드시 한 사원이 한 연도에 2개 이상의 프로젝트를 참여하도록 데이터를 생성하시오
--프로젝트 참여 건수와 별개로 연봉은 당해에 프로젝트 투입건수와 별개로 한번만 집계해야 한다.(이 부분에서 틀림 표시)

/*
 8-18. 사원의 연령대별로 프로젝트 투입기간일의 평균일수를 출력하시오.
*/
WITH TMP_DATA AS (
SELECT A.MEM_ID
     , B.PRJT_ID
     , (SELECT PRJT_NM FROM TN_PRJT WHERE PRJT_ID=B.PRJT_ID) AS PRJT_NM
     , CASE WHEN NVL(A.AGE, 0) BETWEEN 1 AND 9 THEN '10대 이하' 
            WHEN NVL(A.AGE, 0) BETWEEN 10 AND 19 THEN '10대(10~19)'
            WHEN NVL(A.AGE, 0) BETWEEN 20 AND 29 THEN '20대(20~29)'
            WHEN NVL(A.AGE, 0) BETWEEN 30 AND 39 THEN '30대(30~39)'
            WHEN NVL(A.AGE, 0) BETWEEN 40 AND 49 THEN '40대(40~49)'
            WHEN NVL(A.AGE, 0) BETWEEN 50 AND 59 THEN '50대(50~59)'
          ELSE '연령미상' END AS AGE
     , (TO_DATE(B.END_YMD)-TO_DATE(B.STR_YMD))+1 AS DUR_YMD
  FROM TN_MEM A
  LEFT OUTER JOIN TN_PRJM B
    ON A.MEM_ID=B.MEM_ID
 GROUP BY A.MEM_ID
     , B.PRJT_ID
     , CASE WHEN NVL(A.AGE, 0) BETWEEN 1 AND 9 THEN '10대 이하' 
            WHEN NVL(A.AGE, 0) BETWEEN 10 AND 19 THEN '10대(10~19)'
            WHEN NVL(A.AGE, 0) BETWEEN 20 AND 29 THEN '20대(20~29)'
            WHEN NVL(A.AGE, 0) BETWEEN 30 AND 39 THEN '30대(30~39)'
            WHEN NVL(A.AGE, 0) BETWEEN 40 AND 49 THEN '40대(40~49)'
            WHEN NVL(A.AGE, 0) BETWEEN 50 AND 59 THEN '50대(50~59)'
          ELSE '연령미상' END
     , (TO_DATE(B.END_YMD)-TO_DATE(B.STR_YMD))+1
 ORDER BY 1
)
SELECT AGE AS 연령대
     , TRUNC(AVG(DUR_YMD)) AS 평균일수
  FROM TMP_DATA
 GROUP BY AGE
;

--상무님
--날짜빼기에서 1일이 모자라는지 확인하자. (같은날짜를 빼면 0일이다)
SELECT TO_DATE('20240101','YYYYMMDD')-TO_DATE('20240101','YYYYMMDD') AS DAYS FROM DUAL
;

/*
 8-19. 프로젝트 산출물 작성일이 프로젝트 기간 내 작성된건지 표시하고
     , 작성일의 오름차순으로 출력하시오 
*/
SELECT A.PRJT_ID
     , A.PRJT_NM
     , A.STR_YMD
     , A.END_YMD
     , B.DOC_ID
     , B.DOC_NM
     , B.DOC_TP
     , B.FIL_NM
     , B.WRT_DT
     , CASE WHEN B.WRT_DT BETWEEN TO_DATE(A.STR_YMD) AND TO_DATE(A.END_YMD) THEN 'Y'
        ELSE 'N' END AS "작성일기한여부"
  FROM TN_PRJT A
  LEFT JOIN TN_PRJS B
    ON A.PRJT_ID=B.PRJT_ID
 ORDER BY B.WRT_DT ASC
;

/*
 8-20. 산출물 테이블을 제외한 모든 테이블을 조인하여 모든 항목을 출력
 --> 누락되지 않도록 조인 유형을 구현하고, 그 이유를 설명
*/
SELECT A.PRJT_ID
     , A.SUCBD_CMPY_ID
     , B.PRJT_ID AS B_PRJT_ID
     , B.CMPY_ID AS B_CMPY_ID
     , C.CMPY_ID AS C_CMPY_ID
     , D.PRJT_ID AS D_PRJT_ID
     , D.MEM_ID  AS D_MEM_ID
     , E.MEM_ID  AS E_MEM_ID
     , E.MEM_NM  AS E_MEM_NM
     , E.PART_ID AS E_PART_ID
     , F.PART_ID AS F_PART_ID
     , F.PART_NM AS F_PART_NM
     , E.POS_ID  AS E_POS_ID
     , G.POS_ID  AS G_POS_ID
     , G.POS_NM  AS G_POS_NM
     , H.PRJT_ID AS H_PRJT_ID
     , H.DOC_ID  AS H_DOC_ID
     , H.DOC_NM  AS H_DOC_NM
  FROM TN_PRJT A         --> 기준테이블 /* PRJT_ID, SUCBD_CMPY_ID */
  LEFT JOIN TN_PRJC B    --> /* PRJT_ID, CMPY_ID */ 1:1 관계 O, 데이터 증폭 X, 프로젝트 기준 낙찰받은 회사 
    ON A.PRJT_ID=B.PRJT_ID
   AND A.SUCBD_CMPY_ID=B.CMPY_ID
  LEFT JOIN TN_CMPY C    --> /* CMPY_ID */ N:1 관계, A+B는 변화X, C는 증폭 O
    ON B.CMPY_ID=C.CMPY_ID
  LEFT JOIN TN_PRJM D    --> /* PRJT_ID, MEM_ID */ 1:N관계 프로젝트 하나 당 투입되는 MEM_ID 증폭 O
    ON A.PRJT_ID=D.PRJT_ID
  LEFT JOIN TN_MEM E     --> /* MEM_ID */ 증폭된 MEM_ID 당 1:1로 연결 PART_ID랑 POS_ID까지
    ON D.MEM_ID=E.MEM_ID
  LEFT JOIN TN_PART F    --> /* PART_ID */ 증폭된 PART_ID 당 1:1로 연결
    ON E.PART_ID=F.PART_ID
  LEFT JOIN TN_POS G     --> /* POS_ID */ 증폭된 POS_ID 당 1:1로 연결
    ON E.POS_ID=G.POS_ID
  LEFT JOIN TN_PRJS H    --> /* PRJT_ID */ 프로젝트 하나 당 여러개 존재하는 DOC_ID만큼 증폭O
    ON A.PRJT_ID=H.PRJT_ID
 ORDER BY 1
;

--상무님
SELECT T1.*, '====' G1
     , T2.*, '====' G2
     , T3.*, '====' G3
     , T4.*, '====' G4
     , T5.*, '====' G5
     , T6.*, '====' G6
     , T7.*, '====' G7
     , T8.*, '====' G8
  FROM TN_PRJT T1 /* PK=PRJT_ID FK=SUCBD_CMPY_ID */
     , TN_PRJC T2 /* PK=PRJT_ID, CMPY_ID */
     , TN_PRJM T3 /* PK=PRJT_ID, MEM_ID, CMPY_ID */
     , TN_CMPY T4 /* PK=CMPY_ID */
     , TN_MEM  T5 /* PK=MEM_ID FK1=PART_ID FK2=POS_ID */
     , TN_PART T6 /* PK=PART_ID */
     , TN_POS  T7 /* PK=POS_ID */
     , TN_SAL  T8 /* PK=MEM_ID,SAL_YM */
 WHERE 1=1
   AND T2.PRJT_ID(+) = T1.PRJT_ID
   AND T3.PRJT_ID(+) = T2.PRJT_ID
   AND T3.CMPY_ID(+) = T2.CMPY_ID
   AND T4.CMPY_ID(+) = T2.CMPY_ID
   AND T5.MEM_ID(+)  = T3.MEM_ID
   AND T6.PART_ID(+) = T5.PART_ID
   AND T7.POS_ID(+)  = T5.POS_ID
   AND T8.MEM_ID(+)  = T5.MEM_ID
   AND T8.SAL_YM(+)  = SUBSTR(T3.STR_YMD,1,4) /* 연봉과 프로젝트인력의 참여시작일을 연동하여 투입한 해의 연봉을 비교하기 위함. */
 ORDER BY T1.PRJT_ID, T2.CMPY_ID, T3.MEM_ID 
;

/*
 8-21. 프로젝트 참여인력 중, 이사 직급(G06)이 투입된 프로젝트의 연도별 평균 발주금액을 출력하시오.
 --> 조건절에 EXISTS 구문을 활용
 --> 프로젝트 기간이 12개월 이하이면 프로젝트시작일, 12개월 초과이면 프로젝트 종료일 기준으로 적용
*/
SELECT SUBSTR(B.PRJT_PRG, 1, 4) AS YYYY
     , A.PRJT_ID
     , A.PRJT_NM
     , AVG(A.ORDER_AMT) AS 평균발주금액
     , C.MEM_ID
     , C.MEM_NM
     , C.POS_ID
     , (SELECT MAX(POS_NM) FROM TN_POS WHERE POS_ID=C.POS_ID) AS POS_NM
  FROM TN_PRJT A
     , (
        SELECT PRJT_ID
             , PRJT_NM
             , ABS(ROUND(MONTHS_BETWEEN(TO_DATE(END_YMD), TO_DATE(STR_YMD)), 1)) AS MONTHS_DIFF
             , CASE WHEN ABS(TRUNC(MONTHS_BETWEEN(TO_DATE(END_YMD), TO_DATE(STR_YMD))))<=12 THEN STR_YMD
                    WHEN ABS(TRUNC(MONTHS_BETWEEN(TO_DATE(END_YMD), TO_DATE(STR_YMD))))>12 THEN END_YMD 
                ELSE '해당없음' END AS PRJT_PRG  
          FROM TN_PRJT
       ) B
     , (
        SELECT CMPY_ID
             , MEM_ID
             , MEM_NM
             , SEX
             , AGE
             , JOIN_YMD
             , PART_ID
             , POS_ID
          FROM TN_MEM 
         WHERE POS_ID='G06'
       ) C
 WHERE 1=1
   AND A.PRJT_ID=B.PRJT_ID
   AND A.SUCBD_CMPY_ID=C.CMPY_ID
 GROUP BY SUBSTR(B.PRJT_PRG, 1, 4)
     , A.PRJT_ID
     , A.PRJT_NM
     , C.MEM_ID
     , C.MEM_NM
     , C.POS_ID
 ORDER BY 2, 1
;

/*
 8-22. 프로젝트 산출물 유형별 산출물건수를 표시하되, 전체 산출물 건수를 맨 위에 한 행 추가하여 출력하시오.
 --> '산출물유형'항목은 '전체'와 산출물유형(보고서/ 분석/ 설게/ 소스/ 테스트) 값을 표현
*/
SELECT A.PRJT_ID
     , B.PRJT_NM
     , A.DOC_TP
     , COUNT(*) AS CNT
  FROM TN_PRJS A
     , TN_PRJT B
 WHERE A.PRJT_ID=B.PRJT_ID
 GROUP BY GROUPING SETS ((A.PRJT_ID, B.PRJT_NM, A.DOC_TP), ())
-- ORDER BY A.DOC_TP NULLS FIRST, A.PRJT_ID ASC
;

--TN_PRJS 데이터 추가
INSERT ALL 
    INTO TN_PRJS VALUES ('P01', 'D0048', '상세설계서_내부용', 'DSN', '상세설계_내부_V1.0.docx', 'DOCX', 'M003', '20220901')
    -- P02 프로젝트에 HWP 타입 추가
    INTO TN_PRJS VALUES ('P02', 'D0049', '사용자매뉴얼', 'PRT', '사용자매뉴얼_초안.hwp', 'HWP', 'M007', '20220415')
    -- P03 프로젝트에 ZIP 타입 추가 (기존 SRC/ZIP에 이어 추가)
    INTO TN_PRJS VALUES ('P03', 'D0050', '백업데이터_2022', 'SRC', 'backup_20221130.zip', 'ZIP', 'M012', '20221130')
    -- P06 프로젝트에 XLSX 타입 추가 (기존 DSN/XLSX에 이어 추가)
    INTO TN_PRJS VALUES ('P06', 'D0051', '컬럼정의서_추가', 'DSN', '추가컬럼정의.xlsx', 'XLSX', 'M002', '20230810')
    -- P10 프로젝트에 XLSX 타입 추가
    INTO TN_PRJS VALUES ('P10', 'D0052', '취약점조치현황', 'TST', '조치결과표.xlsx', 'XLSX', 'M013', '20250405')
    -- P15 프로젝트에 DOCX 타입 추가
    INTO TN_PRJS VALUES ('P15', 'D0053', 'API변경이력', 'SRC', 'API_Change_Log.docx', 'DOCX', 'M008', '20240401')
    -- P18 프로젝트에 HWP 타입 추가
    INTO TN_PRJS VALUES ('P18', 'D0054', '데이터이관계획서', 'DSN', '데이터이관_V1.hwp', 'HWP', 'M003', '20231110')
    -- P20 프로젝트에 XLSX 타입 추가 (기존에 없던 확장자를 추가하여 중복 유도)
    INTO TN_PRJS VALUES ('P20', 'D0055', '테스트결과집계', 'TST', 'test_summary.xlsx', 'XLSX', 'M002', '20230401')
    -- P23 프로젝트에 DOCX 타입 추가
    INTO TN_PRJS VALUES ('P23', 'D0056', '운영매뉴얼', 'PRT', '운영매뉴얼_V1.docx', 'DOCX', 'M005', '20231215')
    -- P25 프로젝트에 HWP 타입 추가
    INTO TN_PRJS VALUES ('P25', 'D0057', '교육결과보고서', 'PRT', '교육결과_2205.hwp', 'HWP', 'M015', '20220601')
SELECT * FROM DUAL
;

--PK키 추가
ALTER TABLE TN_PRJS 
ADD CONSTRAINT PK_TN_PRJS PRIMARY KEY (PRJT_ID, DOC_ID)
;

COMMIT
;

/*
 8-23. 프로젝트 파일확장자별 파일수가 많은 상위 5건을 표시하되, 평균 파일수를 맨 위에 한 행 표시하시오
*/
SELECT 'NULL' AS PRJT_ID        -- T1.PRJT_ID 대응
     , 'NULL' AS PRJT_NM    -- T1.PRJT_NM 대응
     , 'NULL' AS FIL_TP         -- T2.FIL_TP 대응
     , ROUND(AVG(FIL_CNT), 2) AS FIL_CNT -- T2.FIL_CNT 대응
     , 0 AS SN                 -- T2.SN 대응 (정렬을 위해 0 부여)
  FROM (
        SELECT PRJT_ID
             , FIL_TP
             , COUNT(DOC_ID) AS FIL_CNT
          FROM TN_PRJS
         GROUP BY PRJT_ID
             , FIL_TP
       )
 UNION ALL
SELECT T1.PRJT_ID
     , T1.PRJT_NM
     , T2.FIL_TP
     , T2.FIL_CNT
     , T2.SN
  FROM TN_PRJT T1
 INNER JOIN (
             SELECT PRJT_ID
                  , FIL_TP
                  , COUNT(DOC_ID) AS FIL_CNT
                  , ROW_NUMBER() OVER(ORDER BY COUNT(DOC_ID) DESC) AS SN
               FROM TN_PRJS
              GROUP BY PRJT_ID
                  , FIL_TP
            ) T2
         ON T1.PRJT_ID=T2.PRJT_ID
 WHERE SN<=5
;

--상무님
WITH WA AS
(
SELECT T1.PRJT_ID
     , MAX(T1.PRJT_NM) AS PRJT_NM 
     , T2.FIL_TP
     , COUNT(*) AS CNT
     , DENSE_RANK() OVER(PARTITION BY T1.PRJT_ID ORDER BY COUNT(*) DESC) AS RNK  /* OVER() 절은 GROUP BY 절과 별개로 계산한다. 즉 PARTITION BY 절이 생략되면 전체 행 기준으로 집계한다 */
  FROM TN_PRJT T1
     , TN_PRJS T2
 WHERE 1=1
   AND T2.PRJT_ID = T1.PRJT_ID
 GROUP BY T1.PRJT_ID, T2.FIL_TP
)
SELECT T1.PRJT_ID
     , T1.PRJT_NM 
     , NVL(T1.FIL_TP,'전체') AS FIL_TP /* ROLLUP 컬럼이 1개일때는 NVL()함수로 대체할 수 있다. 단, 2개 이상일때는 GROUPING으로 구분하자 */
     , AVG(T1.CNT) AS CNT
     , NVL2(T1.FIL_TP, MAX(T1.RNK), '') AS RNK /* NVL2(컬럼, 널이아닐때, 널일때) */
  FROM WA T1
 WHERE 1=1
   AND T1.RNK <= 5
 GROUP BY T1.PRJT_ID,T1.PRJT_NM, ROLLUP(T1.FIL_TP)
 ORDER BY T1.PRJT_ID,NVL2(T1.FIL_TP,1,0), MAX(T1.RNK)
;

--NVL말고 GROUPING사용, NVL은 IS NOT NULL 제약조건이나, PK인 컬럼 대상으로만 커버가 가능하다. NULL인 행이 하나라도 있으면, 전체인 행이 2개 출력
WITH WA AS (
 SELECT T1.PRJT_ID
      , MAX(T1.PRJT_NM) AS PRJT_NM
      , T2.FIL_TP
      , COUNT(*) AS CNT
      , DENSE_RANK() OVER(PARTITION BY T1.PRJT_ID ORDER BY COUNT(*) DESC) AS RNK
   FROM TN_PRJT T1
  INNER JOIN TN_PRJS T2
          ON T1.PRJT_ID=T2.PRJT_ID
 GROUP BY T1.PRJT_ID 
     , T2.FIL_TP
)
SELECT T1.PRJT_ID
     , T1.PRJT_NM
     , CASE WHEN GROUPING(T1.FIL_TP)=1 THEN '전체' 
        ELSE T1.FIL_TP END AS FIL_TP
     , AVG(T1.CNT) AS CNT
     , NVL2(T1.FIL_TP, MAX(T1.RNK), '') AS RNK
  FROM WA T1
 WHERE T1.RNK<=5
 GROUP BY T1.PRJT_ID,T1.PRJT_NM, ROLLUP(T1.FIL_TP)
 ORDER BY T1.PRJT_ID,NVL2(T1.FIL_TP,1,0), MAX(T1.RNK)
;

/*
 8-24. 프로젝트 전체 목록을 표시하되, 전체 건수는 맨위, 년도별 건수는 각 년도 시작위치에 행을 추가하시오
 ROLLUP - GROUPING활용(결과의 정렬 순서를 조정한다
*/
--상무님
SELECT DECODE(GROUPING(SUBSTR(T1.STR_YMD,1,4))||GROUPING(T1.PRJT_ID),'11','전체','01',SUBSTR(T1.STR_YMD,1,4),MAX(T1.PRJT_NM)||'('||T1.PRJT_ID||')') AS PRJT_ID
     , MIN(T1.STR_YMD) AS MIN_STR_YMD
     , MAX(T1.END_YMD) AS MAX_END_YMD
     , SUM(1) AS CNT
  FROM TN_PRJT T1
 GROUP BY ROLLUP(SUBSTR(T1.STR_YMD,1,4), T1.PRJT_ID)
 ORDER BY GROUPING(SUBSTR(T1.STR_YMD,1,4)) DESC, SUBSTR(T1.STR_YMD,1,4) DESC
;

/*
 8-25. 프로젝트(TN_PRJT), 사원(TN_PRJM), SI업체(TN_CMPY) 3개 테이블의 식별자ID와 명칭을 하나의 집합으로 출력하시오
 --PIVOT? NO
*/
SELECT 'TN_MEM'  AS TABEL_GUBUN
     , 'MEM_ID'  AS IDEN_ID
     , T1.MEM_ID AS IDEN_VL
  FROM TN_MEM T1
 UNION ALL
SELECT 'TN_PRJT'     AS TABEL_GUBUN
     , 'PRJT_ID'     AS IDEN_ID
     , T2.PRJT_ID AS IDEN_VL
  FROM TN_PRJT T2
 UNION ALL
SELECT 'TN_CMPY'     AS TABEL_GUBUN
     , 'CMPY_ID'     AS IDEN_ID
     , T3.CMPY_ID AS IDEN_VL
  FROM TN_CMPY T3
;

/*
 8-26. 각 SI업체별, 올해의 '월별', 합계낙찰금액, 프로젝트 건수, 총 투입인원수를 출력
 
 프로젝트 건수는 해당월에 진행중인 프로젝트가 있을 때 포함
*/
--세로
SELECT SUBSTR(STR_YMD, 1, 6) AS YYYYMM
     , SUM(SUCBD_AMT)
  FROM TN_PRJT
 WHERE STR_YMD BETWEEN '20260101' AND '20261231'
 GROUP BY SUBSTR(STR_YMD, 1, 6)
 ORDER BY YYYYMM
;  

--가로  
SELECT SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202601', SUCBD_AMT)) AS "01월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202602', SUCBD_AMT)) AS "02월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202603', SUCBD_AMT)) AS "03월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202604', SUCBD_AMT)) AS "04월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202605', SUCBD_AMT)) AS "05월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202606', SUCBD_AMT)) AS "06월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202607', SUCBD_AMT)) AS "07월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202608', SUCBD_AMT)) AS "08월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202609', SUCBD_AMT)) AS "09월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202610', SUCBD_AMT)) AS "10월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202611', SUCBD_AMT)) AS "11월"
     , SUM(DECODE(SUBSTR(STR_YMD, 1, 6), '202612', SUCBD_AMT)) AS "12월"
  FROM TN_PRJT
;

WITH TMP_MONTH AS (
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20260101', 'YYYYMMDD'), LEVEL-1), 'YYYYMM') AS YM
  FROM DUAL
CONNECT BY LEVEL <= 12
)
SELECT *
  FROM (
        SELECT PRJT_ID
             , SUBSTR(STR_YMD, 1, 6) AS STR_YMD
             , SUBSTR(END_YMD, 1, 6) AS END_YMD
          FROM TN_PRJT
         GROUP BY PRJT_ID
             , SUBSTR(STR_YMD, 1, 6)
             , SUBSTR(END_YMD, 1, 6)
       ) A
     , TMP_MONTH B
 WHERE B.YM BETWEEN SUBSTR(A.STR_YMD, 1, 6) AND SUBSTR(A.END_YMD, 1, 6)
 ORDER BY 1
;

--상무님
WITH WA AS
(-- 회사 당 BASE_YM(기준월)별 PRJT_CNT, SUCBD_AMT 
 SELECT T1.CMPY_ID
      , MAX(T1.CMPY_NM) AS CMPY_NM
      , SUBSTR(T3.STR_YMD, 1, 6) AS BASE_YM
      , COUNT(DISTINCT T2.PRJT_ID) AS PRJT_CNT
      , SUM(T3.SUCBD_AMT) AS SUCBD_AMT
  FROM TN_CMPY T1 /* CMPY_ID */
     , TN_PRJC T2 /* PRJT_ID, CMPY_ID */
     , TN_PRJT T3 /* PRJT_ID */
 WHERE 1=1
   AND T1.CMPY_ID=T2.CMPY_ID(+)
   AND T2.PRJT_ID=T3.PRJT_ID(+)
 GROUP BY T1.CMPY_ID
     , SUBSTR(T3.STR_YMD, 1, 6)
), WB AS  
( -- 회사 당 BASE_YM(기준월)별 MEM_CNT
 SELECT T1.CMPY_ID
      , SUBSTR(T2.STR_YMD,1,6) AS BASE_YM
      , COUNT(*) AS MEM_CNT
  FROM TN_PRJM T1 /* PRJT_ID, MEM_ID, CMPY_ID */
     , TN_PRJT T2 /* PRJT_ID */
 WHERE 1=1 
   AND T1.PRJT_ID = T2.PRJT_ID(+)
 GROUP BY T1.CMPY_ID
     , SUBSTR(T2.STR_YMD,1,6)
), WC AS (
SELECT SUBSTR(MIN(BASE_YM), 1, 4) AS MIN_YY
     , SUBSTR(MAX(BASE_YM), 1, 4) AS MAX_YY
     , SUBSTR(MAX(BASE_YM), 1, 4)-SUBSTR(MIN(BASE_YM), 1, 4)+1 AS GAB_YY
  FROM WA
), WAS AS ( -- 모든업체가 모든 달에 데이터를 가지도록 만들기 위해 카타시안 곱!
SELECT T1.CMPY_ID
     , T1.CMPY_NM
     , T2.BASE_YM
  FROM (
        SELECT DISTINCT CMPY_ID, CMPY_NM FROM WA
       ) T1
     , (
        SELECT TO_CHAR(ADD_MONTHS(TO_DATE(T2.MIN_YY||'01', 'YYYYMM'), (LEVEL-1)), 'YYYYMM') AS BASE_YM
          FROM DUAL T1
             , WC T2
        CONNECT BY LEVEL <= T2.GAB_YY*12
       ) T2
)
SELECT T1.CMPY_ID   AS 회사ID
     , T1.CMPY_NM   AS 회사명
     , T1.BASE_YM   AS 기준년월
     , T2.PRJT_CNT  AS 프로젝트건수
     , T2.SUCBD_AMT AS 합계낙찰금액
     , T3.MEM_CNT   AS 총투입인원수
  FROM WAS T1
     , WA T2 -- PRJT_CNT, SUCBD_AMT
     , WB T3 -- MEM_CNT
 WHERE 1=1
   AND T1.CMPY_ID=T2.CMPY_ID(+)
   AND T1.BASE_YM=T2.BASE_YM(+)
   AND T1.CMPY_ID=T3.CMPY_ID(+)
   AND T1.BASE_YM=T3.BASE_YM(+)
   AND (T2.BASE_YM IS NOT NULL OR T3.BASE_YM IS NOT NULL) -- 프로젝트 카운트, 금액, 인원수 있는것만
 ORDER BY T1.CMPY_ID
     , T1.BASE_YM
;

--TN_PRJT 데이터 추가
INSERT INTO TN_PRJT VALUES ('P28', '국가 공급망 실시간 모니터링', '20250101', '20271231', '산업통상자원부', 5500000000, 5200000000, 'C04');
INSERT INTO TN_PRJT VALUES ('P29', '차세대 재난 대응 통합 시스템', '20260215', '20280214', '행안부', 9000000000, 8800000000, 'C10');
INSERT INTO TN_PRJT VALUES ('P30', '디지털 트윈 기반 도시 관리', '20250601', '20270531', '국토부', 6500000000, 6300000000, 'C03');
INSERT INTO TN_PRJT VALUES ('P31', 'AI 기반 기후 변화 예측 플랫폼', '20241001', '20261231', '환경부', 4200000000, 4000000000, 'C09');
INSERT INTO TN_PRJT VALUES ('P32', '정밀 의료 유전체 분석 시스템', '20250320', '20270319', '보건복지부', 7200000000, 7000000000, 'C08');
INSERT INTO TN_PRJT VALUES ('P33', '자율주행 선박 항법 지원 체계', '20240815', '20260814', '해양수산부', 5800000000, 5600000000, 'C14');
INSERT INTO TN_PRJT VALUES ('P34', '지능형 산불 감시망 고도화', '20260110', '20270109', '산림청', 3200000000, 3100000000, 'C13');


/*
 8-27. 파일 확장자별(표측), 파일 유형별(표두)로 산출물 건수를 출력하시오
*/
SELECT FIL_TP
     , NVL(보고서, 0) AS 보고서
     , NVL(분석, 0) AS 분석
     , NVL(설계, 0) AS 설계
     , NVL(소스, 0) AS 소스
     , NVL(테스트, 0) AS 테스트
  FROM (
        SELECT A.PRJT_ID
             , A.DOC_ID
             , A.FIL_TP
             , B.DOC_TP
          FROM TN_PRJS A /* PRJT_ID, DOC_ID */
             , (
                SELECT PRJT_ID
                     , DOC_ID
                     , CASE WHEN A.DOC_NM LIKE '%보고서%' THEN '보고서'
                            WHEN A.DOC_NM LIKE '%분석%' THEN '분석'
                            WHEN A.DOC_NM LIKE '%설계%' THEN '설계'
                            WHEN A.DOC_NM LIKE '%소스%' THEN '소스'
                            WHEN A.DOC_NM LIKE '%테스트%' THEN '테스트'
                        ELSE '해당없음' END AS DOC_TP 
                  FROM TN_PRJS A
               ) B
         WHERE A.PRJT_ID = B.PRJT_ID
           AND A.DOC_ID = B.DOC_ID
       )
 PIVOT (SUM(1) FOR DOC_TP IN ('보고서' AS 보고서, '분석' AS 분석, '설계' AS 설계, '소스' AS 소스, '테스트' AS 테스트))
;

/*
 8-28. 최근 10년간 연도별(표측), 신입 채용한 사원수가 높은 업체 3곳을 출력하시오
*/
--PRJM PK키 추가
ALTER TABLE TN_PRJM ADD CONSTRAINT PK_TN_PRJM PRIMARY KEY (PRJT_ID, MEM_ID)
;

WITH YYYY AS (
 SELECT TO_CHAR(SYSDATE, 'YYYY') - LEVEL + 1 AS YEAR
   FROM DUAL
 CONNECT BY LEVEL <= 10
), MEM_ALL AS (
SELECT DISTINCT A.MEM_ID
     , A.MEM_NM
     , A.JOIN_YY
     , C.CMPY_ID
     , C.CMPY_NM
  FROM (
        SELECT MEM_ID
             , MAX(MEM_NM) AS MEM_NM
             , SUBSTR(JOIN_YMD, 1, 4) AS JOIN_YY
          FROM TN_MEM /* MEM_ID */
         WHERE SUBSTR(JOIN_YMD, 1, 4) BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE, -120), 'YYYY') AND TO_CHAR(SYSDATE, 'YYYY')
         GROUP BY MEM_ID
             , SUBSTR(JOIN_YMD, 1, 4)
       ) A  
     , TN_PRJM B    /* PRJT_ID, MEM_ID */
     , TN_CMPY C    /* CMPY_ID */
 WHERE A.MEM_ID = B.MEM_ID
   AND B.CMPY_ID = C.CMPY_ID
 ORDER BY 1
)
SELECT B.YEAR
     , A.CMPY_NM||': '||SUM(1) AS JOIN_CNT
     , DENSE_RANK() OVER(ORDER BY SUM(1) DESC) AS RANKG
  FROM MEM_ALL A
     , YYYY B
 WHERE A.JOIN_YY=B.YEAR
 GROUP BY B.YEAR
     , A.CMPY_NM
 ORDER BY 1
;

--by 상무님

--상무님이랑 나랑 TN_MEM 구조가 다르다.
--> 수정 DROP & CREATE로 CMPY_ID 추가.
WITH WA AS (
SELECT SUBSTR(T1.JOIN_YMD,1,4) AS JOIN_YY
     , T1.CMPY_ID
     , COUNT(*) AS MEM_CNT
  FROM TN_MEM T1
 WHERE 1=1
   AND T1.JOIN_YMD >= (TO_CHAR(SYSDATE,'YYYY')-9)||'0101'
   AND T1.JOIN_YMD <= TO_CHAR(SYSDATE,'YYYY')||'1231'
 GROUP BY SUBSTR(T1.JOIN_YMD,1,4), T1.CMPY_ID
)
, WB AS (
SELECT T1.*
     , T2.CMPY_NM
     , ROW_NUMBER() OVER(PARTITION BY T1.JOIN_YY ORDER BY MEM_CNT DESC) AS RNK
  FROM WA T1
     , TN_CMPY T2
 WHERE T2.CMPY_ID(+) = T1.CMPY_ID 
)
SELECT T1.JOIN_YY
     , T2.RNK1
     , T2.RNK2
     , T2.RNK3
  FROM 
     ( SELECT TO_CHAR(SYSDATE,'YYYY') - LEVEL + 1 AS JOIN_YY FROM DUAL CONNECT BY LEVEL <= 10 ) T1
     ,
     (
       SELECT T.JOIN_YY
            , MAX(DECODE(T.RNK,1,T.CMPY_NM||':'||T.MEM_CNT)) AS RNK1
            , MAX(DECODE(T.RNK,2,T.CMPY_NM||':'||T.MEM_CNT)) AS RNK2
            , MAX(DECODE(T.RNK,3,T.CMPY_NM||':'||T.MEM_CNT)) AS RNK3
         FROM WB T
        GROUP BY T.JOIN_YY
     ) T2 
 WHERE T1.JOIN_YY = T2.JOIN_YY(+) 
 ORDER BY T1.JOIN_YY
;


/*
 8-29. 최근 3년간, 평균 신입 채용율이 높은 업체 10곳을 채용순위별(표측)
 채용순위는 표측에, 1~10위로 누락없이 표시
 업체명에 해당 업체를 표시하며, 당년 ~ 전전년까지는 해당 연도의 신입사원 채용 수 표시
*/
WITH ALL_MEM_CNT AS (
 SELECT CMPY_ID
      , SUM(1) AS ALL_CNT
   FROM TN_MEM /* MEM_ID */
  GROUP BY CMPY_ID
)
, YY_MEM_CNT AS (
 SELECT SUBSTR(JOIN_YMD, 1, 4) AS YYYY
      , CMPY_ID
      , COUNT(MEM_ID) AS YY_CNT
   FROM TN_MEM /* MEM_ID */
  GROUP BY SUBSTR(JOIN_YMD, 1, 4)
      , CMPY_ID
)
, CMPY_NEW_MEM AS (
 SELECT DISTINCT A.CMPY_ID
      , ROUND((YY_CNT/ALL_CNT)*100, 2) AS NEW_MEM
   FROM TN_MEM A /* MEM_ID */
      , ALL_MEM_CNT B
      , YY_MEM_CNT C
  WHERE A.CMPY_ID=B.CMPY_ID
    AND A.CMPY_ID=C.CMPY_ID
)
, TN_YY AS (
 SELECT TO_CHAR(SYSDATE, 'YYYY') - LEVEL + 1 AS JOIN_YY 
   FROM DUAL 
 CONNECT BY LEVEL <= 3
)
--제미나이
, CMPY_YEARLY_PIVOT AS (
 SELECT CMPY_ID
      , SUM(CASE WHEN YYYY = (SELECT JOIN_YY FROM TN_YY WHERE JOIN_YY=TO_CHAR(SYSDATE, 'YYYY')) THEN YY_CNT ELSE 0 END) AS CNT_0 -- 올해
      , SUM(CASE WHEN YYYY = (SELECT JOIN_YY FROM TN_YY WHERE JOIN_YY=TO_CHAR(SYSDATE, 'YYYY')-1) THEN YY_CNT ELSE 0 END) AS CNT_1 -- 작년
      , SUM(CASE WHEN YYYY = (SELECT JOIN_YY FROM TN_YY WHERE JOIN_YY=TO_CHAR(SYSDATE, 'YYYY')-2) THEN YY_CNT ELSE 0 END) AS CNT_2 -- 재작년
   FROM YY_MEM_CNT
  GROUP BY CMPY_ID
)   
SELECT ROW_NUMBER() OVER(ORDER BY ROUND((C.CNT_0 / B.ALL_CNT) * 100, 2) DESC) AS "채용순위"
     , A.CMPY_ID
     , A.CMPY_NM
     , ROUND((C.CNT_0/B.ALL_CNT)*100, 2) AS NEW_MEM_RATE
     , C.CNT_0 AS "당년"
     , C.CNT_1 AS "전년"
     , C.CNT_2 AS "전전년"
  FROM TN_CMPY A
     , ALL_MEM_CNT B
     , CMPY_YEARLY_PIVOT C
 WHERE A.CMPY_ID = B.CMPY_ID
   AND B.CMPY_ID = C.CMPY_ID
;

/*
 8-30. 직급별/성별(표측), 연령대별(표두) 평균연봉액을 출력하시오.
*/
WITH POS_SEX_BASE AS (
SELECT A.POS_ID
     , A.POS_NM
     , S.SEX
     , S.SEX_NM
  FROM TN_POS A /* POS_ID */
     , (
        SELECT '1' AS SEX, '남자' AS SEX_NM FROM DUAL
         UNION ALL
        SELECT '2' AS SEX, '여자' AS SEX_NM FROM DUAL
       ) S 
)
SELECT T1.POS_NM||'('||T1.POS_ID||')' AS "직급명(직급ID)"
     , T1.SEX_NM AS "성별"
     , T2.SAL_10 AS "10대(~19)"
     , T2.SAL_20 AS "20대(20~29)"
     , T2.SAL_30 AS "30대(30~39)"
     , T2.SAL_40 AS "40대(40~49)"
     , T2.SAL_50 AS "50대(50~59)"
     , T2.SAL_60 AS "60대이상(60~)"
  FROM POS_SEX_BASE T1
     , (
        SELECT C.POS_ID
             , C.SEX
             , ROUND(AVG(DECODE(C.AGE_GB, '10', C.SAL_MONEY)), 2) AS SAL_10
             , ROUND(AVG(DECODE(C.AGE_GB, '20', C.SAL_MONEY)), 2) AS SAL_20
             , ROUND(AVG(DECODE(C.AGE_GB, '30', C.SAL_MONEY)), 2) AS SAL_30
             , ROUND(AVG(DECODE(C.AGE_GB, '40', C.SAL_MONEY)), 2) AS SAL_40
             , ROUND(AVG(DECODE(C.AGE_GB, '50', C.SAL_MONEY)), 2) AS SAL_50
             , ROUND(AVG(DECODE(C.AGE_GB, '60', C.SAL_MONEY)), 2) AS SAL_60
          FROM (
                SELECT A.POS_ID
                     , A.SEX
                     , CASE WHEN A.AGE < 20 THEN '10'
                            WHEN A.AGE < 30 THEN '20' 
                            WHEN A.AGE < 40 THEN '30'
                            WHEN A.AGE < 50 THEN '40'  
                            WHEN A.AGE < 60 THEN '50'
                       ELSE '60' END AS AGE_GB
                     , B.SAL_YM
                     , B.SAL_MONEY
                  FROM TN_MEM A /* MEM_ID */
                     , TN_SAL B /* MEM_ID */
                 WHERE A.MEM_ID=B.MEM_ID(+)
               ) C
         GROUP BY C.POS_ID
             , C.SEX
       ) T2
 WHERE T1.POS_ID = T2.POS_ID(+) 
   AND T1.SEX = T2.SEX(+) 
;

SELECT *
  FROM POS_SEX_BASE A
     , STD_DATA B
 WHERE 
;

SELECT A.POS_NM || '(' || BASE.POS_ID || ')' AS 직급
     , A.SEX_NM AS 성별
     , ROUND(AVG(CASE WHEN ))
  FROM POS_SEX_BASE A
;

--by 상무님
SELECT T1.POS_NM||'('||T1.POS_ID||')' AS "직급명(직급ID)"
     , T1.SEX_NM AS "성별"
     , T2.SAL_10 AS "10대(~19)"
     , T2.SAL_20 AS "20대(20~29)"
     , T2.SAL_30 AS "30대(30~39)"
     , T2.SAL_40 AS "40대(40~49)"
     , T2.SAL_50 AS "50대(50~59)"
     , T2.SAL_60 AS "60대이상(60~)"
  FROM (
        SELECT A.POS_ID
             , A.POS_NM
             , B.SEX_CD
             , B.SEX_NM
          FROM TN_POS A
             ,
             (
              SELECT '1' SEX_CD, '남' SEX_NM FROM DUAL 
               UNION ALL
              SELECT '2' SEX_CD, '여' SEX_NM FROM DUAL
             ) B
       ) T1
     , (
        SELECT A.POS_ID
             , A.SEX
             , ROUND(AVG(DECODE(A.AGE_GB,'10',A.SAL_MONEY)), 2) AS SAL_10
             , ROUND(AVG(DECODE(A.AGE_GB,'20',A.SAL_MONEY)), 2) AS SAL_20
             , ROUND(AVG(DECODE(A.AGE_GB,'30',A.SAL_MONEY)), 2) AS SAL_30
             , ROUND(AVG(DECODE(A.AGE_GB,'40',A.SAL_MONEY)), 2) AS SAL_40
             , ROUND(AVG(DECODE(A.AGE_GB,'50',A.SAL_MONEY)), 2) AS SAL_50
             , ROUND(AVG(DECODE(A.AGE_GB,'60',A.SAL_MONEY)), 2) AS SAL_60
          FROM (
                SELECT A.POS_ID
                     , A.SEX
                     , CASE WHEN A.AGE < 20 THEN '10'
                            WHEN A.AGE < 30 THEN '20' 
                            WHEN A.AGE < 40 THEN '30'
                            WHEN A.AGE < 50 THEN '40'  
                            WHEN A.AGE < 60 THEN '50'
                       ELSE '60' END AS AGE_GB
                     , B.SAL_YM
                     , B.SAL_MONEY
                  FROM TN_MEM A /* MEM_ID */
                     , TN_SAL B /* MEM_ID */
                 WHERE A.MEM_ID=B.MEM_ID(+)
               ) A
           GROUP BY A.POS_ID
               , A.SEX
       ) T2
 WHERE T1.POS_ID=T2.POS_ID(+)
   AND T1.SEX_CD=T2.SEX(+)
 ORDER BY T1.POS_ID, T1.SEX_CD
;

SELECT *
  FROM TN_PRJT
;

SELECT *
  FROM TN_PRJM
;

SELECT *
  FROM TN_PRJS
;

SELECT *
  FROM TN_PRJC
;

SELECT *
  FROM TN_MEM
;

SELECT *
  FROM TN_PART
;

SELECT *
  FROM TN_SAL
;

SELECT *
  FROM TN_POS
;

SELECT *
  FROM TN_CMPY 
;