학교에서 했던 Oracle 기본 문제
새로운 계정 만들기
1단계) DBA 접속
ID : system
PWD : manager
ID : sys
PWD : sys
2단계 ) 개인계정
1) 다른 사용자 - 사용자생성
기본 테이블스페이스 users
임시 테이블스페이스 temp
2) 부여된 롤에서
connect
resource 선택
3)시스템 권한
create any view
drop any view 선택
4) 적용
(여러문장일경우 다 선택해서 해야함)
5 select * (all) distinct 열이름, 집단함수
sum(열이름),
avg(열이름),
max(열이름),
min(열이름),
count(열이름)
1 from 테이블
2 where 레코드에 제한을 가할 때
열이름 연산자 비교값,열이름....부속질의(subquery)
열이름 in (값,,,,,,)
열이름 between 작은값 and 큰값
열이름 like '%_'
% 0 ~ 무한대 ==> *
_ 1바이트 ==> ?
열이름 is null
열이름 is not null
3 group by ~별로 집계낼때
대상 열이름
4 having ~별로 제한을 가할때
집단함수 연산자 비교값,,,,
6 order by 정렬할때
열이름 asc
열이름 desc
relation
1 attribute 원자성 (atomic value) => 1NF
2 attribute 무순서성(물리적 독립성)
3 tupple 유일성 => 기본키(primary key)
-> 개체무결성
4 tupple 무순서성
테이블 생성 및 데이터
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
constraint dept_pk primary key (deptno));
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);
CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);
INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2),
constraint emp_pk primary key (empno),
constraint emp_fk foreign key (deptno)
references dept(deptno));
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-12월-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-2월-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-2월-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-4월-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-9월-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-5월-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-6월-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-4월-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-11월-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-9월-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-5월-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-12월-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-12월-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-1월-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
COMMIT;
1. 사원 테이블의 모든 것을 보여라.
select *
from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/04/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/05/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
2. 직무를 보이시오.
select job
from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
select distinct job //distinct 중복제거
from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
3. 사번, 사원명(열머리글을 사 원 명), 급여, 급여 * 12(열별칭 tot)
select empno, ename "사 원 명", sal, sal*12 tot
from emp;
EMPNO 사 원 명 SAL TOT
---------- ---------- ---------- ----------
7369 SMITH 800 9600
7499 ALLEN 1600 19200
7521 WARD 1250 15000
7566 JONES 2975 35700
7654 MARTIN 1250 15000
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
4. 사원수, 보너스가 지정된 사원수, 급여평균, 보너스 평균을 보이시오.
select count(*), count(comm), avg(sal), avg(nvl(comm,0))
from emp;
COUNT(*) COUNT(COMM) AVG(SAL) AVG(NVL(COMM,0))
---------- ----------- ---------- ----------------
14 4 2073.21429 157.142857
// count(comm)널값이 아닌 값만 수를 셈
5. select empno, ename // #을 뒤에 붙이면 열별칭 가능
from emp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
select empno emp#, ename
from emp;
EMP# ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
select empno "#emp", ename
from emp;
#emp ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
6. select empno, ename, empno || ename, ename || '님' // || 스트링값을 연결해서 표시해줌
from emp
EMPNO ENAME EMPNO||ENAME ENAME||'님'
---------- ---------- -------------------------------------------------- -----------
7369 SMITH 7369SMITH SMITH님
7499 ALLEN 7499ALLEN ALLEN님
7521 WARD 7521WARD WARD님
7566 JONES 7566JONES JONES님
7654 MARTIN 7654MARTIN MARTIN님
7698 BLAKE 7698BLAKE BLAKE님
7782 CLARK 7782CLARK CLARK님
7788 SCOTT 7788SCOTT SCOTT님
7839 KING 7839KING KING님
7844 TURNER 7844TURNER TURNER님
7876 ADAMS 7876ADAMS ADAMS님
7900 JAMES 7900JAMES JAMES님
7902 FORD 7902FORD FORD님
7934 MILLER 7934MILLER MILLER님
7. 급여가 1000 이상이면서 사원명에 s를 포함하는 사원에 대해 사번, 사원명(님을 붙이시오), 급여를 보이시오.
select empno, ename || '님' "사원명", sal
from emp
where sal >= 1000 and lower(ename) like '%s%'
EMPNO 사원명 SAL
---------- ----------- ----------
7566 JONES님 2975
7788 SCOTT님 3000
7876 ADAMS님 1100
8. 급여가 1000 이하이거나 2000 이상이면서 부서번호가 10 또는 30 인 사원에 대해 사원수, 급여최대값, 급여최소값을 보이시오.
select count (*) 사원수, max(sal) 급여최대값, min(sal) 급여최소값
from emp
where sal not between 1000 and 2000 and deptno in (10,30)
사원수 급여최대값 급여최소값
---------- ---------- ----------
4 5000 950
9. 입사일자가 1981년 12월 3일에 입사한 사원에 대해 사번, 사원명, 입사일자를 보이시오.
select sysdate
from dual;
SYSDATE
--------
15/03/04
select emno, ename, hiredate
from emp
where hiredate = '81/12/03'
EMPNO ENAME HIREDATE
---------- ---------- --------
7900 JAMES 81/12/03
7902 FORD 81/12/03
10. 부서번호가 10 또는 30 이면서 입사일자가 81년 1월 1일 이후에 입사한 사원수, 급여합계를 보이시오.
select count(*), sum(sal)
from emp
where deptno in (10,30) and hiredate >= '81/01/01';
COUNT(*) SUM(SAL)
---------- ----------
9 18150
11. 부서번호가 30에 근무하는 사원들에 대해 사번, 사원명, 입사일자, 시스템 날짜 - 입사일짜를 보이시오.
select empno, ename, hiredate, sysdate - hiredate
from emp
where deptno = 30
EMPNO ENAME HIREDATE SYSDATE-HIREDATE
---------- ---------- -------- ----------------
7499 ALLEN 81/02/20 12437.3865
7521 WARD 81/02/22 12435.3865
7654 MARTIN 81/09/28 12217.3865
7698 BLAKE 81/05/01 12367.3865
7844 TURNER 81/09/08 12237.3865
7900 JAMES 81/12/03 12151.3865
6개의 행이 선택됨
select sysdate +100, sysdate - 100
from dual;
SYSDATE+100 SYSDATE-100
----------- -----------
15/06/19 14/12/01
// 날짜보다 100일 후나 100일 전
12. 사원명에 s를 포함하고 보너스가 지정되지 않은 사원에 대해 사번, 사원명, 직무와 부서번호를 연결해서 보이시오.
select empno, ename, job || deptno 직무부서
from emp
where lower(ename) like '%s%' and comm is null
EMPNO ENAME 직무부서
---------- ---------- -------------------------------------------------
7369 SMITH CLERK20
7566 JONES MANAGER20
7788 SCOTT ANALYST20
7876 ADAMS CLERK20
7900 JAMES CLERK30
select
from
where lower(ename) like '%$_test' escape '$';
13. 부서번호별로 사원수, 급여합계를 보이시오.
select deptno, count(*), sum(sal)
from emp
group by deptno
DEPTNO COUNT(*) SUM(SAL)
---------- ---------- ----------
30 6 9400
20 5 10875
10 3 8750
14. 부서번호, 직무별로 급여평균, 사원수를 보이시오.
select deptno, job, avg(sal), count(*)
from emp
group by deptno, job
DEPTNO JOB AVG(SAL) COUNT(*)
---------- --------- ---------- ----------
20 CLERK 950 2
30 SALESMAN 1400 4
20 MANAGER 2975 1
30 CLERK 950 1
10 PRESIDENT 5000 1
30 MANAGER 2850 1
10 CLERK 1300 1
10 MANAGER 2450 1
20 ANALYST 3000 2
9개의 행이 선택됨
15. 관리자사번별로 사원수, 급여의 최대값을 사원수가 3명 이상인 관리자만 보이시오.
select mgr, count(*), max(sal)
from emp
group by mgr
having count(*) >= 3;
MGR COUNT(*) MAX(SAL)
---------- ---------- ----------
7839 3 2975
7698 5 1600
16. 입사일자가 81년 5월 1일 이후에 입사한 사원들에 대해 부서번호별로 사원수, 급여평균을 급여평균이 2000 이상인 부서만 보이시오.
select deptno, count(*), avg(sal)
from emp
where hiredate >= '81/05/01'
group by deptno
having avg(sal) >= 2000
DEPTNO COUNT(*) AVG(SAL)
---------- ---------- ----------
20 3 2366.66667
10 3 2916.66667
17. 사번, 사원명, 급여, 보너스, 급여 + 보너스 (열별칭 tot)를 tot가 큰값부터 보이시오.
select empno, ename, sal,comm, nvl(sal,0)+nvl(comm,0) tot
from emp
order by tot desc
EMPNO ENAME SAL COMM TOT
---------- ---------- ---------- ---------- ----------
7839 KING 5000 5000
7902 FORD 3000 3000
7788 SCOTT 3000 3000
7566 JONES 2975 2975
7698 BLAKE 2850 2850
7654 MARTIN 1250 1400 2650
7782 CLARK 2450 2450
7499 ALLEN 1600 300 1900
7521 WARD 1250 500 1750
7844 TURNER 1500 0 1500
7934 MILLER 1300 1300
7876 ADAMS 1100 1100
7900 JAMES 950 950
7369 SMITH 800 800
14개의 행이 선택됨
18. 부서번호별로 사원수, 급여합계를 급여합계가 적은 것부터 보이시오.
select deptno, count(*), sum(sal) 급여합
from emp
group by deptno
order by 급여합 asc
DEPTNO COUNT(*) 급여합
---------- ---------- ----------
10 3 8750
30 6 9400
20 5 10875
19. union (합집합) // 두테이블의 도메인이 같아야 한다
create table yemp
as
select empno, ename, deptno, sal, comm
from emp
where deptno in (10,20);
사원테이블에서 부서번호가 10인 사번, 사원명, 급여와 yemp에서 급여가 1000 이상인 사원의 사번, 사원명, 급여를 보이시오
select empno, ename, sal
from emp
where deptno = 10
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
select empno, ename, sal
from emp
where deptno = 10
union
select empno, ename, sal
from yemp
where sal >= 1000
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7876 ADAMS 1100
7902 FORD 3000
7934 MILLER 1300
20. 19번을 급여가 많은 것부터 보이시오. //마지막 select절에만 order by를 사용!
select empno, ename, sal
from emp
where deptno = 10
union
select empno, ename, sal
from yemp
where sal >= 1000
order by sal desc
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7782 CLARK 2450
7934 MILLER 1300
7876 ADAMS 1100
21. 19번에서 중복된 튜플도 모두 보이시오. //중복된 열도 다 보는법 union all
select empno, ename, sal
from emp
where deptno = 10
union all
select empno, ename, sal
from yemp
where sal >= 1000
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
7566 JONES 2975
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7876 ADAMS 1100
7902 FORD 3000
7934 MILLER 1300
10개의 행이 선택됨
22. minus (차집합)
사원테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여에서 yemp 테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여를 제외한 사원만 보이시오.
select empno, ename, sal
from emp
where deptno = 10
minus
select empno, ename, sal
from yemp
where sal >= 1000
선택된 행 없음 //작은거에서 큰거빼서 없음
22-1 yemp 테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여에서 사원테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여를 제외한 사원만 보이시오.
select empno, ename, sal
from yemp
where sal >= 1000
minus
select empno, ename, sal
from emp
where deptno = 10
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
23. intersect (교집합)
사원테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여에서 yemp 테이블에서 급여가 1000 이상인 사원의 사번, 사원명, 급여에 동시에 존재하는 사원을 보이시오.
select empno, ename, sal
from emp
where deptno = 10
intersect
select empno, ename, sal
from yemp
where sal >= 1000
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
**함수
**문자함수
24.아스키코드 65번인 문자출력
select chr(65)
from dual;
CHR(65)
-------
A
25. 첫글자를 대문자로해서 표시
select initcap(ename)
from emp;
INITCAP(ENAME)
--------------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
James
Ford
Miller
26. 이름뒤에 is a를 붙이기 // ||와 같은 원리
select concat(ename, ' is a ')
from emp;
CONCAT(ENAME,'ISA')
-------------------
SMITH is a
ALLEN is a
WARD is a
JONES is a
MARTIN is a
BLAKE is a
CLARK is a
SCOTT is a
KING is a
TURNER is a
ADAMS is a
JAMES is a
FORD is a
MILLER is a
select concat(concat(ename, ' is a '),job)
from emp;
CONCAT(CONCAT(ENAME,'ISA'),JOB)
-------------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD is a SALESMAN
JONES is a MANAGER
MARTIN is a SALESMAN
BLAKE is a MANAGER
CLARK is a MANAGER
SCOTT is a ANALYST
KING is a PRESIDENT
TURNER is a SALESMAN
ADAMS is a CLERK
JAMES is a CLERK
FORD is a ANALYST
MILLER is a CLERK
27.15칸으로 맞추구 왼쪽 나머지공간에 *로 채워라
select lpad('Page 1', 15, '*')
from dual;
LPAD('PAGE1',15,'*')
--------------------
*********Page 1
28. 오른쪽 나머지공간을 *로 채움 15개.
select rpad('Page 1', 15, '*')
from dual;
RPAD('PAGE1',15,'*')
--------------------
Page 1*********
29.왼쪽에서부터 xy중복된문자열 제거(공백제거할때 많이 사용)
select ltrim('xyxyxyLast Word', 'xy')
from dual;
LTRIM('XYXYXYLASTWORD','XY')
----------------------------
Last Word
30.오른쪽에서부터 xy중복된 문자열 제거
select rtrim('turnxyxyx','xy')
from dual;
RTRIM('TURNXYXYX','XY')
-----------------------
turn
31.양쪽다 제거할때 사용
select trim('xyxyxyturxyxyxy', 'xy')
from dual;
명령의 1 행에서 시작하는 중 오류 발생 -
select trim('xyxyxyturxyxyxy', 'xy')
from dual
오류 발생 명령행: 1 열: 30
오류 보고 -
SQL 오류: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
32.대치하기. J를 EL로 바꾸기
select replace('JACK and JUE', 'J', 'EL')
from dual;
REPLACE('JACKANDJUE','J','EL')
------------------------------
ELACK and ELUE
select loc, replace(loc, ' ' , '')
from dept;
LOC REPLACE(LOC,'','')
------------- ------------------
NEW YORK NEWYORK
DALLAS DALLAS
CHICAGO CHICAGO
BOSTON BOSTON
// 중간에 공백 제거!!
33.앞에서 두번째 두글자 ,-는 뒤에서 두번째 두글자 substr(열이름, 시작위치, 얻어오는 바이트수)
select ename, substr(ename, 2, 2)
from emp;
ENAME SUBSTR(ENAME,2,2)
---------- -----------------
SMITH MI
ALLEN LL
WARD AR
JONES ON
MARTIN AR
BLAKE LA
CLARK LA
SCOTT CO
KING IN
TURNER UR
ADAMS DA
JAMES AM
FORD OR
MILLER IL
14개의 행이 선택됨
select ename, substr(ename, -2, 2)
from emp;
ENAME SUBSTR(ENAME,-2,2)
---------- ------------------
SMITH TH
ALLEN EN
WARD RD
JONES ES
MARTIN IN
BLAKE KE
CLARK RK
SCOTT TT
KING NG
TURNER ER
ADAMS MS
JAMES ES
FORD RD
MILLER ER
14개의 행이 선택됨
34.instr(열이름, 찾는문자열, 시작위치, 몇번째) 위치값
select instr('CORPORATE FLOOR', 'OR', 3, 2)
from dual; //3번째 자리이후에 2번째 or문자가 14번째자리에 or이있다
INSTR('CORPORATEFLOOR','OR',3,2)
--------------------------------
14
35. 34번문제 반대의 개념
select instr('CORPORATE FLOOR', 'OR', -3, 2)
from dual;
INSTR('CORPORATEFLOOR','OR',-3,2)
---------------------------------
2
36.사원명의 첫바이트부터 'LL'의 위치가 3 이상인 사원들에 대해 사번, 사원명을 보이시오. instr
select empno, ename
from emp
where instr(upper(ename), 'LL', 1,1) >= 3
EMPNO ENAME
---------- ----------
7934 MILLER
37. 사번, 사원명, 사원명의 가운데 바이트를 보이시오.
length, /2, substr
// length 문자열길이 구하는 함수
select empno, ename, substr(ename, (length(ename))/2+1,1)
from emp;
EMPNO ENAME SUBSTR(ENAME,(LENGTH(ENAME))/2+1,1)
---------- ---------- -----------------------------------
7369 SMITH I
7499 ALLEN L
7521 WARD R
7566 JONES N
7654 MARTIN T
7698 BLAKE A
7782 CLARK A
7788 SCOTT O
7839 KING N
7844 TURNER N
7876 ADAMS A
7900 JAMES M
7902 FORD R
7934 MILLER L
14개의 행이 선택됨
38. 관리자사번에서 처음부터 2바이트별로 사원수, 급여합계를 보이시오.
substr
select substr(mgr,1,2), count(*), sum(sal)
from emp
group by mgr
SUBSTR(MGR,1,2) COUNT(*) SUM(SAL)
--------------- ---------- ----------
78 3 8275
1 5000
77 1 1300
76 5 6550
79 1 800
75 2 6000
77 1 1100
7개의 행이 선택됨
select substr(mgr, 1,2), count(*), sum(sal)
from emp
group by substr(mgr,1,2);
SUBSTR(MGR,1,2) COUNT(*) SUM(SAL)
--------------- ---------- ----------
1 5000
75 2 6000
78 3 8275
76 5 6550
77 2 2400
79 1 800
6개의 행이 선택됨
39. 사번, 사원명, 급여, 보너스, 보너스가 null이면 2000으로 간주해서 급여 중 큰 값을 보이시오.
nvl, greatest
select empno, ename, greatest(sal), nvl(comm, 2000)
from emp
EMPNO ENAME GREATEST(SAL) NVL(COMM,2000)
---------- ---------- ------------- --------------
7369 SMITH 800 2000
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975 2000
7654 MARTIN 1250 1400
7698 BLAKE 2850 2000
7782 CLARK 2450 2000
7788 SCOTT 3000 2000
7839 KING 5000 2000
7844 TURNER 1500 0
7876 ADAMS 1100 2000
7900 JAMES 950 2000
7902 FORD 3000 2000
7934 MILLER 1300 2000
14개의 행이 선택됨
////////////////////////////////////////위에꺼 틀림
select empno, ename,sal,comm,greatest( nvl(comm, 2000),sal)
from emp
EMPNO ENAME SAL COMM GREATEST(NVL(COMM,2000),SAL)
---------- ---------- ---------- ---------- ----------------------------
7369 SMITH 800 2000
7499 ALLEN 1600 300 1600
7521 WARD 1250 500 1250
7566 JONES 2975 2975
7654 MARTIN 1250 1400 1400
7698 BLAKE 2850 2850
7782 CLARK 2450 2450
7788 SCOTT 3000 3000
7839 KING 5000 5000
7844 TURNER 1500 0 1500
7876 ADAMS 1100 2000
7900 JAMES 950 2000
7902 FORD 3000 3000
7934 MILLER 1300 2000
14개의 행이 선택됨
40. 직무별로 사원명의 최대값, 사원명의 최소값을 사원명 최대값에 대해 첫번째부터 2바이트가 'ss' 이상 인 직무만 보이시오.
select job, max(ename),min(ename)
from emp
group by job
having max(ename) >= instr(lower(ename), 'ss', 1,2)
---------------------------------------------------------------------------------------------------------
select job, max(ename), min(ename)
from emp
group by job
having lower(substr(max(ename),1,2)) >= 'ss'
JOB MAX(ENAME) MIN(ENAME)
--------- ---------- ----------
SALESMAN WARD ALLEN
41. length(열이름) 문자열의 길이
lengthb(열이름)
select length('가나다라'), lengthb('가나다라')
from dual;
LENGTH('가나다라') LENGTHB('가나다라')
-------------- ---------------
4 12
select length('1234'), lengthb('1234')
from dual;
LENGTH('1234') LENGTHB('1234')
-------------- ---------------
4 4
42. greatest(열이름..................) //열이름들중에 큰값
least(열이름...........) //열이름들중에 작은값
**********************************************************시스템 함수
43. user
uid
select user, uid
from dual;
USER UID
------------------------------ ----------
SYSTEM 5
// 접속한 이름과 접속햇을때 프로세서 아이디
**************************************************************숫자함수
44. abs(열이름) //절대값
select abs(-15), abs(15)
from dual;
ABS(-15) ABS(15)
---------- ----------
15 15
45. ceil(열이름) //무조건 소수점, 올림
select ceil(15.7), ceil(15.3)
from dual;
CEIL(15.7) CEIL(15.3)
---------- ----------
16 16
46. floor(열이름) //소수점 버림
select flooar(15.7), floor(15.0), floor(15.2)
from dual;
FLOOR(15.7) FLOOR(15.0) FLOOR(15.2)
----------- ----------- -----------
15 15 15
47. mod(열이름, 나눌값) // 나누고 나머지값
select mod(6,2), mod(5,2), mod(7,3)
from dual;
MOD(6,2) MOD(5,2) MOD(7,3)
---------- ---------- ----------
0 1 1
48. power(열이름, 몇승) //제곱승 2의 3승
select power(2,3), power(3,2)
from dual;
POWER(2,3) POWER(3,2)
---------- ----------
8 9
49. round(열이름, 반올림할 자릿수) 소수점을 기준으로
select round(1234.5678, 0), round(1234.5678,2), round(1234.5678, -2)
from dual;
ROUND(1234.5678,0) ROUND(1234.5678,2) ROUND(1234.5678,-2)
------------------ ------------------ -------------------
1235 1234.57 1200
50. trunc(열이름, 내림할 자릿수) 소수점을 기준으로
select trunc(1234.5678, 0), trunc(1234.5678,2), trunc(1234.5678, -2)
from dual;
TRUNC(1234.5678,0) TRUNC(1234.5678,2) TRUNC(1234.5678,-2)
------------------ ------------------ -------------------
1234 1234.56 1200
51. 사번, 사원명, 급여, 급여*12를 십의 자리에서 반올림해서 보이시오.
select empno, ename, sal, round(sal*12, -2)
from emp
EMPNO ENAME SAL ROUND(SAL*12,-2)
---------- ---------- ---------- ----------------
7369 SMITH 800 9600
7499 ALLEN 1600 19200
7521 WARD 1250 15000
7566 JONES 2975 35700
7654 MARTIN 1250 15000
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
14개의 행이 선택됨
52. 급여에서 2000을 뺀 값의 절대값이 1000 이상인 사원들에 대해 사번, 사원명, 급여, 급여 -2000(열별칭 mi) 를 mi 의 내림차순으로 보이시오.
select empno, ename, sal, sal-2000 mi
from emp
where abs(sal-2000) >= 1000
order by mi asc
EMPNO ENAME SAL MI
---------- ---------- ---------- ----------
7839 KING 5000 3000
7788 SCOTT 3000 1000
7902 FORD 3000 1000
7900 JAMES 950 -1050
7369 SMITH 800 -1200
53. 사번의 짝,홀수별로 사원수, 급여평균을 보이시오.
select mod(empno,2), count(*), avg(sal)
from emp
group by mod(empno,2)
MOD(EMPNO,2) COUNT(*) AVG(SAL)
------------ ---------- ----------
1 4 2162.5
0 10 2037.5
비교 )
case when 조건 then 결과값
when 조건 then 결과값
...............
else 결과값
end
select case when mod(empno,2)=0 then '짝수'
else '홀수'
end 구분,
count(*), avg(sal), avg(nvl(sal,0))
from emp
group by mod(empno,2)
구분 COUNT(*) AVG(SAL) AVG(NVL(SAL,0))
-- ---------- ---------- ---------------
홀수 4 2162.5 2162.5
짝수 10 2037.5 2037.5
54. 부서번호가 10 또는 30인 사원에 대해 사번의 두번째자리에서 1바이트별로 사원수, 급여합계, 급여평균(10자리에서 내림)을 보이시오.
select substr(empno, 2,1), count(*), sum(sal), trunc(avg(sal),-2)
from emp
where empno in (10,30)
group by substr(empno, 2,1)
SUBSTR(EMPNO,2,1) COUNT(*) SUM(SAL) TRUNC(AVG(SAL),-2)
----------------- ---------- ---------- ------------------
6 2 4100 2000
8 2 6500 3200
5 1 1250 1200
7 1 2450 2400
9 2 2250 1100
4 1 1600 1600
6개의 행이 선택됨
55. 사원명의 길이가 5 이상이면서 사번을 5로 나눈 나머지가 2이상인 사원들에 대해 사번, 사원명, 급여, 급여 *5(백의자리에서 반올림)를 보이시오.
select empno, ename, sal, round((sal*5),-3)
from emp
where length(ename) >= 5 and mod(empno,5) >=2
EMPNO ENAME SAL ROUND((SAL*5),-3)
---------- ---------- ---------- -----------------
7369 SMITH 800 4000
7499 ALLEN 1600 8000
7654 MARTIN 1250 6000
7698 BLAKE 2850 14000
7782 CLARK 2450 12000
7788 SCOTT 3000 15000
7844 TURNER 1500 8000
7934 MILLER 1300 7000
8개의 행이 선택됨
select empno, ename, sal, round((sal*5),-3) 반올림한거, (sal*5) 반올림안한거
from emp
where length(ename) >= 5 and mod(empno,5) >=2
EMPNO ENAME SAL 반올림한거 반올림안한거
---------- ---------- ---------- ---------- ----------
7369 SMITH 800 4000 4000
7499 ALLEN 1600 8000 8000
7654 MARTIN 1250 6000 6250
7698 BLAKE 2850 14000 14250
7782 CLARK 2450 12000 12250
7788 SCOTT 3000 15000 15000
7844 TURNER 1500 8000 7500
7934 MILLER 1300 7000 6500
8개의 행이 선택됨
********날짜 함수
56. sysdate
select sysdate
from dual;
날짜 - 숫자 ==> 날짜 !!!!!!!!!!!이전 1900년 이후로해서 숫자로 날짜를 가지고 잇음
날짜 + 숫자 ==> 날짜 !!!!!!!!!!!이후
날짜 - 날짜 ==> 숫자(일수)//소수점은 시간을 의미
57. 사번, 사원명, 입사일자, 입사일자 100일후, 일사일자 100일전, 현재 날짜-입사일자를 보이시오.
select empno, ename, hiredate, hiredate +100 일후 , hiredate - 100 일전, sysdate-hiredate
from emp
EMPNO ENAME HIREDATE 일후 일전 SYSDATE-HIREDATE
---------- ---------- -------- -------- -------- ----------------
7369 SMITH 80/12/17 81/03/27 80/09/08 12516.483
7499 ALLEN 81/02/20 81/05/31 80/11/12 12451.483
7521 WARD 81/02/22 81/06/02 80/11/14 12449.483
7566 JONES 81/04/02 81/07/11 80/12/23 12410.483
7654 MARTIN 81/09/28 82/01/06 81/06/20 12231.483
7698 BLAKE 81/05/01 81/08/09 81/01/21 12381.483
7782 CLARK 81/06/09 81/09/17 81/03/01 12342.483
7788 SCOTT 82/04/09 82/07/18 81/12/30 12038.483
7839 KING 81/11/17 82/02/25 81/08/09 12181.483
7844 TURNER 81/09/08 81/12/17 81/05/31 12251.483
7876 ADAMS 83/05/12 83/08/20 83/02/01 11640.483
7900 JAMES 81/12/03 82/03/13 81/08/25 12165.483
7902 FORD 81/12/03 82/03/13 81/08/25 12165.483
7934 MILLER 82/01/23 82/05/03 81/10/15 12114.483
14개의 행이 선택됨
select empno, ename, hiredate, hiredate + 100 일후 , hiredate - 100 일전, sysdate-hiredate 입현, hiredate-sysdate
from emp
EMPNO ENAME HIREDATE 일후 일전 입현 HIREDATE-SYSDATE
---------- ---------- -------- -------- -------- ---------- ----------------
7369 SMITH 80/12/17 81/03/27 80/09/08 12516.4839 -12516.4839
7499 ALLEN 81/02/20 81/05/31 80/11/12 12451.4839 -12451.4839
7521 WARD 81/02/22 81/06/02 80/11/14 12449.4839 -12449.4839
7566 JONES 81/04/02 81/07/11 80/12/23 12410.4839 -12410.4839
7654 MARTIN 81/09/28 82/01/06 81/06/20 12231.4839 -12231.4839
7698 BLAKE 81/05/01 81/08/09 81/01/21 12381.4839 -12381.4839
7782 CLARK 81/06/09 81/09/17 81/03/01 12342.4839 -12342.4839
7788 SCOTT 82/04/09 82/07/18 81/12/30 12038.4839 -12038.4839
7839 KING 81/11/17 82/02/25 81/08/09 12181.4839 -12181.4839
7844 TURNER 81/09/08 81/12/17 81/05/31 12251.4839 -12251.4839
7876 ADAMS 83/05/12 83/08/20 83/02/01 11640.4839 -11640.4839
7900 JAMES 81/12/03 82/03/13 81/08/25 12165.4839 -12165.4839
7902 FORD 81/12/03 82/03/13 81/08/25 12165.4839 -12165.4839
7934 MILLER 82/01/23 82/05/03 81/10/15 12114.4839 -12114.4839
14개의 행이 선택됨
58. systimestamp
select systimestaamp
from dual;
SYSTIMESTAMP
----------------------------------
15/03/25 11:37:40.847000000 +09:00
//////////////////현재 시간 자세히
59.add_months(열이름//날짜형, 더할 개월수)
**열이름의 자료형은 date형이다
select add_months(sysdate,20)
from dual;
ADD_MONTHS(SYSDATE,20)
----------------------
16/11/25
select add_months(sysdate,-20)
from dual;
ADD_MONTHS(SYSDATE,-20)
-----------------------
13/07/25
//////////////////////////날짜형으로 나옴
60. last_day(열이름)
select last_day(sysdate)
from dual
LAST_DAY(SYSDATE)
-----------------
15/03/31
61. 지금부터 일을하면 몇 일을 인가?
select last_day(sysdate) - sysdate
from dual
LAST_DAY(SYSDATE)-SYSDATE
-------------------------
6
62. next_day(열이름, 형식)
형식 '월','화',,,,,'일'
형식 1,2...............7 /// 1 일요일 2월요일 3 화요일 4수요일.......7토요일
select next_day(sysdate, '월'),next_day(sysdate, '토')
from dual;
NEXT_DAY(SYSDATE,'월') NEXT_DAY(SYSDATE,'토')
--------------------- ---------------------
15/03/30 15/03/28
//////////////////다가올 요일을 구하기
63. months_between(열이름, 열이름)
select ename, hiredate, months_between(sysdate, hiredate), months_between(hiredate, sysdate)
from emp;
ENAME HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE) MONTHS_BETWEEN(HIREDATE,SYSDATE)
---------- -------- -------------------------------- --------------------------------
SMITH 80/12/17 411.496172 -411.496172
ALLEN 81/02/20 409.399398 -409.399398
WARD 81/02/22 409.334882 -409.334882
JONES 81/04/02 407.980043 -407.980043
MARTIN 81/09/28 402.141333 -402.141333
BLAKE 81/05/01 407 -407
CLARK 81/06/09 405.754236 -405.754236
SCOTT 82/04/09 395.754236 -395.754236
KING 81/11/17 400.496172 -400.496172
TURNER 81/09/08 402.786495 -402.786495
ADAMS 83/05/12 382.657462 -382.657462
JAMES 81/12/03 399.947785 -399.947785
FORD 81/12/03 399.947785 -399.947785
MILLER 82/01/23 398.302624 -398.302624
14개의 행이 선택됨
//과거기준으로 계산해서 - 나옴
64. 현재기준으로 근무개월수(소수점이하 반올림)가 400이상인 사원들에 대해 사번, 사원명, 입사일자를 보이시오.
select empno, ename, hiredate
from emp
where round(months_between(sysdate, hiredate), 0) >= 400 //미래를 먼저 씀
EMPNO ENAME HIREDATE
---------- ---------- --------
7369 SMITH 80/12/17
7499 ALLEN 81/02/20
7521 WARD 81/02/22
7566 JONES 81/04/02
7654 MARTIN 81/09/28
7698 BLAKE 81/05/01
7782 CLARK 81/06/09
7839 KING 81/11/17
7844 TURNER 81/09/08
7900 JAMES 81/12/03
7902 FORD 81/12/03
11개의 행이 선택됨
65. 현재기준으로 근무개월수별로(일의자리 버림) 사원수, 급여합계, 급여평균을 급여평균이 2200 이상인 사원들만 보이시오.
select trunc(months_between(sysdate, hiredate), -1) 근무개월수, count(*) 사원수, sum(sal) 급여합계, avg(nvl(sal,0)) 급여평균
from emp
group by trunc(months_between(sysdate, hiredate), -1)
having avg(nvl(sal,0)) >= 2200
근무개월수 사원수 급여합계 급여평균
---------- ---------- ---------- ----------
400 8 18875 2359.375
66. 현재기준으로 400 개월이전에 입사한 사원들에 대해 사번, 사원명, 입사일자를 입사일자가 최근인 사원부터 보이시오.
select empno, ename, hiredate
from emp
where months_between(sysdate, hiredate) >= 400
order by hiredate desc
EMPNO ENAME HIREDATE
---------- ---------- --------
7839 KING 81/11/17
7654 MARTIN 81/09/28
7844 TURNER 81/09/08
7782 CLARK 81/06/09
7698 BLAKE 81/05/01
7566 JONES 81/04/02
7521 WARD 81/02/22
7499 ALLEN 81/02/20
7369 SMITH 80/12/17
9개의 행이 선택됨
select empno, ename, hiredate
from emp
where add_months(sysdate, -400) >= hiredate
order by hiredate desc
EMPNO ENAME HIREDATE
---------- ---------- --------
7839 KING 81/11/17
7654 MARTIN 81/09/28
7844 TURNER 81/09/08
7782 CLARK 81/06/09
7698 BLAKE 81/05/01
7566 JONES 81/04/02
7521 WARD 81/02/22
7499 ALLEN 81/02/20
7369 SMITH 80/12/17
9개의 행이 선택됨
*************변환함수
to_char(숫자 또는 날짜형 열이름, '형식') 문자형으로
to_date(문자형 또는 숫자형 열이름, '형식') 날짜형으로
to_number(문자형 열이름, '형식') 숫자형으로
////////////////////////////////////날짜형식 요소
mm 월두자리
/ 나 - 넣어서 구분가능
mon 영어타입으로는 월의3자리 문자. 한글타입은 월
month 월의 풀네임(january), 한글타입은 월
dd 날짜, 일수
d 요일을 숫자로 표현한것(ex 일요일 1 월요일 2 화요일 3 ............토요일 7) **정렬하기위해 사용**
dy 요일이름을 앞자리만. 영어는 3글자
day 요일이름
yyyy 년도 4자리
yy 년도 마지막2자리
ww 년도의 몇주차
w 는 월의 몇주차
q 쿼터 분기별로 보기 1사분기 2사분기 3사분기 4사분기
ddd 올해의 몇일 되었나 일수.
hh 시 나타냄
hh12 12시간단위로 표현
hh24 24시간단위로 표현
mi 분
ss 초
am or pm 오전 오후 표기
/////////////////////////////////////////////////////////////////
67. select to_char(sysdate, 'mm/mon - month dd d dy day yyyy yy w ww q ddd'), to_char(sysdate,'hh hh12:hh24 mi ss am')
from dual;
TO_CHAR(SYSDATE,'MM/MON-MONTHDDDDYDAYYYYYYYWWWQDDD') TO_CHAR(SYSDATE,'HHHH12:HH24MISSAM')
---------------------------------------------------------------- ------------------------------------
04/4월 - 4월 01 4 수 수요일 2015 15 1 13 2 091 10 10:10 10 09 오전
숫자형식
to_char('99999') 문자형식으로 자릿수 만듬 부족하면 ####으로 나옴
to_char(sal, '$99,999) 달러 붙여서 표현
to_char(sal/3, '9999.99') 소수점 표현
to_char(sal, 'L099,999') L원화 표시(지역화폐로 표시.일본은 엔화 유럽은 유로), 0은 의미없이 자릿수맞춰서 0으로 채워짐
68.
select empno, to_char(sal, '99999'), to_char(sal, '99,999'), to_char(sal, '$99,999), to_char(sal/3, '9999.99'), to_char(sal, 'L099,999')
from emp;
EMPNO TO_CHAR(SAL,'99999') TO_CHAR(SAL,'99,999') TO_CHAR(SAL,'$99,999') TO_CHAR(SAL/3,'9999.99') TO_CHAR(SAL,'L099,999')
---------- -------------------- --------------------- ---------------------- ------------------------ -----------------------
7369 800 800 $800 266.67 ₩000,800
7499 1600 1,600 $1,600 533.33 ₩001,600
7521 1250 1,250 $1,250 416.67 ₩001,250
7566 2975 2,975 $2,975 991.67 ₩002,975
7654 1250 1,250 $1,250 416.67 ₩001,250
7698 2850 2,850 $2,850 950.00 ₩002,850
7782 2450 2,450 $2,450 816.67 ₩002,450
7788 3000 3,000 $3,000 1000.00 ₩003,000
7839 5000 5,000 $5,000 1666.67 ₩005,000
7844 1500 1,500 $1,500 500.00 ₩001,500
7876 1100 1,100 $1,100 366.67 ₩001,100
7900 950 950 $950 316.67 ₩000,950
7902 3000 3,000 $3,000 1000.00 ₩003,000
7934 1300 1,300 $1,300 433.33 ₩001,300
14개의 행이 선택됨
69. 입사일자가 1981년 12월 1일 이전에 입사한 사원수, 급여합계를 보이시오.
select count(*) 사원수, sum(sal) 급여합계
from emp
where to_char(hiredate, 'yyyy mm dd') <= '1981 12 01'
사원수 급여합계
---------- ----------
9 19675
70.입사일자가 화요일, 급요일에 입사한 사원들에 대해 사번, 사원명, 입사일자(1980/12/01 토요일)를 요일의 오름차순으로 보이시오.
select empno, ename, to_char(hiredate, 'yyyy/mm/dd day') 입사일자
from emp
where to_char(hiredate, 'day') in ('화요일','금요일')
order by to_char(hiredate, 'd') asc
EMPNO ENAME 입사일자
---------- ---------- -----------------------
7782 CLARK 1981/06/09 화요일
7839 KING 1981/11/17 화요일
7844 TURNER 1981/09/08 화요일
7788 SCOTT 1982/04/09 금요일
7698 BLAKE 1981/05/01 금요일
7499 ALLEN 1981/02/20 금요일
6개의 행이 선택됨
71. 입사일자에 대해 분기별로 사원수, 최대급여($12,999), 최소급여(\012,111)를 보이시오.
select to_char(hiredate, 'q') 분기, count(*) 사원수, max(to_char(sal, '$99,999')) 최대급여, min(to_char(sal, 'L099,999')) 최소급여
from emp
group by to_char(hiredate, 'q')
분기 사원수 최대급여 최소급여
-- ---------- -------- ------------------
1 3 $1,600 ₩001,250
3 2 $1,500 ₩001,250
4 4 $5,000 ₩000,800
2 5 $3,000 ₩001,100
72. 입사년도별로 사원수, 급여합계를 사원수가 2명 이상인 년도만 사원수가 많은 것부터 보이시오.
select to_char(hiredate, 'yyyy') 입사년도, count(*) 사원수, sum(sal) 급여합계
from emp
group by to_char(hiredate, 'yyyy')
having count(*) >= 2
order by 사원수 asc
입사년도 사원수 급여합계
---- ---------- ----------
1981 10 22825
1982 2 4300
73. 입사월이 홀수월에 입사한 사원들에 대해 사번, 사원명, 입사일자(1980-01-01 수요일 12:12:01 오전)를 보이시오.
select empno, ename , to_char(hiredate, 'yyyy-mm-dd mon hh12:mi:ss am') 입사일자
from emp
where mod(to_char(hiredate, 'mm'),2) = 1
EMPNO ENAME 입사일자
---------- ---------- -----------------------------------
7654 MARTIN 1981-09-28 9월 12:00:00 오전
7698 BLAKE 1981-05-01 5월 12:00:00 오전
7839 KING 1981-11-17 11월 12:00:00 오전
7844 TURNER 1981-09-08 9월 12:00:00 오전
7876 ADAMS 1983-05-12 5월 12:00:00 오전
7934 MILLER 1982-01-23 1월 12:00:00 오전
6개의 행이 선택됨
74. 입사일자가 상반기, 하반기별로 사원수를 상반기, 하반기 순으로 보이시오.
select case when to_char(hiredate,'q') <= 2 then '상반기'
else '하반기'
end 분기, count(*)
from emp
group by case when to_char(hiredate,'q') <= 2 then '상반기'
else '하반기'
end
order by 분기 asc;
분기 COUNT(*)
--- ----------
상반기 8
하반기 6
기타형식요소
of "문자열"
select sysdate, to_char(sysdate, 'dd "of" mon')
from dual;
SYSDATE TO_CHAR(SYSDATE,'DD"OF"MON')
-------- ----------------------------
15/04/01 01 of 4월
sp (one,two,three...)
th (1th...)
spth (frist,secont,....)
select sysdate, to_char(sysdate,'ddspth'),to_char(sysdate,'ddth'),to_char(sysdate,'ddsp')
from dual;
SYSDATE TO_CHAR(SYSDATE,'DDSPTH') TO_CHAR(SYSDATE,'DDTH') TO_CHAR(SYSDATE,'DDSP')
-------- ------------------------- ----------------------- -----------------------
15/04/01 first 01st one
*******trim 함수
75.
select ename, trim(leading 'A' from ename), trim(trailing 'N' from ename), trim(both 'S' from ename)
from emp;
ENAME TRIM(LEADING'A'FROMENAME) TRIM(TRAILING'N'FROMENAME) TRIM(BOTH'S'FROMENAME)
---------- ------------------------- -------------------------- ----------------------
SMITH SMITH SMITH MITH
ALLEN LLEN ALLE ALLEN
WARD WARD WARD WARD
JONES JONES JONES JONE
MARTIN MARTIN MARTI MARTIN
BLAKE BLAKE BLAKE BLAKE
CLARK CLARK CLARK CLARK
SCOTT SCOTT SCOTT COTT
KING KING KING KING
TURNER TURNER TURNER TURNER
ADAMS DAMS ADAMS ADAM
JAMES JAMES JAMES JAME
FORD FORD FORD FORD
MILLER MILLER MILLER MILLER
14개의 행이 선택됨
leading 왼쪽에서 '?'로 시작하는 문자 제거
trailing 오른쪽 '?'로 시작하는 문자 제거
both '?'의 양쪽의 문자를 제거
select ename, trim(leading 'A' from lower(ename)), trim(trailing 'N' from lower(ename)), trim(both 'S' from lower(ename))
from emp;
ENAME TRIM(LEADING'A'FROMLOWER(ENAME)) TRIM(TRAILING'N'FROMLOWER(ENAME)) TRIM(BOTH'S'FROMLOWER(ENAME))
---------- -------------------------------- --------------------------------- -----------------------------
SMITH smith smith smith
ALLEN allen allen allen
WARD ward ward ward
JONES jones jones jones
MARTIN martin martin martin
BLAKE blake blake blake
CLARK clark clark clark
SCOTT scott scott scott
KING king king king
TURNER turner turner turner
ADAMS adams adams adams
JAMES james james james
FORD ford ford ford
MILLER miller miller miller
14개의 행이 선택됨
76.
select empno, ename
from emp
where regexp_like(ename, '^A');
EMPNO ENAME
---------- ----------
7499 ALLEN
7876 ADAMS
////////// reagexp_like(ename,'^A') 문자열의 시작되는 철자가 동일하면 모두 검색
77.
select regexp_instr('But, soft! What light through yonder window breaks', 'l[[:alpha:]]{4}') result
from dual;
RESULT
----------
17
/// l[[:alpha:]]{4} l 이후로4자리의 단어가 시작하는 위치 light에서 l의 위치가 17
select regexp_instr('lut, softl! What light through yonder window breaks', 'l[[:alpha:]]{4}') result
from dual;
RESULT
----------
18
첫번째 lut는 l 이후로 2자리이므로 안찾음, 두번째 softl!은 뒤에 특수문자! 가 있기 때문에 안찾음 세번째 light는 l 이후로 4자리이므로 이것을 찾게된다.
78.
select rank(2000) within group (order by sal asc), dense_rank(2000) within group (order by sal asc)
from emp;
RANK(2000)WITHINGROUP(ORDERBYSALASC) DENSE_RANK(2000)WITHINGROUP(ORDERBYSALASC)
------------------------------------ ------------------------------------------
9 8
select ename, sal
from emp
order by sal asc;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
SCOTT 3000
FORD 3000
KING 5000
14개의 행이 선택됨
******rank는 중복포함
///////////////// dense_rank는 중복제외
79.
select empno, sal, row_number() over (order by sal asc)
from emp;
EMPNO SAL ROW_NUMBER()OVER(ORDERBYSALASC)
---------- ---------- -------------------------------
7369 800 1
7900 950 2
7876 1100 3
7521 1250 4
7654 1250 5
7934 1300 6
7844 1500 7
7499 1600 8
7782 2450 9
7698 2850 10
7566 2975 11
7788 3000 12
7902 3000 13
7839 5000 14
14개의 행이 선택됨
**상위 top10을 보이시오.
select rk, empno, sal
from
(select empno, sal, row_number() over (order by sal desc) rk
from emp)
where rk <= 10;
RK EMPNO SAL
---------- ---------- ----------
1 7839 5000
2 7788 3000
3 7902 3000
4 7566 2975
5 7698 2850
6 7782 2450
7 7499 1600
8 7844 1500
9 7934 1300
10 7521 1250
10개의 행이 선택됨
80.
select empno, ename, sal, avg(sal)
from emp;
///오류남
select empno, ename, sal, avg(sal) over() avg1
from emp;
EMPNO ENAME SAL AVG1
---------- ---------- ---------- ----------
7369 SMITH 800 2073.21429
7499 ALLEN 1600 2073.21429
7521 WARD 1250 2073.21429
7566 JONES 2975 2073.21429
7654 MARTIN 1250 2073.21429
7698 BLAKE 2850 2073.21429
7782 CLARK 2450 2073.21429
7788 SCOTT 3000 2073.21429
7839 KING 5000 2073.21429
7844 TURNER 1500 2073.21429
7876 ADAMS 1100 2073.21429
7900 JAMES 950 2073.21429
7902 FORD 3000 2073.21429
7934 MILLER 1300 2073.21429
14개의 행이 선택됨
///////////전체 평균
select empno, ename, sal, avg(sal) over(order by sal asc) avg1
from emp;
EMPNO ENAME SAL AVG1
---------- ---------- ---------- ----------
7369 SMITH 800 800
7900 JAMES 950 875
7876 ADAMS 1100 950
7521 WARD 1250 1070
7654 MARTIN 1250 1070
7934 MILLER 1300 1108.33333
7844 TURNER 1500 1164.28571
7499 ALLEN 1600 1218.75
7782 CLARK 2450 1355.55556
7698 BLAKE 2850 1505
7566 JONES 2975 1638.63636
7788 SCOTT 3000 1848.07692
7902 FORD 3000 1848.07692
7839 KING 5000 2073.21429
14개의 행이 선택됨
//////////////////////// 다음행과의 평균을 순차적으로 나타냄
select deptno, empno, ename, sal, avg(sal) over(partition by deptno) avg1
from emp;
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
10 7782 CLARK 2450 2916.66667
10 7839 KING 5000 2916.66667
10 7934 MILLER 1300 2916.66667
20 7566 JONES 2975 2175
20 7902 FORD 3000 2175
20 7876 ADAMS 1100 2175
20 7369 SMITH 800 2175
20 7788 SCOTT 3000 2175
30 7521 WARD 1250 1566.66667
30 7844 TURNER 1500 1566.66667
30 7499 ALLEN 1600 1566.66667
30 7900 JAMES 950 1566.66667
30 7698 BLAKE 2850 1566.66667
30 7654 MARTIN 1250 1566.66667
14개의 행이 선택됨
////////////////// 부서별로(deptno) 평균을 내타냄
select deptno, empno, ename, sal, avg(sal) over(partition by deptno order by sal desc) avg1
from emp;
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
10 7839 KING 5000 5000
10 7782 CLARK 2450 3725
10 7934 MILLER 1300 2916.66667
20 7788 SCOTT 3000 3000
20 7902 FORD 3000 3000
20 7566 JONES 2975 2991.66667
20 7876 ADAMS 1100 2518.75
20 7369 SMITH 800 2175
30 7698 BLAKE 2850 2850
30 7499 ALLEN 1600 2225
30 7844 TURNER 1500 1983.33333
30 7654 MARTIN 1250 1690
30 7521 WARD 1250 1690
30 7900 JAMES 950 1566.66667
14개의 행이 선택됨
/////////// 부서별로 순차적인 평균을 나타냄 (5000+2450)/2는 3725,,,, 중복데이터 계산안함
select deptno, empno, ename, sal, avg(sal) over(partition by deptno order by sal desc rows between unbounded preceding and current row) avg1
from emp;
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
10 7839 KING 5000 5000
10 7782 CLARK 2450 3725
10 7934 MILLER 1300 2916.66667
20 7788 SCOTT 3000 3000
20 7902 FORD 3000 3000
20 7566 JONES 2975 2991.66667
20 7876 ADAMS 1100 2518.75
20 7369 SMITH 800 2175
30 7698 BLAKE 2850 2850
30 7499 ALLEN 1600 2225
30 7844 TURNER 1500 1983.33333
30 7654 MARTIN 1250 1800
30 7521 WARD 1250 1690
30 7900 JAMES 950 1566.66667
14개의 행이 선택됨
///////////////// (1300+2450)/2는 1875 (1875+5000)/2 는 2916.66667 부서가 바뀌게 되면 다시 처음부터 시작. 중복데이터 계산함
81.
select empno,ename, sal, decode(job, 'ANALYST', sal*1.1, 'CLERK', sal*1.15, 'MANAGER', sal*1.2, sal) 보너스
from emp;
EMPNO ENAME SAL 보너스
---------- ---------- ---------- ----------
7369 SMITH 800 920
7499 ALLEN 1600 1600
7521 WARD 1250 1250
7566 JONES 2975 3570
7654 MARTIN 1250 1250
7698 BLAKE 2850 3420
7782 CLARK 2450 2940
7788 SCOTT 3000 3300
7839 KING 5000 5000
7844 TURNER 1500 1500
7876 ADAMS 1100 1265
7900 JAMES 950 1092.5
7902 FORD 3000 3300
7934 MILLER 1300 1495
14개의 행이 선택됨
///decode(검색컬럼, 조건-1 , 결과 값-1,
조건-2, 결과 값-2, ........., 기본 값)
82. group by 절에만 사용가능한 함수
select deptno, job, sum(sal), avg(sal)
from emp
group by deptno, job;
DEPTNO JOB SUM(SAL) AVG(SAL)
---------- --------- ---------- ----------
20 CLERK 1900 950
30 SALESMAN 5600 1400
20 MANAGER 2975 2975
30 CLERK 950 950
10 PRESIDENT 5000 5000
30 MANAGER 2850 2850
10 CLERK 1300 1300
10 MANAGER 2450 2450
20 ANALYST 6000 3000
9개의 행이 선택됨
select deptno, job, sum(sal), avg(sal)
from emp
group by rollup(deptno, job);
DEPTNO JOB SUM(SAL) AVG(SAL)
---------- --------- ---------- ----------
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
10 8750 2916.66667
20 CLERK 1900 950
20 ANALYST 6000 3000
20 MANAGER 2975 2975
20 10875 2175
30 CLERK 950 950
30 MANAGER 2850 2850
30 SALESMAN 5600 1400
30 9400 1566.66667
29025 2073.21429
13개의 행이 선택됨
////////////////// 부서별과 직무별로 합계, 평균을 나타냄
select deptno, job, sum(sal), avg(sal)
from emp
group by cube(deptno, job);
DEPTNO JOB SUM(SAL) AVG(SAL)
---------- --------- ---------- ----------
29025 2073.21429
CLERK 4150 1037.5
ANALYST 6000 3000
MANAGER 8275 2758.33333
SALESMAN 5600 1400
PRESIDENT 5000 5000
10 8750 2916.66667
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
20 10875 2175
20 CLERK 1900 950
20 ANALYST 6000 3000
20 MANAGER 2975 2975
30 9400 1566.66667
30 CLERK 950 950
30 MANAGER 2850 2850
30 SALESMAN 5600 1400
18개의 행이 선택됨
///////////// 직무별, 부서별 각각의 합계, 평균
83. grouping sets
select deptno, job, mgr, sum(sal)
from emp
group by grouping sets((deptno, job), (job, mgr));
DEPTNO JOB MGR SUM(SAL)
---------- --------- ---------- ----------
CLERK 7902 800
PRESIDENT 5000
CLERK 7698 950
CLERK 7788 1100
CLERK 7782 1300
SALESMAN 7698 5600
MANAGER 7839 8275
ANALYST 7566 6000
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000
17개의 행이 선택됨
///group set 절을 사용하면 이전 방법보다 sql문이 간단해지고 또한 실행 시 성능이 빨라짐
select deptno, job, null 관리자, sum(sal)
from emp
group by deptno, job
union
select null, job, mgr, sum(sal)
from emp
group by job, mgr;
DEPTNO JOB 관리자 SUM(SAL)
---------- --------- ---------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
ANALYST 7566 6000
CLERK 7698 950
CLERK 7782 1300
CLERK 7788 1100
CLERK 7902 800
MANAGER 7839 8275
PRESIDENT 5000
SALESMAN 7698 5600
17개의 행이 선택됨
84.
select ename, level, empno, mgr
from emp
start with ename = 'KING'
connect by prior empno = mgr;
ENAME LEVEL EMPNO MGR
---------- ---------- ---------- ----------
KING 1 7839
JONES 2 7566 7839
SCOTT 3 7788 7566
ADAMS 4 7876 7788
FORD 3 7902 7566
SMITH 4 7369 7902
BLAKE 2 7698 7839
ALLEN 3 7499 7698
WARD 3 7521 7698
MARTIN 3 7654 7698
TURNER 3 7844 7698
JAMES 3 7900 7698
CLARK 2 7782 7839
MILLER 3 7934 7782
14개의 행이 선택됨
select substr(lpad('', 3*level-3) ||ename, 1,20) 사원명, level, empno, mgr 관리자사번
from emp
start with ename = 'KING'
connect by prior empno = mgr;
사원명 LEVEL EMPNO 관리자사번
-------------------- ---------- ---------- ----------
KING 1 7839
JONES 2 7566 7839
SCOTT 3 7788 7566
ADAMS 4 7876 7788
FORD 3 7902 7566
SMITH 4 7369 7902
BLAKE 2 7698 7839
ALLEN 3 7499 7698
WARD 3 7521 7698
MARTIN 3 7654 7698
TURNER 3 7844 7698
JAMES 3 7900 7698
CLARK 2 7782 7839
MILLER 3 7934 7782
14개의 행이 선택됨
85.
select ename, connect_by_iscycle Iscycle, level, sys_connect_by_path(ename,'/') path
from emp
start with ename = 'KING'
connect by nocycle prior empno = mgr;
ENAME ISCYCLE LEVEL PATH
--------------------------------------------------------------------------------------------------------------
KING 0 1 /KING
JONES 0 2 /KING/JONES
SCOTT 0 3 /KING/JONES/SCOTT
ADAMS 0 4 /KING/JONES/SCOTT/ADAMS
FORD 0 3 /KING/JONES/FORD
SMITH 0 4 /KING/JONES/FORD/SMITH
BLAKE 0 2 /KING/BLAKE
ALLEN 0 3 /KING/BLAKE/ALLEN
WARD 0 3 /KING/BLAKE/WARD
MARTIN 0 3 /KING/BLAKE/MARTIN
TURNER 0 3 /KING/BLAKE/TURNER
JAMES 0 3 /KING/BLAKE/JAMES
CLARK 0 2 /KING/CLARK
MILLER 0 3 /KING/CLARK/MILLER