7.12 함수, GROUP BY

Database/oracleDB

7.12 함수, GROUP BY

amungstudy 2023. 7. 12. 16:14

-- 대소문자를 바꿔주는 함수
SELECT UPPER(ENAME), LOWER(ename), INITCAP(ename)
FROM emp;

-- 예시
--SELECT * FROM 게시판
--WHERE 게시판제목 LIKE '%oracle%';
-- 이렇게 했을 때 oracle ORACLE 등의 정보는 검색이 되지 않는다.

SELECT ENAME FROM EMP
WHERE ENAME = upper('smith');

-- Smith SMITH smith sMiTh
-- 다양한 데이터가 대소문자 구분 없이 들어와도
-- SQL문 조회에서 대소문자 함수를 사용하면 모든 종류의 데이터를 검색할 수 있다.

-- 문자열의 길이 구하기
SELECT ENAME, LENGTH(ENAME) FROM EMP;

-- Q1. 사원 이름 길이가 5이상인 사람 출력하기
SELECT ENAME FROM EMP WHERE LENGTH(ENAME)>= 5 ;
-- Q2. 직책 이름 길이가 6이상인 데이터만 출력하기
DESC emp;
SELECT * FROM EMP WHERE LENGTH(job) >= 6 ;

-- 함수 2
-- 문자열 추출 SUBSTR(문자열, 시작위치, 길이)
-- 길이를 지정하지 않으면 끝까지 추출한다. SUBSTR(문자열, 시작위치)
SELECT JOB, SUBSTR(job,1,2), SUBSTR(job, 3, 2), SUBSTR(JOB,5) FROM emp;
-- Q1. 사원 이름을 세번째 글자에서 끝까지 출력되게 해주세요.
SELECT SUBSTR(ENAME,3) FROM emp;
-- 다른 함수와 같이 사용
SELECT JOB, SUBSTR(JOB, -LENGTH(JOB)), SUBSTR(JOB, -LENGTH(JOB),2)FROM emp;
-- 뒤에서 두글자 찾기
SELECT JOB, SUBSTR(JOB, -2) FROM EMP;

-- 문자열 데이터에서 위치를 찾는 함수 INSTR
-- INSTR(문자열 데이터, 찾으려는 문자, [시작위치(기본값 1)],[시작위치에서 몇번째(기본값1)])
SELECT INSTR('HELLO, ORACLE!','L') as instr_1;
SELECT INSTR('HELLO, ORACLE!','L',5); -- 시작위치가 5일때
SELECT INSTR('HELLO, ORACLE!','L',2,2); -- 두번째 등장하는 문자열 찾고 싶을때

-- WHERE 절에 사용하기
-- Q1. INSTR함수로 사원이름에 S가 있는 컬럼을 구하기
SELECT * FROM emp WHERE ENAME LIKE '%S%';
SELECT * FROM emp WHERE INSTR(ename,'S') > 0;

-- REPLACE 함수
-- REPLACE(문자열, 찾는문자, 바꿀문자)
SELECT '010-1234-5678',
    REPLACE('010-1234-5678','-',' '),
    REPLACE('010-1234-5678','-') ;
-- LPAD, RPAD
-- LPAD(문자열, 자릿수, [채울 문자])
SELECT 'Oracle',
    LPAD('Oracle',10,'*'),
    LPAD('1234',10,'0'),
    RPAD('800101-1',14,'*'),
    RPAD('Oracle',10),
    LPAD('Oracle',10); -- 채울문자 없으면 기본값 공백

-- CONCAT 함수 : 두 문자열 합침
-- SELECT CONCAT(문자열1, 문자열2)
SELECT CONCAT(EMPNO, ENAME) FROM emp;
SELECT CONCAT(EMPNO, CONCAT(' : ',ENAME)) FROM emp;

-- TRIM 함수 LTRIM, RTRIM 함수
-- 공백제거
SELECT TRIM('     ORACLE     '),
    LTRIM('     ORACLE     '),
    RTRIM('     ORACLE     ');

-- EXCERCISE : ENAME 이 5글자 이상이고, 6글자 미만인 사원정보를 출력
-- MASKING_ENAME 이라는 컬럼을 만듭니다. 사원이름 첫글자만 남기고 나머지 *
-- 사원번호, MASKING_EMPNO 앞 두글자만 놔두고 뒤는 **

DESC emp;
SELECT empno,RPAD(SUBSTR(empno,1,2),LENGTH(empno),'*') AS MASKING_EMPNO,
ename,RPAD(SUBSTR(ename,1,1),LENGTH(ENAME),'*') AS MASKING_ENAME
FROM emp WHERE LENGTH(ename) >=5 AND LENGTH(ename) <6 ;

-- 숫자 처리 함수
-- 반올림
-- ROUND(숫자, [반올림위치])
SELECT ROUND(1234.5678);
SELECT ROUND(1234.5678,0);
SELECT ROUND(1234.5678,1);
SELECT ROUND(1234.5678,2);
SELECT ROUND(1234.5678,-1);
SELECT ROUND(1234.5678,-2);

-- 버림 TRUNC(숫자, 버림위치)
SELECT TRUNC(1234.5678);
SELECT TRUNC(1234.5678,0);
SELECT TRUNC(1234.5678,1);
SELECT TRUNC(1234.5678,2);
SELECT TRUNC(1234.5678,-1);
SELECT TRUNC(1234.5678,-2);

-- 정수를 반환하는 함수 CEIL(숫자 올림), FLOOR(숫자 버림)
SELECT CEIL(3.14);
SELECT FLOOR(3.14);
SELECT CEIL(-3.14); -- 음수의 경우 높은수
SELECT FLOOR(-3.14); -- 낮은 수

-- 나머지 구하기 MOD(숫자, 나눌숫자)
SELECT MOD(15,6);
SELECT MOD(15,2); -- 홀수는 나머지 1
SELECT MOD(14,2); -- 짝수는 나머지 0

-- 5가지 함수 ROUND, TRUNC, CEIL, FLOOR, MOD
-- 연습문제
SELECT * FROM emp;
-- 월급에서 평균 근무일수 21.5 일당 DAY_PAY (소수점 세번째 자리에서 버리고)
-- 시급 하루 근무시간 8시간 TIME_PAY (소수점 두번째 자리에서 반올림 하세요)
SELECT empno, ename, sal,
TRUNC(sal/21.5,2) AS DAY_PAY,
ROUND(TRUNC(sal/21.5,3)/8,1) AS TIME_PAY
FROM emp;

-- 날짜 함수 SYSDATE
-- + - 연산자로 계산 가능
SELECT SYSDATE,
    SYSDATE + 1,
    SYSDATE - 1;
-- 달을 계산해주는 함수 ADD_MONTHS(날짜, 더할 개월 수)
SELECT SYSDATE, ADD_MONTHS(SYSDATE,3);
Q. 입사일HIREDATE이 10주년 되는 날을 출력해주세요.
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE,10*12) AS W10YEAR FROM emp;

-- 개월 차를 구하는 함수 MONTHS_BETWEEN
-- MONTHS_BETWEEN(날짜1, 날짜2)
SELECT ENAME, HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE)/12 AS WORKDATE FROM emp;

-- 돌아오는 요일
-- NEXT_DAY(날짜, 요일문자)
SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일');
-- LAST_DAY(날짜 데이터) 31이냐, 30일이냐, 28일이냐
SELECT LAST_DAY(SYSDATE);

-- 날짜 함수에 반올림, 버림하기
-- ROUND(날짜, 날짜포맷)
-- TRUNC(날짜, 날짜포맷)
-- ISO 8604 포맷을 넣으면 된다.
SELECT SYSDATE,
    ROUND(SYSDATE, 'YYYY'), -- 연도로 반올림
    TRUNC(SYSDATE, 'YYYY'),
    ROUND(SYSDATE, 'Q'),     -- 분기별 반올림
    TRUNC(SYSDATE, 'MM'),
    ROUND(SYSDATE, 'DD');

-- SYSDATE, MONTH관련 함수, 요일, 마지막날짜, ROUND, TRUNC 날짜 포맷

-- 형변환
-- 문자 데이터로 변환 TO_CHAR(데이터, 문자형태)
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS');
SELECT TO_CHAR(SYSDATE,'MM'),   -- 04
        TO_CHAR(SYSDATE, 'MON'), -- APR
        TO_CHAR(SYSDATE, 'MONTH'); -- APRIL
SELECT TO_CHAR(SYSDATE,'DD'),   -- 날짜
        TO_CHAR(SYSDATE, 'DY'), -- WED
        TO_CHAR(SYSDATE, 'DAY'); -- WEDNSDAY
SELECT TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = JAPANESE');
SELECT TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH');

-- 숫자데이터 포매팅하기
SELECT SAL,
    TO_CHAR(SAL, '$999,999'),
    TO_CHAR(SAL, '999,999'),
    TO_CHAR(SAL, '999,999.00'),
    TO_CHAR(SAL, '000999,999')
FROM EMP;

-- TO_NUMBER 숫자로 변환하는 함수
-- TO_NUMBER(데이터, 포맷)
SELECT TO_NUMBER('1,300','999,999');

-- TO_DATE 날짜 데이터로 변환
-- TO_DATE(데이터, 포맷)
SELECT TO_DATE('20230712','YYYY_MM_DD');

-- NULL 처리 함수
-- NVL(널 여부를 검사할 데이터, 널일 경우 반환할 데이터)
SELECT ENAME, COMM, NVL(COMM, 0) FROM EMP;

-- NVL2(널 여부를 검사할 데이터, 널이 아닐경우 반환 데이터, 널일 경우 반환 데이터)
SELECT ENAME, COMM, NVL2(COMM, 'O', 'X') AS ISCOMM FROM EMP;

-- DECODE - CASE (SWITCH - CASE - DEFAULT)
-- DECODE(검사 대상이 될 데이터, 조건1,반환값, 조건2,반환값 .... 조건N, 해당 없을 때 반환값)
SELECT ENAME, JOB, SAL,
    DECODE(JOB,
        'MANAGER',SAL*1.1,
        'SALESMAN', SAL*1.05,
        'ANALYST', SAL,
        SAL*1.03) AS UPSAL
FROM EMP;
-- CASE문으로 작성
SELECT ENAME, JOB, SAL,
    CASE JOB
        WHEN 'MANAGER' THEN SAL*1.1
        WHEN 'SALESMAN' THEN SAL*1.05
        WHEN 'ANALYST' THEN SAL
        ELSE SAL*1.03
    END AS UPSAL
FROM EMP;

-- Excercise 1.
-- EMP 테이블
-- HIREDATE 기준으로 3개월이 지난 첫번째 월요일에 정직원이 됩니다.
-- R_JOB 정직원이 되는 날짜 YYYY-MM-DD 형식으로 출력해주세요.
-- COMM 없는 사원은 N/A로 출력해주세요.
SELECT * FROM emp;
DESC emp;
SELECT empno,ename,hiredate,
TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate,3),'월요일'),'YYYY-MM-DD') AS R_JOB,
NVL(TO_CHAR(COMM), 'N/A') AS COMM
FROM emp;

-- Excercise 2.
-- MGR 매니저 : 직속상관 사원번호
-- CHG_MGR (DECODE OR CASE)
--      매니저 없는 경우 : 0000
--      앞자리 75 : 5555
--      앞자리 76 : 6666
--      앞자리 77 : 7777
--      앞자리 78 : 8888
--      기본값은 그대로 출력

SELECT empno,ename,MGR,
DECODE(SUBSTR(MGR,1,2),
'75','5555',
'76','6666',
'77','7777',
'78','8888',
'','0000',
MGR) AS CHG_MGR
FROM emp;

SELECT EMPNO, ENAME, MGR,
    CASE
        WHEN MGR IS NULL THEN '0000'
        WHEN SUBSTR(MGR, 1, 2)='75' THEN '5555'
        WHEN SUBSTR(MGR, 1, 2)='76' THEN '6666'
        WHEN SUBSTR(MGR, 1, 2)='77' THEN '7777'
        WHEN SUBSTR(MGR, 1, 2)='78' THEN '8888'
        ELSE TO_CHAR(MGR)
    END AS CHG_MGR
FROM EMP;

-- MULTI-ROW FUNCTION
-- 14개의 행이 하나의 행 결과로 반환
SELECT SUM(SAL) FROM EMP;

-- 일반 컬럼과 함께 사용할 수 없다.
SELECT ENAME, SUM(SAL) FROM EMP;

-- 다중행 함수 5총사 SUM, COUNT, MAX, MIN, AVG
-- SUM([선택, DISTINCT OR ALL] 합계를 구할 데이터(컬럼))
SELECT SAL FROM EMP;
SELECT SUM(DISTINCT SAL), SUM(ALL SAL), SUM(SAL) FROM EMP;

-- COUNT([선택, DISTINCT OR ALL] 합계를 구할 데이터(컬럼))
SELECT COUNT(*)
FROM EMP;

DESC EMP;
-- 부서번호 30인 사원의 인원수 파악
SELECT COUNT(*)
FROM EMP
WHERE DEPTNO = 30;

SELECT COUNT(DISTINCT SAL), COUNT(SAL) FROM EMP;

-- 최대값, 최소값
SELECT MAX(SAL) FROM EMP;
SELECT MIN(SAL) FROM EMP;

-- Q. 10번 부서에서 급여 가장 많이 받는 경우와 적게 받는 경우;
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 10;
SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10;

-- 최대 최소값이 날짜에 적용 가능
-- Q. 20번 부서에서 가장 입사일이 오래된 HIREDATE, 가장 최근.
SELECT MAX(HIREDATE) FROM EMP WHERE DEPTNO = 20;  -- 가장 최근
SELECT MIN(HIREDATE) FROM EMP WHERE DEPTNO = 20;  -- 오래된 쪽

-- 평균 : AVG(데이터)
-- Q. 부서번호 30인 사원들의 평균 급여를 출력해주세요.
SELECT ROUND(AVG(SAL), 2) FROM EMP WHERE DEPTNO = 30;

-- 다중행 함수와 함께 원하는 열로 묶어 사용하는
-- GROUP BY 절
SELECT ROUND(AVG(SAL), 2), '10' AS DEPTNO FROM EMP WHERE DEPTNO = 10
UNION
SELECT ROUND(AVG(SAL), 2), '20' AS DEPTNO FROM EMP WHERE DEPTNO = 20
UNION
SELECT ROUND(AVG(SAL), 2), '30' AS DEPTNO FROM EMP WHERE DEPTNO = 30;

-- 문법
--SELECT 조회할 열
--FROM 조회할 테이블
--WHERE 조건식
--GROUP BY 그룹화할 열
--ORDER BY 정렬할 열

SELECT ROUND(AVG(SAL), 2) AS 평균급여, DEPTNO
FROM EMP
GROUP BY DEPTNO;

-- 다중 그룹 및 순서 정렬 가능
-- 평균 급여를 부서별번호와 직책별(JOB)로 정렬하기
SELECT DEPTNO, JOB, ROUND(AVG(SAL), 2) AS 평균급여
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

-- GROUP BY절을 사용할 때 주의사항
-- GROUP BY절에 없는 컬럼을 SELECT절에 포함시키면 ERROR가 발생한다.
--SELECT ENAME, DEPTNO, JOB, ROUND(AVG(SAL), 2) AS 평균급여
--FROM EMP
--GROUP BY DEPTNO, JOB;

-- HAVING 절 : GROUP BY 절에 조건을 줄 때 사용
-- 문법
--SELECT 조회할 열
--FROM 조회할 테이블
--WHERE (조회할 행을 선별하는)조건식
--GROUP BY 그룹화할 열
--HAVING (출력 그룹을 선별하는)조건식
--ORDER BY 정렬할 열
SELECT DEPTNO, JOB, ROUND(AVG(SAL), 2) AS 평균급여
FROM EMP
GROUP BY DEPTNO, JOB
HAVING ROUND(AVG(SAL), 2) >= 2000
ORDER BY DEPTNO, JOB;

-- WHERE 절 HAVING 절 모두 사용하는 경우
SELECT DEPTNO, JOB, ROUND(AVG(SAL), 2) AS 평균급여
FROM EMP
WHERE SAL <= 3000
GROUP BY DEPTNO, JOB
HAVING ROUND(AVG(SAL), 2) >= 2000
ORDER BY DEPTNO, JOB;

-- EX1 : EMP 테이블에서 DEPTNO,평균 급여 AVG_SAL, 최고급여MAX_SAL, 최저급여MIN_SAL,
--사원수 CNT를 출력합니다. 단, 평균급여는 소수점 제외하고 버립니다.
-- 각 부서별로 출력합니다.
SELECT DEPTNO, TRUNC(AVG(SAL)) AS AVG_SAL, MAX(SAL) AS MAX_SAL, MIN(SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO ORDER BY DEPTNO DESC;

-- EX2 :  JOB 같은 직책에 종사하는 사원이 3명 이상인 직책과 인원수를 출력하세요.
SELECT
JOB, COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >=3;