대소문자 구분 함수

종류 

의미 

사용예 

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

10n을곱한값으로표시 

 (1234,‘9999V99')

123400

공백을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
Posted by 딩구르
,