함수(FUNCTION)

• 함수는 하나의 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브프로그램이다.
• 함수는 호출하여 사용하고 호출하면서 값을 전달하고 결과를 리턴 받을 수도 있다.
• 함수는 SELECT, WHERE, ORDER BY, GROUP BY, HAVING 절에 기술할 수 있다.
• 단일 행 함수는 각 행마다 반복적으로 적용되어 입력받은 행의 개수만큼 결과 반환하는 함수이다.
• 그룹 함수는 특정 행들의 집합으로 그룹이 형성되어 적용된다. 각 그룹 당 1개의 결과 반환하는 함수이다.

단일 행 함수

1) 문자 처리 함수

 

LENGTH / LENGTHB

• LENGTH('문자값') : 글자 수 반환
• LENGTHB('문자값') : 글자의 바이트 수 반환
• 한글 한 글자 → 3BYTE
• 영문자, 숫자, 특수문자 한 글자 → 1BYTE

SELECT LENGTH('오라클'), -- 3
       LENGTHB('오라클') -- 9
FROM DUAL;

INSTR

• INSTR('문자값', '문자값' [, POSITION, OCCURRENCE])

SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL;  -- 3
SELECT INSTR('AABAACAABBAA', 'B', 1) FROM DUAL; -- 3
SELECT INSTR('AABAACAABBAA', 'B', -1) FROM DUAL; -- 10
SELECT INSTR('AABAACAABBAA', 'B', 1, 2) FROM DUAL; -- 9
SELECT INSTR('AABAACAABBAA', 'B', 1, -1) FROM DUAL; -- 음수 사용 불가
SELECT INSTR('AABAACAABBAA', 'B', -1, 3) FROM DUAL; -- 3

LPAD / RPAD

• LPAD/RPAD('문자값', 최종적으로 반환할 문자의 길이(바이트)[, 덧붙이고자 하는 문자])

SELECT LPAD('Hello', 10, 'A') FROM DUAL; -- AAAAAHello
SELECT RPAD('Hello', 10, 'AB')FROM DUAL; -- HelloABABA

**-- 20만큼의 길이 중 EMAIL 값은 왼쪽으로 정렬하고 #을 오른쪽으로 채운다.**
SELECT RPAD(EMAIL, 20, '#') FROM EMPLOYEE; -- sun_di@kh.or.kr#####

**-- 220429-3******를 출력**
SELECT RPAD('220429-3', 14, '*') FROM DUAL; -- 220429-3******

LTRIM / RTRIM

• LTRIM/RTRIM('문자값'[, 제거하고자 하는 문자값])

SELECT LTRIM('   KH') FROM DUAL; -- [KH]
SELECT LTRIM('000123456', '0') FROM DUAL; -- [123456]
SELECT LTRIM('123123KH', '123') FROM DUAL; -- [KH]
SELECT LTRIM('123123KH123', '123') FROM DUAL; -- [KH123]

SELECT RTRIM('KH   ') FROM DUAL; -- [KH]
SELECT RTRIM('KH   ', ' ') FROM DUAL; -- [KH]
SELECT RTRIM('000123000456000', '0') FROM DUAL; -- [000123000456]

SELECT LTRIM(RTRIM('000123000456000', '0'), '0') FROM DUAL; -- [123000456]

TRIM

• TRIM([[LEADING | TRAILING | BOTH] '제거하고자 하는 문자값' FROM] '문자값')
• 문자값 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지를 반환한다.

-- 기본적으로 양쪽에 있는 공백 문자를 제거한다.
SELECT TRIM('   KH   ') FROM DUAL; -- [KH]
SELECT TRIM(' ' FROM '   KH   ') FROM DUAL; -- [KH]
SELECT TRIM('Z' FROM 'ZZZKHZZZ') FROM DUAL; -- [KH]
SELECT TRIM(BOTH 'Z' FROM 'ZZZKHZZZ') FROM DUAL; -- [KH]
SELECT TRIM(LEADING 'Z' FROM 'ZZZKHZZZ') FROM DUAL;-- [KHZZZ]
SELECT TRIM(TRAILING 'Z' FROM 'ZZZKHZZZ') FROM DUAL; -- [ZZZKH]

SUBSTR

• SUBSTR('문자값', POSITION[, LENGTH])

SELECT SUBSTR('SHOWMETHEMONEY', 7) FROM DUAL; -- [THEMONEY]
SELECT SUBSTR('SHOWMETHEMONEY', 7, 3) FROM DUAL; -- [THE]
SELECT SUBSTR('SHOWMETHEMONEY', -8, 3) FROM DUAL; -- [THE]
SELECT SUBSTR('쇼우 미 더 머니', 2, 5) FROM DUAL; -- [우 미 더]

**-- EMPLOYEE 테이블에서 여자 사원의 직원명, 성별 코드 조회**
SELECT EMP_NAME AS "직원명", 
       SUBSTR(EMP_NO, 8, 1) AS "성별 코드"
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2';

**-- EMPLOYEE 테이블에서 주민등록번호 첫 번째 자리부터 성별까지 추출한 결과값에 오른쪽에 * 문자를 채워서 14 글자 출력**
-- ex) 991212-1******

SELECT SUBSTR('991212-1222222', 1, 8) FROM DUAL; -- [991212-1]

SELECT RPAD(SUBSTR('991212-1222222', 1, 8), 14, '*') FROM DUAL; -- [991212-1******]

SELECT EMP_NAME AS "이름", 
       EMAIL AS "이메일",
       SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') - 1) AS "아이디"
--       SUBSTR(EMAIL, 1, LENGTH(EMAIL) - 9) AS "아이디" 
--       LPAD(EMAIL, INSTR(EMAIL, '@') - 1) AS "아이디"  
FROM EMPLOYEE;

LOWER / UPPER / INITCAP

• LOWER/UPPER/INITCAP('문자값')

SELECT LOWER('WELCOME TO MY WORLD') FROM DUAL; -- 소문자로 변경 [welcome to my world]
SELECT UPPER('welcome to my world') FROM DUAL; -- 대문자로 변경 [WELCOME TO MY WORLD]
SELECT INITCAP('welcome to my world') FROM DUAL; -- 단어 앞 글자마다 대문자로 변경 [Welcome To My World]

CONCAT

• CONCAT('문자값', '문자값')

SELECT CONCAT('가나다라', 'ABCD') FROM DUAL; -- [가나다라ABCD]
SELECT '가나다라'||'ABCD' FROM DUAL; -- 연결 연산자와 동일한 결과를 출력한다. [가나다라ABCD]

SELECT CONCAT('가나다라', 'ABCD', 'EFG') FROM DUAL; **-- ERROR, CONCAT은 두 개의 문자값만 전달받을 수 있다.**
SELECT '가나다라' || 'ABCD' || 'EFG' FROM DUAL; -- [가나다라ABCDEFG]

• 3개 이상의 문자열 또는 컬럼을 연결하는 경우
  • 문자열 3개 이상을 사용할 경우 늘어나는 수만큼 CONCAT을 중복해서 써야 하는 번거로움이 있습니다. 따라서, 3개 이상의 문자열을 연결할 경우 연결 연산자 '||'를 사용하는 것이 간편합니다.
  • SELECT CONCAT('가나',CONCAT('다라','마바')) FROM DUAL; -- [가나다라마바] SELECT '가나' || '다라' || '마바' FROM DUAL; -- [가나다라마바]
• 응용 : 문자를 띄어서(공백, 스페이스) 연결하기
  • 공백은 ' '(작은따옴표 안에서 스페이스) 로 하여, 문자열처럼 연결 연산자로 연결합니다. 마찬가지로 CONCAT의 경우 2번 이상 써야 하는 번거로움이 있어, 3개 이상의 문자 연결 시 이하의 예제에서는 가급적 '||'를 사용합니다.
  • SELECT CONCAT('가나',CONCAT(' ','다라')) -- [가나 다라] SELECT '가나' ||' '|| '다라' FROM DUAL; -- [가나 다라]

REPLACE

• REPLACE('문자값', '변경하려고 하는 문자값', '변경할 문자값')

SELECT REPLACE('서울시 강남구 역삼동', '역삼동', '삼성동') FROM DUAL; -- [서울시 강남구 삼성동]

-- EMPLOYEE 테이블에서 이메일의 kh.or.kr을 gmail.com 변경해서 조회
SELECT REPLACE('sun_di@kh.or.kr', 'kh.or.kr', 'gmail.com') FROM DUAL; -- [sun_di@gmail.com]

2) 숫자 처리 함수

ABS

• ABS(NUMBER)

SELECT ABS(10.9)FROM DUAL; -- [10.9]
SELECT ABS(-10.9)FROM DUAL; -- [10.9]

MOD

• MOD(NUMBER, NUMBER(나눌값))

--SELECT 10 % 3 FROM DUAL;
SELECT MOD(10,3) FROM DUAL; -- [1]
SELECT MOD(-10,3) FROM DUAL; -- [-1]
SELECT MOD(10, -3) FROM DUAL; -- [1]
SELECT MOD(10.9, 3) FROM DUAL; -- [1.9]

ROUND

• ROUND(NUMBER[, POSITION])
• POSITION : 기본적으로 0, 양수(소수점 기준으로 오른쪽)와 음수(소수점 기준으로 왼쪽)로 입력 가능
• 소수점 아래로는 반올림/내림 적용
• 소수점 위로는 무조건 버림

SELECT ROUND(123.456) FROM DUAL; -- [123]
SELECT ROUND(123.456, 0) FROM DUAL; -- [123]
SELECT ROUND(-10.65) FROM DUAL; -- [-11]
SELECT ROUND(-10.65, 0) FROM DUAL; -- [-11]
SELECT ROUND(123.456, 1) FROM DUAL; -- [123.5]
SELECT ROUND(123.456, 2) FROM DUAL; -- [123.46]
SELECT ROUND(123.456, 4) FROM DUAL; -- [123.456]
SELECT ROUND(123.456, -1) FROM DUAL; -- [120]
SELECT ROUND(123.456, -2) FROM DUAL; -- [100]
SELECT ROUND(123.456, -3) FROM DUAL; -- [0]

CEIL

• CEIL(NUMBER)

SELECT CEIL(123.456) FROM DUAL; -- [124]
--SELECT CEIL(123.456, 2) FROM DUAL; -- ERROR

FLOOR

• FLOOR(NUMBER)

SELECT FLOOR(123.456) FROM DUAL; -- 123
SELECT FLOOR(456.789) FROM DUAL; -- 456

TRUNC

• TRUNC(NUMBER[, POSITION])

SELECT TRUNC(123.456) FROM DUAL; -- [123]
SELECT TRUNC(456.789) FROM DUAL; -- [456]
SELECT TRUNC(456.789, 0) FROM DUAL; -- [456]
SELECT TRUNC(456.789, 1) FROM DUAL; -- [456.7]
SELECT TRUNC(456.789, -1) FROM DUAL; -- [450]

3) 날짜 처리 함수

날짜 포맷 변경

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI:SS'; -- 시간 정보 포함
ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD'; -- 시간 정보 제외

SYSDATE

• SELECT SYSDATE FROM DUAL;

MONTNS_BETWEEN(DATE, DATE)

-- EMPLOYEE 테이블에서 직원명, 입사일, 근무개월수 조회
SELECT EMP_NAME AS "직원명", 
       HIRE_DATE AS "입사일",
       FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS "근무개월수"
FROM EMPLOYEE;

ADD_MONTHS

• ADD_MONTHS(DATE, NUMBER)

-- EMPLOYEE 테이블에서 직원명, 입사일, 입사 후 6개월이 된 날짜를 조회
SELECT EMP_NAME AS "직원명",
       HIRE_DATE AS "입사일",
       ADD_MONTHS(HIRE_DATE, 6) AS "입사 + 6개월"
FROM EMPLOYEE;

NEXT_DAY

• NEXT_DATE(DATE, 요일(문자, 숫자))

**-- 현재 날짜에서 제일 가까운 일요일 조회**
SELECT SYSDATE, NEXT_DAY(SYSDATE,'일요일') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE,'일') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE,1) FROM DUAL; -- 1: 일요일, 2: 월요일, ..., 7: 토요일
--SELECT SYSDATE, NEXT_DAY(SYSDATE, 'SUNDAY') FROM DUAL; -- ERROR (현재 언어가 KOREAN이기 때문)
--SELECT SYSDATE, NEXT_DAY(SYSDATE, 'SUN') FROM DUAL; -- ERROR

SELECT SYSDATE, NEXT_DAY(SYSDATE, 'MONDAY')FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'MON')FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 2)FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일')FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '월')FROM DUAL;

ALTER SESSION SET NLS_LANGUAGE = AMERICAN; -- 기본 설정을 ENGLISH로 바꾸기
ALTER SESSION SET NLS_LANGUAGE = KOREAN; -- 기본 설정을 KOREAN로 바꾸기

LAST_DAY

• LAST_DAY(DATE)

SELECT LAST_DAY(SYSDATE) FROM DUAL; -- [2022-05-31 07:54:11]
SELECT LAST_DAY('20210810') FROM DUAL; -- [2021-08-31 12:00:00]

EXTRACT

• EXTRACT(YEAR|MONTH|DAY FROM DATE)

**-- EMPLOYEE 테이블에서 직원명, 입사년도, 입사월, 입사일 조회**
SELECT EMP_NAME AS "직원명",
--       HIRE_DATE,
       EXTRACT(YEAR FROM HIRE_DATE) AS "입사년도",
       EXTRACT(MONTH FROM HIRE_DATE) AS "입사월",
       EXTRACT(DAY FROM HIRE_DATE) AS "입사일"
FROM EMPLOYEE
--ORDER BY EXTRACT(YEAR FROM HIRE_DATE);
--ORDER BY "입사년도" DESC, "입사월";
ORDER BY 2,3,4;

4) 형 변환 함수

 

 

TO_CHAR

• TO_CHAR(날짜|숫자[, 포멧])
• TO_CHAR(날짜|숫자[, 포멧])
• 숫자 → 문자

SELECT TO_CHAR(1234) FROM DUAL;
SELECT TO_CHAR(1234, '999999') FROM DUAL; -- 6칸의 공간을 확보, 오른쪽 정렬, 빈칸은 공백으로 채운다. [   1234]
SELECT TO_CHAR(1234, '000000') FROM DUAL; -- 6칸의 공간을 확보, 오른쪽 정렬, 빈칸은 0으로 채운다. [ 001234]
SELECT TO_CHAR(1234, 'L999999') FROM DUAL; -- L: 현재 설정된 나라의 화폐 단위 출력 [          ￦1234]
SELECT TO_CHAR(1234, 'L999,999') FROM DUAL; -- 자리수 구분 [          ￦1,234]

• 날짜 → 문자

SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE) FROM DUAL; -- [2022-05-02 07:58:45]
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL; -- [오후 07:58:52]
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL; -- 월요일
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL; -- 월
SELECT TO_CHAR(SYSDATE, 'MONTH') FROM DUAL; -- 5월
SELECT TO_CHAR(SYSDATE, 'MON') FROM DUAL; -- 5월
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD(DY)') FROM DUAL; -- [2022-05-02(월)]

-- EMPLOYEE 테이블에서 직원명, 입사일(2022-05-02)
SELECT EMP_NAME AS "직원명",
--       TO_CHAR(HIRE_DATE, 'YYYY"년"MM"월"DD"일"') AS "입사일"
       TO_CHAR(HIRE_DATE, 'RRRR"년"MM"월"DD"일"') AS "입사일"

FROM EMPLOYEE;

• 날짜 포맷 변경

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI:SS'; -- 시간 정보 포함
ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD'; -- 시간 정보 제외

• 연도 포맷 문자

SELECT TO_CHAR(SYSDATE, 'YYYY'), -- [2022]
       TO_CHAR(SYSDATE, 'RRRR'),-- [2022]
       TO_CHAR(SYSDATE, 'YY'), -- [22]
       TO_CHAR(SYSDATE, 'RR'), -- [22]
       TO_CHAR(SYSDATE, 'YEAR') --[TWENTY TWENTY-TWO]
FROM DUAL;

• 월에 대한 포맷

SELECT HIRE_DATE, 
       TO_CHAR(HIRE_DATE, 'MM'), -- [02]
       TO_CHAR(HIRE_DATE, 'MON'), -- [2월]
       TO_CHAR(HIRE_DATE, 'MONTH'), -- [2월]
       TO_CHAR(HIRE_DATE, 'RM') -- 로마기호 [II]
FROM EMPLOYEE;

• 일에 대한 포맷

SELECT HIRE_DATE, 
       TO_CHAR(HIRE_DATE, 'DY'),
       TO_CHAR(HIRE_DATE, 'D'), -- 1주를 기준으로 며칠째인지 확인
       TO_CHAR(HIRE_DATE, 'DD'), -- 1달을 기준으로 며칠째인지 확인
       TO_CHAR(HIRE_DATE, 'DDD') -- 1년을 기준으로 며칠째인지 확인
FROM EMPLOYEE;

• 요일에 대한 포맷

SELECT HIRE_DATE, 
       TO_CHAR(HIRE_DATE, 'DAY'), -- [화요일]
       TO_CHAR(HIRE_DATE, 'DY')-- [화]
FROM EMPLOYEE;

-- EMPLOYEE 테이블에서 직원명, 입사일(2022-05-02(화)) 조회
SELECT EMP_NAME AS "직원명",
       TO_CHAR(HIRE_DATE, 'YYYY-MM-DD(DY)') AS "입사일"
FROM EMPLOYEE;

-- EMPLOYEE 테이블에서 직원명, 입사일(2022년 05월 02일(화요일)) 조회
SELECT EMP_NAME AS "직원명",
       TO_CHAR(HIRE_DATE, 'YYYY"년 "MM"월 "DD"일"(DY)') AS "입사일"
FROM EMPLOYEE;

TO_DATE

• TO_DATE(숫자|문자[, 포멧])

**-- 숫자 -> 날짜**
SELECT TO_DATE(20201212) FROM DUAL;
SELECT TO_DATE(20201212122530) FROM DUAL; --[2020-12-12 12:25:30]

**-- 문자 -> 날짜**
SELECT TO_DATE(20201212) FROM DUAL;
SELECT TO_DATE('20201212 122530') FROM DUAL; --[2020-12-12 12:25:30]
SELECT TO_DATE('20201212 222530', 'YYYY-MM-DD HH24MISS') FROM DUAL; --[2020-12-12 10:25:30]

• YY와 RR 비교
  • YY는 무조건 현재 세기를 반영하고, RR는 50 미만이면 현재 세기를 반영, 50 이상이면 이전 세기를 반영한다.

SELECT TO_DATE('220502', 'YYMMDD') FROM DUAL; -- [2022-05-02]
SELECT TO_DATE('980502', 'YYMMDD') FROM DUAL; -- [2098-05-02]

SELECT TO_DATE('220502', 'RRMMDD') FROM DUAL; -- [2022-05-02]
SELECT TO_DATE('980502', 'RRMMDD') FROM DUAL; -- [1998-05-02]

TO_NUMBER

• TO_NUMBER('문자값'[, 포맷])

SELECT TO_NUMBER('012345678') FROM DUAL; -- [12345678]
SELECT '123' + '456' FROM DUAL; -- [579], 자동으로 숫자 타입으로 형 변환 뒤 연산처리를 한다.
--SELECT '123' + '456A' FROM DUAL; -- ERROR, 연산(+)이 있기 때문에 산술연산으로 무조건 ! A때문에 형변환 X
--SELECT '10,000,000' + '500,000' FROM DUAL; - ERROR, 콤마가 있으니까 형변환 X

SELECT TO_NUMBER('10,000,000', '999,999,999') FROM DUAL; -- [10000000]
SELECT TO_NUMBER('500,000', '9,999,999') FROM DUAL; -- [500000]

SELECT TO_NUMBER('10,000,000', '999,999,999') + TO_NUMBER('500,000','9,999,999') -- [10500000]
FROM DUAL;

5) NULL 처리 함수

NVL

• NVL(값1, 값2)
• 값1이 NULL이 아니면 값1을 반환, 값1이 NULL이면 값2를 반환한다.

NVL2

• NVL2(값1, 값2, 값3)
• 값1이 NULL이 아니면 값2를 반환, 값이 NULL이면 값3을 반환한다.

NULLIF

• NULLIF(값1, 값2)
• 두 개의 값이 동일하면 NULL을 반환, 두 개의 값이 동일하지 않으면 값1을 반환한다.

-- EMPLOYEE 테이블에서 보너스를 0.1로 동결하여 직원명, 보너스율, 동결된 보너스율, 보너스가 포함된 연봉 조회
SELECT EMP_NAME AS "직원명",
       NVL(BONUS,0) AS "보너스율",
       NVL2(BONUS, 0.1, 0) AS "동결된 보너스율",
       (SALARY + (SALARY *  NVL2(BONUS, 0.1, 0))) * 12 AS "연봉"
FROM EMPLOYEE;

SELECT NULLIF('123', '123') FROM DUAL; -- NULL
SELECT NULLIF('123', '456') FROM DUAL;

SELECT NULLIF(123, 123) FROM DUAL; -- NULL
SELECT NULLIF(123, 456) FROM DUAL;

6) 선택 함수

DECODE

• DECODE(값, 조건1, 결과값1, 조건2, 결과값2, ..., 결과값 N)

-- EMPLOYEE 테이블에서 사번, 직원명, 주민번호, 성별(남자, 여자) 조회
SELECT EMP_ID AS "사번",
       EMP_NAME AS "직원명",
       EMP_NO AS "주민번호" ,
--       SUBSTR(EMP_NO, 8, 1)
       DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남자', 2, '여자', '잘못된 주민번호입니다.') AS "성별"
FROM EMPLOYEE;

-- EMPLOYEE 테이블에서 직원명, 직급 코드, 기존 급여, 인상된 급여를 조회
-- 직급 코드가 J7인 직원은 급여를 10% 인상
-- 직급 코드가 J6인 직원은 급여를 15% 인상
-- 직급 코드가 J5인 직원은 급여를 20% 인상
-- 그 외의 직급의 직원은 급여를 5% 인상

SELECT EMP_NAME AS "직원명",
       JOB_CODE AS "직급코드",
       SALARY AS "기존 급여",
       DECODE(JOB_CODE, 'J7', SALARY * 1.1, 'J6', SALARY * 1.15, 'J5', SALARY * 1.2, SALARY * 1.05) AS "인상된 급여"
FROM EMPLOYEE
ORDER BY JOB_CODE DESC;

CASE

CASE WHEN 조건식1 THEN 결과값1
     WHEN 조건식2 THEN 결과값2
             ...
     ELSE 결과값
     END

**-- EMPLOYEE 테이블에서 사번, 직원명, 주민번호, 성별(남자, 여자) 조회**
SELECT EMP_ID,
       EMP_NAME,
       EMP_NO,
       CASE WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남자'
            WHEN SUBSTR(EMP_NO, 8, 1) = '2' THEN '여자'        
            ELSE '잘못된 주민번호 입니다.'
       END AS "성별"
FROM EMPLOYEE;

**-- EMPLOYEE 테이블에서 직원명, 급여, 급여 등급(1~4 등급) 조회**
-- SALARY 값이 500만원 초과일 경우 1등급
-- SAKARY 값이 500만원 이하 350만원 초과일 경우 2등급
-- SAKARY 값이 350만원 이하 200만원 초과일 경우 3등급
-- 그 외의 경우 4등급
SELECT EMP_NAME AS "직원명",
       TO_CHAR(SALARY, 'FM9,999,999') AS "급여",
       CASE WHEN SALARY > 5000000 THEN '1등급'
            WHEN SALARY > 3500000 THEN '2등급'
            WHEN SALARY > 2000000 THEN '3등급'
            ELSE '4등급'
       END AS "급여 등급"
FROM EMPLOYEE
ORDER BY SALARY DESC;

**-- EMPLOYEE 테이블에서 직원명, 급여, 급여 등급(1~4 등급) 조회**
-- SALARY 값이 500만원 초과일 경우 1등급
-- SALARY 값이 500만원 이하 350만원 초과일 경우 2등급
-- SALARY 값이 350만원 이하 200만원 초과일 경우 3등급
-- 그 외의 경우 4등급
SELECT EMP_NAME AS "직원명",
       TO_CHAR(SALARY, 'FM9,999,999') AS "급여",
       CASE WHEN SALARY > 5000000 THEN '1등급'
            WHEN SALARY > 3500000 THEN '2등급'
            WHEN SALARY > 2000000 THEN '3등급'
            ELSE '4등급'
       END AS "급여 등급"
FROM EMPLOYEE
ORDER BY SALARY DESC;

그룹 함수

• 그룹 함수는 하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등을 하나의 컬럼으로 반환하는 함수이다.
• 모든 그룹 함수는 NULL 값을 자동으로 제외하고 값이 있는 것들만 계산을 한다.

1) SUM 함수

• SUM(NUMBER 타입의 컬럼)
• 제시된 컬럼 값들의 합계를 반환한다.

-- EMPLOYEE 테이블에서 여자 사원의 총 급여의 합계를 조회
SELECT TO_CHAR(SUM(SALARY), 'FML999,999,999')
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2';

2) AVG 함수

• AVG(NUMBER 타입의 컬럼)
• 제시된 컬럼 값들의 평균값을 반환한다.
• 모든 그룹 함수는 NULL 값을 자동으로 제외하기 때문에 AVG 함수를 사용할 때는 NVL 함수와 함께 사용하는 것을 권장한다.

-- EMPLOYEE 테이블에서 전 사원의 급여를 평균을 조회
SELECT TO_CHAR(FLOOR(AVG(NVL(SALARY, 0))), 'FML99,999,999')
FROM EMPLOYEE;

3) MAX / MIN 함수

• MIN/MAX(모든 타입의 컬럼)
• MIN : 제시된 컬럼 값들 중에 가장 작은 값을 반환한다.
• MAX : 제시된 컬럼 값들 중에 가장 큰 값을 반환한다.

SELECT MIN(EMP_NAME), MIN(EMAIL), MIN(SALARY), MIN(HIRE_DATE)
FROM EMPLOYEE;

SELECT MAX(EMP_NAME), MAX(EMAIL), MAX(SALARY), MAX(HIRE_DATE)
FROM EMPLOYEE;

4) COUNT 함수

• COUNT(*|[DISTINCT] 컬럼명)
• COUNT(*) : 조회 결과에서 모든 행의 개수를 반환한다.
• COUNT(컬럼명) : 제시된 컬럼 값이 NULL이 아닌 행의 개수를 반환한다.
• COUNT(DISTINCT 컬럼명) : 제시된 컬럼 값의 중복을 제거한 행의 개수를 반환한다.

-- EMPLOYEE 테이블에서 남자 사원의 수를 조회
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '1';

-- EMPLOYEE 테이블에서 보너스를 받는 직원의 수를 조회
SELECT COUNT(BONUS)
FROM EMPLOYEE;

SELECT COUNT(*)
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;

-- EMPLOYEE 테이블에서 현재 사원들이 속해있는 부서의 수를 조회
SELECT COUNT(DISTINCT DEPT_CODE) 
FROM EMPLOYEE;

 

 

 

* 위 내용은 KH 정보교육원 수업을 정리한 내용을 바탕으로 작성된 글입니다. *