오라클에서 SQL의 주요함수와 그룹함수들 사용하기

Select Round(45.293, 2) From Dual ;
Select Trunc(45.196, 2) From Dual ;
Select Sal, Mod(Sal, 100) From Emp ;

Select Upper('Welcome to Oracle 10g') From Dual ;
Select Lower('Welcome to Oracle 10g') From Dual ;
Select Initcap('Welcome to Oracle 10g') From Dual ;

-- 공백포함. SQL에서는 시작이 1부터.
Select Length('Welcome to Oracle 10g') From Dual ;

-- to 의 o를 찾는 구문. 3번째 이후 2번째 o(10번째 문자)를 찾음.
Select Instr('Welcome to Oracle 10g', 'o', 3, 2) From Dual ;

-- 오라클에서는 substring이 아니라 substr로 쓴다.
Select Substr('Welcome to Oracle 10g', 4, 3) From Dual ;
Select Substr('Welcome to Oracle 10g', -3, 2) From Dual ;

Select Lpad('Oracle 10g', 20, '#') From Dual ;
Select Rpad('Oracle 10g', 20, '#') From Dual ;

Select Ltrim('aaaOracle 10gaaa', 'a') From Dual ;
Select Rtrim('aaaOracle 10gaaa', 'a') From Dual ;
Select Trim('a' From 'aaaOracle 10gaaa') From Dual ;

Select Sysdate From Dual ;

Select Ename, Hiredate, Round(Sysdate - Hiredate, 0) As Duration From Emp ;

Select Sysdate,Hiredate,Round(Months_Between(Sysdate,Hiredate),0) As Months From Emp ;

Select Hiredate, Add_Months(Hiredate, 6) As Sixmonths From Emp ;

Select Hiredate, Last_Day(Hiredate) From Emp ;

-- 형 변환 함수 3개 : TO_NUMBER <-> TO_CHAR <-> TO_DATE
Select Round(Sysdate - To_Date('2006/01/01', 'YYYY/MM/DD'), 0) As Timegap From Emp ;

-- 3시간을 더해준다.
Select To_Char(Sysdate + 3/24, 'YYYY"년" MM"월" DD"일" HH"시" MI"분"') 
                                                    as expect_time From Dual ;

Select To_Char(Sysdate, 'YYYY-MM-DD HH24:MI:SS') From Dual ;

Select Ename, To_Char(Sal, '$999,999') From Emp ;
Select Ename, To_Char(Sal, 'L999,999') From Emp ;

Select Ename, Sal, Comm, Sal * 12 + Nvl(Comm, 0) as gross From Emp ;

Select Translate('MILLER', 'L', '*') From Dual ;
Select Replace('JACK and JUE', 'J', 'BL') From Dual ;
Select Ename, Job, Replace(Job, 'A', '$') as change From Emp ;