기존의 프로그래밍 언어에서 함수를 사용하듯이 SQL언어에서도 다양한 종류의 SQL함수를 제공한다.
SQL 함수에는 크게 단일행 함수와 복수행 함수로 나뉘어진다.
단일행 함수 : 입력 값이 단일 행이며 결과값 또한 하나를 반환함.
복수행 함수 : 입력 값이 복수이며 결과값은 하나를 반환함.
함수를 쓸 때는 항상 조심해야 한다. 경우에 따라 성능이 좋아질 수도 나빠질 수도 있다.
예를 들어 1천 건의 데이터가 모두 널값이 하나도 없는 경우 해당 컬럼에 NVL함수를 적용했다고 하면 1천 번의 널값 검사를 하므로 매우 비효율적이라는 것을 알 수 있다.
우선 단일행 함수가 기초가 되어 있는 상태에서 복수행 함수로 들어간다.
함수를 공부할 때 너무 외우려고 하지 말고 어떤 함수를 보았을 때 그것이 문자 함수인지 날짜 함수인지 숫자 함수인지 등을 구분할 줄은 알아야 한다.
동전 넣는 자판기에는 동전만 넣어야 하듯이 문자 함수에는 문자만 입력으로 주어야 하고, 숫자 함수에는 숫자만 입력으로 주어야 한다.
오라클에서 거의 모든 데이터는 문자가 대다수이다 라는 뜻은 SQL함수에서 문자함수가 가장 많은 비중을 차지한다는 뜻이 된다.
◆ 대소문자 변환함수
| 종류 | 의미 | 사용예 |
| INITCAP | 문자열의 첫번째 문자만 대문자로 변환 | INITCAP(student) -> Student |
| LOWER | 문자열 전체를 소문자로 변환 | LOWER(ABC) -> abc |
| UPPER | 문자열 전체를 대문자로 변환 | UPPER(abc) -> ABC |
◆ 문자열 길이 반환함수
| 종류 | 의미 | 사용예 |
| LENGTH | 문자열의 길이를 반환 | LENGTH('홍길동') -> 3 |
| LENGTHB | 문자열의 바이트 수를 반환 | LENGTHB('홍길동') -> 6 |
[문제] 학생 테이블에서 userid가 6글자 이상인 사람만 뽑아라.
SCOTT> select name, userid
2 from student
3 where lengthb(userid) >= 6;
NAME USERID
---------- ----------
전인하 jun123
박미경 ansel414
김진영 simply
지은경 Gomo00
오유석 yousuk
임유진 YouJin12
서재진 seolly
윤진욱 Samba7
이광훈 huriky
김진경 lovely
조명훈 Rader214
류민정 cleanSky
12 rows selected.
SCOTT> select name, userid
2 from student
3 where lengthb(userid) >= 6;
NAME USERID
---------- ----------
전인하 jun123
박미경 ansel414
김진영 simply
지은경 Gomo00
오유석 yousuk
임유진 YouJin12
서재진 seolly
윤진욱 Samba7
이광훈 huriky
김진경 lovely
조명훈 Rader214
류민정 cleanSky
12 rows selected.
◆ 문자 조작 함수
| 종류 | 의미 | 사용예 |
| CONCAT | 두 문자열을 결합. ||와 동일 | CONCAT('sql','plus') -> sqlplus |
| SUBSTR | 특정 문자 또는 문자열 일부를 추출 | SUBSTR('SQL*PLUS',5,4) -> PLUS |
| INSTR | 특정 문자가 출현하는 첫번째 위치 반환 | INSTR('SQL*Plus','*',1,1) -> 4 |
| LPAD | 오른쪽 정렬 후 왼쪽에 지정한 문자 삽입 | LPAD('sql',5,'*') -> **sql |
| RPAD | 왼쪽 정렬 후 오른쪽에 지정한 문자 삽입 | RPAD('sql',5,'*') -> sql** |
| LTRIM | 왼쪽의 지정 문자를 삭제 | LTRIM('*sql','*') -> sql |
| RTRIM | 오른쪽의 지정 문자를 삭제 | RTRIM('sql*','*') -> sql |
◆ SUBSTR
SCOTT> select substr('SQL*PLUS', -3, 2)
2 from dual;
SU
--
LU
오른쪽부터 3번째 위치로 가서 2 글자를 추출하라는 뜻.2 from dual;
SU
--
LU
SCOTT> select substr('배고파요', 2, 2)
2 from dual;
SUBS
----
고파
2 from dual;
SUBS
----
고파
SCOTT> select substr('배고파요', 2, 3)
2 from dual;
SUBSTR
------
고파요
SCOTT> select substrb('배고파요', 3, 6)
2 from dual;
SUBSTR
------
고파요
SUBSTR은 한글의 경우 글자 하나씩 처리하고, SUBSTRB는 한글이면 한 글자당 2바이트로 처리하기 때문에 주의.2 from dual;
SUBSTR
------
고파요
◆ INSTR
INSTR('비교할 대상', '비교하고자 하는 값', 비교를 시작할 위치, 검색된 결과의 순번)
SCOTT> select instr('SQL*PLUS', '*', 1, 1)
2 from dual;
INSTR('SQL*PLUS','*',1,1)
-------------------------
4
2 from dual;
INSTR('SQL*PLUS','*',1,1)
-------------------------
4
SCOTT> select instr('SQL*P*L*U*S', '*', 1, 2) // 두번째 별표가 나오는 위치 출력
2 from dual;
INSTR('SQL*P*L*U*S','*',1,2)
----------------------------
6
2 from dual;
INSTR('SQL*P*L*U*S','*',1,2)
----------------------------
6
SCOTT> select instr('SQL*P*L*U*S', '*', -1, 2)
2 from dual;
INSTR('SQL*P*L*U*S','*',-1,2)
-----------------------------
8
2 from dual;
INSTR('SQL*P*L*U*S','*',-1,2)
-----------------------------
8
SCOTT> select instr('우*리*나*라*', '나', 1, 1)
2 from dual;
INSTR('우*리*나*라*','나',1,1)
------------------------------
5
2 from dual;
INSTR('우*리*나*라*','나',1,1)
------------------------------
5
SCOTT> select instr('우*리*나*라*', '나', 3, 1)
2 from dual;
INSTR('우*리*나*라*','나',3,1)
------------------------------
5
2 from dual;
INSTR('우*리*나*라*','나',3,1)
------------------------------
5
SCOTT> select instrb('우*리*나*라*', '나', 1, 1)
2 from dual;
INSTRB('우*리*나*라*','나',1,1)
-------------------------------
7
2 from dual;
INSTRB('우*리*나*라*','나',1,1)
-------------------------------
7
SCOTT> select instrb('우*리*나*라*', '나', 7, 1)
2 from dual;
INSTRB('우*리*나*라*','나',7,1)
-------------------------------
7
2 from dual;
INSTRB('우*리*나*라*','나',7,1)
-------------------------------
7
SCOTT> select instrb('우*리*나*라*', '나', -1, 1)
2 from dual;
INSTRB('우*리*나*라*','나',-1,1)
--------------------------------
7
SCOTT> select instrb('우*리*나*라*나*', '나', -1, 1)
2 from dual;
INSTRB('우*리*나*라*나*','나',-1,1)
-----------------------------------
13
2 from dual;
INSTRB('우*리*나*라*나*','나',-1,1)
-----------------------------------
13
SCOTT> select instr('우*리*나*나*라','나',6,1)
2 from dual;
INSTR('우*리*나*나*라','나',6,1)
--------------------------------
7
INSTR과 INSTRB는 한글의 경우 한 글자당 1바이트씩 처리할 것인지, 2바이트로 처리할 것인지에 차이가 있다.2 from dual;
INSTR('우*리*나*나*라','나',6,1)
--------------------------------
7
◆ LPAD와 RPAD
SQL> select lpad(dept_name, 10, '1234567890')
2 from tdept;
LPAD(DEPT_NAME,10,'1
--------------------
12경영지원
123456재무
123456총무
12기술지원
123H/W지원
123S/W지원
123456영업
12영업기획
12345영업1
12345영업2
10 rows selected.
2 from tdept;
LPAD(DEPT_NAME,10,'1
--------------------
12경영지원
123456재무
123456총무
12기술지원
123H/W지원
123S/W지원
123456영업
12영업기획
12345영업1
12345영업2
10 rows selected.
[문제] tdept 테이블의 dept_name컬럼을 아래 예시처럼 출력되게 쿼리를 작성하시오.
SCOTT> select rpad(dept_name, 10, substr('1234567890', lengthb(dept_name)+1))
2 from tdept;
RPAD(DEPT_NAME,10,SU
--------------------
경영지원90
재무567890
총무567890
기술지원90
H/W지원890
S/W지원890
영업567890
영업기획90
영업167890
영업267890
10 rows selected.
SCOTT> select rpad(dept_name, 10, substr('1234567890', lengthb(dept_name)+1))
2 from tdept;
RPAD(DEPT_NAME,10,SU
--------------------
경영지원90
재무567890
총무567890
기술지원90
H/W지원890
S/W지원890
영업567890
영업기획90
영업167890
영업267890
10 rows selected.
◆ LTRIM과 RTRIM
LTRIM과 RTRIM은 회원가입 폼에서 공백 제거시 많이 쓰임.
[문제] 학생 테이블의 전화번호 컬럼에서 지역번호를 출력하시오.
SCOTT> select rtrim(substr(tel, 1, 3), ')') tel
2 from student;
TEL
------
051
055
055
051
051
055
055
051
055
02
051
053
055
052
02
055
16 rows selected.
SCOTT> select rtrim(substr(tel, 1, 3), ')') tel
2 from student;
TEL
------
051
055
055
051
051
055
055
051
055
02
051
053
055
052
02
055
16 rows selected.
◆ REPLACE
REPLACE 함수는 첫번째 인수를 두번째 인수로 바꿔주는 함수이다.
REPLACE(컬럼 또는 문자열, 문자1, 문자2);
[문제] student 테이블의 name 컬럼에서 성 부분을 # 처리하고 이름만 보이게 출력하세요.
SCOTT> select replace(name, substr(name, 1, 1), '#')
2 from student;
REPLACE(NA
----------
#인하
#동훈
#미경
#영균
#동진
#진영
#은경
#유석
#나리
#유진
#재진
#진욱
#광훈
#진경
#명훈
#민정
16 rows selected.
SCOTT> select replace(name, substr(name, 1, 1), '#')
2 from student;
REPLACE(NA
----------
#인하
#동훈
#미경
#영균
#동진
#진영
#은경
#유석
#나리
#유진
#재진
#진욱
#광훈
#진경
#명훈
#민정
16 rows selected.
[문제] student 테이블의 idnum 컬럼에서 주민번호 뒷자리 7자리를 *로 보이게 하시오.
SCOTT> select replace(idnum, substr(idnum, 7), '*******')
2 from student;
REPLACE(IDNUM,SUBSTR(IDNUM,7),'*******')
--------------------------------------------------------------------------------
790702*******
831210*******
840516*******
810321*******
851124*******
820606*******
800412*******
770912*******
850109*******
830121*******
851129*******
790402*******
810913*******
830228*******
841214*******
810819*******
16 rows selected.
SCOTT> select replace(idnum, substr(idnum, 7), '*******')
2 from student;
REPLACE(IDNUM,SUBSTR(IDNUM,7),'*******')
--------------------------------------------------------------------------------
790702*******
831210*******
840516*******
810321*******
851124*******
820606*******
800412*******
770912*******
850109*******
830121*******
851129*******
790402*******
810913*******
830228*******
841214*******
810819*******
16 rows selected.
◆ 숫자 함수
| 종류 | 의미 | 사용예 |
| ROUND | 지정한 자리 이하에서 반올림 | ROUND(123.17,1) -> 123.2 |
| TRUNC | 지정한 자리 이하에서 절삭(버림) | TRUNC(123.17,1) -> 123.1 |
| MOD(m, n) | m을 n으로 나눈 나머지 값 | MOD(12,10) -> 2 |
| CEIL | 지정한 값보다 큰 수 중에서 가장 작은 정수 | CEIL(123.17) -> 124 |
| FLOOR | 지정한 값보다 작은 수 중에서 가장 큰 정수 | FLOOR(123.17) -> 123 |
◆ 날짜 함수
| 종류 | 의미 | 결과 |
| SYSDATE | 시스템의 현재 날짜 | 날짜 |
| MONTHS_BETWEEN | 날짜와 날짜 사이의 개월을 계산 | 숫자 |
| ADD_MONTHS | 날짜에 개월을 더한 날짜 계산 | 날짜 |
| NEXT_DAY | 날짜 후의 첫 월요일 날짜를 계산 | 날짜 |
| LAST_DAY | 월의 마지막 날짜를 계산 | 날짜 |
| ROUND | 날짜를 반올림 | 날짜 |
| TRUNC | 날짜를 절삭 | 날짜 |
그래서 (SYSDATE-HIREDATE)/365로 년수 계산을 많이 한다.
LAST_DAY, NEXT_DAY는 평소에는 잘 쓰이지 않고, 이벤트 시 가끔 쓰임.
날짜에서 TRUNC는 무조건 당일날짜.
날짜에서 ROUND는 정오가 지나면 다음날짜, 안지나면 당일날짜.
SYSDATE는 날짜와 시간까지 가지고 있다.
SCOTT> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS')
2 from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2010-08-10:18:17:28
2 from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2010-08-10:18:17:28
◇ 날짜 계산
| 종류 | 결과 | 의미 |
| 날짜 + 숫자 | 날짜 | 날짜에 일수를 가산하여 날짜 계산 |
| 날짜 - 숫자 | 날짜 | 날짜에 일수를 감산하여 날짜 계산 |
| 날짜 - 날짜 | 일수 | 날짜와 날짜를 감산하여 일수 계산 |
| 날짜 + 숫자 / 24 | 날짜 | 날짜에 시간을 가산하여 날짜 계산 |
◆ 형변환
SQL> create table tt200(no1 number, no2 varchar2(10));
Table created.
SQL> insert into tt200 values (1, '1');
1 row created.
SQL> insert into tt200 values (2, '2');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tt200;
NO1 NO2
---------- ----------
1 1
2 2
결과를 보면 문자는 항상 왼쪽정렬, 숫자는 항상 오른쪽정렬이라는 것을 확인할 수 있다.Table created.
SQL> insert into tt200 values (1, '1');
1 row created.
SQL> insert into tt200 values (2, '2');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tt200;
NO1 NO2
---------- ----------
1 1
2 2
SQL> select no1, no2, no1+no2
2 from tt200;
NO1 NO2 NO1+NO2
---------- ---------- ----------
1 1 2
2 2 4
더한 결과를 보면 숫자이다(자동형변환됨). 단 숫자형태를 띈 것만 자동형변환이 가능하다.2 from tt200;
NO1 NO2 NO1+NO2
---------- ---------- ----------
1 1 2
2 2 4
SQL> insert into tt200 values (3, 'A');
1 row created.
SQL> select no1, no2, no1+no2
2 from tt200;
ERROR:
ORA-01722: invalid number
no rows selected
결과는 아예 안나온다. 왜냐하면 3, 'A'를 넣기 전에는 오라클이 문자 '1', '2'를 숫자로 이해해 주는데, 'A'를 넣은 후에는 명백히 문자이기 때문에 이해해주지 않는다.1 row created.
SQL> select no1, no2, no1+no2
2 from tt200;
ERROR:
ORA-01722: invalid number
no rows selected
◇ 묵시적 형변환
| A의 데이터타입 | B의 데이터타입 | 변환 결과 |
| NUMBER | VARCHAR2, CHAR | B가 NUMBER로 변환됨 |
| VARCHAR2, CHAR | NUMBER | A가 NUMBER로 변환됨 |
◇ 명시적 형변환
| 종류 | 의미 | 사용예 | 결과 |
| TO_CHAR | 숫자, 날짜를 문자타입으로 | TO_CHAR('07-12', 'YYYY-MM') | 2007-12 |
| TO_NUMBER | 문자열을 숫자타입으로 | TO_NUMBER('1000') | 1,000 |
| TO_DATE | 문자열을 날짜타입으로 | TO_DATE('05/03', 'YYYY-MM') | 0005-03 |
◇ 사용가능한 포맷 형식
| 종류 | 의미 | 사용예 | 결과 |
| 9 | 한자리의 숫자 표시 | (1234, '99999') | 1234 |
| 0 | 앞부분을 0으로 표시 | (1234, '099999') | 001234 |
| $ | 달러 기호를 앞에 표시 | (1234, '$99999') | $1234 |
| . | 소수점을 표시 | (1234, '99999.99') | 1234.00 |
| , | 특정위치에 콤마 표시 | (1234, '99,999') | 1,234 |
| MI | 오른쪽에 - 기호 표시 | (1234, '99999MI') | 1234- |
| PR | 음수값을 <>로 표시 | (1234, '99999PR') | <1234> |
| EEEE | 과학적 표기법으로 표시 | (1234, '9,999EEEE') | 1.234E+03 |
| V | 10n을 곱한 값으로 표시 | (1234, '9999V99') | 123400 |
| B | 공백을 0으로 표시 | (1234, 'B9999.99') | 1234.00 |
SCOTT> select sysdate "현재날짜", to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "YYYY",
2 to_char(sysdate, 'YY-MON-DAY:HH:MI:SS') "YY",
3 to_char(sysdate, 'YEAR/MM/DD') "YEAR"
4 from dual
현재날짜 YYYY YY YEAR
------------ ------------------- ------------------------- --------------------
16-AUG-10 2010-08-16:14:52:14 10-AUG-MONDAY :02:52:14 TWENTY TEN/08/16
이런 식으로 포맷형식을 사용하면 된다.2 to_char(sysdate, 'YY-MON-DAY:HH:MI:SS') "YY",
3 to_char(sysdate, 'YEAR/MM/DD') "YEAR"
4 from dual
현재날짜 YYYY YY YEAR
------------ ------------------- ------------------------- --------------------
16-AUG-10 2010-08-16:14:52:14 10-AUG-MONDAY :02:52:14 TWENTY TEN/08/16
◆ 중첩함수
일반적으로 단일행 함수는 중첩하여 실행할 수 있다. 중첩된 함수의 실행순서는 맨 안쪽에 있는 함수부터 실행하며, 결과값을 가장 가까운 바깥쪽 함수의 인수로 넘겨 처리한다.
F3 ( F2 ( F1 ( column, arg1 ), arg2 ), arg3)
◆ 널 변환 함수(NVL, NVL2)
주의사항은 반드시 두 값은 동일한 데이터 타입이어야 한다.
SQL> select sal, comm, (sal*12)+comm
2 from emp;
SAL COMM (SAL*12)+COMM
---------- ---------- -------------
3600
3000
800
1600 300 19500
1250 500 15500
2975
1250 1400 16400
2850
2450
3000
5000
1500 0 18000
1100
950
3000
1300
16 rows selected.
위 결과에서 comm 컬럼의 값이 널인 것은 아무리 연산을 해도 널이 나오는 것을 확인할 수 있다.2 from emp;
SAL COMM (SAL*12)+COMM
---------- ---------- -------------
3600
3000
800
1600 300 19500
1250 500 15500
2975
1250 1400 16400
2850
2450
3000
5000
1500 0 18000
1100
950
3000
1300
16 rows selected.
SQL> select sal, comm, (sal*12)+nvl(comm,0)
2 from emp;
SAL COMM (SAL*12)+NVL(COMM,0)
---------- ---------- --------------------
3600 43200
3000 36000
800 9600
1600 300 19500
1250 500 15500
2975 35700
1250 1400 16400
2850 34200
2450 29400
3000 36000
5000 60000
1500 0 18000
1100 13200
950 11400
3000 36000
1300 15600
2 from emp;
SAL COMM (SAL*12)+NVL(COMM,0)
---------- ---------- --------------------
3600 43200
3000 36000
800 9600
1600 300 19500
1250 500 15500
2975 35700
1250 1400 16400
2850 34200
2450 29400
3000 36000
5000 60000
1500 0 18000
1100 13200
950 11400
3000 36000
1300 15600
16 rows selected.
널 변환 함수인 nvl을 적용하면 comm 컬럼의 값 중 널인 것은 0으로 대체하여 처리를 한다.nvl(job, '백수') // job 컬럼의 값이 널이면 백수
nvl(입사일, sysdate) // 입사일 컬럼의 값이 널이면 현재 일자
nvl(입사일, sysdate) // 입사일 컬럼의 값이 널이면 현재 일자
nvl(a, b)
nvl2(a, b, c)
첫번째는 a가 널이면 b를 써라.nvl2(a, b, c)
두번째는 a가 널이 아니면 b를 쓰고, 널이면 c를 써라.
◆ DECODE 함수
오라클에서만 제공하는 유용한 함수이다. 프로그래밍의 if문과 유사한 기능.
SQL> select decode(expression | column, search1, result1[, search2, result2, ...][, default])
from table_name;
표현식 또는 컬럼의 값이 search1값과 일치하면 result1값을 반환하고, search2값과 일치하면 result2값을 반환.from table_name;
일치하는 값이 없거나 널인 경우에는 기본값을 반환.
기본값이 없는 경우에는 널을 반환.
[문제] 교수 테이블을 조회하여 아래와 같이 근속연수를 계산하여 교수 중 가장 오랜 근속연수를 가진 교수 중 김도훈 교수에게 석좌교수후보라는 문구를 삽입하여 출력하시오.
(근속연수는 계산 후 소수점 첫째 자리까지 나오게 반올림하시고 정렬은 현재직급별로 정렬 후 각 직급별로는 근속연수가 많은 사람 순으로 정렬하시오.)
SCOTT> select profno "교수번호",
2 name "교수이름",
3 position "현재직급",
4 round((sysdate-hiredate)/365, 1) "근속연수",
5 sal "급여",
6 decode(position, '교수', decode(name, '김도훈', '석좌교수후보')) "비고사항"
7 from professor
8 order by 3, 4 desc
교수번호 교수이름 현재직급 근속연수 급여 비고사항
---------- ---------- -------------------- ---------- ---------- ------------
9901 김도훈 교수 28.2 500 석좌교수후보
9905 권혁일 교수 24.6 450
9906 이만식 부교수 21.9 420
9908 남은혁 부교수 19.8 400
9904 염일웅 전임강사 11.7 240
9907 전은지 전임강사 9.2 210
9903 성연희 조교수 17.3 360
9902 이재우 조교수 15.4 320
8 rows selected.
(근속연수는 계산 후 소수점 첫째 자리까지 나오게 반올림하시고 정렬은 현재직급별로 정렬 후 각 직급별로는 근속연수가 많은 사람 순으로 정렬하시오.)
SCOTT> select profno "교수번호",
2 name "교수이름",
3 position "현재직급",
4 round((sysdate-hiredate)/365, 1) "근속연수",
5 sal "급여",
6 decode(position, '교수', decode(name, '김도훈', '석좌교수후보')) "비고사항"
7 from professor
8 order by 3, 4 desc
교수번호 교수이름 현재직급 근속연수 급여 비고사항
---------- ---------- -------------------- ---------- ---------- ------------
9901 김도훈 교수 28.2 500 석좌교수후보
9905 권혁일 교수 24.6 450
9906 이만식 부교수 21.9 420
9908 남은혁 부교수 19.8 400
9904 염일웅 전임강사 11.7 240
9907 전은지 전임강사 9.2 210
9903 성연희 조교수 17.3 360
9902 이재우 조교수 15.4 320
8 rows selected.
◆ CASE 함수
DECODE 함수는 표현식의 값을 '=' 밖에 사용할 수 없지만 CASE함수는 다양한 표현식을 사용할 수 있다.
SQL> select case expression when comparison_exp1 then return_exp1
[when comparison_exp2 then return_exp2
when comparison_exp3 then return_exp3
else else_expression]
end
from table_name;
CASE문의 문법엔 콤마가 들어가지 않는다.[when comparison_exp2 then return_exp2
when comparison_exp3 then return_exp3
else else_expression]
end
from table_name;
[문제] 교수테이블을 조회하여 교수의 급여액수를 기준으로 200 이하는 4급, 201~300까지는 3급, 301~400까지는 2급, 401 이상은 1급으로 표시하여 교수번호, 교수이름, 급여, 등급을 출력하시오.
단, 교수등급을 기준으로 오름차순 정렬하시오.
SCOTT> select profno "교수번호",
2 name "교수이름",
3 sal "급여",
4 case when sal >= 401 then '1급'
5 when sal between 301 and 400 then '2급'
6 when sal between 201 and 300 then '3급'
7 else '4급'
8 end "교수등급"
9 from professor
10 order by 4
교수번호 교수이름 급여 교
---------- ---------- ---------- ---
9901 김도훈 500 1급
9905 권혁일 450 1급
9906 이만식 420 1급
9902 이재우 320 2급
9903 성연희 360 2급
9908 남은혁 400 2급
9907 전은지 210 3급
9904 염일웅 240 3급
8 rows selected.
단, 교수등급을 기준으로 오름차순 정렬하시오.
SCOTT> select profno "교수번호",
2 name "교수이름",
3 sal "급여",
4 case when sal >= 401 then '1급'
5 when sal between 301 and 400 then '2급'
6 when sal between 201 and 300 then '3급'
7 else '4급'
8 end "교수등급"
9 from professor
10 order by 4
교수번호 교수이름 급여 교
---------- ---------- ---------- ---
9901 김도훈 500 1급
9905 권혁일 450 1급
9906 이만식 420 1급
9902 이재우 320 2급
9903 성연희 360 2급
9908 남은혁 400 2급
9907 전은지 210 3급
9904 염일웅 240 3급
8 rows selected.
[문제] student 테이블을 조회하여 각 월별 생일자가 몇 명인지 출력하시오.
SCOTT> select count(decode(to_char(birthdate, 'MM'), '01', 1)) "01",
2 count(decode(to_char(birthdate, 'MM'), '02', 1)) "02",
3 count(decode(to_char(birthdate, 'MM'), '03', 1)) "03",
4 count(decode(to_char(birthdate, 'MM'), '04', 1)) "04",
5 count(decode(to_char(birthdate, 'MM'), '05', 1)) "05",
6 count(decode(to_char(birthdate, 'MM'), '06', 1)) "06",
7 count(decode(to_char(birthdate, 'MM'), '07', 1)) "07",
8 count(decode(to_char(birthdate, 'MM'), '08', 1)) "08",
9 count(decode(to_char(birthdate, 'MM'), '09', 1)) "09",
10 count(decode(to_char(birthdate, 'MM'), '10', 1)) "10",
11 count(decode(to_char(birthdate, 'MM'), '11', 1)) "11",
12 count(decode(to_char(birthdate, 'MM'), '12', 1)) "12",
13 count(birthdate) "합"
14 from student
01 02 03 04 05 06 07 08 09 10 11 12 합
--- --- --- --- --- --- --- --- --- --- --- --- ---
2 1 0 2 1 1 1 1 1 2 2 2 16
SCOTT> select count(decode(to_char(birthdate, 'MM'), '01', 1)) "01",
2 count(decode(to_char(birthdate, 'MM'), '02', 1)) "02",
3 count(decode(to_char(birthdate, 'MM'), '03', 1)) "03",
4 count(decode(to_char(birthdate, 'MM'), '04', 1)) "04",
5 count(decode(to_char(birthdate, 'MM'), '05', 1)) "05",
6 count(decode(to_char(birthdate, 'MM'), '06', 1)) "06",
7 count(decode(to_char(birthdate, 'MM'), '07', 1)) "07",
8 count(decode(to_char(birthdate, 'MM'), '08', 1)) "08",
9 count(decode(to_char(birthdate, 'MM'), '09', 1)) "09",
10 count(decode(to_char(birthdate, 'MM'), '10', 1)) "10",
11 count(decode(to_char(birthdate, 'MM'), '11', 1)) "11",
12 count(decode(to_char(birthdate, 'MM'), '12', 1)) "12",
13 count(birthdate) "합"
14 from student
01 02 03 04 05 06 07 08 09 10 11 12 합
--- --- --- --- --- --- --- --- --- --- --- --- ---
2 1 0 2 1 1 1 1 1 2 2 2 16
[문제] student 테이블을 조회하여 전화번호의 지역번호별로
02:서울, 051:부산, 052:울산, 053:대구, 055:경남, 나머지:기타지역
으로 분류하고 각 지역별로 인원이 몇 명인지 아래와 같이 출력하세요.
SCOTT> select count(decode(rtrim(substr(tel, 1, 3), ')'), '02', '서울')) "서울",
2 count(decode(rtrim(substr(tel, 1, 3), ')'), '051', '부산')) "부산",
3 count(decode(rtrim(substr(tel, 1, 3), ')'), '052', '울산')) "울산",
4 count(decode(rtrim(substr(tel, 1, 3), ')'), '053', '대구')) "대구",
5 count(decode(rtrim(substr(tel, 1, 3), ')'), '055', '경남')) "경남",
6 count(tel) "합계"
7 from student
서울 부산 울산 대구 경남 합계
---------- ---------- ---------- ---------- ---------- ----------
2 5 1 1 7 16
이런 식으로 rtrim을 쓰지 않고 instr을 사용할 수도 있다.
count(decode(to_char(substr(tel, 1, instr(tel, ')', 1, 1)-1)), '02', 0)) "서울"
...
count(*) "합계"
02:서울, 051:부산, 052:울산, 053:대구, 055:경남, 나머지:기타지역
으로 분류하고 각 지역별로 인원이 몇 명인지 아래와 같이 출력하세요.
SCOTT> select count(decode(rtrim(substr(tel, 1, 3), ')'), '02', '서울')) "서울",
2 count(decode(rtrim(substr(tel, 1, 3), ')'), '051', '부산')) "부산",
3 count(decode(rtrim(substr(tel, 1, 3), ')'), '052', '울산')) "울산",
4 count(decode(rtrim(substr(tel, 1, 3), ')'), '053', '대구')) "대구",
5 count(decode(rtrim(substr(tel, 1, 3), ')'), '055', '경남')) "경남",
6 count(tel) "합계"
7 from student
서울 부산 울산 대구 경남 합계
---------- ---------- ---------- ---------- ---------- ----------
2 5 1 1 7 16
이런 식으로 rtrim을 쓰지 않고 instr을 사용할 수도 있다.
count(decode(to_char(substr(tel, 1, instr(tel, ')', 1, 1)-1)), '02', 0)) "서울"
...
count(*) "합계"
[문제] 위의 결과에서 나머지 기타지역도 출력하시오.
SCOTT> select count(decode(rtrim(substr(tel, 1, 3), ')'), '02', '서울')) "서울",
2 count(decode(rtrim(substr(tel, 1, 3), ')'), '051', '부산')) "부산",
3 count(decode(rtrim(substr(tel, 1, 3), ')'), '052', '울산')) "울산",
4 count(decode(rtrim(substr(tel, 1, 3), ')'), '053', '대구')) "대구",
5 count(decode(rtrim(substr(tel, 1, 3), ')'), '055', '경남')) "경남",
6 count(case when rtrim(substr(tel, 1, 3), ')') not in (02, 051, 052, 053, 055) then 1 end) "나머지",
7 count(tel) "합계"
8 from student
서울 부산 울산 대구 경남 나머지 합계
---------- ---------- ---------- ---------- ---------- ---------- ----------
2 5 1 1 7 0 16
SCOTT> select count(decode(rtrim(substr(tel, 1, 3), ')'), '02', '서울')) "서울",
2 count(decode(rtrim(substr(tel, 1, 3), ')'), '051', '부산')) "부산",
3 count(decode(rtrim(substr(tel, 1, 3), ')'), '052', '울산')) "울산",
4 count(decode(rtrim(substr(tel, 1, 3), ')'), '053', '대구')) "대구",
5 count(decode(rtrim(substr(tel, 1, 3), ')'), '055', '경남')) "경남",
6 count(case when rtrim(substr(tel, 1, 3), ')') not in (02, 051, 052, 053, 055) then 1 end) "나머지",
7 count(tel) "합계"
8 from student
서울 부산 울산 대구 경남 나머지 합계
---------- ---------- ---------- ---------- ---------- ---------- ----------
2 5 1 1 7 0 16
'오라클 > SQL' 카테고리의 다른 글
| [SQL] 오라클 전용 Join (0) | 2010.08.17 |
|---|---|
| [SQL] 그룹함수 (0) | 2010.08.16 |
| [SQL] 조건 검색 (0) | 2010.08.10 |
| [SQL] 오라클 데이터 타입 (0) | 2010.08.09 |
| [SQL] SELECT 문장 사용 방법 (0) | 2010.08.09 |
