아래에서 사용하는 emp 테이블은 이전에 올렸던 연습하기 에서 만들었던 테이블을 사용해 연습한 것
특정문자의위치찾기 instr
select instr(찾을 문장, 찾을 알파벳, 시작 위치, 몇 번째 발견);
-- 첫번째 발견한 'o'의 위치를 리턴
select instr('Welcome to Oracle', 'o') from dual;
-- 시작 위치를 지정해 줄 수도 있다
select instr('Welcome to Oracle', 'o', 6) from dual;빈자리를 특정 기호로 채우기
-- 전체 10칸으로 잡고 빈 공간은 (왼쪽부터) #으로 채워라
select lpad('Oracle', 10, '#') from dual;
-- 전체 10칸으로 잡고 빈 공간을 (오른쪽정렬로) #채우기
select rpad('Oracle', 10, '#') from dual;형 변환 함수
: 자료형을 변환시키고자 할 때 사용
: to_char = 날짜형 혹은 숫자형을 문자형으로 변환
: to_date = 문자형을 날짜형으로 변환
: to_number = 문자형을 숫자형으로 변환
/* 날짜 */
-- 오늘 날짜 받아오기
select sysdate from dual;
-- 받아온 날짜를 문자형으로 변환
select to_char(sysdate) from dual;
select to_char(sysdate, 'YYYY-MM-DD DAY') from dual;
select to_char(sysdate, 'YYYY-MM-DD DY DAY') from dual;
select to_char(sysdate, 'YYYY-MM-DD DY DAY AM HH:MI:SS') from dual;
-- 테이블에서 사용하는 모습, emp0의 hiredate 내용 형식에 맞게 문자형으로 변환한다
select * from emp;
select to_char(hiredate, 'YYYY-MM-DD DAY') from emp;
/* 숫자를 문자형으로 변환하기 */
-- 0 = 자리수를 나타내며 자리가 맞지 않을 경우 0으로 채운다.
select to_char(1234567, '00,00000') from dual;
select to_char(1234567, '00,000') from dual;
-- L = 각 지역별 통화를 앞에 표시한다.
select to_char(1234567, 'L0,000,000') from dual;
-- emp테이블에서 'W0,000,000' 과 같이 표현하기
select to_char(sal*10000, 'L99,999,999') from emp;
/* 문자형을 숫자형으로 변환하기 */
-- emp테이블에서 ename, hiredate를 출력할건데, 조건은 hiredate에서 1.날짜가 20070402 인 사람만을 찾아서 2.날짜를 '년/월/일' 형태로 뽑을것이다.
select ename, hiredate from emp where hiredate = to_date(20070402, 'YYYYMMDD');
-- 조건보다 전에 입사한 사람들만 출력해볼것이다
select ename, hiredate from emp where hiredate < to_date(20070301, 'YYYY-MM-DD');
-- 문자포멧인 이만원과 만원을 숫자형식으로 바꾸어 계산하겠다
select to_number('20,000', '99,999') - to_number('10,000', '99,999') from dual;
-- 근데 자리수가 없다면 그냥 계산해도 값이 나오긴 한다.
select '20000' - '10000' from dual;
-- 오늘날짜 +1은 내일날짜 = 날짜 계산이 가능하다는 거지
select to_char(sysdate+1, 'YYYY-MM-DD') from dual;
select to_char(sysdate+1, 'YYYY-MM-DD') from dual;
select to_char(sysdate+25, 'YYYY-MM-DD') from dual;
-- 두 날짜 사이가 몇 개월인지 리턴, 일까지 계산하기 때문에 날이 다르면 값이 더러울 수 있음
select months_between(to_date(20211001, 'YYYY-MM-DD'), to_date(20201011, 'YYYY-MM-DD') ) from dual;
select months_between(to_date(20211001, 'YYYY-MM-DD'), to_date(20201001, 'YYYY-MM-DD') ) from dual;
-- 년을 기준으로 반올림(월이 상반기면 다음 년도로, 하반기면 올해 년도로)
select round( to_date(20211010, 'YYYY/MM/DD'), 'Year') from dual;
select round( to_date(20211010122530, 'YYYY/MM/DD:HH:MI:SS'), 'Q') from dual;
-- emp 테이블에서 오늘 날짜를 기준으로 몇 개월 근무했는지 확인하기 / trunc = 소수점 잘라주기
select ename, sysdate 오늘, hiredate 입사날짜, trunc(months_between(sysdate, hiredate)) 근무개월 from emp;
-- emp 테이블에서 오늘 날짜를 기준으로 몇 년 근무했는지 확인하기
select ename, sysdate 오늘, hiredate 입사날짜, trunc(months_between(sysdate, hiredate) / 12) 근무년수 from emp;
-- 입사일 이후 6개월 후는?
select add_Months(hiredate, 6) from emp;
-- 형식을 주고 싶다면
select to_char(add_months(hiredate, 6), 'YYYY-MM-DD') from emp;
-- 오늘을 기준으로 돌아오는 일요일 날짜는?
select to_char(sysdate, 'YYYY-MM-DD') 오늘,
to_char(next_day(sysdate, '일요일'), 'YYYY-MM-DD') 돌아오는_일요일 from dual;
-- 입사한 달의 마지막날을 구하기
select hiredate, last_day(hiredate) from emp;null 부분 채우기
1.nvl(비어있는지 확인할 부분, 비어있다면 대체할 값)
2.nvl2(확인할 부분, null이 아니면 리턴할 값, null 이라면 리턴할 값
3.nullif(식, 식) = 두 식을 비교해 동일하면 null 반환, 동일하지 않으면 첫번째 값 반환
4. coalesce : 인수중에 null이 아닌 첫 번째 인수를 반환하는 함수
-- 숫자 있는 부분이니 숫자로 대체할 수 있도록
select nvl(comm, 0), comm from emp;
-- 문자 있는 부분은 문자로 대체할 수 있오록
select nvl(job, '비정규직') from emp;
-- 입사일이 비어있으면 오늘날짜로 나오도록
select nvl(hiredate, sysdate) from emp;
-- nvl2(comm 을 검사해서 / 값이 있다면 sal*12+comm / null 이라면 sal*12)
select ename, sal, comm, nvl2(comm, sal*12+comm, sal*12) from emp;
-- nullif : 아래는 (두 식이) 다르니까 첫 번째 식인 a를 반환
select nullif('a', 'b') from dual;
-- comm 이있으면 comm 찍고, comm이 null이라면 sal을 확인하는데 sal이 있으면 sal찍고, sal도 null이라면 0을 찍어라
select ename, sal, comm, coalesce(comm, sal, 0) from emp;DECODE 함수
: switch-case문과 같은 기능을 한다
Decode (표현식, 조건1, 결과1,
조건2, 결과2,
기본결과
)조건1에 맞다면 결과1 리턴, 조건2에 맞다면 결과2를 리턴받고, default로 기본결과를 설정할 수 있다.
select ename, deptno, DECODE(deptno, 10, '경리부', 20, '인사과', 30, '영업부', '사원' ) as dname from emp;
/* CASE : if else if else와 유사한 구조
CASE when 조건1 then 결과1
when 조건2 than 결과2
else 결과
END */
select ename, deptno,
case when deptno=10 then '경리부'
when deptno=20 then '인사과'
else '사원'
end as dname from emp;emp 테이블을 이용해 연습
-- 9월에 입사한 사원만을 출력해보기
select * from emp where substr(hiredate, 4, 2) = '09';
-- 2003년도에 입사한 사원을 알아내기 위한 쿼리문을 작성
SELECT * from emp where hiredate >= to_date('2003/01/01', 'yyyy/mm/dd') AND hiredate <= to_date('2003/12/31', 'yyyy/mm/dd');
SELECT * from emp where hiredate between to_date('2003/01/01', 'yyyy/mm/dd') and to_date('2003/12/31', 'yyyy/mm/dd');
select * from emp where substr(hiredate, 1, 2) = '03';
-- 이름이 '기'로 끝나는 사람을 검색
select * from emp where substr(ename, 3, 2) = '기';
select * from emp where substr(ename, -1, 2) = '기';
select * from emp where ename like '__기';
-- 이름의 두번째 글자에 동이 있는 사원
select * from emp where substr(ename, 2, 1) = '동';
select * from emp where instr(ename, '동', 2) = 2;
select * from emp where ename like '_동%';
-- 직급에 따라 급여 인상(부장 5%, 과장 10%, 대리 13%, 사원 20%)
select empno, ename, job, sal,
decode(job, '부장', sal*1.05,
'과장', sal*1.1,
'대리', sal*1.13,
'사원', sal*1.2) as 인상급여 from emp;
-- 입사 연도는 2자리, 월은 숫자, 요일은 약어로 지정하여 출력하기
select hiredate, to_char(hiredate, 'YY/MON/DD DY') from emp;
-- 급여중에 최소값 검색해보기
select min(sal) from emp;
-- 급여중 최대값 검색해보기
select max(sal) from emp;
-- 에러남, 왜냐면 round는 17개의 값이 출력되는 그룹이고, sum은 하나의 값만 출력되는 싱클이니까
select round(sal, 3), sum(sal) from emp;
-- 사원들의 급여총액, 급여평균, 최대급여, 최소급여를 출력하고 싶다면?
select sum(sal) 급여총액, trunc(avg(sal)) 급여평균, max(sal) 최대급여, min(sal) 최소급여 from emp;
-- 사원 중 최근 입사한사람과 가장 오래전 입사한 사람의 이름 출력하기
select ename 신입사원, hiredate 입사날짜 from emp where hiredate = (select to_date(max(hiredate), 'YY/MM/DD') from emp );
select ename 최장근속, hiredate 입사날짜 from emp where hiredate = (select to_char(to_date(min(hiredate), 'YY/MM/DD')) from emp );
-- 최대 급여, 최대급여를 받는 사람
select ename, sal, job from emp where sal=(select max(sal) from emp);
-- 모든 사원의 수 구하기
select count(ename) from emp; -- null 제외
select count(*) from emp; -- null까지 포함된 전체
-- 커미션 받는 사원수
select count(comm) from emp;
-- 직위의 갯수
select count(DISTINCT job) from emp;Group by 절
: 특정 컬럼을 기준으로 그룹 함수를 사용해야 할 경우 사용하는 절
select job 직위, trunc(avg(sal)) 평균급여 from emp group by job;
select deptno 부서명, max(sal), trunc(avg(sal)) from emp group by deptno order by deptno asc;having 절
: 그룹에 대한 조건을 줄때 having절을 사용
-- 부서별(그룹) 급여 평균이 500이상(그룹 조건) 인 부서번호와 급여 평균을 보고싶다
select deptno, trunc(avg(sal)) from emp group by deptno having avg(sal) >= 500;
-- (논리 순서대로 작성해보기)
-- 최대 급여가 500을 초과하는 부서에 대해 최대급여와 최소 급여를 구해라
select deptno 부서, max(sal) 최대급여, min(sal) 최소급여 from emp group by deptno; -- 부서별로 최대급여, 최소급여를 구함
select deptno 부서, max(sal) 최대급여, min(sal) 최소급여 from emp group by deptno having max(sal)>500; -- 근데 거기서 500이 넘는 부서만 출력
select deptno 부서, max(sal) 최대급여, min(sal) 최소급여 from emp group by deptno having max(sal)>500 order by deptno asc; -- 근데 그걸 또 오름차순으로 정렬해라
-- 사원을 제외하고 급여 총액이 1000이상인 직급별 급여 총액 구하기
select job 직급, sum(sal) 급여총액 from emp group by job; -- 직급별로 급여 총액을 구함
select job 직급, sum(sal) 급여총액 from emp group by job having job !='사원'; -- 근데 사원은 제외해
select job 직급, sum(sal) 급여총액 from emp group by job having job !='사원' and sum(sal)>=1000; -- 그리고 그 중에 1000이 넘는 것만 출력할거야
select job 직급, sum(sal) 급여총액 from emp group by job having job !='사원' and sum(sal)>=1000 order by sum(sal) desc; -- 그걸 또 내림차순 정렬할거다