Oracle 9i #7. 단일 행 함수

-------------------------------------------------------------------------------------
- 제 7장 - 단일 행 함수
-------------------------------------------------------------------------------------

 

 

* initcap - 첫번째 영문자만 대문자로 바꾸어준다.

 

select ename, initcap(ename) from emp; 

 

--> 결과값 : KEVIN --> Kevin

 

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

 

* concat - 문자열 합치기. ||와 같으나 최대 2개만 가능

 

select ename || job || deptno, concat(ename,job) from emp;

 

--> 결과값 : SMITHCLERK20 / SMITHCLERK

 

select ename || job || deptno, concat(ename,job,deptno) from emp;

 

--> 에러 : concat에는 최대 2개까지만 가능..

 

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

 

* substr - 글자 수 기준 문자열 추출 , substrb - 바이트기준 문자열 추출

 

select substr('oracle',1,3), substr('대한민국',1,2) from dual; 

--> 결과값 : ora , 대한 (바이트와 상관없다.)

 

select substrb('oracle',1,3), substrb('대한민국',1,1) from dual;

--> substrb 는 바이트기준으로 출력한다. 한글은 2바이트,영문 1바이트

--> 결과값 : ora /  (3byte ora / 한글은 2바이트이기때문에 출력불가)

 

select substr('oracle',2) from dual;

--> 결과값 : racle ( 2번째 문자부터 끝까지 )

 

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

 
 * length - 글자수,비영어권에서 보편적임 , lengthb - 바이트수
 
select length('oracle'),length('대한민국'),
          lengthb('oracle'),lengthb('대한민국') from dual;

 

 --> 결과값 : 6 / 4 (4글자기준) / 6 / 8 (4글자 8byte기준)
       

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

 

* instr - 특정 문자열이 처음 발견 된 위치 값 리턴

 

select instr('oracle ORACLE oralb','ora',1),
         instr('oracle ORACLE oralb','ora',2),
         instr(lower('oracle ORACLE oralb'),lower('ora'),2)

from dual;

 

--> 결과값 : 1 / 15 / 8 (대소문자구분없이하기위해 소문자로 형변환)

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

        
* lpad, rpad - 특정 크기를 지정하여 공백부분에 특정 문자열로 채워준다.

                      숫자는 바이트라는 것에 절대 유의하자.

 

select lpad('오라클',20,'?'), lpad('?',20,'?'),rpad('?',20,'?'),rpad('오라클',20,'?') from dual;

 

--> 결과값 :

??????????????오라클 / ???????????????????? / ???????????????????? / 오라클??????????????

 

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

 

* ltrim , rtrim - 특정 문자열 혹은 공백 제거

select ltrim('오오징어오라클','오징' ), ltrim('오오징어오라클','클라' ),
       rtrim('오오징어오라클','오징' ), rtrim('오오징어오라클','클라' ),
    rtrim('오징어             ') ,ltrim('             오징어') ,
       rtrim(ltrim ('               오오징어오라클                ') )
from dual;

 

--> 결과값 :

어오라클 / 오오징어오라클 / 오오징어오라클 / 오오징어오 / 오징어 / 오징어 / 오오징어오라클

 

rtrim은 오른쪽에서 부터 제거되는 대신에, 지정 문자역시 뒤집어 입력해야한다.

 

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

 

* translate - 특정 문자열을 사용자가 지정한 매칭값으로 바꿔준다.

 

select translate('oracle',
                      'abcdefghijklmnopqrstuvwxyz',
                     'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
from dual;

--> 결과값 'ORACLE' - 대소문자 변환이 되었다.

 

select translate('공공칠빵',
                      '공일이삼사오육칠팔구빵',
                      '01234567890')
from dual;

--> 결과값 : 0070 (바뀌어질 것과 바뀌는 것의 타입은 전혀 상관없다.)

 

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

 

* replace - 문자열 바꾸기, 대소문자 구별..

 

select replace('oracle oracle9i ORACLEDBA', 'ora', '오라') from dual; 

 

--> 결과값 : 오라cle 오라cle9i ORACLEDBA

 

select replace(upper('oracle oracle9i ORACLEDBA'),upper('ora'), '오라') from dual; 

 

--> 결과값 : 오라CLE 오라CLE9I 오라CLEDBA

--> 대소문자 구별을 없애기 위해, 모든값을 대문자로 강제 형변환하였다.

 

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

 

* reverse - 문자열 순서 뒤집기

 

select reverse('oracle oracle9i ORACLEDBA') from dual; 

 

--> 결과값 : ABDELCARO i9elcaro elcaro

 

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

 

* round - 반올림, 옵션 숫자가 음수면 정수자리, 양수면 소수자리 반올림

 

select 91.459, round(91.459,-3),round(91.459,-2) , round(91.459,-1) ,round(91.459,0) ,
       round(91.459,1) ,round(91.459,2), round(91.459,3)
from dual;

 

--> 결과값 : 91.459 / 0 / 100 / 90 / 91 / 91.5 / 91.46 / 91.459

 

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

 

* trunc - 숫자 절삭, 옵션 숫자가 음수면 정수자리, 양수면 소수자리

select 91.459, trunc(91.459,-3),trunc(91.459,-2) , trunc(91.459,-1) ,trunc(91.459,0) ,
       trunc(91.459,1) ,trunc(91.459,2), trunc(91.459,3)
from dual;

--> 결과값 : 91.459 | 0 | 0 | 90 | 91 | 91.4 | 91.45 | 91.459

 

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

 

* mod(나머지), power(제곱), sqrt(루트)

 

select mod(5,2), power(5,2), sqrt(2) from dual;
--> 결과값 : 1 / 25 / 1.4142135623731

 

* sign

select sign(5-2), sign(5-5), sign(2-5), sign(-3) from dual;
--> 결과값 : 1 / 0 / -1 / -1

 

* chr - ascii 코드에 해당하는 문자 리턴
select chr(65), chr(97), ascii('A'),ascii('a') from dual;
--> 결과값 : A / a / 65 / 97

 

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

-- 1.5.2 날짜 연산 (p.113)
-------------------------------------------------------------------------------------

 

* sysdate

select sysdate+1 , sysdate-1,sysdate-1/24,sysdate+1/24 from dual; 
--> 1 일 증감, 1시간 전,  1시간 후

select sysdate - to_date('2006-10-13', 'yyyy-mm-dd') from dual;
--> 1.67614583333333 (현재 날짜시간에서 해당날짜를 뺀 값, 대략 1.6일정도) 

 

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

 

* to_date
 
select to_date('2006-10-13', 'yyyy-mm-dd') from dual;
--> 2006-10-13 00:00:00

select to_date('20061013','yyyymmdd') from dual;
--> 2006-10-13 00:00:00

select to_date('20060931','yyyymmdd') from dual;
--> 에러.. 달력에 9월 31일은 존재하지 않는다.

 

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

 

* months_between - 달 차이 구하기. (9월과 5월은 4달...)

select months_between('20061014','20060914') from dual;
--> 1 (1달 뒤)

select months_between('20060914','20061014') from dual;
--> -1 (1달 전)

select months_between(sysdate,'20060901') from dual;
--> 1.44128136200717 (대략 1달 보름)

 

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

 

* add_months - 달 계산

select add_months(sysdate,2) from dual;
--> 2006-12-14 16:19:44 (현재 시각에 2달을 더하기.)

select add_months(sysdate,-2) from dual;
--> 2006-08-14 16:20:02 (현재 시각에 2달을 뺐다.)

 

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

 

* next_day - 가장 최근에 돌아오는 특정요일의 날짜 검색

 

select next_day(sysdate,'FRI') from dual;
select next_day(sysdate,'FRIDAY') from dual;

--> 돌아오는 금요일의 날짜와 현재와 같은 시각 리턴

 

select next_day(sysdate,'금') from dual;
select next_day(sysdate,'금요일') from dual; 
--> 한글은 sqlgate에서 실행불가, sqlplus에서 실행하자.

 

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

* last_day - 특정 월의 마지막 날짜 

 

select last_day(sysdate) from dual;

 

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

 

* 현재시각을 기준으로 특정값만 추출 --> 숫자 출력

 

select sysdate,
    to_char(sysdate,'yyyy') 년,to_char(sysdate,'mm') 월,
    to_char(sysdate,'dd') 일 , to_char(sysdate,'hh') 시,
    to_char(sysdate,'mm') 분,to_char(sysdate,'ss') 초
from dual;
--> 2006 10 15 03 10 42

 

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

 

* 현재시각을 기준으로 특정값만 추출 #2  --> 영문출력

 

select sysdate,  -- mon <-> month 같다 dy <-> day
    to_char(sysdate,'year') 년,to_char(sysdate,'month') 월,
    to_char(sysdate,'day') 일 ,to_char(sysdate,'hh') 시,
    to_char(sysdate,'mm') 분,to_char(sysdate,'ss') 초 ,
    to_char(sysdate,'dy')
from dual;
-->two thousand sixoctober  sunday   03 10 02

 

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

 

* 분기 (quarter) , 몇째 주  , 그 해의 주차, 요일에 해당하는 숫자

select to_char(sysdate,'q'), to_char(sysdate,'w'),
       to_char(sysdate,'ww'),to_char(sysdate,'d'),
    to_char(sysdate,'dd'),to_char(sysdate,'ddd')
from dual;
--> 4(4분기), 3(10월 3째주), 42(2006년 42주차),
--- 2 (월요일), 16(16일), 289(2006년 289일째) d / dd / ddd

 

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

 

* 요일값 구하기

 

--> to_char(sysdate,'d') 
--> 리턴값은 다음과 같은 숫자 - 일요일(1) 월(2) 화(3)수(4)목(5)금(6)토(7)

리눅스기반 오라클에서 리턴값 한글로 강제 변환 방법
 
1) 방법 1  (case구문은 oracle 9i 부터 사용)

select case to_char(sysdate,'d')
    when '1' then '일요일'
       when '2' then '월요일'
       when '3' then '화요일'
       when '4' then '수요일'
       when '5' then '목요일'
       when '6' then '금요일'
       when '7' then '토요일'
       end "오늘의 요일명"   --> alias , 쌍따옴표 주의!!
from dual;

 

2) 방법 2
 
select case
       when to_char(sysdate,'d')='1' then '일요일'
       when to_char(sysdate,'d')='2' then '월요일'
       when to_char(sysdate,'d')='3' then '화요일'
       when to_char(sysdate,'d')='4' then '수요일'
       when to_char(sysdate,'d')='5' then '목요일'
       when to_char(sysdate,'d')='6' then '금요일'
       when to_char(sysdate,'d')='7' then '토요일'
       end "오늘의 요일명"
from dual;

 

3) 방법 3 (오라클 8i 이전에 주로 사용..)

 

select decode(to_char(sysdate,'d'),'1','일요일'
                                   ,'2','월요일'
                                   ,'3','화요일'
                                   ,'4','수요일'
                                   ,'5','목요일'
                                   ,'6','금요일'
                                   ,'7','토요일')
"오늘의 요일명"
from dual;                              

 

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

 

* 현재 시각 (표준시각 current_date)

select sysdate, current_date from dual;

--> 2006-10-19 20:20:14(시스템시간) / 2006-10-19 11:20:15(세계표준시)

 

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

* 날짜 타입 서수형으로의 변환

 

select to_char(sysdate, 'yyspth'),to_char(sysdate, 'mmspth'),
       to_char(sysdate, 'ddspth')
from dual;
--> sixth tenth sixteenth (200 '6'년 '10'월 '16'일)

 

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

* 현재 날짜 원하는 형식으로 변환

select to_char(sysdate,'yyyy"년" mm"월" dd"일"')
from dual;
--> 2006년 10월 16일

 

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

* 'fmyyyy-mm-dd' 날짜중 0을 제거/삽입 

select ename, to_char(hiredate,'yyyy-mm-dd') hiredate,
       to_char(hiredate,'fmyyyy-mm-dd') hiredate, --> 제거 
      to_char(hiredate,'fmyyyy-mmfm-dd') hiredate --> /yyyy제거 mm삽입 
from emp;
--> 1981-04-02 -> 1981-4-2 (0을 삭제하자.)

 

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

* 통화기호와 자리표시

 

select ename,sal,
       to_char(sal,'09999'),  --> 00800   --> 01600
       to_char(sal,'$9,999'), -->  $800   --> $1,600
       to_char(sal,'L9,999')  -->  $800   --> $1,600
from emp;

 

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

* 현재 설정 (언어, 통화, 달력등 보기)

select * from v$nls_parameters;

 

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

* 날짜 계산

select '20061016'-'20061010'
from dual;
--> 6 (문자열 숫자열로 오라클 서버가 자동 형변환)

 

select sysdate-'20061010'
from dual;
--> 에러

 

select to_char(sysdate,'yyyymmdd')-'20061010'
from dual;
--> 6 (강제형변환을 해주어야한다)

 

select sysdate - to_date('20061010','yyyymmdd')
from dual;
--> 6.43899305555555

 

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

 

* to_yminterval

select sysdate, add_months(sysdate,14),
       sysdate + to_yminterval('01-02') -- only Oracle 9i upper!
from dual; 
--> 2006-10-16 10:33:15 / 2007-12-16 10:33:15 / 2007-12-16 10:33:15
--- 14개월 / 1년 2개월 후 

 

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

 

* to_dsinterval

select sysdate + to_dsinterval('001 02:03:04')
from dual;
--> 1일 2시간 3분 4초 후... / 2006-10-17 12:37:41

 

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

 

* to_yminterval + to_dsinterval

select sysdate
     + to_yminterval('01-02')
     + to_dsinterval('001 02:03:04')
from dual;
--> 1년 2월 1일 2시간 3분 4초 후.. / 2007-12-17 12:39:39

 

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

 

* extract - Oracle 9i이상에서만 동작한다.

    --> 날짜데이터에서 특정값을 숫자형으로 추출..(우측정렬) 
    --> to_char와 결과물은 같지만, to_char는 문자열이다.(좌측)

select sysdate,
       extract(year from sysdate),
       to_char(sysdate,'yyyy'),   
       extract(month from sysdate),
       to_char(sysdate,'mm'),
       extract(day from sysdate)+1,  --> 원래 숫자형이므로 형변환 X
       to_char(sysdate,'dd')+1  --> 1을 더하면서 강제형변환이 일어났다.
from dual;

 

-->

2006-10-19 20:28:53 / 2006(우) / 2006(좌) / 10(우) / 10(좌) / 20(우) / 20(우)

 

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

 

* 실수로 반복 입력한 데이터의 삭제.. --> rowid와 rownum을 이용..

select rownum, rowid, name,jubun
from member;

--> 잘못입력된 데이터의 데이터 입력시 자동생성되는 rowid와 rownum을 검색하자

 

delete member
where rowid like 'AAAHZuAAJAAAAAP%';

--> rowid를 검색하여, 그 행을 조건절을 이용하여 삭제.

 

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

* 클라이언트 정보 검색

 

select userenv('language') "language",
       userenv('terminal') "terminal",
       userenv('sessionid') "sessionid"
from dual;

 

--> 현재 설정된 언어 값 / 접속컴퓨터터미널이름 / 세션ID

AMERICAN_AMERICA.KO16MSWIN949 / MVP386 /167

 

select uid, user from dual;

--> 59 (USER ID) / SCOTT(접속계정)

 

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

* 순위 매기기

방법 1)

 

select ename "사원명",deptno "부서번호",sal "급여",
       rank() over(order by sal desc) "전체등수",   --공동랭크 포함 - 공동2등 다음 4등
       rank() over(partition by deptno order by sal desc) "부서별등수",
       dense_rank() over(order by sal desc) 전체서열, --공동랭크 불포함 - 공동2등 다음 4등
       dense_rank() over(partition by deptno order by sal desc) 부서별서열
from EMP;

 

 

방법 2) 인라인 쿼리문 - 사실상 이 문제에는 필요없다.

 

select *
from
(
select ename "사원명",deptno "부서번호",sal "급여",
       rank() over(order by sal desc) "전체등수",   --공동랭크 포함 - 공동2등 다음 4등
       rank() over(partition by deptno order by sal desc) "부서별등수",
       dense_rank() over(order by sal desc) 전체서열, --공동랭크 불포함 - 공동2등 다음 4등
       dense_rank() over(partition by deptno order by sal desc) 부서별서열
from EMP
) T
where "전체등수" <=10
order by 2,3 desc;

 

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

-- Quiz )

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

 

1. member 테이블에서 여자만 출력하시오.

 

select *

from member

where substr(jubun,7,1) in (2,4)

 

select *

from member

where substr(jubun,7,1) =  '2' or substr(jubun,7,1) =  '4'

--> 위의 in 구문보다 아래의 OR 구문이 대용량DB에서 속도면에서 유리하다.

--> 2와 4에 홑따옴표(')를 붙여주지 않아도 동작은 되지만, 무결성을 위해 붙여주자

 

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

 

2. 아래와 같은 테이블이 있다.

 

create table filetab
(fileno number,
filename varchar2(200)
) tablespace users;

 

insert into filetab values(1,'c:\aaa\bbb\ccc\sales.xls');
insert into filetab values(2,'d:\aaa\salesinfo.doc');
insert into filetab values(3,'c:\research.xxls');
insert into filetab values(4,'d:\aaa\bbb\marketing.hwp');

 

1) 확장자가 xls인 파일만 출력하시오.

 

select *

from filetab

where filename like '%.xls';

 

--> 데이터중에 .xls와 .xxls가 있다. 점(.)을 꼭 넣어 구분해주자.

 

2) 아래와 같이 출력하시오.

 

--------------------------------
 fileno      filename
--------------------------------
1            sales.xls
2            salesinfo.doc
3            research.xxls
4            marketing.hwp

 

-->

select fileno,

         reverse(substr(reverse(filename),1,instr(reverse(filename),'\',1)-1)) filename
from filetab;

 

 

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

 

Quiz ) - 2006.10.14.16:36:00

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

 

1. 오늘 입대하면 언제 제대할까? (군 기간은 2년)
select add_months(sysdate,24) from dual;
--> 2008-10-14 16:36:34

 

2. 오늘 입대하면 몇끼를 먹어야 제대할까? (단, 하루3끼)
select (add_months(sysdate,24)-sysdate)*3 from dual;
--> 2193

 

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

 

Quiz ) 아래와 같이 출력하시오.

 

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

 이름  주민번호 계통 성별 나이

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

 

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

 

insert into member values('귀화남','7510165234567');
insert into member values('귀화녀','7611126234567');

commit;

 

select * from MEMBER;

 

 

방법 1) 복잡하고 잘못된 코딩

 

select name 이름,jubun 주민번호,

    case when substr(jubun,7,1) in ('1','2','3','4')
       then '한국계'
      else '외국계'
    end "원래국적" ,
    decode(substr(jubun,7,1),'1','남'
                                        ,'3','남'
                                        ,'5','남'
                                       ,'여') "성별" , --> 계통

    case when substr(jubun,7,1) in ('1','2') then to_char(sysdate,'yyyy') - ('19' || substr(jubun,1,2))
         when substr(jubun,7,1) in ('3','4') then to_char(sysdate,'yyyy') - ('20' || substr(jubun,1,2))
         when substr(jubun,7,1) in ('5','6') then to_char(sysdate,'yyyy') - ('19' || substr(jubun,1,2))         
           when substr(jubun,7,1) in ('7','8') then to_char(sysdate,'yyyy') - ('20' || substr(jubun,1,2))  
        end "현재나이"       --> 나이계산
from member;

 

방법 2) 간단한 코딩 

 

select T.*,
add_months(to_char(sysdate,'yyyy')||'-02-20',12*(60-현재나이)) "정년일",
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') - 입사일 "근무일수",
add_months(to_char(sysdate,'yyyy')||'-02-20',12*(60-현재나이)) -
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') "남은일수"
from
(
select name 성명, jubun 주민번호, hiredate 입사일,
  case when substr(jubun,7,1) in ('1','2','3','4') then '한국계'
       else '외국계' end 혈통,
  case when substr(jubun,7,1) in ('1','3','5') then '남'
       else '여' end 성별,
  case when substr(jubun,7,1) in ('1','2','5','6') 
       then extract(year from sysdate) - (to_number(substr(jubun,1,2))+1899)
       else extract(year from sysdate) - (to_number(substr(jubun,1,2))+1999)
       end 현재나이     
from MEMBER
) T;

-->
귀화인 7510165234567 외국계 남 31
귀화여 7611126234567 외국계 여 30
이순신 7001031234567 한국계 남 36
김하늘 8012252234567 한국계 여 26
남자애 0005023234567 한국계 남 6
여자애 0103014234567 한국계 여 5

 

 

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

-- 컬럼 추가
----------------------------------------------------------------------------------

 

alter table member
add hiredate date;

 

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

 

Quiz ) member 테이블에서 아래와 같이 추출. 단 정년은 60세 되는해의 2월 20일이다.

 

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

성명 주민번호 입사일 계통 성별 현재나이 근무일수 정년일 남은일수

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

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

 

select *
from member;

 

update member set hiredate=to_date('1998-01-04','yyyy-mm-dd')
where name='이순신'; 
update member set hiredate=to_date('1999-12-14','yyyy-mm-dd')
where name='김하늘'; 
update member set hiredate=to_date('2002-09-10','yyyy-mm-dd')
where name='남자애'; 
update member set hiredate=to_date('2003-03-20','yyyy-mm-dd')
where name='여자애'; 
update member set hiredate=to_date('2000-01-30','yyyy-mm-dd')
where name='귀화인'; 
update member set hiredate=to_date('2000-01-30','yyyy-mm-dd')
where name='귀화여'; 

commit;

 

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

 

정답 )

 

select T.*,

add_months(to_char(sysdate,'yyyy')||'-02-20',12*(60-현재나이)) "정년일",

to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') - 입사일 "근무일수",

add_months(to_char(sysdate,'yyyy')||'-02-20',12*(60-현재나이)) -
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') "남은일수"

from
(
select name 성명, jubun 주민번호, hiredate 입사일,

  case when substr(jubun,7,1) in ('1','2','3','4') then '한국계'
       else '외국계' end 혈통,

  case when substr(jubun,7,1) in ('1','3','5') then '남'
       else '여' end 성별,

  case when substr(jubun,7,1) in ('1','2','5','6') 
       then extract(year from sysdate) - (to_number(substr(jubun,1,2))+1899)
       else extract(year from sysdate) - (to_number(substr(jubun,1,2))+1999)
       end 현재나이     

from MEMBER
) T;

 

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

Quiz ) emp테이블의 사원중 1년간 총연봉(급여+보너스)가 30000이상인 사람 추출

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

 

방법1)

select ename, coalesce(sal*12+comm, comm, sal*12, 0)
from emp
where coalesce(sal*12+comm, comm, sal*12, 0) >= 30000;

 

방법2) 인라인쿼리

select *
from
(
select ename 사원명, coalesce(sal*12+comm,comm,sal*12,0) 연봉
from emp
) T
where T.연봉 >= 30000; 

 

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

 

Quiz ) 급여가 아닌 연봉으로 순위 출력

 

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

select *
from
(
select ename "사원명",deptno "부서번호",to_char(coalesce(sal*12+comm,comm,sal*12,0),'$999,999') "연봉",
       rank() over(order by coalesce(sal*12+comm,comm,sal*12,0) desc) "전체등수",   --공동랭크 포함 - 공동2등 다음 4등
       rank() over(partition by deptno order by coalesce(sal*12+comm,comm,sal*12,0) desc) "부서별등수",
       dense_rank() over(order by coalesce(sal*12+comm,comm,sal*12,0) desc) 전체서열, --공동랭크 불포함 - 공동2등 다음 4등
       dense_rank() over(partition by deptno order by coalesce(sal*12+comm,comm,sal*12,0) desc) 부서별서열
from EMP
) T
where "전체등수" <=10
order by 2,3 desc;       coalesce(sal*12+comm,comm,sal*12,0)

 

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

 

 7장 연습문제

1. 현재 날짜 출력하고 컬럼명은 'Current Date'로 출력하시오

select to_char(sysdate,'yyyy-mm-dd') "Current Date"
from dual;

 

2. EMP 테이블에서 현재 급여에서 15%증가된 급여를 사원번호, 이름,업무,급여,

  증가된 급여(New Salary), 증가액(Increase)를 출력

select empno,ename,job,sal,round(nvl(sal+sal*0.15,0),0) "인상된 급여",round(nvl(sal*0.15,0),0) "증가액"
from emp;

 

3. EMP테이블에 이름,입사일,입사일로부터 6개월 후 처음 돌아오는 월요일의 날짜 출력

select ename,hiredate,next_day(add_months(hiredate,6),'monday')
from emp;

 

4. EMP테이블에서 이름,입사일, 입사일로부터 현재까지의 월수, 총급여, 현재급여 출력

 

select ename,hiredate,round(months_between(sysdate,hiredate),0) 근무개월수,
       round(months_between(sysdate,hiredate),0)*sal 총월급,
       round(months_between(sysdate,hiredate),0)*(nvl(sal,0)+nvl(comm,0)) 총급여
from emp
order by 5 desc;

 

5. 다음과 같이 출력하시오.

 

Dream Salary

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

KING earns   $5,000 monthly but wants   $15,000
SCOTT earns   $3,000 monthly but wants    $9,000
FORD earns   $3,000 monthly but wants    $9,000
JONES earns   $2,975 monthly but wants    $8,925

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

select ename || ' earns' || to_char(sal,'$999,999') || ' monthly but wants ' || to_char(sal*3,'$999,999')
from emp
order by sal desc;

 

6. EMP테이블에서 모든 사원의 이름과 급여를 출력.

  단, 이름은 15자리로 지정하고, 공백부분은 *로 채워라.

select ename,lpad(to_char(sal,'$9,999'),15,'*')
from emp
order by sal

 

7. EMP테이블에서 모든 사원의 이름,업무,입사일,입사요일 출력

select ename,job,hiredate,
       case
         when to_char(hiredate,'d') =1 then '일요일'
            when to_char(hiredate,'d')='2' then '월요일'
            when to_char(hiredate,'d')='3' then '화요일'
            when to_char(hiredate,'d')='4' then '수요일'
            when to_char(hiredate,'d')='5' then '목요일'
            when to_char(hiredate,'d')='6' then '금요일'
            when to_char(hiredate,'d')='7' then '토요일'
       end "입사요일"      
from emp;

 

8. EMP테이블에서 이름이 6글자 이상인 사원의 이름,이름글자수,업무 출력

select ename,length(ename) 이름길이,job
from emp
where length(ename) >= 6
order by ename desc;

 

9. EMP테이블에서 모든 사원의 정보를 이름,업무,급여,보너스,급여+보너스 출력

select ename,job,sal,nvl(comm,0) 보너스,nvl2(sal+comm,comm,0) "급여+보너스"
from emp
order by 5 desc;

[출처] Oracle 9i #7. 단일 행 함수|작성자 진카자마