상세 컨텐츠

본문 제목

SQL select 명령어 및 내장함수 예제

Machine Learning

by 에스프리터 2018. 1. 15. 17:50

본문


< 연산자 우선순위; >
-- ( ) > 산술 ( *, / > + , - ), 관계연산자 > 논리 > 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='여';




본 포스팅은 IT 교육기관인 KIC 캠퍼스의 지원을 받은 리포트입니다.
본문은 '남궁성, JAVA의 정석 3rd Edition' 교재를 공부하면서 주요 내용을 요약하고 있습니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.



태그

관련글 더보기

댓글 영역