Oracle 3

col b_year format a10;

select substr(hiredate,1,2) b_year from emp

 

select ltrim(rtrim(mail, substr(mail,instr(mail,'.'))),
substr(mail,1,instr(mail,'@'))) from t1  ->회사명뽑기

최종변경:

select substr(mail,1,instr(mail,'@')-1) ID,                         //instr은 해당문자를 만날떄까지의 자리수
ltrim(rtrim(mail, substr(mail,instr(mail,'.'))) ,substr(mail,1,instr(mail,'@'))) com1,
substr(mail,instr(mail,'.')+1) com2
 from t1

컬럼의 간격을 조정하기 위해

col id format a10;   ,  col com1 format a10,    col com2 format a10;으로 조정해준다.

기록된 ed는 save 파일명 으로 저장해주고 @파일명으로 실행한다.

select sysdate+1 from dual ->날짜값 +1(일수)

select sysdate, round(sysdate,'year') , round(sysdate,'month') from dual;  ->연도를 올리거나 내림(달로 판정-7월 이후 올림),  월을 올리거나 내림(일로판정-16일 이후 올림)

 

select trunc(sysdate,'year') from dual;  ->12/08/02인 경우 12/01/01로 잘라줌

select * from emp where hiredate>='81/01/01' 

select months_between(sysdate,'11/01/01')/12 from dual  

select floor(months_between(sysdate,'11/01/01')/12) from dual

from절이후의 자료들부터 검색이 되므로  rename을 시키면 where절에서 인식이 가능하다.

select * from emp e where e.sal>=100;

from ->where   (select에 지정한 rename은 where절에서 사용이 안됨. )

select->order by

 

select num+100 upnew from t1 rewt1 where num+100>=110 order by upnew desc

 

형식맞추기(http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions094.htm#i78039)

select sysdate- to-date('10-10-10','yy-mm-dd') from dual;

(todate를 쓰지않고 빼주면 날짜가 아닌 문자로 인식하므로 오류)

select months_between(sysdate, hiredate)from emp  //현재와 고용된 날짜의 달수를 계산. 년도로 계산하고 싶으면 /12해주면 됨.

 

add_months 

 select add_months(sysdate,3) from dual  //오늘로부터 3개월 지난날의 날짜는?

 

Q.근속기간이 10년 이상된 자료를 구하세요.

select first_name,hire_date, trunc(months_between(sysdate,hire_date)/12) info
from employees where trunc(months_between(sysdate,hire_date)/12) >=10

Q2.근속기간이 150개월 이상된 자료를 구하세요

select trunc(months_between(sysdate,hire_date)) from employees where trunc(months_between(sysdate,hire_date))>=150

Q3.근속기간이 150개월 이상인 사람에게 급여를 7%인상하려고 한다. 그때 인상된 급여와 이름과 부서, 그리고 현재급여를 구하삼

select first_name 이름, salary 현재급여, salary*1.07 인상급여 from employees  where trunc(months_between(sysdate,hire_date))>=240
/

 

next day     //특정 날짜 이후에 해당하는 요일의 날짜구하기

select next_day(sysdate,'금요일')from dual

 

last day  //해당날짜의 마지막 날(일) 구하기

select last_day('12-02-04') from dual

 

날짜형식 바꾸기

alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss'; 

alter session set nls_date_format ='yyyy-mm-dd ddd hh24:mi:ss';

 

RR

               00-49(입력)         50-99(입력)

00-49       현재                         이전

50-99      다음                          현재

rr은 주로 시스템에서 사용하고 yyyy-mm..형식은 일반적으로 날짜형식을 보여주기에 사용한다.

    <-to_number       <-to_char

Number        character     Date

     ->to_char         ->to_date

 

to_char

select sysdate,to_char(sysdate,'yyyy-mm-dd ddd day q hh24::mi:ss') from dual;

select to_char(1000.565656, '9,999.9') from dual;

 

to_date  //날짜형으로 변환하는 함수

select first_name, hire_date from employees where hire_date=to_date(19900103,'yy-mm-dd')

select sysdate-to_date('2011-01-01','yyyy-mm-dd') from dual;   //현재시간에서 임의날짜를 뺄때 to_date를 활용해서 형식을 변환하여 계산할 수 있다.

 

 

to_number //자료의 문자를 숫자형으로 변환하는 함수

 

select select '2,000'+'3,000' from dual; //'2,000','3,000'문자이므로 수행이 되지않음

select to_number('2,000','9,999')-to_number('3,000','9,999') from dual;   //to_number로 숫자로 변환하여 계산해 준다.

 

nvl //null일 경우 다른 값으로 변환

select first_name, nvl(commission_pct,0) from employees;  //commission의 null값을 0으로 채워줌

select first_name,nvl(to_char(commission_pct),'null') from employees; //commission_pct를 문자로 변환해주고 nvl을 이용해 commission_pct의 값이 없으면 null로 채워준다.

 

nvl2//null인지 아닌지에

select first_name,nvl2(commission_pct,commission_pct,0) from employees; //commission_pct가 null이 아니면 commission_pct를 반환해주고 commission_pct가 null이면 0을 반환해준다.

 

Decode//조건과 선택

select first_name||last_name name, salary "before salary"
, decode(job_id,'IT_PROG',salary*1.2,'PR_REP',salary*1.1
, 'HR_REP',salary*1.0, 'ST_MAN',salary*1.07,salary) as "after salary"
from employees; //first_name과 last_name을 붙여서 name으로 보여주고 salary를 before salary라는 컬럼명으로 보여줌. job_id가 위와 같으면 salary를 상이하게 적용함

 

select decode(substr(job_id,1,2),'IT','it','PR','pr','others') from employees;

 

조건에 따른것 말고 범위로 지정해서 조건처리하는 함수

CASE

select case  when  lower(job)= 'clerk' then 'c'
when  lower(job) = 'analyst' then 'a'
else 'others' end result from emp   //exp(lower(job)이 반복됨.

select case  lower(job) when 'clerk' then 'c'
when 'analyst' then 'a'
else 'others' end result from emp     //

Q.연봉이 2000미만 lower 2000이상 2000미만 middle 3000이상 5000미만 good 5000이상 very good

select case when sal<2000 then 'lower'
               when (sal<3000) then 'middle'
    when (sal<5000) then 'good'
    else 'very good'
end "salary result" from emp

Q.decode를 사용해서   <5000  '5000이하'

  >=5000 and <10000 '5000이상 100000미만

>=10000 and <150000 "10000이상 15000미만'

나머지.... 15000이상

case...와 decode로 표현

A.case로 표현

select first_name, salary, case when salary<5000 then '5000이하'
when salary<10000 then '500이상 10000미만'
when salary<15000 then '1000이상 15000미만'
else '범위에 없음'
end "case result" from employees
/

A.decode로 표현

select first_name, salary, decode(trunc(salary/5000),0,'5000이하',1,'5000이상 10000미만',
2,'10000이상 15000미만') from employees;

 

과제1.

테이블 -이름,주민번호

홍길동-800101-1010111

출력결과 - 이름,주민번호,성별,나이

select name, id, decode(substr(id,8,'1'),1,'남자',2,'여자','3','남자','4','여자') 성별, trunc((sysdate-to_date(substr(id,1,6),'rrmmdd'))/365)
 from ho

months_between을 사용한 방법:

select name, id ,
trunc(months_between(sysdate,to_date(substr(id,1,6),'rrmmdd'))/12)
from te

과제2. 부서가 80의 각 사원별 세율을 작성  (처리결과는 decode나 case하나씩)

0~1999.99   0%

2000~3999.99  0.9%

4000~5999.99     20%

6000~7999.99    30%

8000~9999.99  40%

10000 이상  45%

출력결과 :  이름, 연봉, 세율, 실질연봉

 

//decode

select first_name 이름, salary 현재연봉, decode(trunc(salary/2000),0,'0%',1,'0.9%',2,'20%',3,'30%',4,'40%','45%') 세율,
decode(trunc(salary/2000),0,salary,1,salary*1.09-salary,2,salary*1.2-salary,3,salary*1.3-salary,4,salary*1.4-salary,salary*1.45-salary)실제세금 from employees

//case

select first_name||last_name name, salary
, case trunc(salary/2000) when 0 then '0%'
when 1 then '0.9%'
when 2 then '20%'
when 3 then '30%'
when 4 then '40%'
else '45%' end "세율" ,
case trunc(salary/2000) when 0 then salary
when 1 then salary-(salary*0.09)
when 2 then salary-(salary*0.2)
when 3 then salary-(salary*0.3)
when 4 then salary-(salary*0.4)
else salary-(salary*0.45) end "실제세금" from employees

 

select sum(컬럼명) from 테이블명;

select avg(컬럼명) from 테이블명;

select count(컬렴명)from 테이블명;

max,min,stdev,variance

select sum(commission_pct), count(commission_pct), count(*), avg(commission_pct)  from employees;

avg - >null값을 제외한 값의 평균을 구해준다.

select name. sum(salary)from employees; ->단일그룹의 함수가아니다(name은 단일함수이고 sum은 그룹함수 이므로 함꼐 사용할 수 없다.)

select count(distinct department_id) from employees; //중복을 제거한 카운트!!!