[Oracle] SQL 주요함수 -1-

---

2015. 7. 7. 15:13 Naver Blog 백업용

---

DUAL 테이블

- 기본적으로 ORACLE에서 제공해주는 테이블

- 테스트를 위한 가상테이블, 더미테이블

- 가상의 컬럼, 레코드(각각 하나씩만)를 제공해주는 테이블

- SQL문의 형식이 SELECT와 FROM 모두 채워져야하기 때문에 생긴 테이블

- FROM에 쓸 테이블이 없어 EMP를 사용해보겠습니당

ex) SELECT SYSDATE FROM EMP; -> 14개 ROWS가 출력됨

SELECT SYSDATE FROM DUAL; -> 1개 ROW가 출력됨

 

 

숫자 관련 함수

- 함수(매개변수) 의 형태로 주로 사용됨

- ABS: 절대값 

- FLOOR: 소수점아래 버려 / ROUND: 올림

ex) SELECT 34.56, FLOOR(34.56), ROUND(34.56) FROM DUAL;

ROUND의 소수점 N째자리 올림

ex) SELECT 34.56, ROUND(34.56,1) FROM DUAL; --> 34.6

SELECT 34.56, ROUND(34.56,-1) FROM DUAL; --> 30

- TRUNC: 특정 자릿수에서 잘라내

ex) SELECT TRUNC(34.5678,1) FROM DUAL;

 

 

문자 처리 함수

- ORACLE은 보통 1부터 시작한다.

- LOWER, UPPER : 소문자 대문자로 바꿔

- INITCAP : 각 단어의 첫글자만 대문자로 바꿔

- LENGTH : 단어 길이 리턴

- SUBSTR : 문자열 일부 추출

ex) SELECT SUBSTR(ENAME, 1, 3) FROM EMP;

SUBSTR(ENAME,1,3)

------------------------

SMI

ALL

WAR

JON

- INSTR : 특정 문자의 위치를 구해

ex)  SELECT INSTR('WELCOME TO ORACLE', 'O', 1, 2) FROM DUAL;

INSTR('WELCOMETOORACLE','O',1,2)

--------------------------------------------

10

- LPAD/RPAD : 특정 기호로 채워

ex) SELECT LPAD('ORACLE',10,'*') FROM DUAL; -> ****ORACLE

SELECT RPAD('ORACLE',10,'*') FROM DUAL; -> ****ORACLE

- LTRIM/RTRIM : 공백 문자를 삭제해

ex) SELECT LTRIM('    ORACLE') FROM DUAL; -> ORACLE

- TRIM : 가장 왼쪽이나 오른쪽에 (임의로 들어간) 특정 문자를 잘라내

ex) SELECT TRIM('P' FROM 'PPPPPPPORACLEPPPPPPPPPPPP') FROM DUAL; -> ORACLE

하지만, 왼쪽이나 오른쪽에 있지 않은경우는 그대로 출력

ex) SELECT TRIM('P' FROM 'PPPPPPPORACPPPPLE') FROM DUAL; -> ORACPPPPLE

 

- 단일행 / 그룹함수

단일행함수

레코드를 하나하나 돌면서 FUNCTION 적용한 결과를 리턴

그룹함수

레코드를 그룹으로 FUNCTION 적용한 결과를 리턴

 

DBGUIDE 사이트 -> 분석/설계 경진대회

 

-문제- 

 

1. "SMITH의 급여 800만원"의 유형으로 출력할 수 있도록 concat함수를 
    써서   작업하세요.  단, 급여가 1000만원 미만인 직원에 대해서 작업 

 

SELECT CONCAT(CONCAT(ENAME,'의 급여'),CONCAT(SAL,'만원'))

FROM EMP 

WHERE SAL<1000;

 

2. 입사일이 81년인 직원에 대해서 각 직원의 이름과  입사일을 나타내시오.

    (substr이용) 

 

SELECT ENAME, SUBSTR(HIREDATE,4,5) 

FROM EMP 

WHERE HIREDATE BETWEEN '81/01/01' AND '81/12/31';

 

SELECT ENAME, HIREDATE

FROM EMP

WHERE SUBSTR(HIREDATE,1,2)=81;

 

3. 각 직원의 이름,job,급여를 나타내되 급여는 5자리로   나타내며 부족한 
    자릿수는 *로 표시한다.  급여가 2000만원 이하인 직원만 나타내기

 

SELECT ENAME,JOB,RPAD(SAL,5,'*') 

FROM EMP 

WHERE SAL<2000;

 

 

4. 3번의 결과에서 *를 없애고 출력해보세요..단,함수를 이용하여 작업 

 

SELECT ENAME,JOB,TRIM('*' FROM RPAD(SAL,5,'*')) 

FROM EMP 

WHERE SAL<2000;

 

5. emp 테이블에서 scott의 사원번호, 성명, 담당업무(소문자로), 부서번호를조회한다.

 

SELECT EMPNO, ENAME, LOWER(JOB), DEPTNO 

FROM EMP 

WHERE ENAME='SCOTT';

 

SELECT EMPNO, ENAME, LOWER(JOB), DEPTNO

FROM EMP

WHERE ENAME = UPPER('scott');

 

OR

 

SELECT EMPNO, ENAME, LOWER(JOB), DEPTNO

FROM EMP

WHERE LOWER(ENAME) = 'scott';

 

 

6. emp 테이블에서 이름의 첫글자가 'K'보다 크고 'Y'보다 작은 사원의 사원번호, 이름, 업무, 급여, 부서번호를 조회한다. 단, 이름순으로 정렬하여라.

 

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO 

FROM EMP 

WHERE SUBSTR(ENAME,1,1) BETWEEN 'K' AND 'Y' 

ORDER BY ENAME;

 

* a(65) A(97) 

 

7. emp 테이블에서 10번부서의 사원에 대하여 담당업무 중 좌측에 'A'를  삭제하고 급여 중 좌측의 1을 삭제하여 출력하여라. 

 

SELECT ENAME, TRIM('A' FROM JOB), TRIM(1 FROM SAL) 

FROM EMP 

WHERE DEPTNO=10;

 

SELECT ENAME, LTRIM(JOB,'A'), LTRIM(SAL,1)

FROM EMP

WHERE DEPTNO=10;