'DBMS/오라클기초' 카테고리의 글 목록 (2 Page)

DBMS/오라클기초

오라클 기초#6(Transaction Control Language) 2013.01.27
오라클 기초#5(Data Manipulation Language) 2013.01.27
오라클 기초#4 ( JOIN, ROWID, ROWNUM ) 2013.01.27
오라클 기초#3 (subquery) 2013.01.27
오라클 기초#2(함수) 2013.01.27

오라클 기초#6(Transaction Control Language)

2013. 1. 27. 12:28

[ TCL ] (Transaction Control Language )

- 트랜잭션 : 분리되어서는 안되는 논리적 작업 단위

- TCL:DML문이 실행되어 DBMS에 저장되거나 되돌리기 위해 실행해야 하는 SQL

1) 트랜잭션의 시작

- DBMS에 처음 접속했을때
- COMMIT 또는 ROLLBACK했을때

2) 트랜잭션의 종료

- COMMIT 또는 ROLLBACK했을때
- DDL(CREATE,DROP,..)등이 실행되었을때
- DB가 종료될때

3) TCL의 종류

- COMMIT : SQL문의 결과를 영구적으로 DB에 반영하는 SQL문
- ROLLBACK : SQL문의 결과를 취소하는 SQL문
- SAVEPOINT : 트랜잭션의 한 지점에 표시하는 임시 저장점

SQL> INSERT INTO DEPT VALUES(60,'하나','대구');
SQL> INSERT INTO DEPT VALUES(61,'둘','대전');
SQL> SAVEPOINT A;
SQL> INSERT INTO DEPT VALUES(62,'셋','부산');
SQL> INSERT INTO DEPT VALUES(63,'넷','서울');
SQL> ROLLBACK TO A; -- A지점 이후만 작업이 취소됨

4) TCL관련 특성

(1) READ CONSISTENCY(읽기 일관성- 해당 ROW에 대해서)

- 어떤 사용자가 변경중인 행을 다른 사용자가 변경할 수 없게 하는 기술로써 변경중인 사용자에 의해 COMMIT또는 ROLLBACK이 실행된 후 변경되는 특성

(2) LOCK(잠금현상)

- 다른 사용자에 의해 DML문이 실행되어져서 동일한 데이터를 INSERT, UPDATE,DELETE할때 한 쪽 사용자는 LOCK이 걸리는 현상

1> ROW-LEVEL LOCK (행레벨만 LOCK이 걸림)

세션1)
SQL> UPDATE DEPT SET LOC='제주'
2 WHERE DEPTNO=51; -- 하나의 행만 lock이 걸림

세션2)
SQL> delete from dept where deptno=52; -- 가능(다른 행이므로 가능!)

2> TABLE-LEVEL LOCK (테이블전체에 LOCK이 걸림)

세션1>
SQL> UPDATE DEPT SET LOC='제주'; -- 테이블전체에 lock이 걸림

세션2>
SQL> delete from dept where deptno=53; -- lock이 걸림

저작자표시

'DBMS > 오라클기초' 카테고리의 다른 글

오라클 기초#8(제약조건:constraint) (0)	2013.01.27
오라클 기초 #7(table) (0)	2013.01.27
오라클 기초#5(Data Manipulation Language) (0)	2013.01.27
오라클 기초#4 ( JOIN, ROWID, ROWNUM ) (0)	2013.01.27
오라클 기초#3 (subquery) (0)	2013.01.27

오라클 기초#5(Data Manipulation Language)

2013. 1. 27. 12:24

[ DML (Data Manipulation Language) ]

- 테이블내의 데이터를 입력,수정,삭제할때 사용하는 SQL언어

- 데이터를 실제로 DB에 저장할때는 COMMIT, 취소할때는 ROLLBACK을 사용한다.

1) INSERT문

- 데이터를 추가할때 사용

형식)

INSERT INTO 테이블명(컬럼명1,컬럼명2,..)
VALUES(값1,값2,..)

예1)

INSERT INTO DEPT(DEPTNO,DNAME,LOC)
VALUES(50,'회계','서울');
COMMIT; --- 실제로 DB에 저장

-- 모든컬럼값을 모두 추가할때는 컬럼명 생략 가능!

SQL> INSERT INTO DEPT
2 VALUES(60,'인사부','대구');

-- 형변환 가능

SQL> INSERT INTO DEPT
2 VALUES('70','영업부','부산');

SQL> INSERT INTO EMP(EMPNO,ENAME,JOB,DEPTNO)
2 VALUES(1000,'홍길동','학생',70);

2) DELETE 문

-- 데이터를 삭제할때 사용

형식)

DELETE FROM 테이블명 WHERE 조건절;

예1) 부서번호가 70번인 부서를 DEPT테이블에서 삭제하시오.

DELETE FROM DEPT WHERE DEPTNO=60;
COMMIT;

-- 참조하는 자식데이터가 존재하면 데이터를 삭제할 수 없음

SQL> DELETE FROM DEPT WHERE DEPTNO=70;
DELETE FROM DEPT WHERE DEPTNO=70
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record f

==> 자식데이터를 먼저지우고 부모데이터를 지운다.

SQL> DELETE FROM EMP WHERE DEPTNO=70;

1 row deleted.

SQL> DELETE FROM DEPT WHERE DEPTNO=70;

1 row deleted.

SQL> COMMIT;

-- DEPT테이블에서 부서번호가 50이상인 부서를 삭제해 보세요.

SQL> DELETE FROM DEPT WHERE DEPTNO>=50;

-- EMP테이블에서 사원이름이 '김철수'이거나 급여가 2000이상인 사원들을 삭제해 보세요.(COMMIT하지 말고 다시 ROLLBACK하세요)

SQL> DELETE FROM EMP
2 WHERE ENAME='김철수' OR SAL>=2000;

3) UPDATE문

- 데이터를 수정할 때 사용

형식)

UPDATE 테이블명
SET 컬럼명1=수정값,컬럼명2=수정값,..
WHERE 조건절;

예) 사원이름이 'ALLEN'인 사원의 부서를 10번,급여를 3000으로 수정하시오.

UPDATE EMP
SET DEPTNO=10,SAL=3000
WHERE ENAME='ALLEN';

COMMIT;

-- 부서번호가 20번이고 급여가 2000이상인 사원들의 부서를 30번부서로 수정하세요.

UPDATE EMP
SET DEPTNO=30
WHERE DEPTNO=20 AND SAL>=2000;

COMMIT;

-- 10번 부서사원들의 급여를 10%인상하세요.

SQL> UPDATE EMP
2 SET SAL=SAL*1.1
3 WHERE DEPTNO=10;

-- 회원정보를 갖는 테이블

CREATE TABLE MEMBER(
ID VARCHAR2(10) PRIMARY KEY,
PWD VARCHAR2(10),
PHONE VARCHAR2(15),
REGDATE DATE);

-- 구매 테이블

DROP TABLE CART;
CREATE TABLE CART(
NUM NUMBER PRIMARY KEY,
ID VARCHAR(10) REFERENCES MEMBER(ID),
ITEM VARCHAR2(20),
PRICE NUMBER );

INSERT INTO MEMBER VALUES('SONG','1234','02-111-1234',SYSDATE);

INSERT INTO CART VALUES(1,'SONG','사탕',1000);

COMMIT;

저작자표시

'DBMS > 오라클기초' 카테고리의 다른 글

오라클 기초 #7(table) (0)	2013.01.27
오라클 기초#6(Transaction Control Language) (0)	2013.01.27
오라클 기초#4 ( JOIN, ROWID, ROWNUM ) (0)	2013.01.27
오라클 기초#3 (subquery) (0)	2013.01.27
오라클 기초#2(함수) (0)	2013.01.27

오라클 기초#4 ( JOIN, ROWID, ROWNUM )

2013. 1. 27. 12:15

[ 조인(**)]

- 하나의 테이블로는 원하는 컬럼정보를 얻어올 수 없을때 관련된 테이블을 논리적으로 결합하여 원하는 컬럼정보를 얻어오는 방법.

- 조인조건 : 논리적으로 결합되는 1개이상의 테이블에는 반드시 '공통컬럼'이 존재해야 하며 이 공통컬럼은 동일한 데이터 타입이어야 한다.

1) EQUI조인

- 가장 일반적인 조인.WHERE 절에 공통컬럼들이 동등연산자(=)에 의해 비교되는 조인

예1)사원번호,이름,부서번호,부서이름,부서위치를 조회하시오.

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

EMPNO ENAME       DEPTNO DNAME            LOC
--------- --------- ---------- -------------- ----------
      7369 SMITH                   20 RESEARCH       DALLAS
      7499 ALLEN                   30 SALES             CHICAGO
      7521 WARD                    30 SALES             CHICAGO
      7566 JONES                   20 RESEARCH      DALLAS
      7654 MARTIN                  30 SALES            CHICAGO
      .....

예2) 30번 부서의 사원들의 사원번호,이름,부서번호,부서명을 출력하시오.

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=30;

Q1) 부서명이 'ACCOUNTING'에 근무하는 사원들의 이름,급여,부서명을 출력해 보세요.

SELECT E.ENAME,E.SAL,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='ACCOUNTING';

2) CROSS JOIN

- 2개이상의 테이블에서 조인될때 공통컬럼에 의한 결합이 발생되지 않는경우 (일반적으로 실수로 조인을 하는 경우가 대부분임)

SELECT E.EMPNO,
             E.ENAME,
             E.DEPTNO,
             D.DNAME,
             D.LOC
   FROM EMP E,DEPT D;


EMPNO ENAME DEPTNO DNAME         LOC
-------- -------- -------- ------------- ------------
     7369 SMITH              20 ACCOUNTING   NEW YORK
     7499 ALLEN              30 ACCOUNTING   NEW YORK
     7521 WARD               30 ACCOUNTING   NEW YORK
....
48 rows selected.

3) SELF 조인

- 참조해야 하는 컬럼이 자신의 테이블에 있는 다른 컬럼인 경우 사용되는 조인

예) 각 사원의 사원이름,매니저이름을 출력하시오.

SELECT E1.ENAME "사원이름",E2.ENAME "매니져이름"
FROM EMP E1,EMP E2
WHERE E1.MGR=E2.EMPNO
ORDER BY E1.EMPNO;

4) Outer 조인

- 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른쪽 테이블에는 데이터가 존재하지 않는 경우 모든 데이터를 출력하게 하는 조인

예) 부서번호,부서명,사원이름을 출력하시오.

SELECT D.DEPTNO,D.DNAME,E.ENAME
FROM DEPT D,EMP E
WHERE D.DEPTNO=E.DEPTNO(+);

Q1)EMP테이블과 DEPT테이블을 조인하여 부서번호,부서명,이름,급여 출력하세요.

SELECT D.DEPTNO,D.DNAME,E.ENAME,E.SAL
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

Q2)이름이 'ALLEN'인 사원의 부서명을 출력하세요.

SELECT E.ENAME "사원이름",D.DNAME "부서명"
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND E.ENAME='ALLEN';

Q3)사원의 이름,부서번호,부서명,급여를 출력하시오.
(단,DEPT테이블의 모든 부서를 출력하세요.OUTER조인)

SELECT E.ENAME,D.DEPTNO,D.DNAME,E.SAL
FROM EMP E,DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO;

Q4)EMP테이블에 있는 EMPNO와 MGR을 이용해서 아래와 같이 출력되도록 하세요.
사원번호 사원이름 매니져이름 매니져직업
.......

SELECT E1.EMPNO,E1.ENAME,E2.ENAME,E2.JOB
FROM EMP E1,EMP E2
WHERE E1.MGR=E2.EMPNO
ORDER BY E1.EMPNO;

## ANSI조인

1) INNER 조인

예1) 사원번호,이름,부서명,부서위치를 조회하시오.

SELECT E.EMPNO,E.ENAME,D.DNAME,D.LOC
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;

예2) 급여가 2000이상인 사원번호,이름,부서명,급여를 조회하시오.

SELECT E.EMPNO,E.ENAME,D.DNAME,E.SAL
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE E.SAL>=2000;

Q1) 부서명'ACCOUNTING'에 근무하는 사원들의 이름,급여,부서명 출력하세요.
(INNER JOIN사용)

SELECT E.ENAME,E.SAL,D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE D.DNAME='ACCOUNTING';

2) OUTER조인

- RIGHT OUTER JOIN,LEFT OUTER JOIN,FULL OUTER JOIN

예1) 부서번호,부서명,근무사원이름,급여 출력

SELECT D.DEPTNO,D.DNAME,E.ENAME,E.SAL
FROM DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO=E.DEPTNO;

[ ROWID와 ROWNUM ]

- 오라클에서 테이블을 생성하면 자동으로 생성되는 컬럼
- ROWID : ROW의 고유번호(중간에 ROW를 삭제해도 변하지 않음)
- ROWNUM(*) : 행의 인덱스(중간에 ROW삭제시 변함)

SQL> SELECT ROWID,ROWNUM,EMPNO FROM EMP;

ROWID                  ROWNUM      EMPNO
------------------ ---------- ----------
AAAE+4AABAAAK/hAAA          1       7369
AAAE+4AABAAAK/hAAB          2       7499
AAAE+4AABAAAK/hAAC          3       7521
AAAE+4AABAAAK/hAAD          4       7566
...

예1) EMP테이블에서 급여가 가장 낮은 사람 10명의 번호,이름,급여를 출력하세요.(서브쿼리==>인라인뷰)

SELECT EMPNO,ENAME,SAL
FROM (SELECT *
FROM EMP
ORDER BY SAL ASC)
WHERE ROWNUM<11;

Q1) 부서별 월급총액이 가장 많은 순서대로 2위까지 해당하는 부서와 월급총액을 출력하세요.

SELECT DEPTNO,
              SUMSAL
FROM ( SELECT DEPTNO,
                          SUM(SAL) "SUMSAL"
                FROM EMP
            GROUP BY DEPTNO
            ORDER BY SUM(SAL) DESC)
WHERE ROWNUM<3;

Q2) EMP테이블에서 가장 많은 JOB의 이름을 출력하시오.

SELECT * FROM (
      SELECT JOB,COUNT(*) CNT
      FROM EMP
      GROUP BY JOB
      ORDER BY CNT DESC)
WHERE ROWNUM=1;

저작자표시

'DBMS > 오라클기초' 카테고리의 다른 글

오라클 기초#6(Transaction Control Language) (0)	2013.01.27
오라클 기초#5(Data Manipulation Language) (0)	2013.01.27
오라클 기초#3 (subquery) (0)	2013.01.27
오라클 기초#2(함수) (0)	2013.01.27
오라클 기초#1 (0)	2013.01.27

오라클 기초#3 (subquery)

2013. 1. 27. 12:09

[ 서브쿼리]

- 하나의 SQL문장절에 포함된 또 다른 SELECT문장.
두번 질의를 해야 하는 얻을 수 있는 결과를 한번의 질의로 해결이 가능하게 하는 쿼리

- 특징

* 괄호를 묶어야 한다.

* 실행순서는 대부분 서브쿼리가 먼저 수행되고 메인쿼리가 수행된다.

* 서브쿼리는 아래와 같은 위치에 올 수 있다.
SELECT/DELETE/UPDATE 문장의 FROM절/WHERE절
INSERT 문장의 INTO절
UPDATE문장의 SET절

1) 단일행 서브쿼리

- 서브쿼리의 실행결과가 하나의 컬럼과 하나의 행만을 리턴해주는 쿼리. 즉 하나의 데이터만을 리턴

예1) 사원번호가 7369인 사원과 같은 직업을 갖는 사원들의 사원번호, 이름,직업을 출력하시오.

SELECT JOB FROM EMP WHERE EMPNO=7369;-- 서브쿼리

SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB='CLERK'; -- 메인쿼리

==> 위의 작업을 서브쿼리로 바꿔보면

SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE JOB=(SELECT JOB
FROM EMP
WHERE EMPNO=7369);

EMPNO ENAME JOB
------- -------- --------
7369    SMITH   CLERK
7900    JAMES CLERK
7934    MILLER CLERK

예2) 급여가 평균급여보다 많은 사원들의 사원번호,급여를 출력하시오.

SQL> SELECT ROUND(AVG(SAL)) FROM EMP; -- 서브쿼리

SQL> SELECT EMPNO,SAL FROM EMP WHERE SAL>2077; -- 메인쿼리

-->위의 작업을 서브쿼리로 바꿔보기

SELECT EMPNO,SAL
FROM EMP
WHERE SAL>(SELECT ROUND(AVG(SAL)) FROM EMP);

Q1) 'ALLEN'과 같은 부서에 근무하는 사원들의 모든 정보를 출력하세요.

SELECT *
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='ALLEN')
AND ENAME!='ALLEN';

Q2) 'MARTIN'과 같은 급여를 받는 사원의 이름,사원번호,급여를 출력하세요.

SELECT ENAME,EMPNO,SAL
FROM EMP
WHERE SAL=(SELECT SAL FROM EMP WHERE ENAME='MARTIN') ;

Q3) 최고급여를 받는 사원의 모든 정보를 출력하세요.

SELECT * FROM EMP
WHERE SAL=(SELECT MAX(SAL) FROM EMP);

Q4) 사원번호가 7369인 사원과 같은 직업을 갖고, 7369인 사원보다 많은 급여를 받는 사원을 조회하시오.

SELECT * FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7369) AND
SAL>(SELECT SAL FROM EMP WHERE EMPNO=7369);

Q5) 부서의 최소급여가 30번 부서의 최소급여보다 많은 부서번호와 최소 급여를 출력해 보세요.

SELECT DEPTNO,MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL)>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

[결과]
DEPTNO MIN(SAL)
-------- ----------
10 1300

2) 복수행 서브쿼리

- 서브쿼리의 실행결과가 여러개의 행을 리턴

- 연산자 : IN,ANY,ALL,EXISTS

예1) 부서번호가 10번인 사원의 급여와 같은 급여를 받는 사원의 이름과 급여를 출력

SELECT SAL FROM EMP WHERE DEPTNO=10;-- 서브쿼리

SAL
----------
2450
5000
1300

==> 위와 같이 서브쿼리의 결과가 여러행인 경우는 =을 쓸 수 없다.

SELECT ENAME,SAL
FROM EMP
WHERE SAL IN(SELECT SAL
FROM EMP
WHERE DEPTNO=10);

예2) 부서번호가 10번인 사원의 급여과 같거나 많은 급여를 받는 사원의 이름과 급여를 출력(IN연산자는 >=등과 같은 연산자 사용못함)

SELECT ENAME,SAL
FROM EMP
WHERE SAL>=ANY(SELECT SAL
FROM EMP
WHERE DEPTNO=10);

예3) 30번 부서의 가장많은 급여보다 더 많은 급여를 받는 사원번호,급여 출력하시오.

SELECT SAL FROM EMP WHERE DEPTNO=30;-- 서브쿼리

-- ALL은 모든 조건이 모두 만족해야 함

SELECT EMPNO,SAL
FROM EMP
WHERE SAL>=ALL(SELECT SAL
FROM EMP
WHERE DEPTNO=30);

예4) EXISTS - 데이터가 존재하면 수행

SELECT * FROM DEPT
WHERE EXISTS(SELECT *
FROM EMP
WHERE DEPTNO=30);

-- 부하직원이 있는 사원을 출력하시오.(상호관련서브쿼리)
SELECT *
FROM EMP E1
WHERE EXISTS(SELECT * FROM EMP E2
WHERE E2.MGR=E1.EMPNO);

EMPNO ENAME JOB          MGR HIREDATE SAL COMM DEPTNO
------ --------- ----         ----- ---------- ---- ------- -------
7902    FORD    ANALYST     7566   81/12/03 3000 20
7698    BLAKE   MANAGER   7839   81/05/01 2850 30
7839    KING      PRESIDENT          81/11/17 5000 10

3) 복수컬럼 서브쿼리

- 서브쿼리의 실행결과가 여러개의 컬럼과 여러개의 행을 리턴해 주는 쿼리

예1) 부서번호가 30번인 사원들의 급여와 커미션이 같은 사원들의 이름과 부서번호,커미션을 출력하시오.

SELECT ENAME,DEPTNO,COMM
FROM EMP
WHERE (SAL,COMM) IN(SELECT SAL,COMM FROM EMP WHERE DEPTNO=30);

저작자표시

'DBMS > 오라클기초' 카테고리의 다른 글

오라클 기초#6(Transaction Control Language) (0)	2013.01.27
오라클 기초#5(Data Manipulation Language) (0)	2013.01.27
오라클 기초#4 ( JOIN, ROWID, ROWNUM ) (0)	2013.01.27
오라클 기초#2(함수) (0)	2013.01.27
오라클 기초#1 (0)	2013.01.27

오라클 기초#2(함수)

2013. 1. 27. 11:37

[ 함수 (Function) ]

- 어떠한 일을 수행하는 기능으로써 주어진 인수(argument)를 재료로 처리를 하여 그 결과값을 반환함.

< 함수의 종류 >

- 단일행함수 : 하나의 행(row)당 하나의 결과값을 반환하는 함수

- 복수행함수 : 여러개의 행당 하나의 결과값을 반환하는 함수

< 단일행함수 >

<1> 문자함수

- CHR(아스키코드)

SQL> SELECT CHR(65) FROM DUAL;

    [결과]
    CH
    --
    A

- CONCAT(컬럼명,'붙일문자')

SQL> SELECT CONCAT(ENAME,'IS A'),JOB FROM EMP;

    [결과]
   CONCAT(ENAME,'ISA') JOB
------------------------ -------------
    SMITHIS A                    CLERK
    ALLENIS A                    SALESMAN
    WARDIS A                     SALESMAN
    JONESIS A                    MANAGER
        ...

- INITCAP('문자열'):시작문자를 대문자로 변환

SQL> SELECT INITCAP('the lion') FROM DUAL;

INITCAP('THELION
----------------
The Lion

- LOWER('문자열') : 소문자로 변환

SQL> SELECT LOWER('THE LION') FROM DUAL;

    LOWER('THELION')
    ----------------
    the lion

- LPAD('문자열',자릿수,'채울문자')

SQL> SELECT LPAD(SAL,10,'#') FROM EMP;

LPAD(SAL,10,'#')
------------------------
#######800
######1600
######1250
######2975
######1250
######2850
######2450

- RPAD('문자열',자릿수,'채울문자')

SQL> SELECT RPAD(SAL,10,'#') FROM EMP;

RPAD(SAL,10,'#')
-------------------
800#######
1600######
1250######
2975######
1250######
2850######
2450######
5000######

- LTRIM('문자열1','없앨문자열')

SQL> SELECT LTRIM('THE LION','THE') FROM DUAL;

LTRIM('THE
----------
LION

SQL> SELECT LTRIM('LION THE','THE') FROM DUAL;

LTRIM('LIONTHE',
----------------
LION THE

SQL> SELECT LTRIM(' LION',' ') FROM DUAL;

LTRIM('L
--------
LION

- RTRIM('문자열1','없앨문자열')

SQL> SELECT RTRIM('APPLE IS','IS') FROM DUAL;

    RTRIM('APPLE
    ------------
    APPLE

SQL> SELECT RTRIM('APPLE ',' ') FROM DUAL;

RTRIM('APP
----------
APPLE

SQL> SELECT RTRIM('APPLE%%%%%','%') FROM DUAL;

RTRIM('APP
----------
APPLE

- REPLACE(문자열,'원래문자열','바꿀문자열')

SQL> SELECT REPLACE('MY NAME','NAME','JOB') FROM DUAL;

REPLACE('MYN
------------
MY JOB

- SUBSTR('문자열',위치,갯수)

SQL> SELECT SUBSTR('ABCDEF',3,2) FROM DUAL;

    SUBS
    ----
    CD

- INSTR('문자열1','위치찾는문자열') : 문자의 위치 찾기 (없으면 0)

SQL> SELECT INSTR('SONG@DAUM.NET','@') FROM DUAL;

INSTR('SONG@DAUM.NET','@')
--------------------------
5

SQL> SELECT INSTR('SONGDAUM.NET','@') FROM DUAL;

    INSTR('SONGDAUM.NET','@')
    -------------------------
                0

SQL> SELECT INSTR('SONGDAUM.NET','NET') FROM DUAL;

    INSTR('SONGDAUM.NET','NET')
    ---------------------------
                 10

- LENGTH('문자열') : 문자열 길이

SQL> SELECT ENAME,LENGTH(ENAME) FROM EMP;

ENAME LENGTH(ENAME)
---------- -------------
SMITH 5
ALLEN 5
WARD 4

- NVL(컬럼명,값) : 컬럼값이 NULL인경우 값으로 채우기

SQL> SELECT ENAME,NVL(COMM,0) FROM EMP;

    ENAME                NVL(COMM,0)
    -------------------- -----------
    SMITH                          0
    ALLEN                        300
    WARD                         500
    JONES                          0
    MARTIN                      1400
    BLAKE                          0
    CLARK                          0

<2> 날짜함수

- SYSDATE(*) :현재날짜 얻어오기

SQL> SELECT SYSDATE FROM DUAL;

    SYSDATE
    --------
    12/11/20

- ADD_MONTHS(날짜,더할개월수)

SQL> SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;

    ADD_MONT
    --------
    13/02/20

SQL> SELECT ENAME,ADD_MONTHS(HIREDATE,6) FROM EMP;

    ENAME      ADD_MONT
    ----------- --------
    SMITH         81/06/17
    ALLEN         81/08/20
    WARD          81/08/22
    JONES         81/10/02
    MARTIN       82/03/28

- MONTHS_BETWEEN(날짜1,날짜2) : 날짜1과 날짜2의 개월수

SQL> SELECT ENAME,HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) "근무개월수"
2 FROM EMP;

    ENAME   HIREDATE 근무개월수
    --------- -------- ----------
    SMITH     80/12/17 383.111281
    ALLEN     81/02/20        381
    WARD      81/02/22 380.949991
    JONES    81/04/02 379.595152
    MARTIN   81/09/28 373.756443

<3> 날짜를 문자로 변환하는 함수(**)

- TO_CHAR(날짜,'형식')

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;

    TO_CHAR(SYSDATE,'YYY
    --------------------
    2012-11-20

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM DUAL;

    TO_CHAR(SYSDATE,'YYYY-MM-DDHH:MI:SS')
    --------------------------------------
    2012-11-20 11:04:26

SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD AM HH:MI:SS') FROM DUAL;

    TO_CHAR(SYSDATE,'YYYY/MM/DDAMHH:MI:SS')
    ----------------------------------------------------
    2012/11/20 오전 11:05:54

SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;--24시간단위

    TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS'
    --------------------------------------
    2012/11/20 11:07:22

SQL> SELECT TO_CHAR(SYSDATE,'YYYY"년"MM"월"DD"일" HH"시"MI"분"SS"초"') "현재시간"
2 FROM DUAL;

       현재시간
    --------------------------------------------
    2012년11월20일 11시10분46초

<4> 문자를 날짜로 변환하는 함수

- TO_DATE('날짜에해당하는문자열','해석할서식')

SQL> SELECT TO_DATE('20-11-2012','DD-MM-YYYY') FROM DUAL;

    TO_DATE(
    --------
    12/11/20

SQL> SELECT TO_DATE('1980-11-20','YYYY-MM-DD') FROM DUAL;

    TO_DATE(
    --------
    80/11/20

<5> 수학관련 함수

- ABS(숫자) : 절대값

SQL> SELECT ABS(-10) FROM DUAL;

      ABS(-10)
    ----------
        10

- CEIL(숫자) : 올림값구하기

SQL> SELECT CEIL(3.2) FROM DUAL;

     CEIL(3.2)
    ----------
         4

- FLOOR(숫자) : 내림값 구하기

SQL> SELECT FLOOR(3.2) FROM DUAL;

    FLOOR(3.2)
    ----------
         3

- ROUND(숫자) : 반올림값 구하기

SQL> SELECT ROUND(3.2) FROM DUAL;

    ROUND(3.2)
    ----------
         3

SQL> SELECT ROUND(3.7) FROM DUAL;

    ROUND(3.7)
    ----------
         4

SQL> SELECT ROUND(3.1256,2) FROM DUAL;--소수점2째자리에서반올림적용

    ROUND(3.1256,2)
    ---------------
           3.13

SQL> SELECT ROUND(3.1256,3) FROM DUAL;--소수점3째자리에서반올림적용

    ROUND(3.1256,3)
    ---------------
          3.126

- MOD(숫자1,나눌값) : 나머지 구하기

SQL> SELECT MOD(5,3) FROM DUAL;

      MOD(5,3)
    ----------
         2

- TRUNC(숫자,절삭할 소숫점자릿수)

SQL> SELECT TRUNC(2.3456,3) FROM DUAL;

    TRUNC(2.3456,3)
    ---------------
          2.345

< 복수행 함수(*) >

<1> COUNT(컬럼명) : 갯수 얻어오기

SQL> SELECT COUNT(EMPNO) "사원수" FROM EMP;

        사원수
    ----------
        12

SQL> SELECT COUNT(COMM) FROM EMP; --NULL은 COUNT하지 않음

    COUNT(COMM)
    -----------
          4

SQL> SELECT COUNT(*) FROM EMP;

<2> SUM(컬럼명) : 컬럼의 합 구하기

-- 전체사원의 급여의 총합을 구해보세요.

SQL> SELECT SUM(SAL) "급여합" FROM EMP;

<3> AVG(컬럼명) : 컬럼의 평균 구하기

SQL> SELECT AVG(SAL) "급여평균" FROM EMP;

<4> MIN(컬럼명),MAX(컬럼명) : 컬럼의 최소값,컬럼의 최대값 구하기

SQL> SELECT MIN(SAL) "최소급여",MAX(SAL) "최대급여" FROM EMP;

(4) GROUP BY : 컬럼을 기준으로 그룹으로 묶음

-- EMP테이블에서 부서별 급여의 평균을 구하시오.

     SELECT DEPTNO,AVG(SAL) 급여평균
     FROM EMP
     GROUP BY DEPTNO;

-- EMP테이블에서 급여가 2000이상인 사원들의 부서별 급여총합을 구하세요.

     SELECT DEPTNO,SUM(SAL) "급여총합"
     FROM EMP
     WHERE SAL>=2000
     GROUP BY DEPTNO;

-- EMP테이블에서 부서별 최대급여,최소급여를 출력해 보세요.(부서번호로 오름차순정렬)

     SELECT DEPTNO,MAX(SAL) "최대급여",MIN(SAL) "최소급여"
     FROM EMP
     GROUP BY DEPTNO
     ORDER BY DEPTNO ASC

-- 급여가 2000이상인 사원들의 부서별 평균급여을 구하고 평균급여가 높은순으로 정렬하시오.

    SQL> SELECT DEPTNO,AVG(SAL) "AVG"
      2 FROM EMP
      3 WHERE SAL>=2000
      4 GROUP BY DEPTNO
      5 ORDER BY AVG(SAL) DESC; -- ORDER BY AVG DESC :ALIAS사용가능!

-- 각 직업별 평균급여와 최대급여,최소급여를 구해보세요.

      SQL> SELECT JOB,AVG(SAL) "평균급여",MAX(SAL) "최대급여",MIN(SAL) "최소급여"
           2 FROM EMP
           3 GROUP BY JOB;

JOB              평균급여   최대급여   최소급여
------------- ---------- -------- -------
CLERK           1016.66667      1300      800
SALESMAN            1400       1600     1250
PRESIDENT            5000       5000     5000

-- 각 부서별 같은 업무를 하는 사람의 평균급여를 구하여 부서별,업무별로 오름차순 정렬해서 출력

     SELECT DEPTNO,JOB,AVG(SAL)
     FROM EMP
     GROUP BY DEPTNO,JOB
     ORDER BY DEPTNO,JOB ;

DEPTNO JOB             AVG(SAL)
--------- ------------ ----------
         10 CLERK             1300
         10 MANAGER        2450
         10 PRESIDENT      5000
         20 ANALYST         3000
               ......

-- 사원들의 부서별 평균급여를 출력하시오.단 평균급여가 2000이상인 부서만 출력되도록 하시오.
## WHERE 절에는 그룹함수(AVG,MAX,MIN,SUM,..을 사용할 수 없다.)

     SELECT DEPTNO,AVG(SAL)
     FROM EMP
     GROUP BY DEPTNO
     HAVING AVG(SAL)>=2000;

## SELECT 구문의 실행순서(**)

SELECT	컬럼명,..	5
FROM	테이블명	1
WHERE	조건절	2
GROUP BY	절	3
HAVING	절	4
ORDER BY	절	6

     SELECT DEPTNO,AVG(SAL) AG
     FROM EMP
     GROUP BY DEPTNO
     HAVING AG>=2000; -- HAVING절에서는 ALIAS를 사용할 수 없다.

     SELECT DEPTNO,AVG(SAL) AG
     FROM EMP
     GROUP BY DEPTNO
     HAVING AVG(SAL)>=2000
     ORDER BY AG DESC; -- ALIAS사용가능!

-- 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력해보세요.

    SQL> SELECT JOB,COUNT(*) 인원수
      2 FROM EMP
      3 GROUP BY JOB
      4 HAVING COUNT(*)>=4;

    JOB                    인원수
    ------------------ ----------
    SALESMAN                    4

Q1) 10번 부서의 급여의 평균,최고,최저,인원수를 구하세요.

    SELECT AVG(SAL),MAX(SAL),MIN(SAL),COUNT(*)
    FROM EMP
    WHERE DEPTNO=10;

Q2) 각 부서별 평균급여,최고급여,최저급여를 구하세요. 단 평균급여가 2000이상인 부서만 출력하세요.

    SQL> SELECT DEPTNO,AVG(SAL),MAX(SAL),MIN(SAL)
          2 FROM EMP
          3 GROUP BY DEPTNO
          4 HAVING AVG(SAL)>=2000;

    DEPTNO   AVG(SAL)   MAX(SAL)   MIN(SAL)
    ---------- ---------- ---------- ----------
        20 2258.33333       3000        800
        10 2916.66667       5000       1300

Q3) 각 부서별 인원수를 구해보세요. 인원수가 3명 이상인 부서만 출력되도록 하세요.

    SQL> SELECT DEPTNO,COUNT(*)
          2 FROM EMP
          3 GROUP BY DEPTNO
          4 HAVING COUNT(*)>=3;

    DEPTNO   COUNT(*)
    ---------- ----------
        30          6
        20          3
        10          3

Q4) 각 부서별 평균급여,전체월급을 구하여 평균월급이 많은 순으로 출력하세요.

    SQL> SELECT DEPTNO,AVG(SAL) "AVG",SUM(SAL)
          2 FROM EMP
          3 GROUP BY DEPTNO
          4 ORDER BY AVG DESC;

    DEPTNO        AVG   SUM(SAL)
    ---------- ---------- ----------
        10 2916.66667       8750
        20 2258.33333       6775
        30 1566.66667       9400

(5) 기타함수

DECODE(대상컬럼,조건1,결과값1,조건2,결과값2
..., 기본값) 별칭

예)

SELECT JOB,

SAL,

           DECODE(JOB, 'ANALYST',SAL*1.1,
                                 'CLERK',SAL*1.2,
                                 'SALESMAN',SAL*1.3,
                           SAL) 보너스
FROM EMP;

    JOB                       SAL     보너스
    ------------- ---------- ----------
    CLERK                  800        960
    SALESMAN          1600       2080
    SALESMAN          1250       1625
    MANAGER            2975       2975
    SALESMAN          1250       1625
    MANAGER            2850       2850

CASE WHEN 조건1 THEN 결과값1
         WHEN 조건2 THEN 결과값2
        WHEN 조건3 THEN 결과값3
         ELSE 기본값
END 별칭

예)

SELECT JOB,SAL,
             CASE WHEN JOB='ANALYST' THEN SAL*1.1
                      WHEN JOB='CLERK' THEN SAL*1.2
                      WHEN JOB='SALESMAN' THEN SAL*1.3
                      ELSE SAL
            END BONUS
   FROM EMP;

-- 각 부서별 평균급여를 구하고 평균급여가 3000이상이면 높음, 2000이상이면 보통,1000이상이면 낮음을 출력하세요.

SELECT DEPTNO,AVG(SAL),
             CASE WHEN AVG(SAL)>=3000 THEN '높음'
                      WHEN AVG(SAL)>=2000 THEN '보통'
                     WHEN AVG(SAL)>=1000 THEN '낮음'
                      ELSE '판단불가'
               END "급여수준"
FROM EMP
GROUP BY DEPTNO;

   DEPTNO   AVG(SAL) 급여수준
    ------ ---------- --------------
           30 1566.66667 낮음
           20 2258.33333 보통
           10 2916.66667 보통
          ...

저작자표시

'DBMS > 오라클기초' 카테고리의 다른 글

오라클 기초#6(Transaction Control Language) (0)	2013.01.27
오라클 기초#5(Data Manipulation Language) (0)	2013.01.27
오라클 기초#4 ( JOIN, ROWID, ROWNUM ) (0)	2013.01.27
오라클 기초#3 (subquery) (0)	2013.01.27
오라클 기초#1 (0)	2013.01.27

PREV 1 2 3 NEXT

New