대소문자 구분 함수
|
종류 |
의미 |
사용예 |
|
INITCAP |
문자열의 첫번째 문자만 대문자로변환 |
INITCAP(student) → Student |
|
LOWER |
문자열 전체를 소문자로변환 |
LOWER(ABC) → abc |
|
UPPER |
문자열 전체를 대문자로변환 |
UPPER(abc) → ABC |
문제 : 학생 테이블에서 '김영균' 학생의 이름, 사용자 아이디를 출력하되 사용자
아이디는 첫글자가 대문자로 변환하여 출력하여라.
SQL> select name, initcap(userid)
from student
where name = '김영균';
문제 : 학생 테이블에서 학번이 '20101' 인 학생의 사용자 아이디를 소문자와
대문자로 변환하여 출력하여라.
SQL> select lower(userid), upper(userid)
from student
where studno = 20101;
문자열 길이 변환 함수
|
종류 |
의미 |
사용예 |
|
LENGTH |
문자열의길이를반환 |
LENGTH('홍길동‘) → 3 |
|
LENGTHB |
문자열의바이트수를반환 |
LENGTHB('홍길동‘) → 6 |
예제 : 부서 테이블(department)에서 부서 이름의 길이를 문자수와 바이트수로
각각 출력하여라
SQL> SELECT dname, LENGTH(dname), LENGTHB(dname)
FROM department
문자 조작 함수
|
종류 |
의미 |
사용예 |
|
CONCAT |
두문자열을결합.||와동일 |
CONCAT('sql','plus')->sqlplus |
|
SUBSTR |
특정문자또는문자열일부를추출 |
SUBSTR('SQL*PLUS',5,4)->Plus |
|
,5,4) --> 5번째 자리부터 4글자 뽑아라,…. ,-4, 4)? , SUBSTRB는 바이트 | ||
|
| ||
|
INSTR |
특정문자가출현하는첫번째위치반환 |
INSTR('SQL*Plus','*‘,1,1)->4 |
|
,1,1) 에서 앞의 1은 시작 위치, 뒤의 1은 원하는 글자 위치 (원하는 글자가 여러 개 있을 경우, 첫번째냐 두번째냐 세번째냐 지정) | ||
|
| ||
|
LPAD |
오른쪽정렬후왼쪽에지정한문자삽입 |
LPAD('sql',5,'*')->**sql |
|
RPAD |
왼쪽정렬후오른쪽에지정한문자삽입 |
RPAD('sql',5,'*')->sql** |
|
LTRIM |
왼쪽의지정문자를삭제 |
LTRIM('*sql','*')->sql |
|
RTRIM |
오른쪽의지정문자를삭제 |
RTRIM('sql*','*')->sql |
|
REPLACE |
첫 번째 인수를 두 번째 인수로 교체 |
REPLACE('sql**','*','#')->sql## |
예제 : 학생 테이블에서 1학년 학생의 주민등록번호에서 생년월일과 태어난 달을 추출하여
이름, 주민등록번호, 생년월일, 태어난 달을 출력하여라.
SQL> SELECT name, idnum, SUBSTR(idnum,1,6) birth_date,
SUBSTR(idnum,3,2) birth_mon
FROM student
WHERE grade = '1';
예제 : 부서 테이블의 부서이름 칼럼에서 '과' 글자의 위치를 출력하여라
SQL> SELECT dname, INSTR(dname, '과',1,1)
FROM department;
문제 : 학생 테이블의 전화번호 칼럼에서 지역번호를 출력하여라.
SQL> select substr(tel,1,instr(tel,')',1,1)-1)
from student;
SQL> Select substr(tel,1,instr(tel,’)’)-1) “지역”
From student;
SQL> Select RTRIM(SUBSTR(tel,1,3),’)’) as "지역"
From student;
문제 : 학생테이블에서 userid 에서 2번째 a의 위치가 5번째인 학생의
name,userid 를 출력하세요
NAME USERID
---------- ----------
윤진욱 Samba7
SQL> select name, userid
from student
where instr(userid,'a',1,2)=5;
LPAD, RPAD 함수
예제 : 교수 테이블에서 직급 칼럼의 왼쪽에 '*' 문자를 삽입하여 10바이트로 출력하고
교수 아이디 칼럼은 오른쪽에 '+' 문자를 삽입하여 12 바이트로 출력하여라.
SQL> SELECT position, LPAD(position,10,'*') lpad, userid,
RPAD(userid,12,'+') rpad
FROM professor;
문제 : TDEPT 테이블의 dept_name 컬럼을 아래처럼 출력되게 하세요
12경영지원
123456재무
123456총무
12기술지원
123H/W지원
……………
SQL> select lpad(dept_name,10,'1234567890')
2 from tdept；
문제 : TDEPT 테이블의 DEPT_NAME 컬럼을 아래 예시처럼 출력 되게 쿼리를 작성하세요
경영지원90
재무567890
총무567890
기술지원90
H/W지원890
S/W지원890
영업567890
영업기획90
영업167890
영업267890
10 rows selected
SQL> select rpad(dept_name,10,
2 substr('1234567890',lengthb(dept_name)+1,10))
3 from tdept;
LTRIM, RTRIM 함수
예제 : 부서 테이블에서 부서 이름의 마지막 글자인 ‘과’를 삭제하여 출력하여라.
SQL> SELECT dname, RTRIM(dname, '과‘)
FROM department;
문제 : student 테이블의 name 컬럼에서 성 부분을 # 처리하고 이름만 보이게 출력하세요.
SQL> select replace(name,substr(name,1,1),'#')
from student;
REPLACE(NAME,SUBSTR(
--------------------
#인하
#동훈
#미경
.................
16 rows selected.
문제 : student 테이블의 idnum 컬럼에서 주민번호 뒷자리 7자리를 * 로 보이게 출력하세요
SQL> select replace(idnum,substr(idnum,7,7),'******')
from student;
REPLACE(IDNUM,SUBSTR(IDNUM,7,7),'******')
-------------------------------------------
790702******
831210******
문제 : Student 테이블에서 tel 컬럼의 국번을 아래와 같이 되도록 출력하세요.
단, 모든 국번은 3자리라고 가정합니다
NAME TEL REPLACE
--------- --------------- ---------------
전인하 051)781-2158 051)###-2158
이동훈 055)426-1752 055)###-1752
………….
임유진 02)312-9838 02)###-9838
***************************************************************
SQL> select name, tel,
replace(tel,substr(tel,instr(tel,')',1)+1,
(instr(tel,'-',1))-(instr(tel,')',1)+1)),'###')
from student;
****************************************************************
SQL> select replace(tel,
substr(tel,instr(tel,')')+1,3),'###')
from student;
SQL> select replace(tel,
substr(tel,instr(tel,')',1,1)+1,3),'###')
from student;
숫자 함수
|
종류 |
의 미 |
사용예 |
|
ROUND |
지정한자리이하에서반올림 |
ROUND(123.17,1) → 123.2 |
|
TRUNC |
지정한자리이하에서절삭 |
TRUNC(123.17,1) → 123.1 |
|
MOD |
m을n으로나눈나머지값 |
MOD(12,10) → 2 |
|
CEIL |
지정한값보다큰수중에서가장작은정수 |
CEIL(123.17) → 124 |
|
FLOOR |
지정한값보다작은수중에서가장큰정수 |
FLOOR(123.17) → 123 |
예제 : (자릿수 엄청 중요함)
SQL> select round(123.12316,4), trunc(123.7653,2),
ceil(123.4512), floor(123.677), mod(12,5)
from student;
ROUND(123.12316,4) TRUNC(123.7653,2) CEIL(123.4512) FLOOR(123.677) MOD(12,5)
------------------ ----------------- -------------- -------------- ----------
123.1232 123.76 124 123 2
ROUND 함수
예제 : 교수 테이블에서 101번 학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 반올림 한 값과 소수점 왼쪽 첫째 자리에서 반올림 한 값을 출력하여라.
SQL> select name, sal, sal/22, round(sal/22), round(sal/22,2), round(sal/22,-1)
from professor
where deptno = 101;
NAME SAL SAL/22 ROUND(SAL/22) ROUND(SAL/22,2) ROUND(SAL/22,-1)
-------------------- ---------- ---------- ------------- --------------- ----------------
김도훈 500 22.7272727 23 22.73 20
성연희 360 16.3636364 16 16.36 20
이만식 420 19.0909091 19 19.09 20
전은지 210 9.54545455 10 9.55 10
TRUNC 함수
예제 : 교수 테이블에서 101번 학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 절삭한 값과 소숫점 왼쪽 첫째자리에서 절삭한 값을 출력하여라.
SQL> SELECT name, sal, sal/22, TRUNC(sal/22), TRUNC(sal/22,2),
TRUNC(sal/22,-1)
FROM professor
WHERE deptno=101;
MOD 함수
예제 : 교수 테이블에서 101번 학과 교수의 급여를 보직수당으로 나눈 나머지를 계산하여 출력하여라.
SQL> SELECT name, sal, comm, MOD(sal,comm)
FROM professor
WHERE deptno = 101;
CEIL,FLOOR 함수
예제 : 19.7보다 큰 정수 중에서
가장 작은 정수와 12.345보다 작은 정수 중에서 가장 큰 정수를 출력하여라
SQL> SELECT CEIL(19.7),FLOOR(12.345)
FROM dual;
|
종 류 |
의 미 |
결 과 |
|
SYSDATE |
시스템의 현재 날짜 |
날짜 |
|
MONTHS_BETWEEN |
날짜와 날짜 사이의 개월을 계산 |
숫자 |
|
1월1일, 7월1일 과 1월31일, 7월1일 을 MONTHS_BETWEEN 쓰면 결과값이 같으므로 사용할때 주의한다 | ||
|
| ||
|
ADD_MONTHS |
날짜에 개월을 더한 날짜 계산 |
날짜 |
|
NETX_DAY |
날짜후의 첫 월요일 날짜를 계산 |
날짜 |
|
LAST_DAY |
월의 마지막 날짜를 계산 |
날짜 |
|
ROUND |
날짜를 반올림 |
날짜 |
|
TRUNC |
날짜를 절삭 |
날짜 |
날짜 + 숫자 = 날짜
날짜 - 숫자 = 날짜
날짜 - 날짜 = 일수
예제 : 교수번호가 9908인 교수의 입사일을 기준으로 입사 30일 후와 60일 후의 날짜를 출력하여라
SQL> SELECT name, hiredate, hiredate+30, hiredate+60
FROM professor
WHERE profno = 9908;
※ 참고 : MONTHS_BETWEEN 비교
SQL> select (sysdate-hiredate)/30 "A_DATE",
months_between(sysdate,hiredate) B_date
from professor;
A_DATE B_DATE
---------- ----------
359.522061 354.34393
203.688728 200.731027
226.855395 223.569737
159.355395 157.053608
316.388728 311.860059
SYSDATE
예제 : 시스템의 현재 날짜를 출력하여라.
SQL> SELECT SYSDATE FROM dual;
MONTHS_BETWEEN, ADD_MONTHS
MONTHS_BETWEEN(date1,date2) - date1과 date2 사이의 개월 수 계산
ADD_MONTHS(date,개월 수) - date에 개월 수를 더한 날짜 계산
예제 : 입사한지 120개월 미만인 교수의 교수번호, 입사일, 입사일로부터
현재일까지의 개월수, 입사일에서 6개월 수의 날짜를 출력하여라.
SQL> SELECT profno, hiredate,
MONTHS_BETWEEN(SYSDATE,hiredate) TENURE,
ADD_MONTHS(hiredate,6) REVIEW
FROM professor
WHERE MONTHS_BETWEEN(SYSDATE,hiredate) < 120;
LAST_DAY,NEXT_DAY
NEXT_DAY : date 날짜 이후의 첫 번째 'day' 요일의 날짜를 계산
LAST_DAY : date 날짜가 속한 달의 마지막 날짜를 계산
예제 : 오늘이 속한 달의 마지막 날짜와 다가오는 토요일의 날짜 출력하기
SQL> SELECT SYSDATE,LAST_DAY(SYSDATE),
NEXT_DAY(SYSDATE,'SAT')
FROM dual;
ROUND, TRUNC 함수
ROUND (date[, 'format']) - 날짜를 반올림
TRUNC (date[, 'format']) - 날짜를 절삭
예제 : 시간 정보 생략하는 경우 ROUND함수,TRUNC함수 비교하기
SQL> SELECT TO_CHAR(SYSDATE,'YY/MM/DD HH24:MI:SS')NORMAL,
TO_CHAR(TRUNC(SYSDATE), 'YY/MM/DD HH24:MI:SS') TRUNC,
TO_CHAR(ROUND(SYSDATE), 'YY/MM/DD HH24:MI:SS') ROUND
FROM dual;
출력결과
NORMAL TRUNC ROUND
-------------- --------------- ---------------
07/12/01 20:56:26 07/12/01 00:00:00 07/12/02 00:00:00
해설: ROUND 함수는 정오를 넘으면 다음날을 출력한다.
TRUNC 함수는 시간정보와 상관없이 당일 날을 출력한다.
두 함수 모두 시간정보는 00:00:00을 출력한다.
데이터 타입 변환
날짜를 찍는 방법
|
날짜 : 년도 : YYYY - 2012 , YY - 12 , YEAR - 전체이름 RRRR - 2012 , RR - 12 → 권장사항 10g이상은.. 월 : MM - 01 , MON - JAN , month : 전체이름 일 : DD - 03 , DAY - 전체이름 시간 : HH - 12시간제 , HH24 - 24시간제 분 : MI 초 : SS |
|
종 류 |
의 미 |
사 용 예 |
결 과 |
|
TO_CHAR |
숫자,날짜를 문자타입으로 변환 |
TO_CHAR('07-12','YYYY-MM') |
Dec-07 |
|
TO_NUMBER |
문자열을 숫자타입으로 변환 |
TO_NUMBER('1000') |
1000 |
|
TO_DATE |
문자열을 날짜타입으로 변환 |
TO_DATE('05/03','YYYY-MM') |
0005-03 |
예제 : 학생테이블에서 전인하 학생의 학번과 생년월일중에서 년월만 출력하기
SQL> SELECT studno, TO_CHAR(birthdate,'YY-MM') birthdate
FROM student
WHERE name = '전인하';
SQL> select sysdate, to_char(sysdate,'YYYY-MM-DD') "YYYY",
to_char(sysdate,'YY-MM-DD') "YY",
to_char(sysdate,'Year/MON/DAY') "Year"
from dual;
SYSDATE YYYY YY Year
------------ -------------------- ---------------- ----------
03-JAN-12 2012-01-03 12-01-03 Twenty Twelve/JAN/TUESDAY
SQL> select sysdate, to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss')
from dual;
SYSDATE TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS
------------ --------------------------------------
03-JAN-12 2012-01-03:16:29:36
예제 : 교수 테이블에서 101번 학과 교수들의 이름, 직급, 입사일을 출력하여라.
SQL> SELECT name, position,
TO_CHAR(hiredate, 'MON "the" DDTH "of" YYYY') hiredate
FROM professor
WHERE deptno=101;
문제 : 학생테이블의 birthdate 컬럼을 이용하여 생일이 5월인 사람만 이름,생일 을 출력하세요.
SQL> select name, birthdate
from student
where to_char(birthdate, 'MM')='05';
NAME BIRTHDATE
-------------------- ------------
박미경 16-MAY-84
|
종 류 |
의 미 |
사용예 |
결 과 |
|
9 |
한자리의숫자표시 |
(1234,‘99999’) |
1234 |
|
0 |
앞부분을0으로표시 |
(1234,‘099999‘) |
1234 |
|
$ |
달러기호를앞에표시 |
(1234,‘$99999’) |
$1,234 |
|
. |
소수점을표시 |
(1234,‘99999.99’) |
1234 |
|
, |
특정위치에‘,’표시 |
(1234,‘99,999’) |
1,234 |
|
MI |
오른쪽에-기호표시 |
(1234,‘99999MI') |
1234- |
|
PR |
음수값을<>으로표시 |
(1234,‘99999PR') |
<1234> |
|
EEEE |
과학적표기법으로표시 |
(1234,‘9.999EEEE) |
1.23E+03 |
|
V |
10n을곱한값으로표시 |
(1234,‘9999V99') |
123400 |
|
B |
공백을0으로표시 |
(1234,‘B9999.99') |
1234 |
예제 : 보직수당을 받는 교수들의 이름, 급여, 보직수당, 그리고 급여와 보직수당을 더한 값에 12를 곱한 결과를 연봉으로 출력하여라.
SQL> SELECT name, sal, comm, TO_CHAR((sal+comm)*12,'9,999') sal2
FROM professor
WHERE comm IS NOT NULL;
문법) To_NUMBER(char)
예제 : 문자인 1을 숫자로..
SQL> SELECT TO_NUMBER('1') num
FROM dual;
문법) TO_DATE(char, 'format')
예제: 교수 테이블에서 입사일이 'june 01,01' 인 교수의 이름과 입사일을 출력하여라.
SQL> SELECT name, hiredate
FROM professor
WHERE hiredate = TO_DATE( 'june 01,01', 'MONTH DD, YY');
중첩함수
문법) F3(F2(F1(column, arg1), arg2, arg3)
예제 : 학생 주민등록번호에서 생년월일을 추출하여 'YY/MM/DD'로 출력하라.
SQL> SELECT TO_CHAR(TO_DATE(SUBSTR(idnum, 1, 6),'YYMMDD'),
'YY/MM/DD') hiredate
FROM student;
nvl(a,b) : a가 null이면 b로 치환해라.
nvl2(a,b,c) : a가 null 아니면 b를 쓰고
a가 null 이면 c를 써라.
예제 : 201번 학과 교수의 이름, 직급, 보직수당, 급여와 보직수당의 합계를 출력
하되 보직수당이 NULL인 경우에는 보직수당을 0으로 계산한다.
SQL> SELECT name, position, sal, comm, sal+comm, sal+NVL(comm,0)s1,
NVL(sal+comm, sal)s2
FROM professor
WHERE deptno=201;
SQL> select name, to_char(nvl2(profno, profno, 0), '0999')
from student;
NAME TO_CH
---------- -----
전인하 9903
이동훈 0000
박미경 0000
김영균 9906
박동진 0000
DECODE 함수
예제 : 교수 테이블에서 교수의 소속 학과 번호를 학과 이름으로 변환하여 출력하여라.
학과 번호가 101이면 '컴퓨터공학과',102이면 '멀티미디어공학과',
201이면 '전자공학과', 나머지 학과 번호는 '기계공학과(default)로 변환하여라
SQL> SELECT name, deptno, DECODE(deptno,101,'컴퓨터공학과',
102,'멀티미디어 공학과',
201,'전자공학과',
'기계공학과') DNAME
FROM professor;
문제 : 102번 학과 학생들에 대해 주민등록번호의 7번째 문자가 1인 경우에는 ‘남자’,
2인 경우에는 ‘여자’로 변환하여 출력하여라.
SQL> select name,idnum,decode(substr(idnum,7,1),1,'남자',
2,'여자') 성별
from student
where deptno=102;
문제 : 교수테이블을 조회하여 아래와 같이 근속연수를 계산하여
교수 중 가장 오랜 근속연수를 가진 교수 중 김도훈 교수에게 석좌교수후보라는 문구를 삽입하여 출력하시오.
(근속연수는 계산 후 소수점 첫째 자리까지 나오게 반올림하시고
정렬은 현재직급별로 정렬 후 각 직급별로는 근속연수가 많은 사람 순으로 정렬하세요)
요약: 직급이 교수인 사람중에 김도훈 교수에게 석좌교수후보 입력 근속연수구하기
교수번호 교수이름 현재직급 근속연수 급여 비고사항
-------- -------- ---------- --------- -------- ----------
9901 김도훈 교수 28.1 500 석좌교수후보
9905 권혁일 교수 24.6 450
9906 이만식 부교수 21.9 420
9908 남은혁 부교수 19.7 400
9904 염일웅 전임강사 11.7 240
9907 전은지 전임강사 9.2 210
9903 성연희 조교수 17.2 360
9902 이재우 조교수 15.3 320
SQL> select profno "교수번호", name "교수이름", position "현재직급",
To_char(round((sysdate-hiredate)/365.1),'09,9') "근속연수", sal "급여",
decode(position,'교수',decode(name,'김도훈','석좌교수후보')) "비고사항"
from professor
order by 3 ,4 desc;
CASE 함수
예제 : 교수 테이블에서 소속학과에 따라 보너스를 다르게 계산하여 출력하여라.
학과 번호별로 보너스는 다음과 같이 계산한다. 학과번호가 101이면 급여의 10%
102 이면 20%, 201 이면 30%, 나머지 학과는 0% 이다.
SQL > SELECT name, deptno, sal,
CASE WHEN deptno=101 THEN sal*0.1
WHEN deptno=102 THEN sal*0.2
WHEN deptno=201 THEN sal*0.3
ELSE 0
END bonus
FROM professor;
NAME DEPTNO SAL BONUS
------ -------- -------- ---------
김도훈 101 500 50
..(이하중략)
CASE 문
문제 : 학생 테이블에서 키의 범위에 따라 ‘A’,’B’,’C’,’D’ 4개의 등급으로 나누어 출력
하여라. 등급 기호는 키가 180~190 이면 ‘A’, 170~179 이면 ‘B’, 160~169이면
‘C’, 160 미만이면 ‘D’ 이다. 아래의 공간에 쿼리를 적어보세요.
SQL> select name, height,
case when height between 180 and 190 then 'A'
when height between 170 and 179 then 'B'
when height between 160 and 169 then 'C'
else 'D'
end grade
from student;
문제 : 교수테이블 (professor) 를 조회하여 교수의 급여액수를 기준으로 200 이하는 4급, 201 – 300 까지는 3급 , 301 – 400 까지는 2급, 401 이상은 1급으로 표시하여 교수의 번호, 교수이름, 급여, 등급을 출력하세요.(단, 교수등급을 오름차순으로 정렬하세요)
SQL> select profno, name, sal,
case when sal<=200 then '4급'
when sal between 201 and 300 then '3급'
when sal between 301 and 400 then '2급'
when sal>=401 then '1급'
end grade
from professor
order by 4;
PROFNO NAME SAL GRADE
---------- -------------------- ---------- ------
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.
연습문제
* 학생 테이블을 참조하여 다음 질문에 답하세요(1-5)
1번************************************************************************************
1. 사용자 아이디에서 문자열 길이가 7 이상인 학생의 이름과 사용자 아이디를
출력하여라. 단, 사용자 아이디는 소문자로 출력하여라
SQL> select name, lower(userid)
2 from student
3 where length(userid)>=7;
NAME LOWER(USERID)
-------------------- --------------------
박미경 ansel414
임유진 youjin12
조명훈 rader214
류민정 cleansky
2번************************************************************************************
2. 주민등록번호를 기준으로 학생들의 이름, 사용자 아이디, 생년월일을 출력하되
사용자 아이디는 소문자로, 생년월일은 ‘2007-01-01’ 형식으로 출력하여라.
SQL> select name 이름, lower(userid) "사용자 아이디",
to_char(birthdate, 'YYYY-MM-DD') 생년월일
from student
order by to_char(idnum);
이름 사용자 아이디 생년월일
-------------------- -------------------- --------------------
오유석 yousuk 1977-10-12
윤진욱 samba7 1979-04-02
전인하 jun123 1979-07-02
지은경 gomo00 1980-04-12
김영균 mandu 1981-01-11
류민정 cleansky 1981-08-19
이광훈 huriky 1981-10-13
김진영 simply 1982-06-06
........
16 rows selected.
SQL> select name, lower(userid),
to_char(to_date(substr(idnum, 1, 6), 'rr-mm-dd'), 'yyyy-mm-dd')
from student;
3번************************************************************************************
3. 학생의 이름, 지도교수 번호를 출력하여라. 단, 지도교수가 배정되지 않은
학생은 지도 교수 번호를 0000 으로 출력하여라.
SQL> select name 이름, case when nvl(profno, 0000)=0000 then '0000'
else to_char(profno)
end "지도교수 번호"
from student;
이름 지도교수 번호
--------------- --------------------------
전인하 9903
이동훈 0000
박미경 0000
김영균 9906
박동진 0000
김진영 9905
지은경 9907
오유석 9905
하나리 0000
임유진 9907
서재진 0000
윤진욱 9905
이광훈 9903
김진경 9902
조명훈 0000
류민정 9907
16 rows selected.
SQL> select name, to_char(nvl2(profno, profno, 0), '0999')
from student;
SQL> select name, NVL2(to_char(profno),to_char(profno), '0000') “교수번호”
from student;
4번************************************************************************************
4. 학생을 3개 팀으로 분류하기 위해 학번을 3으로 나누어 나머지가 0이면 ‘A’,
1 이면 ‘B’, 2이면 ‘C’으로 분류하여 학생번호, 이름, 학과번호, 팀이름을
출력하여라.
SQL> select studno 학생번호, name 이름, deptno 학과번호,
case when mod(studno,3)=0 then 'A'
when mod(studno,3)=1 then 'B'
when mod(studno,3)=2 then 'C'
end 팀이름
from student;
학생번호 이름 학과번호 팀
---------- -------------------- ---------- --
10101 전인하 101 A
20101 이동훈 201 B
10102 박미경 101 B
10103 김영균 101 C
20102 박동진 201 C
10201 김진영 102 B
10104 지은경 101 A
.....
16 rows selected.
SQL> select studno, name, deptno,
decode(mod(studno, 3), 0, 'A',
1, 'B',
2, 'C'
) team
from student;
5번************************************************************************************
5. 학생 전화번호의 지역번호가 ’02’이면 ‘서울’, ‘051’이면 ‘부산’,
’052’이면 ‘울산’, ‘053’이면 ‘대구’, 나머지는 ‘Etc’ 로 하여
학생이름,전화번호,지역명(Loc)을 출력하여라.
SQL> select name 학생이름, tel 전화번호,
decode(substr(tel,1,instr(tel,')',1,1)-1),02,'서울',
051,'부산',
052,'울산',
053,'대구',
'Etc') "지역명(Loc)"
from student;
SQL> select name, tel,
decode(substr(tel, 1, instr(tel, ')' ,1)-1), '02', '서울',
'051', '부산',
'052', '울산',
'053', '대구',
'Etc') Loc
from student;
학생이름 전화번호 지역명(L
-------------------- -------------------------- --------
전인하 051)781-2158 부산
이동훈 055)426-1752 Etc
박미경 055)261-8947 Etc
김영균 051)824-9637 부산
박동진 051)742-6384 부산
김진영 055)419-6328 Etc
지은경 055)418-9627 Etc
오유석 051)724-9618 부산
하나리 055)296-3784 Etc
임유진 02)312-9838 서울
서재진 051)239-4861 부산
윤진욱 053)487-2698 대구
이광훈 055)736-4981 Etc
김진경 052)175-3941 울산
조명훈 02)785-6984 서울
류민정 055)248-3679 Etc
16 rows selected.
* 교수 테이블을 참조하여 아래의 질문에 답하세요 (6-7)
6번************************************************************************************
6. 교수 테이블에서 입사일이 1~3월인 모든 교수의 급여를 15% 인상하여 정수로
출력하되 반올림된 값과 절삭된 값을 출력하여라. 급여는 세자리마다 ‘,’ 기호를
삽입한다.
SQL> select name 교수명, to_char(round(sal*0.15,0)+sal,'99,999') 반올림,
to_char(trunc(sal*0.15,0)+sal,'99,999') 절삭
from professor
where to_char(hiredate,'MM') between 1 and 3;
교수명 반올림 절삭
-------------------- -------------- --------------
권혁일 518 517
SQL> select name, sal,
CASE when to_char(hiredate,'mm') between 1 and 3
THEN to_char(round(sal*1.15,0), '99,999')
END round,
CASE when to_char(hiredate,'mm') between 1 and 3
THEN to_char(trunc(sal*1.15),'99,999')
END trunc
from professor
wher to_char(hiredate,'mm') between 1 and 3;
7번************************************************************************************
7. 교수들의 근무 개월 수를 현재일을 기준으로 계산하되, 근무 개월순으로 정렬
하여 출력하여라. 단, 개월수의 소수점이하는 출력하지 않는다.
SQL> select name 교수이름, hiredate 입사일,
round(months_between(sysdate,hiredate)) 근무개월수
from professor
order by 3 desc;
교수이름 입사일 근무개월수
-------------------- ------------ ----------
김도훈 24-JUN-82 354
권혁일 08-JAN-86 312
....
8 rows selected.
SQL> select name, hi redate, trunc((sysdate-hiredate)/30, 0) work_month
from professor
order by 3 desc;
SQL> select trunc((sysdate-hiredate)/24,0) as 근무개월
from professor
order by 근무개월;
* 부서테이블을 참조하여 다음 질문에 적절한 SQL문을 작성하여라(8-9).
8번************************************************************************************
8.학과 이름과 위치를 결합하여 다음과 같은 형식으로 출력하여라.
형식) 컴퓨터공학과는 1호관
SQL> select dname||'는 '||loc
from department
where loc is not null;
----------------------------------------------------------
컴퓨터공학과는 1호관
멀티미디어학과는 2호관
전자공학과는 3호관
기계공학과는 4호관
9번************************************************************************************
9. Emp테이블에서 아래처럼 출력하세요
ename
---------
Ford567890
MILLER7890
……………..
SQL> select rpad(ename, 10,
substr('1234567890',lengthb(ename)+1,10)) "ename"
from emp;
ename
-----------
SMITH67890
ALLEN67890
WARD567890
JONES67890
MARTIN7890
BLAKE67890
CLARK67890
SCOTT67890
KING567890
TURNER7890
ADAMS67890
JAMES67890
FORD567890
MILLER7890
14 rows selected.
'Oracle > Oracle - SQL' 카테고리의 다른 글
| SQL정리 6장 join (0) | 2012.01.07 |
|---|---|
| SQL정리 5장 그룹함수 (0) | 2012.01.05 |
| SQL정리 1-3장 (0) | 2012.01.04 |
| SQL - SELECT 문 (0) | 2012.01.02 |
| SQL - 기본개념 (0) | 2012.01.02 |
