12/11/29 SQL 및 JAVA

자바 계열은 인스톨 프로그램이 아닌 프로그램이 많다.

OS에 자유롭다. 여러개를 깔수 있다. 설치 삭제가 편리하다.

다른 버젼별 테스트시에는 불편하다.

/*문자 조작 함수*/

select lpad('Hello',10,'*') from dual;

select rpad('Hello',10,'*') from dual;

select replace('010.1234.5678','.','-') from dual;

/* 숫자 함수 */

          /* 대상숫자 지정자리수 */

select round(45.926,2) from dual; /*소수 2째 자리까지*/

select round(45.926,0) from dual; 

select round(45.926) from dual;

/* 문제 1: emp표 현재 급여 1.15배 더 급여를 받도록 연산하고 

알리아스를 New Salary로 부여하고 소숫점 이하 자리는 반올림해서

제거하고 현재급여 얼마 만큼 인상되었는지 연산하고 알리아스를 Increase로 표시

하고 소숫점 이하 자리는 반올림, 

사원번호,이름,급여,인상된 급여, 인상된 급여외 현재급여의 차액만 표시

*/

select empno,ename,sal,round(sal*1.15) "New Salary",round(sal*1.15-sal) "Increase" from emp; /*문제 1 완료*/

/* trunc : 절삭 */

select trunc(45.926,2) from dual;

select trunc(45.926,0) from dual;

select trunc(45.926) from dual;

/* mod : 나머지값 구하기 */

select mod(17,2) from dual;

/*날짜 함수*/

select sysdate from dual;

/*months_between : 월 간격  뒷 달            앞 달 */

select months_between('2012-03-23','2010-01-23') from dual;

select months_between('2010-01-23','2012-03-23') from dual;

/* 문제 : emp표 이름과 입사일,입사일과 현재날짜(오늘)와의 월 간격(표시A=months_worked)을 표시하시오. 소수점 이하는 반올림*/

select ename,hiredate,round(months_between(sysdate,hiredate)) "months_worked" from emp;

/*next_day : 요일을 제공하고 돌아오는 다음요일의 날짜 */

/*               날짜         돌아오는 다음 요일*/

select next_day('2012-11-29','금요일') from dual;

select next_day('2012-11-29','월') from dual;

/*1(일요일)~7(토요일)*/

select next_day('2012-11-29',2) from dual;

/*last_day : 월의 마지막날*/

select last_day('2012-12-01') from dual;

/* 날짜에서 round 사용하기 : round: 두번째 전달 데이터로 지정된 값을 기준으로 해서 첫번째 전달 날짜를 반올림*/

select round(sysdate) from dual;

select round(sysdate,'year') from dual;

select round(sysdate,'month') from dual;

select round(sysdate,'q') from dual; /* q: 분기 */

select trunc(sysdate) from dual;

select trunc(sysdate,'year') from dual;

select trunc(sysdate,'month') from dual;

select trunc(sysdate,'q') from dual;

/* 

변환 함수

날짜 포맷팅

to_date()와 to_char(날짜)함수에서 사용할 수 있는 포맷팅 옵션

AD,A.D. -AD 표시

AM,PM,A.M.,P.M. - 오전/오후 표시

BC,B.C. -BC 표시

RM - 월의 로마식 표기

CC,SCC - 세기 표시(1999는 20세기로, 2001은 21세기로 표현됨)

Y,YY,YYY,YYYY - 연도의 숫자값(각각 1,2,3,4 자리로 표현)

YEAR - 연도를 문자로 표현(예,"Two thousand one")

RR - 네 자리 연도 숫자 중 뒤의 두 자리를 사용하여 앞의 두 자리

     숫자를 반환. 뒤의 두 자리가 50보다 작을 때는 현재 연도가 그대로 사용되지만, 

     50보다 크다면 1을 줄여 사용한다는 것에 주의

     예)RR('99

MON - 월의 세 자리 문자식 표현(JAN,FEB 등)

MONTH - 월의 문자식 표현(JANUARY,FEBRUARY 등)

MM - 월의 숫자식 표현(1~12)

WW - 주간의 숫자식 표현(1~53)

W - 월 내의 주간을 숫자식으로 표현(1~5)

D - 주간 내의 일을 숫자식으로 표현(1~7)

DD - 월 내의 일을 숫자식으로 표현(1~31)

DDD - 연도 내의 일을 숫자식으로 표현(1~365)

DAY - 주간 내의 일을 문자식으로 표현

HH,HH12 - 시간(1~12)

HH24 - 24시간 표현(0~23)

MI - 분(0~59)

SS - 초(0~59)

SSSS - 하루 내의 초 단위(0~86399)

숫자 포맷팅

9 - 숫자를 주어진 자리수대로 반환(예,to_char(111,999) = 111)

9,999 - 정해진 위치에 콤마를 넣는다.

(예,to_char(1234,'9,999') = 1,234)

999.00 - 정해진 위치에 소수점을 넣는다.

(예,to_char(123,'999.99') = 123.00)

$9999 - 숫자 앞에 달러 기호를 넣는다.

FM99 - 리턴된 문자값의 앞에 있는 공백은 지운다.

rn,RN - 주어진 숫자를 로마 수자로 표기(각각 소문자와 대문자)

X - 주어진 숫자를 16진수로 표기(예,to_char(20,'XX') = 14

to_char : 날짜 -> 문자로 변환

          숫자 -> 문자로 변환

*/

select to_char(sysdate,'YY-MM-DD') from dual;

select to_char(sysdate,'YY-MM-DD HH:MI:SS') from dual;

select to_char(sysdate,'DD-MON-RR HH:MI:SS') from dual;

select to_char(sysdate,'WWsp') from dual; /*기수화*/

select to_char(sysdate,'WWspth') from dual; /*서수화*/

select to_char(1234,'9999') from dual;

select to_char(1234,'0000') from dual;

select to_char(1234,'99999') from dual;

select to_char(1234,'00000') from dual;

select to_char(1234,'999') from dual; /*자리수 모자라서 샾으로 표기*/

select to_char(1234,'0000.00') from dual; 

select to_char(1234,'$0000') from dual;

select to_char(1234,'L0000') from dual; /*지역통화*/

/*

to_date : 문자 -> 날짜

변환 함수는 데이터를 연동해서 코딩할떄 주로 사용된다.

*/

select to_date('12-11-05','YYYY-MM-DD') from dual;

select to_date('12-11-05','DD-MM-RR') from dual; /*서구식 날짜 표기*/

/*

to_number : 문자 -> 숫자

길이 측정과 값을 구하는 차이는 매우 크다.

*/

select to_number('100','999') from dual;

select to_number('100') from dual; /*포맷 형식 생략 가능*/

select to_number('-100.001') from dual;

/*

기타 함수

nvl(value1,value2):value1이 null이면 value2를 사용

                   value1과 value2의 자료형이 일치해야 한다.

                   자료형이 일치하도록 강요를 한다.

*/

select ename,sal,comm,nvl(comm,0) from emp;

select ename,sal,comm,nvl(comm,0),nvl(comm,0)*12*sal from emp;

select ename, to_char(comm) from emp;

select ename, nvl(to_char(comm),'No Commission') from emp;

/* 문제 1번

emp표 에서 사원이름 월급을 뽑고, 월급과 커미션을 더한 값을 출력.

컬럼명을 [실급여]라고 해서 출력. 단 NULL값은 나타나지 않게 SQL문장을 작성하시오.

*/

select * from emp;

select ename,sal,sal+nvl(comm,0) "[실급여]" from emp; 

/* nvl2 특징

nvl2(value1,value2,value3):value1이 null인지 평가 null이면 value3,

                           null이 아니면 value2사용

                           자료형은 불일치해도 됨 , 수치 계산을 안해서 거의 안 쓴다.

*/

select nvl2(comm,'commission','no commission') Nonullcomm from emp;

/*

nullif(value1,value2):두개의 값이 일치하면 null, 두 개의 값이 일치하지 않으면 value1

*/

select ename,job,nullif(length(ename),length(job)) from emp;

/*

coalesce(valu1,value2,value3......) : null값이 아닌 값을 사용 , 자료형이 일치 해야한다.

*/

select comm,sal,coalesce(comm,sal) from emp;

/*

case 컬럼 when 비교값 then 결과값

         when       then

         when       then

         (else 결과값)

*/

select ename,sal,job,

       case job when 'SALESMAN' then sal*0.1

                when 'MANAGER' then sal*0.2

                when 'ANALIST' then sal*0.3

                else sal*0.4

       end "Bonus"

from emp;

/* 문제

emp표에서 이름,급여,직책 뽑고 급여가 4000~5000 A , 3000 ~ 4000 미만 B,

*/

select ename,sal,job, /* 범용적으로 사용 가능한 언어*/

       case when sal>=4000 and sal<=5000 then 'A'

            when sal>=3000 and sal<4000 then 'B'

            when sal>=2000 and sal<3000 then 'C'

            when sal>=1000 and sal<2000 then 'D'

            else 'F'

       end "Grade"

from emp; 

/*

decode(oracle전용)=비교만 가능

decode(컬럼,비교값,반환값)

*/

select ename,sal,job,

       decode(job, 'SALESMAN' , sal*0.1,

                   'MANAGER' , sal*0.2,

                   'ANALIST' , sal*0.3,

                   sal*0.4)

       "Bonus"

from emp;

select ename,sal,job,

       decode(trunc(sal/1000),5,'A',

                              4,'A',

                              3,'B',

                              2,'C',

                              1,'D',

                              'F')

       "Grade"

from emp;

/* 집합 함수 -> avg() : null을 제외한 모든 값들의 평균을 반환.null값은 평균 계산에서 무시됨 */

select round(avg(sal)) "평균" from emp;

/* max() : 레코드 내에 있는 여러 값 중 가장 큰 값을 반환 */

select max(sal) from emp;

/* min() : 레코드 내에 있는 여러 값 중 가장 작은 값을 반환 */

select min(sal) from emp;

/* sum() : 레코드들이 포함하고 있는 모든 값을 더하여 반환 */

select sum(sal) from emp;

/* count() : null을 제외한 값을 가진 모든 레코드의 수를 반환 */

select count(comm),count(sal) from emp;

select max(sal),min(sal),round(avg(sal)),sum(sal) 

from emp where deptno=10;

select count(*) from emp where deptno=20;

select count(ename) from emp where deptno=20;

select count(nvl(comm,0)) from emp;

/* group by & having 

select 절에 집합함수 적용시 개별 컬럼을 지정할 수 없다.

개별 컬럼을 지정할 경우에는 반드시 group by 절에 지정된

컬럼만 가능

*/

select max(sal),min(sal),round(avg(sal)),sum(sal) 

from emp

group by deptno;

/* 

where절에는 집합함수를 사용할 수 없음.

집합함수를 사용하고 싶다면 having절 사용

오류의 예)

*/

select deptno, avg(sal) from emp

where avg(sal)>=7000

group by deptno;

/*정상구문*/

select deptno, round(avg(sal))

from emp

group by deptno

having round(avg(sal))>=2000;

select deptno,round(avg(sal))

from emp

where deptno in(10,20,30)

group by deptno

having round(avg(sal))>=1000

order by deptno;

/*

문제

1) emp 테이블에서 job,직책(job)별로 사번이 제일 늦은 사람을 구하고

 그 결과 내에서 사번이 79로 시작하는 결과만 보여주세요.

*/

select job, max(empno)

from emp

group by job

having max(empno) like '79%';

/* 직책, 직책()별 총월급을 출력하되, 직위가 'MANAGER' 인

사원들은 제외하라. 그리고 총월급이 5000보다 큰 직위와

총월급만 출력하라.

*/

select job,sum(sal)

from emp

where job not in 'MANAGER'

group by job

having sum(sal)>5000;