6월12일 수업내용

input -> 함수 -> output

단일행함수
문자함수
숫자함수
날짜함수
변환함수
기타

복수행함수(그룹함수)


select lower(ename), upper(ename), initcap(ename) from emp;

SELECT
lower(ename), //소문자화
upper(ename), //대문자화
initcap(ename) //첫자만 대문자
FROM emp;


SELECT empno, ename, sal FROM emp WHERE lower(ename)='scott';

SELECT empno, ename, sal
FROM emp
WHERE lower(ename)='scott';

SELECT empno, ename, sal
FROM emp
WHERE ename=upper('scott');

SELECT empno, ename, sal
FROM emp
WHERE initcap(ename)=initcap('scott');

//양항을 함수를 써서 일치 시키는것이 좋다.


SELECT ename||mgr from emp;

SELECT concat(ename,mgr) from emp;
//단 두개만 가능
SELECT concat('dear ',ename) from emp;

SELECT to_number(concat('10',sal))+1 from emp;
//10을 문자화 하여 월급앞에 붙인후 숫자로 변환후 1을 더함

SELECT * from emp where length(ename)>5;
//이름의 길이가 5자 이상인 사람만

SELECT ename, instr(ename,'A') from emp;

SELECT ename, instr(ename,'L'4,1) from emp;
// 4<- 찾는 시작위치
// 1<- 찾는문자 반복순서
// instr(ename,'A') 은 instr(ename,'A',1,1) 과 동일

select LPAD(sal,10,'*') from emp;
select RPAD(sal,10,'*') from emp;
// 10 칸중 sal이 차지하는 칸을 제외한 나머지를 *으로 채워라

select LPAD(sal,6,' ') from emp;

select LTRIM('MILLER','MI') from dual;
//MI만 삭제되어 나옴
select LTRIM('MILLER','IL') from dual;
//효과없음
select RTRIM(sal,'0') from emp;

select trim(leading 'M' from ename) from emp;

select trim(trailing 'N' from ename) from emp;

select trim(both 'S' from ename) from emp;

SELECT
trasrate(sal,'1234567890','일이삼사오육칠팔구영'),
replace(job, 'SALE', 'ORDER')
FROM emp;
//tras 는 1:1
//repl 는 All:All

select CHR(65)||CHR(97) from dual;
//Aa
select job||CHR(13)||ename from ename;
//13 <-엔터


select round(456.789,0), round(456.789,2), round(456.789,-2) from dual;
//457 456.79 500
//반올림

select trunc(456.789,0), trunc(456.789,2), trunc(456.789,-2) from dual;
//456 456.78 400
//버림

NVL(expr1,expr2)
//expr1이 NULL이 아니면 expr1, NULL이면 expr2
NVL2(expr1,expr2,expr3)
//expr1이 NULL이 아니면 expr2, NULL이면 expr3을 출력
nullit(expr1,expr2)
//expr1=expr2이면 NULL을 출력, expr1<>expr2 이면 expr1

select NVL2(comm,sal+comm,sal) from emp;

select comm+sal as "plus", NVL(comm,0)+sal as "NVL", NVL2(comm,sal+comm,sal) as "NVL2" from emp;
800 800
1900 1900 1900
NULL과 덧셈은 NULL

select CHR(ASCII(A)) from dual;
//???항목명 길이..


select sysdate as "Current Date" from dual;

select add_months(sysdate, 10) from dual;

select sysdate, hiredate, months_between(sysdate, hiredate) from emp;

select next_day(sysdate,'월요일'), (last_day(sysdate)-sysdate) from dual;
//돌아오는 월요일
//30(날짜)-12(날짜)=18

select (sysdate-(7*3)) "3주전", (sysdate-(7*2)) "2주전", (sysdate+(7*3)) "3주후" from dual;

//7 일주일의 날수 * 주수 연산..


select hiredate, round(hiredate, 'Month'), round(hiredate,'Year'), trunc(hiredate, 'Month'), trunc(hiredate,'Year') from emp;
//7월인경우 year에 round를 쓰면 1년 PLUS
//7월인경우 year에 trunc를 쓰면 연도는 그대로

//1983-12-25 round month 적용하면 1984-01-01로 나옴

select TO_CHAR(sysdate, 'yyyy"년" mm"월" dd"일"') from dual;
// ''안에 "" 사용하는것에 주의
TO_CHAR //숫자or날짜 -> 문자
TO_NUMBER //문자형숫자 -> 숫자
TO_DATE //문자형식 날짜 -> 날짜

select to_char(sal, '$999,999.00') from emp;
//$999,999.00형식에 맞춰서 sal을 넣어서 표현하라
//9만 사용가능

select to_char(sal*12,'9,999') from emp;
//형식을 넘을때(overflow) #으로 표시됨

select to_char(sal*12,'L9,999') from emp;
//L은 지역통화표시

select TO_CHAR(hiredate,'fmdd month yyyy') from emp;
//fm 을 쓰면 앞에 0이 나오지 않도록 함 01-> 1


TO_DATE('03-02-01', 'mm-dd-yy')
//문자를 날짜형식에 맞춰서 내보냄

select (TO_DATE('03-02-01', 'YY-MM-dd'))-(TO_DATE('03-02-01', 'mm-dd-yy')) from dual;




ex01:
select sysdate as "Current Date" from dual;

select ename, length(ename), job from emp where length(ename)>=6;

select ename, job, sal, comm, sal+comm from emp;

문제01) EMP 테이블에서 SCOTT 의 정보를 사원번호, 성명, 담당업무(소문자로), 부서번호를 출력하여라.
select empno, ename, lower(job), deptno from emp where lower(ename)=lower('scott');

문제02) EMP 테이블에서 SCOTT  의 정보를 사원번호, 성명, 담당업무, 부서번호를 출력하여라.
select empno, ename, job, deptno from emp where upper(ename)=upper('scott');

문제03) DEPT 테이블에서 첫 글자만 대문자로 변환하여 모든 정보를 출력하여라.
select initcap(*) from dept;//불가
select initcap(dname), initcap(loc) from dept;

문제04) 두개의 SELECT 문이 있다. 결과의 차이점을 설명하여라.
select empno, ename, job, concat(empno,ename) e_name, concat(ename,empno) e_empno, concat(ename,job) e_job from emp where deptno='10';

select deptno no,danem,loc, concat(ddptno,dname) d_name, concat(dname,deptno) d_deptno, concat(dname,loc) d_loc from dept;

문제05) EMP 테이블에서 이름의 첫글자가 'K' 보다 크고 'Y' 보다 작은 사원의 정보를 사원번호, 이름, 업무, 급여, 부서번호를 출력하여라. 단 이름순으로 정렬하여라.
select * from emp where ASCII(UPPER(substr(ename,1,1)))>ASCII('K') and ASCII(UPPER(substr(ename,1,1)))<ASCII('Y') ORDER BY ename;

문제06) EMP 테이블에서 20번 부서 중 이름의 길이 및 급여의 자릿수를 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 출력하여라.
SELECT empno, ename, Length(ename) "L_ENAME", sal, Length(sal) "L_SAL" from emp where deptno='20';

문제07) EMP 테이블에서 이름 중 첫번째 'L'자의 위치와 두번째 'L'자의 위치를 출력하여라.
SELECT ename, instr(ename,'L',1,1) "E_L1", instr(ename,'L',1,2) "E_L2" from emp;

문제08) 문제없음

문제09) 아래의 결과를 출력하여라.
SELECT ename, LPAD(ename,15,'*') "L_ENAME", sal, LPAD(sal,10,'*') "L_SAL" from emp;

문제10) 아래의 결과를 출력하여라.
SELECT ename, RPAD(ename,15,'*') "R_ENAME", sal, RPAD(sal,10,'*') "R_SAL" from emp;

문제11) EMP 테이블에서 10번 부서에 대하여 담당 업무 중 좌측에 'A'를 삭제하고 급여 중 좌측의 1을 삭제하여 출력하여라.
select ename, job, CASE WHEN deptno='10' THEN LTRIM(job,'A') ELSE job END "LT_A", sal, LTRIM(sal,'1') "LT_1" from emp;

문제12) EMP 테이블에서 10번 부서에 대하여 담당 업무중 우측에서 'T'를 삭제하고 급여 중 우측의 0 을 삭제하여 출력하여라.
select ename, job, RTRIM(job,'K') "R_T", sal, RTRIM(sal,'0') "RT_0" from emp where deptno='10';

문제13) EMP 테이블에서 성명을 소문자로 바꾸어 출력하여라.
select empno, ename, lower(ename) "U-LOWER", sal, translate(sal,'1234567890','일이삼사오육칠팔구영') "N_H" from emp;

문제14) EMP 테이블에서 JOB 에 'A'를 '$'로 바꾸어 출력하여라.
select ename, job, replace(job,'A','$')"replace_J", sal from emp;

문제15) 다음의 결과를 분석하여라.
select round(4567.678) "ROUND_1", round(4567.678,0) "ROUND_2", round(4567.678,2) "ROUND_3", round(4567.678,-2) "ROUND_4" from dual;

문제16) 다음의 결과를 분석하여라.
select trunc(4567.678) "TRUNC_1", trunc(4567.678,0) "TRUNC_2", trunc(4567.678,2) "TRUNC_3", trunc(4567.678,-2) "TRUNC_4" from dual;

문제17) EMP 테이블에서 급여를 30으로 나눈 나머지를 구하여 출력하여라.
select sal, mod(sal,30) from emp;

문제18) EMP 테이블에서 20번 부서 중 이름과 담당업무를 연결하여 출력하여라. 단 담당업무를 한 줄 아래로 출력하여라.
select empno, ename, job, ename||CHR(10)||job "ENAME_JOB" from emp where deptno='20';

문제19) EMP 테이블에서 현재까지 근무일 수가 몇주 몇일 인가를 출력하여라. 단 근무일 수가 많은 사람 순으로 출력하여라.
select ename, hiredate, sysdate, (sysdate-hiredate+1) "Total Days", trunc((sysdate-hiredate+1)/7) "WEEKS", TRUNC(MOD((sysdate-hiredate+1),7)) "DAYS" from emp ORDER BY hiredate;

문제20) EMP 테이블에서 10번 부서 중 현재까지의 근무 월수를 계산하여 출력하여라.
select ename, hiredate, sysdate, months_between(sysdate,hiredate) "M_BETWEEN", trunc(months_between(sysdate,hiredate)) "T_BETWEEN" from emp;

문제21) EMP 테이블에서 10번 부서 중 입사 일자로부터 5개월이 지난 후 날짜를 계산하여 출력하여라.
select ename, hiredate, add_months(hiredate,5) "A_MONTH" from emp where deptno='10';

문제22) EMP 테이블에서 10번 부서 중 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라.
select ename, hiredate, NEXT_DAY(hiredate,'금요일') "N_DAY", NEXT_DAY(hiredate,'금요일') "N_6", NEXT_DAY(hiredate,'토요일') "N_7" from emp where deptno='10';
//??

문제23) EMP 테이블에서 입사한 달의 근무 일수를 계산하여 출력하여라. 단 토요일과 일요일도 근무 일수에 포함한다.
select empno, ename, hiredate, last_day(hiredate) "L_LAST", (last_day(hiredate)-hiredate+1)  "L_DAY" from emp;
//첫날도 근무일수에 산입해야하므로 +1을 해줌

문제24) EMP 테이블에서 10번 부서 중 입사한 달의  ROUND과 TRUNC 함수를 비교합니다.
select ename, hiredate, round(hiredate,'month') "M_ROUND", trunc(hiredate,'month') "M_TRUNC", round(hiredate,'year') "Y_ROUND", trunc(hiredate,'year') "Y_TRUNC" from emp where deptno='10';

문제25) EMP 테이블에서 10번 부서 중 입사 일자를 '1 MAY 1981'와 1998 년 1 월 1 일'의 형태로 출력하여라.
select ename, hiredate, initcap(to_char(hiredate,'dd month YYYY')) "T_HIREDATE", to_char(hiredate,'YYYY" 년 " MM" 월 " DD" 일"') "T_KOR" from emp where deptno='10';

문제26) EMP 테이블에서 부서 20 중 급여 앞에 $를 삽입하고 3 자리마다 ,를 출력하여라.
select empno, ename, job, sal, to_char(sal,'$999,999') from emp where deptno='20';

문제27) February 22, 1981 에 입사한 사원의 정보를 이름, 업무, 입사일자를 출력하여라.
select ename, job, initcap(to_char(hiredate, 'month dd, YYYY')) "T_HIRE" from emp where hiredate=to_date('February 22 1981', 'month dd YYYY');

문제28) EMP 테이블에서 JOB이 ANALYST 이면 급여 증가는 10%이고 JOB 이 CLERK 이면 급여 증가는 15%이고, JOB 이 MANAGER 이면 급여 증가는 20%입니다. 다른 업무에 대해서는 급여의 증가가 없습니다. 사원번호, 이름, 업무. 급여, 증가된 급여를 출력하여라.
select empno, ename, job, sal, CASE when job ='ANALYST' then (sal*1.1) when job ='CLERK' then (sal*1.15) when job ='MANAGER' then (sal*1.2) ELSE sal END "D_SAL" from emp;

문제29) 다음의 결과를 출력하여라.
select a.deptno, b.dname, RPAD(RPAD(b.dname,15,' '),21,'*') "T_RPAD", RPAD(b.dname,21,'*') "R_R", b.loc from emp a, dept b where a.deptno=b.deptno ;