내장함수
- 단일행 함수
숫자형 함수 : MOD()
NULL 함수 : NVL()
문자형 함수: LENGTH()
날짜형 함수 : MONTHS_BETWEEN(), SYSDATE
MONTHS_BETWEEN() --> 특정날짜 개월수
SYSDATE --> 오늘날짜
- 날짜용 연산자 : 날짜 + 숫자, 날짜 - 숫자 날짜값 변환
--SYSDATE + 1 (내일날짜) , SYSDATE -1 (어제날짜)
형변환함수 : TO_CHAR()
숫자를 날짜, 문자를 날짜로 바꿀때 사용
숫자형 함수 :
- 반올림 된 값 반환 : ROUND()
- 버림 된 값 반환 : TRUNC()
문자형 함수 :
- 대문자로 변환 : UPPER()
- 소문자로 변환: LOWER()
- 첫글자만 대문자로 변환 : INITCAP()
- 특정문자의 위치반환 : INSTR()
- 부분문자열 반환 : SUBSTR()
- 문자열 제거 : TRIM()
- LEADING - 왼쪽 문자 제거
- TRAILING - 오른쪽 문자제거
- BOTH - 왼,오른쪽 문자제거
-문자열 늘이기
- LPAD('abc', 5, '*') --> **abc 왼쪽에 *써서 만들어라 5글자로 만들어라
- RPAD('abc', 5, '*') --> abc** 오른쪽에 *써서 만들어라 5글자로 만들어라
-문자 변환
-REPLACE('JACK AND JUE', 'J' , 'BL') --> BLACK AND BLUE 로 바뀐다.
-TRANSLATE ('JACK AND JUE' , 'J', 'BL') --> J가 B로 바뀐다! --> BACK AND BUE
현재날짜시간반환 : SYSDATE
개월수 반환 : MONTHS_BETWEEN()
개월수 더한다 : ADD_MONTHS()
요일에 해당 날짜반환 : NEXT_DAY()
NULL 관련 함수 :
- NULL인 경우 값변환 : NVL()
- NULL이 아닌경우, NULL인경우 값변환 : NVL2()
- 인자1값과 인자2값이 같으면 NULL반환
- 다르면 인자1값을 반환 : NULLIF()
MOD(employee_id, 2) --> employee_id를 2로 나눈 나머지값
SELECT employee_id, MOD(employee_id, 2)
FROM employees;
SELECT employee_id, salary, commission_pct, salary * salary*NVL(commission_pct, 0.0) "실급여"
FROM employees;
commission_pct ㅡㅡ> 0일경우 값이 null값으로 나온다.
NVL(commission_pct, 0.0) : null인경우 0.0으로 리턴하고, commission_pct 그 값 그대로 사용하라는 뜻
LENGTH(first_name) ---> 문자열 함수 first name의 길이를 알려준다.
SELECT employee_id, first_name, LENGTH(first_name)
FROM employees;
입사날짜, 근무 개월수 알아낼때 (입사날짜 - 오는날짜)
SELECT employee_id, hire_date, SYSDATE, MONTHS_BETWEEN(SYSDATE , hire_date) 근무개월수
FROM employees;
급여 3자리 마다 ,콤마를 써준다
SELECT employee_id, salary, commission_pct,
TO_CHAR(salary + salary*NVL(commission_pct, 0.0), 'L9,999,999.0') "실급여"
FROM employees;
숫자형 함수
반올림 된 값 반환 : ROUND( xxxx.xx , 소수점 x번째까지 표현해라 )
SELECT ROUND(45.923) --> 46
,ROUND(45.923,0) --> 46 --> 소수점이하 0자리 (1의 자리)까지 표현
,ROUND(45.923,1) --> 45.9 --> 소수점이하 1자리까지 표현
,ROUND(45.923,2) --> 45.92 --> 소수점이하 2자리까지 표현
,ROUND(45.923,-1) --> 50 --> 소수점이하 -1자리 (10의 자리)까지 표현
FROM dual;
버림 된 값 반환 : TRUNC()
SELECT ROUND(45.923) --> 45
,TRUNC(45.923,0) --> 45 --> 소수점이하 0자리 (1의 자리)까지 표현
,TRUNC(45.923,1) --> 45.9 --> 소수점이하 1자리까지 표현
,TRUNC(45.923,2) --> 45.92 --> 소수점이하 2자리까지 표현
,TRUNC(45.923,-1) --> 40 --> 소수점이하 -1자리 (10의 자리)까지 표현
FROM dual;
문자형 함수
대문자로 변환 : UPPER()
소문자로 변환 : LOWER()
첫글자만 대문자로 변환 : INITCAP()
SELECT UPPER('heLLo'), LOWER('heLLo'), INITCAP(heLLo')
FROM dual;
특정문자의 위치반환 : INSTR()
SELECT INSTR('hellojava', 'a') ---> 7
, INSTR('hellojava', 'a' , 8) ---> 9 8번 index부터 찾아라
, INSTR('hellojava', 'b') ---> 0 존재하지 않는 문자는 0으로 반환한다.
, INSTR('hellojava', 'a', -1) ---> 9 음수일 경우 뒤에서부터(오른쪽에서부터) 찾는다
FROM dual;
부분문자열 반환 : SUBSTR()
SELECT SUBSTR('hellojava' , 2, 3) ---> ell --> 2번째 문자열부터 3개의 부분문자열을 찾아라
FROM dual;
문자열 제거 : TRIM()
SELECT TRIM( LEADING 'a' FROM 'aABCDaaDEFaaHaa') ---> 왼쪽 문자 'a' 제거
,TRIM( TRAILING 'a' FROM 'aABCDaaDEFaaHaa') ---> 오른쪽 문자 'a' 제거
,TRIM( BOTH 'a' FROM 'aABCDaaDEFaaHaa') ---> 왼,오른쪽 문자 'a'제거
FROM dual;
TRIM( LEADING 'a' FROM 'aaaABCD') ---> 이면 왼쪽 aaa 가 제거된다.
문자열 늘이기 : 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' ) ---> 'BCCARD'에서 BC 라는 문자를 KB로 바꿔라
,TRANSLATE('BCCARD' , 'BC' , 'KB' ) ---> 'BCCARD'에서 B는 K로, C는 B로 바뀌어라
FROM dual; --> KBBARD로 값이 나온다.
SELECT REPLACE('JACK AND JUE', 'J' , 'BL')
FROM dual; --> BLACK AND BLUE 로 바뀐다.
SELECT TRANSLATE ('JACK AND JUE' , 'J', 'BL') --> J가 B로 바뀐다!
FROM dual; --> BACK AND BUE
ㅡㅡ문제 : 이름에 'E' 또는 'e' 를 포함한 사원의 사번, 이름을 출력하시오ㅡㅡㅡㅡㅡㅡ
SELECT employee_id, first_name
FROM employees
WHERE INSTR(first_name, 'E') > 0 OR INSTR(first_name, 'e') > 0;
SELECT employee_id, first_name
FROM employees
WHERE INSTR(UPPER(first_name), 'E') > 0; --> 대문자 E 들어간 사람들만 나옴
-- INSTR(LOWER(first_name), 'e') > 0; --> 소문자 e 들어간 사람들만 나옴
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
LIKE 연산자 : % , _ 사용한다. % - 0개이상, _ ㅡ 1개 ★★쓰는것을 권장안함★★
이름에 'E' 또는 'e' 를 포함한 사원의 사번, 이름을 출력하시오
SELECT employee_id, first_name
FROM employees
WHERE LOWER(first_name) LIKE '%e%'; --> first_name 값이 ABC --> FALSE
ABe -- > TRUE e --> TRUE eAB --> TRUE
ㅡㅡ직무명이 SALES로 시작하는 모든 직무번호와 직무명을 출력하시오.ㅡㅡㅡㅡㅡㅡㅡ
SELECT job_id, job_title
FROM jobs
WHERE job_title LIKE 'SALES%';
ㅡㅡ직무명이 Manager로 시작하는 모든 직무번호와 직무명을 출력하시오.ㅡㅡ
SELECT job_id, job_title
FROM jobs
WHERE job_title LIKE '%Manager';
ㅡㅡ사원이름에 an을 포함한 사원의 사번과 이름을 출력하시오.ㅡㅡ
SELECT employee_id, first_name
FROM employees
WHERE first_name LIKE '%an%';
ㅡㅡ이름이 'J'로 시작하고 n문자를 포함한 사원의 사번과 이름을 출력하시오.ㅡㅡ
SELECT employee_id, first_name
FROM employees
WHERE first_name LIKE 'J%n%';
ㅡㅡ입사월이 11월인 사원의 사번, 입사일자를 출력하시오
SELECT employee_id, hire_date
FROM employees
WHERE hire_date LIKE '__/11/__'; --> __2개 어떤것이 와도 상관없다
ㅡㅡ사원이름에 an을 포함한 사원의 사번과 이름을 출력하시오.
SELECT employee_id, first_name
FROM employees
WHERE INSTR(first_name, 'an') > 0;
ㅡㅡ실급여(급여+급여*수당률)가 10000보다 많은 사원들을 출력하시오.
ㅡㅡ단, 실급여는 일의 자리에서 반올림하고 실급여를 많이 받는 사원부터 출력한다.
SELECT employee_id 사번, salary 급여,commission_pct 수당률,
ROUND(salary + salary * salary*NVL(commission_pct, 0.0), -1) "실급여"
FROM employees
WHERE ROUND(salary + salary * salary*NVL(commission_pct, 0.0), -1) > 10000
ORDER BY 실급여 DESC;
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
날짜형 함수
날짜+숫자, 날짜-숫자, 날짜값반환, 날짜-날짜 일수를 반
SELECT SYSDATE, SYSDATE +1, SYSDATE -1, SYSDATE -15
FROM dual;
현재날짜시간반환 : SYSDATE
개월수 반환 : MONTHS_BETWEEN()
개월수 더한다 : ADD_MONTHS()
요일에 해당 날짜반환 : NEXT_DAY()
SELECT SYSDATE - TO_DATE('23/05/15')
FROM dual; ---> 날짜를 TO_DATE 문자열로 변환시켜 계산한다!
개월수 반환
SELECT SYSDATE, MONTHS_BETWEEN(SYSDATE, '23/05/15')
FROM dual;
개월수 더한다
SELECT ADD_MONTHS(SYSDATE, 5)
FROM dual;
요일에 해당날짜반환
SELECT SYSDATE, NEXT_DAY(SYSDATE, '월')
FROM dual;
형변환함수
문자형-->숫자형 : TO_NUMBER()
문자형-->날짜형 : TO_DATE()
숫자형-->문자형 : TO_CHAR()
날짜형-->문자형 : TO_CHAR()
날짜형-->숫자형 안된다!!!! 날짜형--> 문자형 --> 숫자형
숫자형-->날짜형 안된다!!!! 숫자형--> 문자형 --> 날짜형
자동형변환
SELECT '1' || 2 FROM dual; --> 숫자2가 문자로 자동 형변환됨!! 문자12라는 문자값이 결합되어서나옴
SELECT '1' + 2 FROM dual; --> 문자형 1이 숫자형으로 자동형변환됨!! 3이라는 숫자값이 더해져서 나옴
SELECT employee_id, hire_date
FROM employees
WHERE hire_date >= '08/01/01'; --> 문자형 08/01/01이 날짜형으로 자동형변환됨
날짜형 -> 문자형
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS')
FROM dual;
24시 기준으로 변환하면 시간 뒤에 HH24로 해주면된다.
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS')
FROM dual;
숫자형 -> 문자형
SELECT 12345.678, TO_CHAR(12345.678, '9,999,999.0000')
TO_CHAR(12345.678, '9,999,999.0')
TO_CHAR(12345.678, '00000000.0')
FROM dual;
문자형 -> 숫자형
SELECT '1,234.5', TO_NUMBER('1,234.5', '9,999.0') +10
FROM dual;
문자형 -> 날짜형
SELECT '2023-06-14', SYSDATE - TO_DATE('2023-06-14 오후 02:30', 'YYYY-MM-DD am HH:MI')
FROM dual;
날짜형 주의점 : 시분초정보 포함
SELECT employee_id, hire_date
FROM employees
WHERE hire_date > '08/07/01'; --> 2008년 7월 2일부터 ? (x)
--> 2008년 7월1일 0분0초보다 큰 시간(o)
--2008년 7월 2일 이후 입사자를 출력하시오.
SELECT employee_id, hire_date
FROM employees
WHERE TO_DATE(hire_date, 'YY/MM/DD') > TO_DATE ('08/07/01');
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;
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
인자1값과 인자2값이 같으면 NULL반환, 다르면 인자1값을 반환 : NULLIF()
SELECT employee_id, salary, NULLIF(salary, 24000)
FROM employees;
조건함수 : 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;
CASE절
ㅡㅡ관리자없는 사원은 '관리자없음', 있는 사원은 '관리자있음'을 출력하시오
SELECT employee_id, manager_id,
CASE manager_id
WHEN IS NULL THEN '관리자없음'
(WHEN THEN --> 조건이 또있으면 적어주면 됨!)
ELSE '관리자있음'
END
FROM employees;
ㅡㅡ부서번호가 80이면 ' 영업부', 60이면 'IT', 그외의 부서번호는 '그외의 부서'를 출력하시오
SELECT employee_id, department_id,
CASE WHEN department_id = 80 THEN '영업부'
WHEN department_id = 60 THEN 'IT부'
ELSE '그외의부서'
END
FROM employees;
CASE에 부서번호를 넣으면
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 등급, salary DESC;
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
여러행 함수 : SUM(), COUNT(), AVG(), MAX(), MIN()
SELECT SUM(salary) --총급여
,COUNT(department_id) --부서번호를 갖는 사원행수 (department_id가 null인 행은 제외)
,COUNT(*) --모든사원수
,AVG(salary) --평균급여
,MAX(salary) --최대급여
,MIN(salary) --최소급여
FROM employees;
ㅡㅡ부서별 부서번호, 총급여, 사원수, 평균급여,최대급여,최소급여를 출력하시오
ㅡㅡ사원수가 적은 부서부터 출력한다.
SELECT department_id, sum(salary), count(*), avg(salary), max(salary), min(salary)
FROM employees
WHERE
GROUP BY department_id;
ORDER BY count(*);
ㅡㅡ입사일자별 입사일자, 사원수를 출력하시오
ㅡㅡ입사일자가 오래된 일자부터 출력한다.
SELECT hire_date, COUNT(*)
FROM employees
GROUP BY hire_date
ORDER BY hire_date;
ㅡㅡ입사년도별 년도, 입사자수를 출력하시오ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
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;
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
'수업 > DB' 카테고리의 다른 글
| 230616 (SUBQUERTY, Scalar Subquery,SELECT 문제풀이) (0) | 2023.06.16 |
|---|---|
| 230615 (JOIN, UNION) (2) | 2023.06.15 |
| 230615 DB HAVING 절 (0) | 2023.06.15 |
| 230613 DB (SELECT, oracle developer 사용, 포트번호확인) (1) | 2023.06.13 |
| 230612 oracle DB (설치확인, DBMS, 테이블 구조확인, 자료형종류) (2) | 2023.06.12 |