[ORACLE] JOIN과 숫자함수, 문자함수 실습

# 실습을 위하여 DEPT와 EMP 테이블을 사용하였습니다.

 

1. JOIN

- 오라클에서 조인을 사용하기 위한 조건은 다음과 같습니다.

 1) FROM 절에 2개 이상의 테이블 설정

2) 각 테이블은 콤마(,)로 구분

3) SELECT 절의 컬럼 앞에 어느 테이블 컬럼인지 기재

SQL> SELECT EMP.ENAME, EMP.JOB, DEPT.DEPTNO, DEPT.LOC
  2  FROM DEPT, EMP
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO;

ENAME      JOB           DEPTNO LOC
---------- --------- ---------- -------------
CLARK      MANAGER           10 NEW YORK
KING       PRESIDENT         10 NEW YORK
MILLER     CLERK             10 NEW YORK
JONES      MANAGER           20 DALLAS
FORD       ANALYST           20 DALLAS
ADAMS      CLERK             20 DALLAS
SMITH      CLERK             20 DALLAS
SCOTT      ANALYST           20 DALLAS
WARD       SALESMAN          30 CHICAGO
TURNER     SALESMAN          30 CHICAGO
ALLEN      SALESMAN          30 CHICAGO
JAMES      CLERK             30 CHICAGO
BLAKE      MANAGER           30 CHICAGO
MARTIN     SALESMAN          30 CHICAGO

# ALIAS 사용

- 해당 테이블의 이름을 일일히 입력하기란 번거롭습니다. 쿼리문을 간결하게 만들기 위해 테이블명에 alias를 지정하여 쿼리를 실행하면 동일 결과값이 나옵니다.

- 여기서 DEPT 테이블을 A로 EMP 테이블에 B로 Alias를 설정 하였습니다.

SQL> SELECT B.ENAME, B.JOB, A.DEPTNO, A.LOC
  2  FROM DEPT A, EMP B
  3  WHERE B.DEPTNO=A.DEPTNO;

ENAME      JOB           DEPTNO LOC
---------- --------- ---------- -------------
CLARK      MANAGER           10 NEW YORK
KING       PRESIDENT         10 NEW YORK
MILLER     CLERK             10 NEW YORK
JONES      MANAGER           20 DALLAS
FORD       ANALYST           20 DALLAS
ADAMS      CLERK             20 DALLAS
SMITH      CLERK             20 DALLAS
SCOTT      ANALYST           20 DALLAS
WARD       SALESMAN          30 CHICAGO
TURNER     SALESMAN          30 CHICAGO
ALLEN      SALESMAN          30 CHICAGO
JAMES      CLERK             30 CHICAGO
BLAKE      MANAGER           30 CHICAGO
MARTIN     SALESMAN          30 CHICAGO

14 rows selected.

# AND 조건 추가

- 해당 쿼리에 조건을 추가하고 싶을시 where절에 'AND'나 'OR' 연산자를 사용합니다.

- 아래 예시에서는 AND 연산자를 통하여 DEPT 테이블 LOC 컬럼 데이터가 'DALLAS'인 조건을 추가하여 값을 출력합니다.

SQL> SELECT B.ENAME, B.JOB, A.DEPTNO, A.LOC
  2  FROM DEPT A, EMP B
  3  WHERE B.DEPTNO=A.DEPTNO AND A.LOC='DALLAS';

ENAME      JOB           DEPTNO LOC
---------- --------- ---------- -------------
JONES      MANAGER           20 DALLAS
FORD       ANALYST           20 DALLAS
ADAMS      CLERK             20 DALLAS
SMITH      CLERK             20 DALLAS
SCOTT      ANALYST           20 DALLAS

 

2. 숫자 함수

- select 절에서 컬럼에 숫자함수를 적용하여 원하는 함수를 적용하여 출력할 수 있습니다.

 

1) ceil (올림함수)

- 해당 컬럼 데이터가 소수점일시 반올림하여 해당 결과를 출력합니다.

SQL> select ceil(1.5+2.7) from dual;

CEIL(1.5+2.7)
-------------
            5

2) mod (나머지값 출력)

- mod(컬럼데이터, 나누고자하는 숫자) : 컬럼데이터에서 숫자로 나눈 나머지값을 출력합니다.

- 해당 쿼리는 50을 6으로 나눈 값 8과 나머지 2중 나머지값을 출력하였습니다.

SQL> select mod(50,6) from dual;

MOD(50,6)
----------
         2

 

3) round (반올림)

- round는 해당 소수점 첫째자리에서 0.5이상이면 반올림하여 정수값을 출력합니다.

SQL> select round(6.1) from dual;
ROUND(6.1)
-------------

            6

- round(num,-1)  : 소수점 앞에서 반올림

- 지정값이 -1일시 소수점 첫째자리 앞인 1자리에서 반올림합니다.

SQL> select round(6.1,-1) from dual;

ROUND(6.1,-1)
-------------
           10

 

4) Trunc(m,n)

- 해당 조건에 맞는 자리수 이후 데이터를 절삭한 후 결과값을 출력합니다.

- m : 함수를 수행할 숫자, 컬럼명 

- n  : 절삭을 수행할 자리수

SQL> select trunc(sal,-2) from emp
  2  where sal>2000;

TRUNC(SAL,-2)
-------------
         2900
         2800
         2400
         3000
         5000
         3000

6 rows selected.

 

5) floor(num)

- num보다 작은 정수중 가장 큰 정수 출력합니다.

SQL> select floor(5.9) from dual;

FLOOR(5.9)
----------
         5

 

3. 문자함수

1) translate (함수수행할 문자, 검색문자, 치환문자)

- 해당 컬럼데이터에서 검색문자를 찾아 해당 문자를 지정한 값으로 치환합니다.

SQL> select name, mobile_num, translate(mobile_num, '1234567890'||MOBILE_NUM, '1234567890') tel
  2  from fun_test;

NAME       MOBILE_NUM           TEL
---------- -------------------- --------------------
A          010-4123-7234        01041237234
B          010-6337-6995        01063376995
C          010-5033-6752        01050336752

SQL> select name, mobile_num, translate(mobile_num, '1234567890-','1234567890') tel
  2  from fun_test;

NAME       MOBILE_NUM           TEL
---------- -------------------- --------------------
A          010-4123-7234        01041237234
B          010-6337-6995        01063376995
C          010-5033-6752        01050336752

 

2) length

- 문자의 길이를 출력합니다.

SQL> select length(mobile_num) from fun_test;

LENGTH(MOBILE_NUM)
------------------
                13
                13
                13

 

3) vsize

- 데이터의 크기를 추출합니다.

SQL> select vsize(mobile_num) from fun_test;

VSIZE(MOBILE_NUM)
-----------------
               13
               13
               13

 

4) 시간 추출

- sysdata, systimestamp 로 현재 날짜와 시간을 출력합니다.

# 날짜 포맷 변경
SQL> alter session set NLS_DATE_FORMAT='YYYY/MM/DD';
Session altered.

# 현재 날짜와 시간 출력
SQL> SELECT SYSDATE,SYSTIMESTAMP FROM DUAL;

SYSDATE    SYSTIMESTAMP
---------- ----------------------------------------
2021/07/15 15-JUL-21 06.40.55.109966 PM +09:00