SELECT FIRST_NAME || ‘ ‘ || LAST_NAME AS NAME FROM EMPLOYEES;
||은 CONCAT
SELECT SALARY AS “본봉”, SALARY + SALARY * 0.3 AS “보너스”
FROM EMPLOYEES;
SELECT -SALARY FROM EMPLOYEES;
NVL, NVL2
SELECT EMPLOYEE_ID,
NVL(MANAGER_ID,’0’)
FROM EMPLOYEES;
NVL함수 내의 두 형태는 같아야함
SELECT NVL(NULL, ‘0’) FROM DUAL;
SELECT NVL2(NULL,10, 20) FROM DUAL;
–NULL이면 10
SELECT NVL2(0, 10, 20) FROM DUAL;
–NULL이 아니면 20
SELECT SALARY AS “본봉”,
SALARY + SALARY * NVL(COMMISSION_PCT,0)
AS “보너스”
FROM EMPLOYEES;
SELECT SALARY AS “본봉”,
NVL2(COMMISSION_PCT,SALARY + SALARY * COMMISSION_PCT, SALARY)
AS “보너스”
FROM EMPLOYEES;
NULLIF
SELECT NULLIF( 10 , 5 + 5 ) FROM DUAL;
COALESCE
SELECT COALESCE(NULL, NULL,1,2,3) FROM DUAL;
– 1출력
SYSDATE
SELECT SYSDATE FROM DUAL;
SELECT JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = ‘AD_PRES’
OR JOB_ID = ‘AD_VP’;
IN
SELECT JOB_ID
FROM EMPLOYEES
WHERE JOB_ID IN(‘AD_PRES’,’AD_VP’);
BETWEEN
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY BETWEEN 10000 AND SALARY ;
IS NULL
SELECT EMPLOYEE_ID,
COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL;
LIKE
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE ‘A%’;
–A로 시작하는 문자열에 대해서
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE ‘A_’;
–A로시작하는 두글자 검색
SELECT EMPLOYEE_ID,
FIRST_NAME
FROM EMPLOYEES
ORDER BY FIRST_NAME DESC;
SELECT EMPLOYEE_ID, HIRE_DATE
FROM EMPLOYEES;
LOWER
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE LOWER(FIRST_NAME) LIKE ‘al%’;
INITCAP
SELECT INITCAP(‘database managemaent system’) from dual;
–맨앞만 대문자로
SELECT INSTR( ‘DATABASE MANAGEMENT SYSTEM’ , ‘BASE’ ) FROM DUAL;
– BASE의 위치 찾기 (DB는 대부분 시작 인덱스가 1이다. )
SELECT CEIL( 4.99) FROM DUAL;
SELECT CEIL( 4.01) FROM DUAL;
– 해당 수보다 큰 정수값 (5 출력됨)
SELECT FLOOR( 4.99 ) FROM DUAL;
SELECT FLOOR( 4.00001) FROM DUAL;
– 해당 수보다 작은 정수값
SELECT ROUND(3.3364,3) FROM DUAL;
– 넷째자리에서 반올림함(3.336 출력됨)
SELECT MOD (9999,6) FROM DUAL;
– 나머지
SELECT POWER ( 2,10) FROM DUAL;
–2의 10승
LPAD
SELECT EMPLOYEE_ID, LPAD(SALARY, 10 , ‘*’) FROM EMPLOYEES;
– 10칸을만들되 왼쪽 나머지는 *로 채울것
TRIM
SELECT TRIM( ‘#’ FROM ‘####DATA#BASE####’) FROM DUAL;
– 앞뒤 특정문자 제거
SELECT TRIM( ‘ ‘ FROM ‘ DATA BASE ‘) FROM DUAL;
– 앞뒤 공백 제거
SELECT LTRIM(‘ DATA BASE ‘,’ ‘) FROM DUAL;
– 왼쪽 공백 제거
REPLACE
SELECT REPLACE(‘ DATA BASE ‘, ‘ ‘ ,’’) FROM DUAL;
– 공백 제거
CEIL
SELECT CEIL( 4.99) FROM DUAL;
SELECT CEIL( 4.01) FROM DUAL;
– 해당 수보다 큰 정수값 (5 출력됨)
FLOOR
SELECT FLOOR( 4.99 ) FROM DUAL;
SELECT FLOOR( 4.00001) FROM DUAL;
– 해당 수보다 작은 정수값
ROUND
SELECT ROUND(3.3364,3) FROM DUAL;
– 넷째자리에서 반올림함(3.336 출력됨)
TRUNC
SELECT TRUNC( 3.3864,1) FROM DUAL;
– 1자리만 남기고 버림(3.3나옴)
MOD
SELECT MOD (9999,6) FROM DUAL;
– 나머지
POWER
SELECT POWER ( 2,10) FROM DUAL;
–2의 10승
SIGN
SELECT SIGN(9999) FROM DUAL;
– 1나옴
SELECT SIGN(-9999) FROM DUAL;
– -1나옴
SELECT SIGN(0) FROM DUAL;
– 0나옴
ADD_MONTHS
SELECT ADD_MONTHS( SYSDATE, 3 ) FROM DUAL;
SELECT ADD_MONTHS( ‘15/01/01’,3 ) FROM DUAL;
SELECT LAST_DAY( SYSDATE ) FROM DUAL;
–해당월에 마지막 일
SELECT MONTHS_BETWEEN( ADD_MONTHS(SYSDATE,3), SYSDATE) FROM DUAL;
SELECT EMPLOYEE_ID,
JOB_ID,
ROUND(MONTHS_BETWEEN( END_DATE, START_DATE))
FROM JOB_HISTORY;
SELECT NEXT_DAY( SYSDATE, 7) FROM DUAL;
– 1->SUN 7->SAT
TO_CHAR
SELECT EMPLOYEE_ID,
TO_CHAR(SALARY, ‘99999’ )
FROM EMPLOYEES;
– 숫자 <-> 문자
– 5자리가 넘어가면 #####으로 표시된다.
SELECT EMPLOYEE_ID,
TO_CHAR(SALARY, ‘000099999’ )
FROM EMPLOYEES;
– 숫자 <-> 문자
–앞에 0000으로 채운다.
SELECT EMPLOYEE_ID,
TO_CHAR(SALARY, ‘999,999’ )
FROM EMPLOYEES;
– 숫자 <-> 문자
SELECT TO_CHAR( 3.141592, ‘9.99’) FROM DUAL;
– 3.14 나옴
SELECT TO_CHAR( 3.141592, ‘9999.9999’) FROM DUAL;
– 3.1416 나옴
SELECT EMPLOYEE_ID,
TO_CHAR(SALARY,’FM99999999’)
FROM EMPLOYEES;
– 왼쪽으로 다 땡겨준다
SELECT EMPLOYEE_ID,
TO_CHAR(SALARY,’FM$99999999’)
FROM EMPLOYEES;
– 왼쪽으로 다 땡겨준다
SELECT EMPLOYEE_ID,
TO_CHAR(SALARY,’FML99999999’)
FROM EMPLOYEES;
– 왼쪽으로 다 땡기고 원화를 넣어준다.
SELECT TO_CHAR(-12345,’99999MI’) FROM DUAL;
– 12345-
SELECT TO_CHAR(-12345,’S99999’) FROM DUAL;
– -12345 출력
SELECT TO_CHAR(12345,’S99999’) FROM DUAL;
– +12345 출력
SELECT TO_CHAR(-12345,’99999PR’) FROM DUAL;
– <12345> 출력
SELECT TO_CHAR(12345,’99999PR’) FROM DUAL;
SELECT TO_CHAR( 1234, ‘RN’) FROM DUAL;
–로마자로 표기
SELECT TO_CHAR( 1234, ‘XXXX’ ) FROM DUAL;
–16진수 대문자로 표기
SELECT TO_CHAR( 1234, ‘xxxx’ ) FROM DUAL;
–16진수 소문자로 표기
SELECT TO_NUMBER( ‘1234567’,’99999999’) FROM DUAL;
SELECT EMPLOYEE_ID,
TO_CHAR(HIRE_DATE, ‘yyyy-mm-dd’)
FROM EMPLOYEES;
SELECT EMPLOYEE_ID,
TO_CHAR(HIRE_DATE, ‘yyyy”년”-mm”월”-dd”일”‘)
FROM EMPLOYEES;
SELECT EMPLOYEE_ID,
TO_CHAR(HIRE_DATE, ‘yyyy”년”-mm”월”-dd”일” hh24”시”:mi:ss’)
FROM EMPLOYEES;
SELECT EMPLOYEE_ID,
TO_CHAR(HIRE_DATE, ‘yyyy”년”-mm”월”-dd”일” day pm hh:mi:ss’)
FROM EMPLOYEES;
SELECT
TO_CHAR(SYSDATE, ‘yyyy”년”-mm”월”-dd”일” ddd”일째 되는날”‘)
FROM DUAL;
SELECT TO_CHAR(SYSDATE, ‘RR’)
FROM DUAL;
SELECT TO_CHAR(TO_DATE(‘30’,’RR’), ‘YYYY’) FROM DUAL;
– 현재 세기
SELECT TO_CHAR(TO_DATE(‘60’,’RR’), ‘YYYY’) FROM DUAL;
– 이전 세기