1) 오라클 함수
함수(내장함수)
- 단일행 함수
숫자형함수: MOD() 나머지값을 구하는 함수
NULL함수: NVL()
문자형함수: LENGTH()
날짜형함수: MONTHS_BETWEEN(), SYSDATE
형변환함수: TO_CHAR() 숫자를 문자로, 날짜를 문자로 바꾸는 함수/
SELECT employee_id, MOD(employee_id, 2)
FROM employees;
SELECT employee_id 사원 , salary 급여, commission_pct, salary+salary*NVL(commission_pct, 0.0) 실급여
FROM employees;
//산술연산 참여시 NULL값 주의 (예) 1000+NULL = NULL
SELECT employee_id, first_name, LENGTH(first_name)
FROM employees;
SELECT employee_id 사번, hire_date 입사일 , SYSDATE, MONTHS_BETWEEN(SYSDATE, hire_date) 근무개월수
FROM employees;
SELECT employee_id 사번 , salary 급여, commission_pct, TO_CHAR( salary+salary*NVL(commission_pct, 0.0), 'L9,999,999.0' )실급여
FROM employees;
1) 숫자형함수
--반올림된 값 반환: ROUND()
SELECT ROUND(45.923), --46
ROUND(45.923,0), --46 : 소수점이하0자리까지 표현
ROUND(45.923, 1), --45.9 : 소수점이하 1자리까지 표현
ROUND(45.923, 2), --45.92
ROUND(45.923, -1) -- 50 : 소수점이하 -1자리(10의자리)까지 표현
FROM dual;
--버림된 값 반환: TRUNC()
SELECT TRUNC(45.923), --45
TRUNC(45.923,0), --45 : 소수점이하0자리까지 표현
TRUNC(45.923, 1), --45.9 : 소수점이하 1자리까지 표현
TRUNC(45.923, 2), --45.92
TRUNC(45.923, -1) -- 40 : 소수점이하 -1자리(10의자리)까지 표현
FROM dual;
2) 문자형함수
--대문자로 변환: UPPER()
--소문자로 변환: LOWER()
--첫글자만 대문자로 변환: INITCAP()
SELECT UPPER('heLLo'), LOWER('heLLo'), INITCAP('heLLo')
FROM dual;
--특정문자의 위치 반환: INSTR()
SELECT INSTR('hellojava', 'a') --7
,INSTR('hellojava', 'a' , 8) --9
,INSTR('hellojava', 'b' , 8) --0 값이 없음
,INSTR('hellojava', 'a' , -1) --끝에서부터 찾으라는 소리 9
FROM dual;
--부분문자열 반환: SUBSTR()
SELECT SUBSTR('hellojava', 2, 3) --ell
,SELECT SUBSTR('hellojava', 2) -ellojava
FROM dual;
--문자열 제거: TRIM()
SELECT TRIM( LEADING 'a' FROM 'aaABCaDEFaHa') --가장 왼쪽에 있는 문자열 a 제거 (ABCaDEFaHa)
,TRIM( TRAILING 'a' FROM 'aaABCaDEFaHa') -- 가장 오른쪽에 있는 문자열 a 제거(aaABCaDEFaH)
,TRIM( BOTH 'a' FROM 'aaABCaDEFaHa') --양쪽에 있는 문자열 a제거 (ABCaDEFaH)
FROM dual;
-- 문자열 늘리기: LPAD(), RPAD()
SELECT LPAD ('abc', 5, '*') --**abc
, RPAD ('abc', 5, '*') --abc**
, RPAD( LPAD ('abc', 5, '*'), 7, '*') --**abc**
FROM dual;
-- 문자열 변환함수: REPLACE(), TRANSLATE()
SELECT REPLACE ('BCCARD', 'BC', 'KB') --KBCARD
,TRANSLATE('BCCARD', 'BC', 'KB') --KBBARD
,REPLACE('JACK AND JUE', 'J', 'BL'), --BLACK AND BLUD
TRANSLATE(JACK AND JUE', 'J', 'BL') -- BACK AND BUE
FROM dual;
-- 사원의 사번, 이름을 출력하시오
--단, 이름에 'E' 또는 'e' 를 포함한 사원일 것
SELECT employee_id 사번, first_name 이름
FROM employees
WHERE INSTR(UPPER(first_name), 'E') > 0
-- INSTR(LOWER(first_name), 'E') > 0
-- 이름에 'an'를 포함한 사원의 사번, 이름을 출력하시오
SELECT employee_id 사번, first_name 이름
FROM employees
WHERE INSTR(first_name, 'an') > 0
-- 실급여(급여+급여*수당률)가 10000보다 많은 사원들을 출력하시오
--단, 실급여는 일의자리에서 반올림하고 실급여를 많이 받는 사원부터 출력한다
SELECT employee_id 사번, first_name 이름, salary 급여, ROUND(salary+salary*commission_pct, -1) 실급여
FROM employees
WHERE ROUND(salary+salary*NVL(commission_pct, 0), -1) > 10000
ORDER BY 실급여 DESC;
3) 날짜형함수
날짜용 연산자: 날짜+숫자, 날짜 - 숫자 - > 날짜값반환 / 날짜 - 날짜 -> 일수를반환
SELECT SYSDATE, SYSDATE+1, SYSDATE-1, SYSDATE-15
FROM dual;
SELECT SYSDATE 오늘, SYSDATE - TO_DATE('23/05/15') "KOSTA"
FROM dual;
--현재날짜시간값반환 : SYSDATE
--개월수를반환 : MONTHS_BETWEEN()
--개월을 더한다 : ADD_MONTHS()
--요일에 해당 날짜반환 : NEXT_DAY()
SELECT SYSDATE 오늘,
MONTHS_BETWEEN(SYSDATE, '23/05/15') "KOSTA *개월",
ADD_MONTHS(SYSDATE, 5) 5개월후,
NEXT_DAY(SYSDATE, '월')"다음주월요일"
FROM dual;
4) 형변환
--문자형 -> 숫자형 : TO_NUMBER ()
--문자형 -> 날짜형 : TO_DATE ()
--숫자형 -> 문자형 : TO_CHAR ()
--날짜형 -> 문자형 : TO_CHAR ()
--자동형변환
SELECT '1' || 2 --12 (문자) (숫자형2가 문자형으로 자동형변환됨)
, '1' + 2 --3 (문자형 1이 숫자형으로 자동형변환됨)
FROM dual;
SELECT employee_id, hire_date
FROM employees
WHERE hire_date >= '08/01/01'; --문자형08/01/01이 날짜형으로 자동형변환됨 (비교대상으로 쓰이는 hire_date 컬럼이 날짜자료형이기 때문에)
--날짜형 > 문자형
SELECT SYSDATE 오늘, TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS') 오
FROM dual;
--숫자형 > 문자형
SELECT 12345.678, TO_CHAR(12345.678, '9,999,999.000'),
TO_CHAR(12345.678, '9,999,999.0'),
TO_CHAR(12345.678, '0000000.0')
FROM dual;
--문자형 > 숫자형
SELECT '1,234.5' 숫자, TO_NUMBER('1,234.5','9,999.0')+10 "10더하기"
FROM dual;
--문자형 > 날짜형
SELECT '2023-06-14', SYSDATE - TO_DATE('2023-06-14 오후 02:30', 'YYYY-MM-DD am HH:MI')
FROM dual;
--날짜형 주의점: 시분초정보 포함
SELECT
FROM employees
WHERE hire_date > '08/07/01' ; --2008년 7월 2일부터? (x) , 2008년7월 1일 0시 0분 0초보다 큰 (o)
--2008년 7월 2일 이후(부터) 입사한 입사자를 출력하시오
SELECT employee_id, hire_date
FROM employees
WHERE TO_DATE(hire_date, 'YY/MM/DD') > TO_DATE('08/01/01');
5) NULL 관련함수
--NULL인 경우 값변환 : NVL()
--NULL이 아닌경우 NULL인 경우 값변환: NVL2()
--인자1값과 인자2값이 같으면 NULL 반환, 다르면 인자1값을 반환 : NULLIF()
--사원들중 관리자가 없는 사원은 출력하시오
SELECT employee_id, manager_id
FROM employees
WHERE manager_id IS NULL;
--관리자가 없는 사원은 '관리자없음'을, 관리자 있는 사원은 관리자 번호를 출력하시오.
SELECT employee_id, manager_id, NVL(TO_CHAR(manager_id), '관리자없음')
FROM employees;
SELECT employee_id, manager_id, NVL2(manager_id, ''||manager_id, '관리자없음')
FROM employees;
--관리자없는 사원은 '관리자없음', 관리자 있는 사원은 '관리자있음'으로 출력하시오
SELECT employee_id, manager_id, NVL2(manager_id, '관리자있음' , '관리자없음')
FROM employees;
SELECT employee_id 사번, salary 급여, NULLIF(salary, 24000)
FROM employees;
----------------------------------------------------------------------------------------------------------
6) 조건 함수 : DECODE
--관리자없는 사원은 '관리자없음', 관리자 있는 사원은 '관리자있음'으로 출력하시오
SELECT employee_id 사번, manager_id 관리자사번, DECODE(manager_id, NULL, '관리자없음' , '관리자있음') "관리자여부"
FROM employees;
-- 부서번호가 80이면 '영업부', 60이면 'IT', 그 외의 부서번호는 '그외의 부서' 라고 출력하시오.
SELECT employee_id 사번, department_id 부서번호, DECODE(department_id, 80, '영업부', 60, 'IT', '그외의 부서')"부서분류"
FROM employees;
7) CASE절
--관리자없는 사원은 '관리자없음', 관리자 있는 사원은 '관리자있음'으로 출력하시오
SELECT employee_id 사번, manager_id 관리자번호,
CASE
WHEN manager_id IS NULL THEN '관리자없음'
ELSE '관리자있음'
END
FROM employees;
-- 부서번호가 80이면 '영업부', 60이면 'IT', 그 외의 부서번호는 '그외의 부서' 라고 출력하시오.
SELECT employee_id 사번, department_id 부서번호,
CASE department_id WHEN 80 THEN '영업부'
WHEN 60 THEN 'IT부'
ELSE '그외의 부서'
END "부서"
FROM employees;
--사원의 사번, 급여, 급여등급을 출력하시오.
-- 등급은 급여가 15000 이상 A등급, 10000이상 B등급, 5000이상 C등급 5000미만 D등급
--등급이 높은 순으로, 같은 급여일 경우 사번이 빠른 순서대 출력하시오
SELECT employee_id 사번, salary 급여,
CASE WHEN salary>=15000 THEN 'A등급'
WHEN salary>=10000 THEN 'B등급'
WHEN salary>=5000 THEN 'C등급'
ELSE 'D등급'
END "급여등급"
FROM employees
ORDER BY 급여 DESC, 사번;
------------------------------------------------------------------------------------------------------
--여러행 함수 : SUM(), COUNT(), AVG(), MAX(), MIN()
SELECT SUM(salary) 총급여
, COUNT(department_id) "부서번호有사원수"
, COUNT(*) 모든사원수
, ROUND(AVG(salary), 0) 평균급여
, MAX(salary) 최대급여
, MIN(salary) 최소급여
FROM employees;
-- 부서별 부서번호, 총급여, 사원수, 평균급여, 최대급여, 최소급여를 출력하시오
--사원수가 적은 부서부터 출력하세요
SELECT department_id, SUM(salary), COUNT(*), AVG(salary), MAX(salary), MIN(salary)
FROM employees
GROUP BY department_id
ORDER BY COUNT(*) ;
--입사일자별 입사일자, 사원수를 출력하시오
--입사일자가 오래된 일자부터 출력하시오
SELECT hire_date, COUNT(*)
FROM employees
GROUP BY hire_date
ORDER BY hire_date;
-- 입사년도별 년도, 입사자수를 출력하시오
-- 입사일자가 오래된 년도부터 출력하시오.
SELECT TO_CHAR(hire_date, 'YYYY') , COUNT(*)
FROM employees
GROUP BY TO_CHAR(hire_date, 'YYYY')
ORDER BY 1;
SELECT SUBSTR(TO_CHAR(hire_date), 1, 2) , COUNT(*)
FROM employees
GROUP BY SUBSTR(TO_CHAR(hire_date), 1, 2)
ORDER BY 1;
[예습할 것 ] havinh join
[금] sunQuery
[주말] DML (INSERT UPDATE DELETE) SEQUENE객체 VIEW객체 > 모델링
DDL
'JAVA 수업 > SQL 수업 기록' 카테고리의 다른 글
| 0619 수업 내용 (0) | 2023.06.19 |
|---|---|
| 0616 수업 내용 (2) | 2023.06.16 |
| 0615 공부 내용 (2) | 2023.06.15 |
| 0613 수업 내용 (2) | 2023.06.13 |
| 0612 수업내용 (0) | 2023.06.12 |
