<이공계전문기술연수> 3. DB 그룹 함수

@group by

-> 그룹함수는 단 한개의 결과값만 산출하기 때문에, 그룹함수를 이용하여 여러개의
결과값을 산출하기 위해 적용될 그룹의 기준을 정해 주는 것!
-> 각 부서별 급여 총 합계


@@ 조인(JOIN)

-> 여러테이블의 레코드를 조합하여 하나의 열로 표현한것!!
-> 두 개 이상의 테이블에서 연관성을 가지고 있는 데이터들을 따로 분류하여 가상의 테이블을 이용하여 출력함
-> 서로 다른 테이블에서 각각의 공통 값을 이용함으로써 필드를 조합함


2가지문법

1. ORACLE 전용문법

2. ANSI 표준 구문

 

 

@join 종류

- INNER JOIN(내부조인) : 교집합(일반적으로 아무것도 명시 하지 않았을때 나타남)

- OUTER JOIN(외부조인) : 합집합
  -> LEFT OUTER JOIN (왼쪽 외부 조인)
  -> RIGHT OUTER JOIN (오른쪽 외부 조인)
  -> FULL OUTER JOIN (완전 외부 조인) 

@ SET Operator -> 집합 연산자

-> 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법!
-> 여러개의 질의결과를 연결하여 하나로 결합하는 방식
-> 각 테이블의 조회결과를 하나의 테이블에 합쳐서 반환
-> 조건
- SELECT 구문의 "컬럼 수가 동일"
- 동일 컬럼위치에 데이터 타입이 상호 호환이 가능해야함!

 

-> UNION , UNION ALL, INTERSECT, MINUS

A = {1,4,5,3,2}
B = {7,6,3,8,5,9,10}

UNION :A U B ={1,2,3,4,5,6,7,8,9,10}
-> 중복된 데이터를 제거하고 첫번째 컬럼 기준으로 오름차순 정렬!!!

UNION ALL : A U B = {1,4,5,3,2,7,6,3,8,5,9,10}
-> 중복된 데이터를 제거하지 않고, 정렬도 하지 않음!!

INTERSECT : A ∩ B = {3,5}

MINUS : A - B = {1,4,2}
B - A = {7,6,8,9,10}

@서브쿼리(SubQuery)

-> 하나의 SQL문 안에 포함되어 있는 또다른 SQL문
-> 알려지지않은 조건에 근거한 값들을 검색하는 SELECT문장을 작성하는데 유용함
-> 메인쿼리가 서브쿼리를 포함하는 종속적관계
-> 사용 시 서브쿼리에는 소괄호를 묶어야함

(SELECT ...)

-> 서브쿼리 내부에서 ORDER BY 문법 지원 안됨

@서브쿼리 유형(SUBQUERY)

1. 단일행 서브쿼리
-> 서브쿼리 수행결과가 단일행, 단일 열인 경우
2. 다중행 서브쿼리
-> 서브쿼리 수행결과가 단일열, 여러 행이 나오는 경우
3. 다중열 서브쿼리
-> 서브쿼리 수행결과가 단일행, 다중 열이 나오는 경우
4. 다중행 다중열 서브쿼리
-> 서브쿼리 수행결과가 다중행, 다중열이 나오는 경우
5. 상관 서브쿼리
-> 메인쿼리의 값을 서브쿼리에 주고 서브쿼리를 수행한 다음 그 결과를 다시 메인쿼리로 반환해서
수행하는 쿼리(루프)
6. 스칼라 서브쿼리
-> 상관쿼리 이면서 결과값이 1개인 서브쿼리

@단일행 서브쿼리
-> 서브쿼리 조회 결과 갯수가 1개일때

--그룹함수 -> 하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균등을 하나의 컬럼으로 리턴하는 함수!!

--SUM (해당 컬럼의 값들의 총 합을 구하는 함수)
select sum(salary) from employee where substr(emp_no,8,1) = 1; --남자애들 월급 총합

--employee 테이블에서 부서코드(dept_code)가 D5 직원의 총 연봉의 합
--연봉은(보너스를 포함한 금액)


select * from employee;
select sum((salary+(salary*nvl(bonus,0)))*12) from employee where dept_code = 'D5';

--AVG : 해당 컬럼 값들의 평균을 구하는 함수!

select floor(avg(salary)) from employee;


--COUNT : 테이블에서 조건을 만족하는 행의 개수를 반환
select * from employee;

select count(*) from employee where dept_code= 'D5';

--EMPLOYEE테이블에서 사원들이 속해있는 부서의 수를 조회

select * from employee;

select count(distinct dept_code) from employee ;  -- null을 세지 않는다



-- MAX / MIN 
-- 지정한 그룹의 최댓값/최솟값을 구하여 리턴
-- employee테이블에서 급여가 가장 높은 급여, 가장 낮은 급여
select max(salary), min(salary) from employee;

select max(hire_date), min(hire_date) from employee;

 

---

함수 최종 연습 문제

--함수 최종 연습 문제

--1. 직원명과 이메일 , 이메일 길이를 출력하시오
--		  이름	    이메일		이메일길이
--	ex) 	홍길동 , hong@kh.or.kr   	  13

select emp_name , email, length(email) AS 이메일길이 from employee;

--2. 직원의 이름과 이메일 주소중 아이디 부분만 출력하시오
--	ex) 노옹철	no_hc
--	ex) 정중하	jung_jh

select emp_name , substr(email,1,instr(email,'@',1,1)-1) from employee;              ------------다시~!!!!

--3. 60년생의 직원명과 년생, 보너스 값을 출력하시오 
--	그때 보너스 값이 null인 경우에는 0 이라고 출력 되게 만드시오
--	    직원명    년생      보너스
--	ex) 선동일	62	0.3
--	ex) 송은희	63  	0

select emp_name, substr(emp_no,1,2), NVL(bonus,0) from employee where substr(emp_no,1,1) = '6';
 
--4. '010' 핸드폰 번호를 쓰지 않는 사람의 수를 출력하시오 (뒤에 단위는 명을 붙이시오)
--	   인원
--	ex) 3명

select count(emp_name)||'명' from employee where phone not like '010%';
SELECT COUNT(PHONE)||'명' FORM EMPOLOYEE WHERE PHONE NOT LIKE '010%';


--5. 직원명과 입사년월을 출력하시오 
--	단, 아래와 같이 출력되도록 만들어 보시오
--	    직원명		입사년월
--	ex) 전형돈		2012년12월
--	ex) 전지연		1997년 3월

select emp_name, extract(year from hire_date)||'년'||extract(month from hire_date)||'월'
from employee;

--6. 직원명과 주민번호를 조회하시오
--	단, 주민번호 9번째 자리부터 끝까지는 '*' 문자로 채워서출력 하시오
--	ex) 홍길동 771120-1******

select emp_name, rpad(substr(emp_no,1,8),14,'*') from employee;
SELECT EMP_NAME, SUBSTR(EMP_NO,1,8)||'******' FROM EMPLOYEE; -------------------------


--7. 직원명, 직급코드, 연봉(원) 조회
  --단, 연봉은 ￦57,000,000 으로 표시되게 함
    -- 연봉은 보너스포인트가 적용된 1년치 급여임

select * from employee;
select emp_name, job_code,
to_char((salary + salary*NVL(bonus,0))*12 , 'L999,999,999')
from employee;


--8. 부서코드가 D5, D9인 직원들 중에서 2004년도에 입사한 직원의 사번 사원명 부서코드 입사일

select emp_id, emp_name, dept_code, hire_date
from employee
where dept_code in('D5','D9') and 
substr(hire_date,1,2)= 04 ;

--9. 직원명, 입사일, 오늘까지의 근무일수 조회 
--	* 주말도 포함 , 소수점 아래는 버림
--??????????? 주말포함이 무슨말????
select emp_id, hire_date, floor((sysdate - hire_date)) from employee;


--10. 모든 직원의 나이 중 가장 많은 나이와 가장 적은 나이를 출력 하여라. (나이만 출력)
select substr(extract(year from sysdate),3,4)-max(substr(emp_no,1,2)) from employee;

select abs(substr(extract(year from sysdate),3,4) - MAX(substr(emp_no,1,2))) 가장많은나이,
abs(substr(extract(year from sysdate),3,4) - MIN(substr(emp_no,1,2))) 가장적은나이
from employee;

select max(2019-(1900+substr(emp_no, 1,2))+1) 최대나이,
min(2019-(1900+substr(emp_no, 1,2))+1)
from employee;

-- 11.  회사에서 야근을 해야 되는 부서를 발표하여야 한다.
-- 부서코드가 D5,D6,D9  야근, 그외는 야근없음 으로 출력되도록 하여라. 
-- 출력 값은 이름,부서코드,야근유무 (부서코드 기준 오름차순 정렬함.)
--   (부서코드가 null인 사람도 야근없음 임)
select * from employee;

select emp_name, dept_code, case when dept_code = 'D5' then '야근'
when dept_code = 'D6' then '야근' when  dept_code = 'D9' then '야근' 
else '야근없음' end 야근유무 from employee order by 2; 

select emp_name, dept_code, case when dept_code = 'D5' 
OR dept_code = 'D6'OR  dept_code = 'D9' then '야근' 
else '야근없음' end 야근유무 from employee order by 2; 



--12. 직원명, 부서코드, 생년월일, 나이 조회
-- 단, 생년월일은 주민번호에서 추출해서, 
-- ㅇㅇ년 ㅇㅇ월 ㅇㅇ일로 출력되게 함.
-- 나이는 주민번호에서 추출해서 날짜 데이터로 변환한 다음, 계산함
--	* 주민번호가 이상한 사람들은 제외시키고 진행 하도록(200,201,214 번 제외)
--	* HINT : NOT IN 사용

--년도로 변환하는 숫자가 YY로 변환하면 20이 붙고
--RR 사용시 50 보다 숫자가 큰 경우 19, 50 보다 숫자가 작은경우 20
select * from employee;
select emp_name, dept_code, substr(emp_no,1,2)||'년'||substr(emp_no,3,2)||'월'||substr(emp_no,5,2)||'일' 생년,
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,6),'RRMMDD'))+1 나이 
from employee
WHERE EMP_ID NOT IN ( 200, 201, 214);

SELECT EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,6),'RRMMDD')) FROM EMPLOYEE
WHERE EMP_ID NOT IN (200, 201, 214);      -- RR????  YY????
 
--13. 직원들의 입사일로 부터 년도만 가지고, 각 년도별 입사인원수를 구하시오.
--  아래의 년도에 입사한 인원수를 조회하시오. 마지막으로 전체직원수도 구하시오
--
--	-------------------------------------------------------------------------
--	 1998년   1999년   2000년   2001년   2002년   2003년   2004년  전체직원수
--	-------------------------------------------------------------------------
select * from employee;
select sum(decode(substr(hire_date,1,2),'98','1','0')) "1998년",  
sum(decode(substr(hire_date,1,2),'99','1','0'))"1999년",
sum(decode(substr(hire_date,1,2),'00','1','0'))"2000년",
sum(decode(substr(hire_date,1,2),'01','1','0'))"2001년",
sum(decode(substr(hire_date,1,2),'92','1','0'))"2002년",
sum(decode(substr(hire_date,1,2),'93','1','0'))"2003년",
sum(decode(substr(hire_date,1,2),'94','1','0'))"2004년",
count(emp_name) 전체직원수
from employee;


--1990년도면 아무값이나 출력 나머지는 아무것도 없음(NULL)
SELECT (DECODE(EXTRACT(YEAR FROM HIRE_DATE),'1999','ASDF'))
FROM EMPLOYEE;
--COUNT 는 NULL을 세지 않는다~!~!~!


--14.  부서코드가 D5이면 총무부, D6이면 기획부, D9이면 영업부로 처리하시오.
--   단, 부서코드가 D5, D6, D9 인 직원의 정보만 조회함
--  => case 사용
--   부서코드 기준 오름차순 정렬함.
select * from employee;
select  dept_code,case when dept_code = 'D5' then '총무부' when dept_code = 'D6' then '기획부'
when dept_code = 'D9' then '영업부' end from employee 
where dept_code like 'D5' or dept_code like 'D6' or dept_code like 'D9' order by 1;

--WHERE DEPT_CODE IN('D5','D6','D9') ORDER BY 2;


--각 부서별 급여 총합계
select dept_code ,count(salary) from employee not in by dept_code;

--각 부서별 급여의 평균

select dept_code , floor(avg(salary)) from employee not in by dept_code;

--테이블에서 부서코드, 보너스를 지급받는 사원 수를 조회

select dept_code, count(bonus) from employee where bonus is not null not in by dept_code;

--employee 테이블에서 emp_no의 8번째 자리가 1이면 남, 2면 여로 분류
--성별별 급여의 평균(점수), 급여 합계, 인원수 출력

select decode(substr(emp_no,8,1),1,'남',2,'여') as 성별,
floor(avg(salary)) 평균, sum(salary) 합계, count(*) 인원수
from employee not in by decode(substr(emp_no,8,1),1,'남',2,'여');