SQL 내장함수 알아보기

< 연산자 우선순위; >

-- ( ) >산술 ( *, / >+ , - ), 관계연산자 >논리 >is null, like, int >bet

-- between >not >>and >or

< funtion(함수); - 내장함수 연습 >

- 문자함수 (select)

select lower('Hello'), upper('Hello') from dual; -- 대소문자로 변경

select initcap('hello world') from dual; - 첫글자 대문자

select concat ('Hello', 'world') from dual; -- 2개 붙이기

select substr('Hello world', 3) from dual; -- He 삭제

select substr('Hello world', 3, 3) from dual; -- 3번째 3개의 글자만 취할 것

select instr('Hello world', 'e') from dual; -- e가 몇번째에 있는지 확인

select instr('Hello world', 'o') from dual; -- o가 몇번째에 있는지 확인

select instr('Hello world', 'o', 6) from dual; -- ??

select instr('Hello world', 'o', 1, 2) from dual; -- ??

select Ipad('Hello', 10, '*') from dual; -- 확인 필요??

select replace('010.123.3456','.','-') from dual; -- .을 -로 대체

Q. 문제 : sawon 테이블에서 이름에 '이'가 포함된 직원이 있으면 '이' 부터 2글자 출력

select sawon_name, substr(sawon_name, instr(sawon_name, '이'),2) from sawon where sawon_name like '%이%';

< 숫자함수 >

select round(45.678,0), round(45.678,1), round(45.678,-1) from dual; --  반올림 / 1자리 반올림, -1자리 \반올림

select sawon_name, sawon_pay, sawon_pay * 0.025 from sawon;

select trunc(45.678), trunc(45.678,2) from dual; -- 소수 둘째자리 절삭

select mod(12,2) from dual;

< 날짜 >

select sysdate from dual; -- 현재 db server의 날짜 및 시간. mysql이나 mariadb에서는 now()를 쓰기도 함

select sysdate + 5, sysdate -5, sysdate + 500 from dual;

select sawon_name, sysdate - sawon_ibsail from sawon;

- 혹시 '금'이 인식되지 않으면 alter session nls_language=korean;

select sysdate, last_day(sysdate), next_day(sysdate,'금') from dual;

select months_between(sysdate, '17-5-5') from dual;

select add_months(sysdate, 3) from dual;

< 형변환 함수 >

select sawon_pay * 0.5, sawon_pay * '0.5' from sawon; -- 자동 형변환

< 강제 형변환 >

- 문자열을 날짜로 : to_date()

select sysdate -to_date('2010-1-1') from dual;

-- 날짜나 숫자를 문자로 : to_char(숫자[날짜], '서식')

select to_char(sysdate + 3, 'YYYY+MM+DD HH:MI:SS') from dual;

select to_char(sysdate + 3, 'YEAR') from dual; -- 연도를 문자로 표시

select to_char(sysdate + 3, 'MM') from dual; -- 달을 숫자로 표시

select to_char(sysdate + 3, 'WW') from dual; -- 년 중 몇주차

select to_char(sysdate + 3, 'W') from dual; -- 월 중 몇주차

select to_char(1234, '9,990.0') from dual;

select to_char(12, '9,990.0') from dual;

select to_char(12.567, '9,990.9') from dual;

select to_char(12.567, '0,000.9') from dual;

< 기타 함수 >

-- nvl(value1, value2) : value1이 null이면 value2를 취함

select sawon_name, nv1(sawon_jik, '임시직') from sawon;

- nv12(value1, value2, value3) : value1이 null인지 평가

select sawon_name,nvl2(sawon_jik,'정규직','임시직') from sawon;

select sawon_name,nvl2(sawon_pay,sawon_pay,sawon_pay - 1000) from sawon;

-nullif(value1, value2) : 2개의 값이 일치하면 null을 아니면 value1을 취함

select nullif(length('abc'), length('efgh')) from dual;

select sawon_name, sawon_jik, nullif(sawon_jik,'대리') from sawon;

< 순위 지정 함수 : rank() >

select sawon_no, sawon_name, sawon_pay, rank()over(order by sawon_pay desc) as rank from sawon;

< 조건 표현식 >

- 형식1

- case 표현식 when 비교값1 then 결과값1 ... else 결과값n end

select case 10 / 5

when 5 then '안녕' -- 결과값이 10/5 = 5에 해당된다면

when 2 then '수고' -- 결과값이 6에 해당된다면

else '잘가' end from dual; -- 그 외엔...

select sawon_name,

case sawon_pay

when 3000 then '연봉 3000'

when 3000 then '연봉 4000'

else '기타' end as result from sawon;

select sawon_name, sawon_jik,

case sawon_jik

when '부장' then sawon_pay * 0.5

when '과장' then sawon_pay * 0.4

else sawon_pay * 0.4 end as donation from sawon;

- 형식2

- case when 조건1 then 결과값1...else 결과값n end

select sawon_name,

case when sawon_gen='남' then 'M'

case when sawon_gen='여' then 'F' end as result from sawon where sawon_jik='사원';

end as result from sawon;

select sawon_name,

case

when sawon_pay >= 5000 then '고액연봉'

when sawon_pay >= 3000 then '보통연봉'

else '부족연봉'

end as res from sawon;

< 문제.1 10년 이상 근무하면 감사합니다, 5년 이상 근무하면 '고마워요' 그 외는 '열심히' 라고 표현 >

--      (1990년 이후 직원만 참여), 특별수당(pay기준) 10년 이상 10%, 5년 이상 5%, 나머지 3%

--      수당은 정수만 출력(반올림)

-- 출력 형태     사번     직원명       표현    특별수당

--              1      홍길동    감사합니다    150

select sawon_no, sawon_name,

case

when trunc((sysdate - sawon_ibsail) / 365) >= 10 then '감사합니다'

when trunc((sysdate - sawon_ibsail) / 365) >= 5 then '고마워요'

else '열심히' end as 표현,

case

when trunc((sysdate - sawon_ibsail) / 365) >= 10 then round(sawon_pay * 0.1)

when trunc((sysdate - sawon_ibsail) / 365) >= 5 then round(sawon_pay * 0.05)

else round(sawon_pay * 0.03) end as 특별수당

from sawon where sawon_ibsail >= '1990-1-1';

< 문제.2 입사 후 10년 이상이면 왕고참, 5년 이상이면 고참, 3년 이상이면 보통, 나머지는 일반으로 표현 >

-- 출력 형태     직원명      직급     입사년월일   근무개월수    구분    부서

-- 홍길동      부장    2000.1.15    5678      왕고참   총무

-- 부서는 buser 테이블을 참조

select * from sawon;

select sawon_name, sawon_jik,

to_char(sawon_ibsail, 'YYYY.MM.DD') as 입사년월일,

trunc(months_between(sysdate, sawon_ibsail)) as 근무개월수,

case

when (sysdate - sawon_ibsail) / 365 >= 10 then '왕고참'

when (sysdate - sawon_ibsail) / 365 >= 5 then '고참'

when (sysdate - sawon_ibsail) / 365 >= 3 then '보통'

else '일반' end as 구분,

case buser_num

when 10 then '총무'

when 20 then '영업'

when 30 then '전산'

when 40 then '관리'

end as buser

from sawon;

<  복수 행 함수(aggregation functions) / 전체 자료를 그룹별로 처리 >

-- count(), sum(), avg(), std(), variance()...

select sum(sawon_pay) as sum, avg(sawon_pay) as avg from sawon; -- 합계 및 평균값

select max(sawon_pay) as max, min(sawon_pay) as min from sawon; -- 최고값 및 최저값

update sawon set sawon_pay=null where sawon_no=3;

commit;

select * from sawon;

select avg(sawon_pay), avg(nvl(sawon_pay, 0)) from sawon;

select avg(sawon_pay) from sawon where sawon_pay is not null;

select sum(sawon_pay) / 15, sum(sawon_pay) / 16 from sawon;

select avg(sawon_pay), avg(nvl(sawon_pay,0)) from sawon;

select avg(Sawon_pay) from sawon where sawon_pay is not null;

select sum(sawon_pay) / 15, sum(sawon_pay) / 16 from sawon;

select count (sawon_no), count(sawon_name),

count(sawon_jik), count(sawon_pay), count(*) from sawon;

< 과장은 몇명? >

select count(*) as 인원 from sawon where sawon_jik ='과장';

< 2000년 이전에 입사한 남직원은 몇명? >

select count(*) from sawon

where sawon_ibsail <'2000-1-1' and sawon_gen='남';

< 2000년 이후에 입사한 여직원의 급여합, 급여평균, 인원수? >

select sum(sawon_pay), avg(sawon_pay), count(*) from sawon

where sawon_ibsail >= '2000-1-1' and sawon_gen='여';