반응형
Notice
Recent Posts
Recent Comments
Link
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 29 | 30 | 31 |
Tags
- javascript
- Oracle
- 정보처리기사정리
- 이안의평일코딩
- 평일코딩
- 정보처리기사
- 자바스크립트 코딩테스트
- 정보처리기사실기요약
- 스프링
- spring
- 오라클
- 국비코딩
- 정보처리기사요약
- 코딩테스트
- 리액트네이티브
- 자바의정석
- react
- Java의정석
- 리액트
- typescript
- ReactNative
- 국비IT
- 자스코테
- 타입스크립트
- php
- 정보처리기사실기정리
- 정보처리기사실기
- CSS
- 자바스크립트
- VUE
Archives
- Today
- Total
이안의 평일코딩
Oracle 6일차 - SQL 복수행함수2 본문
반응형
2020.08.11(화)
<SQL 함수>
1. 단일행 함수
1) 문자 함수
LENGTH => 문자 갯수
SUBSTR => 문자 분해
RPAD =>문자 첨부
INSTR => 문자의 위치
2) 숫자 함수
ROUND => 반올림
CEIL => 올림
3) 날짜 함수
SYSDATE => 시스템의 시간
MONTHS_BETWEEN => 개월수
4) 변환 함수
TO_CHAR => 문자열 변환
5) 기타 함수
NVL => NULL값을 다른 값으로 변경
2. 집합 함수
COUNT => 갯수 (로그인, 아이디 중복체크)
MAX => 최대값 (자동 증갑건호)
ROLLUP => ROW단위 계산 => 통계
CUBE => ROW, COLUMN단위 전체 통계
REGEXP_LIKE => 검색
A, B, C
SELECT ename FROM emp WHERE ename LIKE '%A%' OR ename LIKE '%B%' OR ename LIKE '%C%';
=> SELECT ename FROM emp WHERE REGEXP_LIKE(ename, 'ABC');
SQL> SELECT deptno, COUNT(*) FROM emp;
SELECT deptno, COUNT(*) FROM emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
DEPTNO COUNT(*)
---------- ----------
30 6
20 5
10 3
SQL> SELECT deptno, COUNT(*) FROM emp GROUP BY deptno ORDER BY deptno ASC;
DEPTNO COUNT(*)
---------- ----------
10 3
20 5
30 6
SQL> SELECT deptno, job, COUNT(*) FROM emp GROUP BY (deptno, job) ORDER BY deptno ASC;
DEPTNO JOB COUNT(*)
---------- ------------------ ----------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 ANALYST 2
20 CLERK 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
-- emp 테이블에서 job => job => mgr => count, max(sal), min(sal)
SQL> SELECT job, mgr, COUNT(*), MAX(sal), MIN(sal) FROM emp GROUP BY (job, mgr);
JOB MGR COUNT(*) MAX(SAL) MIN(SAL)
------------------ ---------- ---------- ---------- ----------
CLERK 7902 1 800 800
PRESIDENT 1 5000 5000
CLERK 7698 1 950 950
CLERK 7788 1 1100 1100
CLERK 7782 1 1300 1300
SALESMAN 7698 4 1600 1250
MANAGER 7839 3 2975 2450
ANALYST 7566 2 3000 3000
-- MAX, MIN
SQL> INSERT INTO emp(empno,ename) VALUES(7788, 'HongGD'); //7788 중복이라 추가안됨
INSERT INTO emp(empno,ename) VALUES(7788, 'HongGD')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C006999) violated
SQL> SELECT MAX(empno)+1 FROM emp; // MAX+1이면 임의로 중복되지 않는 수로 사용 가능
MAX(EMPNO)+1
------------
7935
SQL> INSERT INTO emp(empno,ename) VALUES((SELECT MAX(empno)+1 FROM emp), 'HongGD1');
SQL> INSERT INTO emp(empno,ename) VALUES((SELECT MAX(empno)+1 FROM emp), 'HongGD2');
SQL> INSERT INTO emp(empno,ename) VALUES((SELECT MAX(empno)+1 FROM emp), 'HongGD3');
SQL> INSERT INTO emp(empno,ename) VALUES((SELECT MAX(empno)+1 FROM emp), 'HongGD4');
SQL> INSERT INTO emp(empno,ename) VALUES((SELECT MAX(empno)+1 FROM emp), 'HongGD5');
SQL> SELECT ename, empno FROM emp;
ENAME EMPNO
-------------------- ----------
SMITH 7369
ALLEN 7499
WARD 7521
JONES 7566
MARTIN 7654
BLAKE 7698
CLARK 7782
SCOTT 7788
KING 7839
TURNER 7844
ADAMS 7876
JAMES 7900
FORD 7902
MILLER 7934
HongGD1 7935
HongGD2 7936
HongGD3 7937
HongGD4 7938
HongGD5 7939
19 rows selected.
SQL> ROLLBACK; // 입력한 것 취소, commit하면 rollback 불가능
Rollback complete.
SQL> SELECT ROUND(AVG(sal),2), SUM(sal) FROM emp;
//소수점 2자리까지)
ROUND(AVG(SAL),2) SUM(SAL)
----------------- ----------
2073.21 29025표준편차값, 분산값
SQL> SELECT STDDEV(sal), VARIANCE(sal) FROM emp;
STDDEV(SAL) VARIANCE(SAL)
----------- -------------
1182.50322 1398313.87
SQL> SELECT deptno, job, COUNT(*), ROUND(AVG(sal),2) FROM emp GROUP BY deptno, job;
DEPTNO JOB COUNT(*) ROUND(AVG(SAL),2)
---------- ------------------ ---------- -----------------
20 CLERK 2 950
30 SALESMAN 4 1400
20 MANAGER 1 2975
30 CLERK 1 950
10 PRESIDENT 1 5000
30 MANAGER 1 2850
10 CLERK 1 1300
10 MANAGER 1 2450
20 ANALYST 2 3000
ROLLUP(dept마다 각각 통계)
SQL> SELECT deptno, job, COUNT(*), ROUND(AVG(sal),2) FROM emp GROUP BY ROLLUP(deptno, job);
DEPTNO JOB COUNT(*) ROUND(AVG(SAL),2)
---------- ------------------ ---------- -----------------
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
10 3 2916.67
20 CLERK 2 950
20 ANALYST 2 3000
20 MANAGER 1 2975
20 5 2175
30 CLERK 1 950
30 MANAGER 1 2850
30 SALESMAN 4 1400
30 6 1566.67
14 2073.21CUBE(전체통계)
SQL> SELECT deptno, job, COUNT(*), ROUND(AVG(sal),2) FROM emp GROUP BY CUBE(deptno, job);
DEPTNO JOB COUNT(*) ROUND(AVG(SAL),2)
---------- ------------------ ---------- -----------------
14 2073.21
CLERK 4 1037.5
ANALYST 2 3000
MANAGER 3 2758.33
SALESMAN 4 1400
PRESIDENT 1 5000
10 3 2916.67
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
20 5 2175
20 CLERK 2 950
20 ANALYST 2 3000
20 MANAGER 1 2975
30 6 1566.67
30 CLERK 1 950
30 MANAGER 1 2850
30 SALESMAN 4 1400
집합 함수
= COUNT()
= MAX()
= AVG(), SUM()
= ROLLUP()
= CUBE()
= RANK(), DENSE_RANK()
RANK() => RANK() OVER(ORDER BY 컬럼명 ASC|DESC)
1
2
3
3
5
DENSE_RANK() => DENSE_RANK() OVER(ORDER BY 컬럼명 ASC|DESC)
1
2
3
3
4
SQL> SELECT ename, sal, RANK() OVER(ORDER BY sal DESC) "rank" FROM emp;
ENAME SAL rank
-------------------- ---------- ----------
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 10
ADAMS 1100 12
JAMES 950 13
SMITH 800 14
SQL> SELECT ename, sal, DENSE_RANK() OVER(ORDER BY sal DESC) "rank" FROM emp;
ENAME SAL rank
-------------------- ---------- ----------
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 3
BLAKE 2850 4
CLARK 2450 5
ALLEN 1600 6
TURNER 1500 7
MILLER 1300 8
WARD 1250 9
MARTIN 1250 9
ADAMS 1100 10
JAMES 950 11
SMITH 800 12
연봉 높은 베스트5 출력
SQL> SELECT ename, sal, rownum FROM (SELECT ename, sal FROM emp ORDER BY sal DESC) WHERE rownum <=5;
ENAME SAL ROWNUM
-------------------- ---------- ----------
KING 5000 1
SCOTT 3000 2
FORD 3000 3
JONES 2975 4
BLAKE 2850 5
CEIL 페이지 갯수
SQL> SELECT COUNT(*) FROM genie_music;
COUNT(*)
----------
200
SQL> SELECT CEIL(COUNT(*)/10.0) FROM genie_music;
CEIL(COUNT(*)/10.0)
-------------------
20반응형
'Back-end > Oracle' 카테고리의 다른 글
| Oracle 8일차 - OUTER JOIN, DDL(Data Definition Language) (0) | 2020.08.13 |
|---|---|
| Oracle 7일차 - INNER JOIN (0) | 2020.08.12 |
| Oracle 5일차 - SQL 복수행함수 (0) | 2020.08.10 |
| Oracle 4일차 - SQL 단일행함수2 / 지니뮤직 (0) | 2020.08.07 |
| Oracle 3일차 - SQL 단일행함수 (0) | 2020.08.06 |
'Back-end/Oracle' Related Articles
more
Comments