[DBMS_09] 함수 (그룹 숫자 문자 날짜)
1. 그룹함수
여러개의 행 또는 테이블 전체에 대하여 하나의 결과만을 가져오는 함수
그룹함수만 일반컬럼과 함께 사용될 수 없다
1) AVG() : 평균 구하는 함수
2) COUNT() : 행의 개수를 반환하는 함수 (null 값은 무시함)
3) MAX() : 최대값을 구하는 함수
4) MIN() : 최소값을 구하는 함수
5) SUM() : 총 합을 구하는 함수
2) COUNT() : 행의 개수를 반환하는 함수 (null 값은 무시함)
-- 그룹함수와 일반컬럼은 함께 사용될 수 없다
SELECT COUNT(commission_pct), salary FROM employees;
-- 사원 테이블에서 사번을 가지고있는 모든 사원의 수를 출력
SELECT COUNT (employee_id) FROM employees;
--사원테이블의 모든 사원의 수를 출력
SELECT COUNT (*) FROM employees;
--아래의 쿼리문은 부서번호를 가지고있지 않은 인원이 1명 존재하기 때문에 전체인원인 107명을 출력할 수 없다
SELECT COUNT (department_id) FROM employees;
--사원테이블에서 관리자 id를 가진 사원 수를 출력
SELECT COUNT(manager_id) FROM employees;
-- 사원테이블에서 전체 사원수와 보너스를 받는 사원의 수를 각각 출력
*그룹함수는 일반컬럼과는 함께 사용할 수 없지만, 그룹함수는 중복 사용할 수 있다!
SELECT COUNT(*) allCnt, COUNT(commission_pct) comm
FROM employees;
-- 직종이 'SA_REP' 인 사원들의 수와 평균 급여와, 최고급여, 최저급여, 급여의 총합계를 화면에 출력
SELECT COUNT(*) SR_COUNT, AVG(salary),MAX(salary), MIN(salary), SUM(salary)
FROM employees WHERE job_id = 'SA_REP';
2. 숫자함수
1) ROUND() : 반올림
2) TRUNC() : 버림
3) POWER() : 제곱
4) MOD() : 나머지 값
SELECT ROUND(0.12345678,2),
ROUND(3.141592,3) FROM dual;
-- 숫자함수는 그룹함수를 포함 할 수있다!
SELECT ROUND(AVG(salary),2) FROM employees;
--4의 2제곱
SELECT POWER(4,2) FROM dual;-- 10%3 나머지
SELECT MOD(10,3) FROM dual;
-- 10/3 몫을 소숫점 두자리까지 구하고싶다
SELECT TRUNC(10/3,2) FROM dual;
--짝수 사번을 가진 사원들의 사원이름을 출력
SELECT employee_id, first_name FROM employees
WHERE MOD(employee_id,2) = 0;
3. 문자함수
1.) initcap() :첫 문자를 대문자로 변환
2) lower() : 대문자 -> 소문자
3) upper() : 소문자 -> 대문자
4) substr() : 특정문장 잘라내기
5) replace() : 필요한 문장 교체
6) length() : 문장의 길이
1. initcap() :첫 문자를 대문자로 변환
SELECT initcap('goodmorning') FROM dual;
--initcap은 공백이나 /를 구분자로 인식한다
SELECT initcap('good/morning') FROM dual;
2. lower() : 대문자 -> 소문자
SELECT lower('GOOD MORNING') FROM dual;
문자함수는 일반컬럼과 사용가능! 그룹함수만 안됨!!
SELECT first_name, lower(first_name) FROM employees;
대소문자 상관없이 비교하고싶을땐
모두 대문자로 만들어 비교하거나 모두 소문자로 만들어 비교하기
SELECT first_name FROM employees
WHERE lower(first_name) = 'michael';3. upper() : 소문자 -> 대문자
SELECT UPPER('hi hello') FROM dual;
4. substr() : 특정문장 잘라내기
SELECT substr('good morning', 1, 4) FROM dual;
-- 입사일, 입사년도만 출력
SELECT hire_date, substr(hire_date, 7, 10) HIRE_YEAR FROM employees;
5. replace() : 필요한 문장 교체
--
SELECT replace('good morning tom', 'o','i') FROM dual;
SELECT replace('good morning tom', 'tmo','john') FROM dual;
6. length() : 문장의 길이
--사원테이블에서 이름이 여섯글자 초과인 사원들의 이름만 출력
SELECT first_name FROM employees
WHERE length(first_name) > 6;
4. 날짜 함수
1) sysdate() : 오늘 날짜 키워드
2) months_between() : 날짜와 날짜 사이의 개월 수
3) add_months() : 특정 날짜로 부터 몇개월이 경과했는지 확인
4) to_char() : 원하는 형식으로 날짜 포멧을 변경하는 함수
1) sysdate() : 오늘 날짜 키워드
SELECT sysdate FROM dual;
2) months_between() : 날짜와 날짜 사이의 개월 수
--사원테이블에서 모든 사원들이 입사일로부터 오늘까지 몇개월이 경과했는지 출력
SELECT first_name, sysdate, hire_date, months_between( sysdate, hire_date) FROM employees;
-- '11/04/2020' 부터 오늘까지의 개월 수 출력
SELECT months_between( sysdate,'11/04/2020') FROM dual;
---- 위의 문제 소숫점 삭제 하기 '11/04/2020' 부터 오늘까지의 개월 수 출력
SELECT TRUNC(months_between(sysdate, '11/04/2020')) FROM dual;*trunc 뒤의 소숫점을 없애고 싶을땐 아예 생략 가능하다!
-- 입사기간이 200개월 이상인 사원들의 이름, 입사일, 근무개월수를 조회하시오 ( 단, 근무개월수는 소수점 이하 자릿수를 삭제)
SELECT first_name, hire_date, trunc( months_between(sysdate, hire_date) , 0)
FROM employees
WHERE months_between(sysdate, hire_date) >= 200;3) add_months() : 특정 날짜로 부터 몇개월이 경과했는지 확인
-- 오늘 날짜로부터 6개월 뒤의 날짜를 출력
-- 사번이 120번인 사원이 입사 후 3년 6개월이 되는 날 퇴사한다. 이 사원의 이름과 입사일, 퇴사일을 출력
SELECT first_name, hire_date, add_months(hire_date, 12*3 + 6) RETIRE_DATE FROM employees
WHERE employee_id = 120;
--오늘을 기준으로 가장 빨리 돌아오는 월요일을 출력
/* 일 : 1, 월:2, 화:3 ... 토:7 */
SELECT SYSDATE, next_day(sysdate,2) FROM dual;4) to_char() : 원하는 형식으로 날짜 포멧을 변경하는 함수
yyyy,yy : 연도 / mm : 월 / dd : 일 / month,mon : 월 / day : 요일 / scc : 세기 / HH:시간 / MS :분 / SS:초
SELECT to_char(sysdate,'yyyy/mm/dd') from dual;
SELECT to_char(sysdate,'yy/mm/dd') from dual;
SELECT to_char(sysdate, 'yyyy,mm,dd, month') from dual;
SELECT to_char(sysdate, 'yyyy,mm,dd, mon') from dual;
SELECT to_char(sysdate, 'yyyy,mm,dd, day') from dual;
SELECT to_char(sysdate, 'scc yyyy-mm-dd') from dual;
/*기본 12시간 기준*/
SELECT to_char(sysdate, 'mm-dd HH') from dual;
/*HH24 : 24시간 기준*/
SELECT to_char(sysdate, 'mm-dd HH24') from dual;
/*MI : SS 분 초*/
SELECT to_char(sysdate, 'mm-dd HH24:MI:SS') from dual;
5. 기타 함수
1) NVL() : NULL값을 다른 데이터로 변경
2) DECODE() : SWITCH문의 역할을 하는 함수
3) CASE() : else if문의 역할을 하는 함수
1) NVL() : NULL값을 다른 데이터로 변경
--사원 테이블에서 사번, 이름, 보너스를 출력
SELECT employee_id, first_name, NVL(commission_pct,0)*salary
FROM employees;
2) DECODE() : SWITCH문의 역할을 하는 함수
SELECT department_id, DECODE(department_id,
20, '마케팅',
60, '전산',
90, '경영',
'기타부서')
FROM employees
ORDER BY department_id;
-- 부서테이블에서 manager_id가 null인경우 'x'로 변경하여 부서번호, 상사번호를 출력 ( 상사번호가 있다면 그대로 출력)
SELECT department_id, DECODE(manager_id, NULL, 'X', manager_id ) FROM departments;
-- 이름과 직종을 출력하되 직종이 'IT_FROG'인 사람들은 개발자, 직종이 'SA_MAN'인 사원들은 '영업', 그렇지 않은 나머지 직종들은 '기타'라고 출력
SELECT first_name, DECODE(job_id ,'IT_FROG', '개발자','SA_MAN','영업', '기타' ) FROM employees ;
3) CASE() : else if문의 역할을 하는 함수
case when 조건식 then 컬럼값
when 조건식2 then 컬럼값
else 조건이 일치하지 않을때의 컬럼값
end 컬럼명
SELECT first_name, department_id,
CASE WHEN department_id = 20 THEN'마케팅'
WHEN department_id = 60 THEN '전산실'
WHEN department_id = 90 THEN '경리부'
ELSE '기타부서'
END dept
FROM employees;
-- 회사에서 입사일에 따라 퇴직금을 차등 지급하려고 한다
이름, 입사일, 급여, 퇴직금 순으로 출력
(04년도 이전에 입사한 사원 : 급여*100, 04~ 06년도에 입사한 사원 : 급여 *50, 07이후에 입사한 직원 : 급여 *20)
SELECT first_name, hire_date, salary,
CASE WHEN hire_date < '01/01/2004' THEN salary * 100
WHEN hire_date <'01/01/2007' THEN salary * 50
ELSE salary * 20
END SERVERANCE_PAY
FROM employees
ORDER BY hire_date;