#15. 오라클 함수(TRIM, ROUND...)_23.04.10
-- (1) 집합
-- A집합 : 2조인 사람
SELECT * FROM PYSTU WHERE PY_TEAM =2;
-- B집합 : 성이 '김'씨인 사람
SELECT * FROM PYSTU WHERE PY_NAME LIKE '김%';
-- 교집합(INTERSECT) : 결과 값이 같은 데이터만 출력
SELECT * FROM PYSTU WHERE PY_TEAM =2
INTERSECT
SELECT * FROM PYSTU WHERE PY_NAME LIKE '김%';
-- 합집합(UNION) : 모든 결과 데이터를 출력
SELECT * FROM PYSTU WHERE PY_TEAM =2
UNION
SELECT * FROM PYSTU WHERE PY_NAME LIKE '김%';
-- 차집합(MINUS) : 먼저 작성한 집합(SELECT문)에서
-- 나중에 작성한 집합(SELECT문)의 데이터를 빼고 출력
SELECT * FROM PYSTU WHERE PY_TEAM =2
MINUS
SELECT * FROM PYSTU WHERE PY_NAME LIKE '김%';
-- (2) TRIM() : 공백제거 , LTRIM(), RTRIM()
SELECT ' 인천일보 아카데미 'AS 데이터,
TRIM(' 인천일보 아카데미 ') AS TRIM,
LTRIM(' 인천일보 아카데미 ') AS LTRIM,
RTRIM(' 인천일보 아카데미 ') AS RTRIM
FROM DUAL;
-- (3) 절대값 : ABS()
SELECT ABS(5) AS "절대값 5",
ABS(-10) AS "절대값-10"
FROM DUAL;
-- (4) 반올림 : ROUND()
SELECT
ROUND (1234.5678) AS ROUND, -- 1235
ROUND (1234.5678, 0) AS ROUND_0, -- 1235 : (기본값) 소숫점 0번째 자리에서 출력
ROUND (1234.5678, 1) AS ROUND_1, -- 1234.6 : 소숫점 1번째 자리에서 출력(두번째 자리에서 반올림)
ROUND (1234.5678, 2) AS ROUND_2, -- 1234.57 : 소숫점 2번째 자리에서 출력(세번째 자리에서 반올림)
ROUND (1234.5678, -1) AS ROUND_MINUS1, -- 1230 : 일의 자리에서 반올림
ROUND (1234.5678, -2) AS ROUND_MINUS2 -- 1200 : 십의 자리에서 반올림
FROM DUAL;
-- (4) 버림 : TRUNC()
SELECT
TRUNC (1234.5678) AS TRUNC, -- 1234
TRUNC (1234.5678, 0) AS TRUNC_0, -- 1234 : (기본값) 소숫점 0번째 자리에서 출력
TRUNC (1234.5678, 1) AS TRUNC_1, -- 1234.5 : 소숫점 1번째 자리에서 출력(두번째 자리에서 반올림)
TRUNC (1234.5678, 2) AS TRUNC_2, -- 1234.56 : 소숫점 2번째 자리에서 출력(세번째 자리에서 반올림)
TRUNC (1234.5678, -1) AS TRUNC_MINUS1, -- 1230 : 일의 자리에서 반올림
TRUNC (1234.5678, -2) AS TRUNC_MINUS2 -- 1200 : 십의 자리에서 반올림
FROM DUAL;
-- (6) 가까운 정수 찾기 : CEIL(본인보다 큰), FLOOR(본인보다 작은)
SELECT
CEIL(3.14),
FLOOR(3.14),
CEIL(-3.14) ,
FLOOR(-3.14),
ROUND(-3.6)
FROM DUAL;
-- (7) 나머지 값 구하기 : MOD(A, B) => A % B
-- 15를 6으로 나눌때 몫과 나머지를 구하시오
SELECT
FLOOR(15/6) AS 몫,
MOD(15, 6) AS 나머지
FROM DUAL;
-- 1년은 몇주 + 며칠(나머지 일수)로 이루어져 있는가?
SELECT
'1년은' || FLOOR(365/7) || '주' || MOD(365,7)|| '일로 이루어져 있다'
FROM DUAL;
-- (8) 날짜 관련 함수
-- DATE 날짜 + 숫자 : 숫자만큼 이후 날짜
-- DATE 날짜 - 숫자 : 숫자만큼 이전 날짜
-- DATE 날짜1 - 날짜2 : 두 날짜간의 일수 차이
-- DATE 날짜1 + 날짜2 : 연산 불가능
-- 날짜 형식 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
SELECT
SYSDATE AS "NOW(오늘)",
SYSDATE+1 AS "TOMORROW(내일)",
SYSDATE-1 AS "YESTERDAY(어제)"
FROM DUAL;
-- 오늘, 일주일 전, 일주일 후, (일주일전)과 (일주일후)의 일수
SELECT
SYSDATE AS "NOW(오늘)",
SYSDATE-7 AS "일주일전",
SYSDATE+7 AS "일주일후",
(SYSDATE+7) - (SYSDATE-7) AS "일수 차이"
FROM DUAL;
-- (9) 개월 수 구하기 : MONTHS_BETWEEN(A, B)
-- 빠른 날짜를 B에 작성
SELECT
MONTHS_BETWEEN( '2023/08/21','2023/03/21') AS "개월 수"
FROM DUAL;
-- (10) 개월 수 더하기 : ADD_MONTHS(A, B)
-- A : 날짜, B : 더할 개월 수
SELECT
ADD_MONTHS(SYSDATE, 5)
FROM DUAL;
-- (11) 다가올 요일에 해당하는 날짜 구하기: NEXT_DAY(A, B)
-- A: 날짜, B: 요일
SELECT
NEXT_DAY(SYSDATE, '금요일') AS "돌아오는 금요일"
FROM DUAL;
-- (12) LAST_DAY : 이번달 / 특정달의 마지막 일수
SELECT
LAST_DAY(SYSDATE) AS "이번달 마지막 일은?"
FROM DUAL;
-- 2024년 2월은 마지막 일 출력하기
-- SYSDATE : '2023/04/10'
SELECT
LAST_DAY('2024/02/01') AS "2024년 2월의 마지막 일은?"
FROM DUAL;
' - ' 은 숫자가 아니고 숫자로 바꿀 수 없기 때문에 오류가 발생
/*
(13) TO_CHAR, TO_NUMBER,TO_DATE
1) TO_CHAR : 숫자 또는 날짜형태의 데이터를 문자데이터로 전환
EX) String.valueOf(100)
2) TO_NUMBER : 문자형태의 데이터를 숫자데이터로 전환
ex)Integer.parseInt("100")
3) TO_DATE : 문자형태의 데이터를 닐짜데이터로 전환
YYYY(YEAR) : 년도
MM(MONTH) : 월
DD(DAY) : 일
HH() : 시
MI : 분
SS : 초
DAY : 요일
*/
SELECT TO_CHAR(SYSDATE, 'MM/DD DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL; -- 2023
SELECT TO_CHAR(SYSDATE, 'YY') FROM DUAL; -- (20)23
SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL; -- 04
SELECT TO_CHAR(SYSDATE, 'MON') FROM DUAL; -- 4월
SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL; -- 10
SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL; -- 2 [일주일 기준 일수 (일요일 = 1) ]
SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL; -- 100 [1년 기준 일수] (1년 중 100일 지남)
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL; -- 월요일
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL; -- 월
SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL; -- 15 (1년 기준 주차) - 52주 중에 15주차
SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL; -- 2 (한달 기준 주차)
-- 5월 8일은 몇주차?
SELECT TO_CHAR(TO_DATE('23/04/01') , 'W') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DL') FROM DUAL; -- 2023년 4월 10일 월요일 [해당지역 날짜 형식]
-- 시간형식 변형
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT SYSDATE FROM DUAL;
-- TO_CHAR(SYSDATE, 'AM')
SELECT TO_CHAR(SYSDATE, 'AM') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'PM') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'PM') || TO_CHAR(SYSDATE, 'HH') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'PM') || TO_CHAR(SYSDATE, 'HH24') FROM DUAL;
-- 오전 11:29:30 출력(SYSDATE)
SELECT TO_CHAR(SYSDATE,'AM HH:MI:SS') FROM DUAL;
-- 2023년 4월 11일 00시 00분 00초
-- 날짜 계산하기
SELECT TO_DATE('2023/04/11 00:00:00') - SYSDATE FROM DUAL;
-- 시간(단위) 남은 시간 구하기
SELECT FLOOR((TO_DATE('2023/04/11 00:00:00') - SYSDATE )* 24) FROM DUAL;
-- 분(단위) 남은 시간 구하기
SELECT FLOOR((TO_DATE('2023/04/11 00:00:00') - SYSDATE )* 24 * 60) FROM DUAL;
-- 초(단위) 남은 시간 구하기
SELECT FLOOR((TO_DATE('2023/04/11 00:00:00') - SYSDATE )* 24 * 60 *60) FROM DUAL;
-- 4월 11일까지 XX시간 XX분 XX초 남았습니다.
SELECT '4월 11일까지' || FLOOR((TO_DATE('2023/04/11 00:00:00') - SYSDATE )* 24) ||
'시간' || FLOOR((TO_DATE('2023/04/11 00:00:00') - SYSDATE )* 24 * 60) ||
'분' || FLOOR((TO_DATE('2023/04/11 00:00:00') - SYSDATE )* 24 * 60 *60) ||
'초 남았습니다!' FROM DUAL;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
-- TO_DATE()
SELECT
TO_DATE('2023/08/21') - TO_DATE('2023/03/21')
FROM DUAL;
-- TO_NUMBER() : 문자 데이터를 숫자형 데이터로 변환하는 함수
SELECT
TO_NUMBER('123') - TO_NUMBER('100') FROM DUAL;
SELECT '010-1234-5678' AS 핸드폰번호,
TO_NUMBER(REPLACE('01012345678', '-')) AS "TO_NUMBER(핸드폰)"
FROM DUAL;
CREATE TABLE PSTU(
PNAME NVARCHAR2(10),
AGE NUMBER,
PIG NVARCHAR2(5)
);
INSERT INTO PSTU VALUES('유아인', 30, 'I');
INSERT INTO PSTU(PNAME) VALUES('조형섭');
SELECT * FROM PSTU;
/* [1] NULL 처리함수 : NVL(), NVL2() => 실제 데이터가 변하는 것은 아니고 출력값만 변한다.
NVL() : NULL값에 한해 데이터를 바꿔준다
NVL(COL_NAME, 0) : 컬럼의 데이터가 NULL일 경우 0으로 바꿔준다.
NVL2() : NULL값인 데이터와 NULL이 아닌 데이터를 바꿔준다.
NVL2([COL_NAME, 'NULL이 아닐 경우', 'NULL일 경우)
*/
SELECT * FROM PSTU;
SELECT PNAME, NVL(AGE,20) FROM PSTU;
SELECT PNAME, NVL(AGE,20), NVL2(PIG, 'IG','V30') FROM PSTU;
-- [2] DECODE문 : Java에서 switch-case문과 비슷
SELECT ENAME, DEPTNO,
DECODE( DEPTNO, 10, '인사팀',
20, '영업팀',
30, '홍보팀') AS 담당부서
FROM EMP
ORDER BY DEPTNO;
/*
switch(DEPTNO) {
case 10 :
DNAME = "인사팀";
case 20 :
DNAME = "영업팀";
case 30 :
DNAME = "홍보팀";
}
*/
-- [3] CASE - WHEN : Java에서 if-else
SELECT ENAME, DEPTNO,
CASE
WHEN DEPTNO = 10 THEN '인사팀' -- WHEN 조건1 THEN RETURN1
WHEN DEPTNO = 20 THEN '영업팀' -- WHEN 조건2 THEN RETURN2
WHEN DEPTNO = 30 THEN '홍보팀' -- WHEN 조건3 THEN RETURN3
END AS 담당부서
FROM EMP
ORDER BY DEPTNO;
-- 동등비교
SELECT ENAME, DEPTNO,
CASE DEPTNO -- CASE {COL_NAME}
WHEN 10 THEN '인사팀' -- WHEN 컬럼값1 THEN RETURN1
WHEN 20 THEN '영업팀' -- WHEN 컬럼값2 THEN RETURN2
WHEN 30 THEN '홍보팀' -- WHEN 컬럼값3 THEN RETURN3
END AS 담당부서 -- END 작성
FROM EMP
ORDER BY DEPTNO;
/*
[4] 그룹화 함수 ★★★
1. SUM() : 지정한 컬럼 데이터 합
2. AVG() : 지정한 컬럼 데이터 평균
3. COUNT() : 지정한 컬럼 데이터 갯수
4. MAX() : 지정한 컬럼 데이터 최댓값
5. MIN() : 지정한 컬럼 데이터 최솟값
*/
SELECT * FROM EMP;
SELECT SUM(SAL) AS "전 직원의 급여 합계" FROM EMP;
-- 직원의 평균 급여를 소솟점 둘째자리까지 출력하여라!
SELECT ROUND(AVG(SAL),2)AS "평균급여" FROM EMP;
-- 최고급여와 최저급여, 급여를 받는 사람의 수를 구하여라!
SELECT MAX(SAL) AS "최고급여",MIN(SAL) AS "최저급여", COUNT(SAL) AS "급여받는 인원수" FROM EMP;
-- Q. 부서번호 20번인 사람이 몇명인지, 그들의 급여 합계와 평균을 조회하고
-- 가장 많은 급여와 가장 적은 급여를 조회하세요!
-- (1) 부서번호 20인 사람이 몇명인지
SELECT COUNT(SAL) FROM EMP WHERE DEPTNO=20;
-- (2) 부서번호 20번 급여의 합계 평균
SELECT SUM(SAL) , AVG(SAL) FROM EMP WHERE DEPTNO=20;
-- (3) 부서번호 20번 최고급여, 최저급여
SELECT MAX(SAL),MIN(SAL) FROM EMP WHERE DEPTNO=20;
-- (4) 정리
SELECT
COUNT(SAL),
SUM(SAL),
AVG(SAL),
MAX(SAL),
MIN(SAL)
FROM EMP
WHERE DEPTNO=20;
/*
[5] GROUP BY : 특정 컬럼 또는 데이터를 기준으로 데이터를 그룹으로 묶음
※어떤 것을 묶어야 더 효율적인가 생각
※그룹을 묶게 되면 기존 컬럼 외에 다른 컬럼 외에 다른 컬럼은 조회가 불가능 하다
(그룹화 함수 사용가능)
SELECT
FROM
WHERE
GROUP BY [그룹화 할 COLUMN을 지정(여러개 지정)]
ORDER BY [정렬할 COLUMN];
*/
-- 부서별 평균 급여
SELECT DEPTNO, ROUND(AVG(SAL))
FROM EMP
GROUP BY DEPTNO ,EMPNO -- 부서별로 묶겠다!
ORDER BY DEPTNO;
-- Q. 직급(JOB)별로 인원수, 급여힙계, 급여 평균을 구하시오!
SELECT JOB AS 직급, COUNT(*) AS 인원수, SUM(SAL)AS 급여합계, ROUND(AVG(SAL))AS 급여평균
FROM EMP
GROUP BY JOB
ORDER BY JOB;
-- ROLLUP : 그룹화 데이터의 합계를 함께 출력
-- 인원수가 많은 직급 순으로 정렬
SELECT JOB AS 직급, COUNT(EMPNO) AS 인원수, SUM(SAL)AS 급여합계, ROUND(AVG(SAL))AS 급여평균
FROM EMP
GROUP BY ROLLUP(JOB)
ORDER BY COUNT(*) ASC;
-- [6] HAVING : GROUP BY 와 짝궁! GROUP BY절을 사용해서 그룹화 된 결과 중
-- 그룹을 선별하는 조건식
/*
SELECT
FROM
WHERE
GROUP BY [그룹화 할 COLUMN을 지정(여러개 지정)]
HAVING [묶인 그룹을 제한하는 조건식]
ORDER BY [정렬할 COLUMN];
*/
-- EX) 10번 부서와 30번 부서의 직원 중에서 급여가 1500보다 높은 직원은 몇명인가?
SELECT DEPTNO, COUNT(*)
FROM EMP
WHERE SAL > 1500
GROUP BY DEPTNO
HAVING DEPTNO IN (10, 30)
ORDER BY DEPTNO;
-- WHERE절 : 우리가 출력하는 모든 것에 대한 조건을 건다.
-- HAVING절 : 그룹화 한 데이터 안에서만 조건을 건다.
-- Q1. EMP테이블에서 부서번호 (DEPTNO), 평균급여(AVG), 최고급여(MAX), 최저급여(MIN), 사원수(COUNT)를 출력
-- (평균급여는 소숫점 둘째자리까지 출력, 각 부서번호 오름차순 정렬)
SELECT DEPTNO AS 부서번호, ROUND(AVG(SAL),2)AS 급여평균, MAX(SAL) AS 최고급여, MIN(SAL) AS 최저급여, COUNT(*)
FROM EMP
GROUP BY DEPTNO
ORDER BY COUNT(*) ASC;
-- Q2. 같은 직무(JOB)에 종사하는 사원이 3명 이상인 직급과 인원수 출력
SELECT JOB, COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >= 3
ORDER BY COUNT(*) ASC;
-- Q3. 사원들의 입사 연도를 기준으로 부서별로 몇명이 입사했는지 출력
-- TO_CHAR(HIREDATE, 'YYYY') 를 이용
-- GROUP BY로 DEPTNO와 TO_CHAR(HIREDATE, 'YYYY')를 지정
SELECT DEPTNO,TO_CHAR(HIREDATE, 'YYYY'), COUNT(*)
FROM EMP
GROUP BY DEPTNO,
TO_CHAR(HIREDATE, 'YYYY')
ORDER BY DEPTNO,
TO_CHAR(HIREDATE, 'YYYY');
-- Q4. 수당(COMM)을 받는 사원수와 받지 않는 사원수 출력
-- 수당을 받으면 Y, 받지 않으면 N으로 출력, 수당이 0인 사람도 받는다고 가정
SELECT NVL2(COMM, 'Y', 'N'), COUNT(*)
FROM EMP
GROUP BY NVL2(COMM, 'Y', 'N');
-- Q5. 각 부서의 입사 연도별 사원수, 최고급여, 급여합계, 평균급여를 출력하고
-- 각 부서별 총계를 출력하세여(ROLLUP)
SELECT DEPTNO AS 직급,TO_CHAR(HIREDATE, 'YYYY'), COUNT(*) AS 인원수,MAX(SAL), SUM(SAL)AS 급여합계, ROUND(AVG(SAL))AS 급여평균
FROM EMP
GROUP BY DEPTNO , ROLLUP(TO_CHAR(HIREDATE, 'YYYY'))
ORDER BY DEPTNO , TO_CHAR(HIREDATE, 'YYYY') ASC;