숫자 함수
○ ABS : 절대값
select abs(10), abs(-10) from dual;
ABS(10) ABS(-10)
---------- ----------
10 10
○ CEIL : 올림값
select ceil(10.733), ceil(11.001) from dual;
CEIL(10.733) CEIL(11.001)
------------ ------------
11 12
○ FLOOR : 내림값
floor(10.733), floor(11.001), floor(-1.5) from dual;
FLOOR(10.733) FLOOR(11.001) FLOOR(-1.5)
------------- ------------- -------------
10 11 -2
○ ROUND : 반올림
select ROUND(10.733), ROUND(11.021,-1), ROUND(98.754,2) from dual; //-1: 1의자리
ROUND(10.733) ROUND(11.021,-1) ROUND(98.754,2)
------------- ---------------- ---------------
11 10 98.75
○ TRUNC : 버림값
select TRUNC(98.754), TRUNC(11.021,-1), TRUNC(127.236,2), TRUNC(-1.5) from dual;
TRUNC(98.754) TRUNC(11.021,-1) TRUNC(127.236,2) TRUNC(-1.5)
------------- ---------------- ---------------- ----------------
98 10 127.23 -1
○ MOD(a,b) : a÷b 의 나머지
select mod(31,2), mod(31,5), mod(31,8) from dual;
MOD(31,2) MOD(31,5) MOD(31,8)
---------- ---------- ----------
1 1 7
○ POWER(a,b) : a의 b제곱
select power(3,2), power(3,3) from dual;
POWER(3,2) POWER(3,3)
---------- ----------
9 27
○ SQRT : 제곱근
select sqrt(2), sqrt(9), sqrt(4) from dual;
SQRT(2) SQRT(9) SQRT(4)
---------- ---------- ----------
1.41421356 3 2
날짜 함수
○ SYSDATE : 현재 날짜
select sysdate-1 어제, sysdate 오늘, sysdate+1 내일 from dual;
어제 오늘 내일
-------- -------- --------
22/08/30 22/08/31 22/09/01
select ename, round(sysdate-hiredate) from employee;
ENAME ROUND(SYSDATE-HIREDATE)
-------------------- -----------------------
SMITH 15233
ALLEN 15168
WARD 15166
JONES 15127
MARTIN 14948
BLAKE 15098
CLARK 15059
SCOTT 12834
KING 14898
TURNER 14968
ADAMS 12834
ENAME ROUND(SYSDATE-HIREDATE)
-------------------- -----------------------
JAMES 14882
FORD 14882
MILLER 14831
○ MONTHS_BETWEEN : 두 날짜 사이 개월수 반환
select ename, sysdate, hiredate, trunc(months_between(sysdate,hiredate)) from employee;
ENAME SYSDATE HIREDATE TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE))
-------------------- -------- -------- ---------------------------------------
SMITH 22/08/31 80/12/17 500
ALLEN 22/08/31 81/02/20 498
WARD 22/08/31 81/02/22 498
JONES 22/08/31 81/04/02 496
MARTIN 22/08/31 81/09/28 491
BLAKE 22/08/31 81/05/01 495
CLARK 22/08/31 81/06/09 494
SCOTT 22/08/31 87/07/13 421
KING 22/08/31 81/11/17 489
TURNER 22/08/31 81/09/08 491
ADAMS 22/08/31 87/07/13 421
JAMES 22/08/31 81/12/03 488
FORD 22/08/31 81/12/03 488
MILLER 22/08/31 82/01/23 487
○ TRUNC(날짜,기준) : 인자로 받은 날짜를 특정 기준 버림
select hiredate, trunc(hiredate,'month') from employee;
HIREDATE TRUNC(HI
-------- --------
80/12/17 80/12/01
81/02/20 81/02/01
81/02/22 81/02/01
81/04/02 81/04/01
81/09/28 81/09/01
81/05/01 81/05/01
81/06/09 81/06/01
87/07/13 87/07/01
81/11/17 81/11/01
81/09/08 81/09/01
87/07/13 87/07/01
81/12/03 81/12/01
81/12/03 81/12/01
82/01/23 82/01/01
○ ADD_MONTHS : 특정 날짜 + 개월수
select ename, hiredate, add_months(hiredate,6) from employee;
ENAME HIREDATE ADD_MONT
-------------------- -------- --------
SMITH 80/12/17 81/06/17
ALLEN 81/02/20 81/08/20
WARD 81/02/22 81/08/22
JONES 81/04/02 81/10/02
MARTIN 81/09/28 82/03/28
BLAKE 81/05/01 81/11/01
CLARK 81/06/09 81/12/09
SCOTT 87/07/13 88/01/13
KING 81/11/17 82/05/17
TURNER 81/09/08 82/03/08
ADAMS 87/07/13 88/01/13
JAMES 81/12/03 82/06/03
FORD 81/12/03 82/06/03
MILLER 82/01/23 82/07/23
○ NEXT_DAY(날짜,'0요일') : 날짜 이후 돌아오는 첫 요일 인자 반환
select sysdate, next_day(sysdate,'토요일') from DUAL;
SYSDATE NEXT_DAY
-------- --------
22/08/31 22/09/03
○ LAST_DAY : 해당 달 마지막 날짜
select ename, hiredate, last_day(hiredate) from employee;
ENAME HIREDATE LAST_DAY
-------------------- -------- --------
SMITH 80/12/17 80/12/31
ALLEN 81/02/20 81/02/28
WARD 81/02/22 81/02/28
JONES 81/04/02 81/04/30
MARTIN 81/09/28 81/09/30
BLAKE 81/05/01 81/05/31
CLARK 81/06/09 81/06/30
SCOTT 87/07/13 87/07/31
KING 81/11/17 81/11/30
TURNER 81/09/08 81/09/30
ADAMS 87/07/13 87/07/31
JAMES 81/12/03 81/12/31
FORD 81/12/03 81/12/31
MILLER 82/01/23 82/01/31
변환 함수
○ TO_CHAR : 날짜/숫자 -> 문자
select ename, hiredate, to_char(hiredate,'yy-mm') from employee;
ENAME HIREDATE TO_CHAR(HI
-------------------- -------- ----------
SMITH 80/12/17 80-12
ALLEN 81/02/20 81-02
WARD 81/02/22 81-02
JONES 81/04/02 81-04
MARTIN 81/09/28 81-09
BLAKE 81/05/01 81-05
CLARK 81/06/09 81-06
SCOTT 87/07/13 87-07
KING 81/11/17 81-11
TURNER 81/09/08 81-09
ADAMS 87/07/13 87-07
JAMES 81/12/03 81-12
FORD 81/12/03 81-12
MILLER 82/01/23 82-01
select ename, hiredate, to_char(hiredate,'YYYY/MM/DD') from employee;
ENAME HIREDATE TO_CHAR(HIREDATE,'YY
-------------------- -------- --------------------
SMITH 80/12/17 1980/12/17
ALLEN 81/02/20 1981/02/20
WARD 81/02/22 1981/02/22
JONES 81/04/02 1981/04/02
MARTIN 81/09/28 1981/09/28
BLAKE 81/05/01 1981/05/01
CLARK 81/06/09 1981/06/09
SCOTT 87/07/13 1987/07/13
KING 81/11/17 1981/11/17
TURNER 81/09/08 1981/09/08
ADAMS 87/07/13 1987/07/13
JAMES 81/12/03 1981/12/03
FORD 81/12/03 1981/12/03
MILLER 82/01/23 1982/01/23
select to_char(sysdate,'YYYY/MM/DD, AM HH:MI:SS') from dual;
//MON:8월 MM:08
//AM: 오전,오후 표시
//HH: 12시간 기준, HH24: 24시간 기준
TO_CHAR(SYSDATE,'YYYY/MM/DD,AMHH:MI:SS')
------------------------------------------------------
2022/08/31, 오후 05:12:32
select to_char(sysdate,'DDD,AM HH:MI:SS') from dual;
//D : 요일을 숫자로 (일 1 월 2..)
//DD : 일자
//DDD : 1년 기준 일수
//WW : 1년 기준 주차
//DAY : 요일
//DL : 년 월 일 요일
TO_CHAR(SYSDATE,'DDD,AMHH:MI:SS')
--------------------------------------
243,오후 05:20:16
※ 숫자 변환 형식
, => 천단위 자리 구분 기호
0 => 숫자 표시, 남은 자리 0으로 채움
9 => 숫자 표시, 남은 자리 빈칸으로 채움
L => 각 지역별 통화 기호
select ename, to_char (salary, 'L999,999') from employee;
ENAME TO_CHAR(SALARY,'L999,999')
-------------------- ------------------------------------
SMITH ￦800
ALLEN ￦1,600
WARD ￦1,250
JONES ￦2,975
MARTIN ￦1,250
BLAKE ￦2,850
CLARK ￦2,450
SCOTT ￦3,000
KING ￦5,000
TURNER ￦1,500
ADAMS ￦1,100
JAMES ￦950
FORD ￦3,000
MILLER ￦1,300
○ TO_DATE(문자,날짜형식) : 문자 -> 날짜
select ename, hiredate from employee where hiredate=to_date(19810220,'yymmdd');
ENAME HIREDATE
-------------------- --------
ALLEN 81/02/20
○ TO_NUMBER(숫자,형식) : 문자 -> 숫자
select to_number('100,000','999,999')-to_number('50,000','999,999') from dual;
TO_NUMBER('100,000','999,999')-TO_NUMBER('50,000','999,999')
------------------------------------------------------------
50000
일반 함수
○ NVL(a,b) : a가 null일 때 b 반환
select ename, salary, salary*12+nvl(commission,0) 연봉 from employee;
○ NVL2(a,b,c) : a가 null 아니면 b, null일 때 c
select ename, salary, nvl2(commission, salary*12+commission, salary*12) 연봉 from employee;
ENAME SALARY 연봉
-------------------- ---------- ----------
SMITH 800 9600
ALLEN 1600 19500
WARD 1250 15500
JONES 2975 35700
MARTIN 1250 16400
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 18000
ADAMS 1100 13200
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
○ NULLIF(a,b) : a,b 두 표현식 동일하면 null 반환, 동일하지 않으면 첫번째 표현식 반환
select nullif('A','A'), nullif('A','B') from dual;
N NU
- --
A
○ COALESCE(a,b,c,..) : a, b, c 중 null이 아닌 첫번째 값 반환
○ DECODE : switch, case문과 유사
DECODE(표현식, 조건1, 결과1, 조건2, 결과2, .., 기본결과)
select code, pname,
decode(substr(code,3,1),'1','안산','2','청주','3','대구','4','대전','5','창원','6','마산')
from producttbl;
CODE PNAME DECODE(SUBST
---------------- ---------------------------------------- ------------
ss6654 냉장고 마산
lg33453 청소기 대구
ss1344 에어컨 안산
ss4883 청소기 대전
○ CASE : if, else문과 유사
select ename, dno
, case when dno=10 then 'ACCOUNTING'
when dno=20 then 'RESEARCH'
WHEN DNO=30 THEN 'SALES'
WHEN DNO=40 THEN 'OPERATIONS'
ELSE 'DEFAULT'
END AS 부서이름
FROM EMPLOYEE;
ENAME DNO 부서이름
-------------------- ---------- --------------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
SCOTT 20 RESEARCH
KING 10 ACCOUNTING
TURNER 30 SALES
ADAMS 20 RESEARCH
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING
그룹 함수
○ SUM : 그룹의 누적 합계
select sum(commission) as 커미션총액 from employee;
커미션총액
----------
2200
○ AVG : 그룹의 평균
○ COUNT : 그룹의 총개수
select count(*) as "사원수" from employee;
사원수
----------
14
select count(commission) as "커미션 받는 사원 수" from employee;
커미션 받는 사원 수
-------------------
4
○ MAX : 그룹의 최대값 / MIN : 그룹의 최소값
select max(hiredate), min(hiredate) from employee;
MAX(HIRE MIN(HIRE
-------- --------
87/07/13 80/12/17
GROUP BY : 카테고리화
select 칼럼명 from 테이블명 where 조건 group by 컬럼명 order by 컬럼명 순서;
○
select dno as 부서번호, avg(salary) as 급여평균 from employee group by dno;
부서번호 급여평균
---------- ----------
30 1566.66667
10 2916.66667
20 2175
○
select avg(salary) as 급여평균 from employee group by dno;
급여평균
----------
1566.66667
2916.66667
2175
○오류
select dno, dname, avg(salary) from employee group by dno; //dname: dno의 하위 카테고리이므로 오류
○
select dno, job, sum(salary) from employee group by dno, job;
DNO JOB SUM(SALARY)
---------- ------------------ -----------
20 MANAGER 2975
20 ANALYST 6000
10 PRESIDENT 5000
10 CLERK 1300
30 SALESMAN 5600
10 MANAGER 2450
20 CLERK 1900
30 MANAGER 2850
30 CLERK 950
○
select dno, job, count(*), sum(salary) from employee group by dno, job order by dno, job;
DNO JOB COUNT(*) SUM(SALARY)
---------- ------------------ ---------- -----------
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
20 ANALYST 2 6000
20 CLERK 2 1900
20 MANAGER 1 2975
30 CLERK 1 950
30 MANAGER 1 2850
30 SALESMAN 4 5600
문제
○ substr 사용해 사원 이름, 입사년도, 입사 달 출력
select ename, substr(hiredate,1,2) 입사년도, substr(hiredate,4,2) 월 from employee;
ENAME 입사년도 월
-------------------- ---------------- ----------------
SMITH 80 12
ALLEN 81 02
WARD 81 02
JONES 81 04
MARTIN 81 09
BLAKE 81 05
CLARK 81 06
SCOTT 87 07
KING 81 11
TURNER 81 09
ADAMS 87 07
JAMES 81 12
FORD 81 12
MILLER 82 01
○ 9월 입사 직원
select * from employee where substr(hiredate,4,2)='09';
ENO ENAME JOB MANAGER HIREDATE SALARY COMMISSION DNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
○ 사원번호 짝수 사원
select * from employee where mod(eno,2)=0;
○ decode 함수 => 직급 따라 급여 인상
analist 200, salesman 180, manager 150, clerk 100 인상
select eno, ename, job, decode(job,'ANALYST',salary+200,'SALESMAN',salary+180,'MANAGER',salary+150,'CLERK',salary+100) as 인상된연봉 from employee;
ENO ENAME JOB 인상된연봉
---------- -------------------- ------------------ ----------
7369 SMITH CLERK 900
7499 ALLEN SALESMAN 1780
7521 WARD SALESMAN 1430
7566 JONES MANAGER 3125
7654 MARTIN SALESMAN 1430
7698 BLAKE MANAGER 3000
7782 CLARK MANAGER 2600
7788 SCOTT ANALYST 3200
7839 KING PRESIDENT
7844 TURNER SALESMAN 1680
7876 ADAMS CLERK 1200
7900 JAMES CLERK 1050
7902 FORD ANALYST 3200
7934 MILLER CLERK 1400
○ 직급 종류 개수
select count(distinct job) from employee;
COUNT(DISTINCTJOB)
------------------
5
'Programming > 국비학원' 카테고리의 다른 글
| 220905 - 오라클 - 데이터 정의어, 데이터 조작어, 트랜잭션 (0) | 2022.09.06 |
|---|---|
| 220902 - 오라클 - 셀프 조인, 서브 쿼리, 다중행 서브쿼리 (0) | 2022.09.03 |
| 220830 - 오라클 - SQL PLUS, 쿼리문 (0) | 2022.08.31 |
| 220829 - 데이터베이스 - SQL PLUS (오라클) - 데이터 질의어 (0) | 2022.08.30 |
| 220826 - 오라클 환경구축 (0) | 2022.08.28 |