Oracle을 활용한 SQL 입문 # 2. order by절과 날짜, string등 다양한 함수

지난시간 복습

연결 연산자 문제 2: 교수 테이블(professor)을 사용하여
교수의 이름과 직급이 아래와 같이
홍길동 (교수) , 홍길동 ‘교수’ 이렇게 나오도록
출력해보세요.
출력된 컬럼 이름 은 교수님 입니다.

select name || ' (' || position || ')',
       name || ' ''' || position || '''',
  from professor

WHERE 절 문제 3 : EMP 테이블에서
1982년 1월 1일 이후에 입사한 사원의
사원번호, 성명,
업무, 급여, 입사일자(hiredate), 부서번호를 출력하여라

select empno, ename, job, sal, hiredate, deptno
  from EMP
 where hiredate > '1982-01-01';
 --sql에서는 날짜도 대소비교가 가능하다

연산자 문제 3 : EMP테이블에서 입사일자가 82년도에
입사한 사원의 사원번호, 성명, 담당업무, 급여,
입사일자, 부서번호를 출력하여라.

select empno, ename, job, sal
  from emp
 where hiredate between '1982-01-01'
                    and '1982-12-31'

• 데이터베이스에 날짜가 실제로 저장되어 있는 형식이 따로 있다
  날짜의 경우 1981로 출력될 수 있으나 실제로는 81로 저장되어 있다.
  따라서 LIKE 연산자 같은 경우 81로 찾아야 한다.
  조회되는 현 시점에 날짜 데이터의 형식을 원하는 포맷으로 변경
  alter session set nls_date_format = 'YYYY/MM/DD';

연습문제 student 테이블에서 여학생 정보만 출력
조회되는 현 시점에 날짜 데이터의 형식을 원하는 포맷으로 변경

alter session set nls_date_format = 'YYYY/MM/DD';
# order by절 : 데이터의 정렬
```sql
select *
  from EMP
 order by sal desc;

desc : descending
asc : ascending

예제 ) emp 테이블에서 부서번호별, 연봉별 정렬 ( 큰순서대로)

select *
  from EMP
 order by deptno asc, sal desc; 
 --부서번호는 정상 출력, sal은 큰 순서대로이므로
 --desc로 내림차순 출력

select empno eno
from EMP
order by eno; -- 알리아스 사용도 가능하다

select empno eno
from EMP
where eno = 7369; -- 알리아스 사용 불가. where 절이 먼저 옴
--select절보다 나중에 수행되는 것은 order by 절 밖에 없으므로 order by절에서만 사용 가능

위 내용을 이해하려면 구문의 연산 순서를 파악할 필요가 있다.
from절 - where절 - group by절 - having절 - select절 - order by절
이 순서대로 연산이 이뤄지기 때문에
select절에서 설정한 ALIAS가 사용이 가능한 것

dual table : 테이블이 필요없는 select절 완성시

select 24*65
  from dual; 

select는 from절과 세트이기 때문에 from절이 있어야 출력되는데
dual 테이블을 이용하면 이 부분을 해결할 수 있다.

select sysdate
  from dual; --dual 테이블 = 더미 테이블

함수

함수 : input이 함수의 특성에 맞게 변환되어 출력(output)

1. round 함수 : 반올림함수

select round(1234.56),   -- 1234
       round(1234.56, 1) -- 1234.6
  from dual;

뒤에 자리수를 지정해주면 소수점내 n자리에서 반올림해준다.

2. initcap : 첫글자만 대문자로 변환

select 'abcd', initcap('abcd')
  from dual;
-- 'abcd', 'Abcd'

select ename, initcap(ename)
  from emp;-- 사람 이름 표기할 때 좋겠군!

'Smith' 처럼 사람이름을 표기할 때 유용하게 사용할 수 있다.

대표적 단일 행 함수 (한 번에 한 건씩 처리하는 함수)

lower 소문자 치환 & upper 대문자 치환

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

length 개수 , lengthb : 글자의 크기 출력

select 'abcd', length('abcd'), lengthb('abcd'),
       '한글', length('한글'), lengthb('한글')
  from dual;
--abcd    4    4    한글    2    4

substr : 추출함수

substr(원본문자열, 시작위치, 개수)

select 'abcdefg', substr('abcdefg',1,2)
      , substr('abcdefg',-2,1)
  from dual;
--abcdefg    ab    fg
--시작 위치는 음수로 역으로 올 수 있다. 추출 방향은 그러나 양수와 같다.

연습문제 student 테이블에서 여학생 정보만 출력

select *
  from student
 where substr(JUMIN, 7, 1) = 2;
 --주민등록번호의 뒷자리 첫째자리를 활용한 성별 조건 부여

select *
 from student
 where jumin like '______2%';
 -- jumin 컬럼 데이터의 특성을 이해하면
 -- 자리수를 활용한 like 색인도 가능

연습문제 : 2월에 태어난 학생을 출력

 select *
   from student
  where substr(jumin, 3, 2) = '02'; --sol1

select *
  from student
 where birthday like '_____02%'; --sol2

select *
  from student 
 where substr(birthday, 6, 2) = '02'; --sol3

 select *
  from student 
 where substr(birthday, -5, 2) = '02'; --sol4

instr : 원본문자열에서 특정문자의 위치값을 출력

instr(대상문자열, 찾고자하는 문자열, 스캔시작 위치, 발견 횟수)

select 'abcdefghi', instr('abcdefghi', 'ghi', 1, 1)
 from dual; -- 7

 select 'abcdefghia', instr('abcdefghia', 'a', 1, 2)
 from dual; --10

 select 'abcab', instr('abcab','d')
   from dual; -- 찾고자하는 문자열이 없는 경우 0 을 출력

 select 'abcab', instr('abcab', 'a', -3, 1)
   from dual;  -- 시작위치 마이너스 사용가능 -- 단 이 경우에는 스캔방향 바뀜

select instr('abcababcad', 'a', -6, 1)
  from dual; -- 뒤에서 6번째 b부터 거꾸로 스캔 -- 4

단일행 함수 실습 1
문제1) EMP 테이블에서 scott의 사원번호, 성명,
담당업무(소문자로),담당업무(대문자로),
첫 글자만 대문자로 변환하여 출력하여라.

select empno, ename, lower(job) 담당업무, upper(job) 담당업무, initcap(job)
from EMP
where ename = 'SCOTT';
-- 7788    SCOTT    analyst    ANALYST    Analyst

단일행 함수 실습 2
문제2) EMP 테이블에서 이름의 첫 글자가
'K'보다 큰 사원의 사원번호, 이름, 업무를 출력하여라.

select empno, ename, job
  from EMP
 where substr(ename, 1, 1) > 'K';
--결과
7369    SMITH    CLERK
7521    WARD    SALESMAN
7654    MARTIN    SALESMAN
7788    SCOTT    ANALYST
7844    TURNER    SALESMAN
7934    MILLER    CLERK

단일행 함수 실습 3
문제3) EMP 테이블에서 이름이 6자리 이상인
사원의 이름과 업무를 출력하여라. \

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

단일행 함수 실습 4
문제4) EMP 테이블에서 이름 중 'L'자의 첫 위치를
출력하여라.

select ename, instr(ename, 'L', 1, 1)
from emp
where ename like '%L%';

정리 : sub : 추출, in : 안에 있는 것을 찾아준다
in은 R의 locate 개념과 비슷

pad 함수 : 삽입함수

pad(원본문자열, 총자리수, 채울문자열)

select lpad(ename, 10, '*'), -- 왼쪽으로 채우기
       rpad(ename, 10, '*')  -- 오른쪽으로 채우기
  from emp;

 select lpad(name,10, '*')
   from student; 
 -- 바이트수를 기준으로 한다. 한글 테이블이니 10을 집어넣더라도 *은 4개만 들어옴
****서진수
****서재수
****이미경
****김재수
****박동호

trim : 제거함수

trim (원본 문자열, 제거문자열)

 select 'abcba', 
        ltrim('abcba', 'a'),
        rtrim('abcba', 'a'),-- 제거할 대상을 만나면 작업 중단. ltrim의 뒤 a, rtrim의 앞 a는 제거 X
        trim('     abcba ') -- 양쪽 '공백만' 제거 가능
        -- trim 함수 : 겉보기와 다르게 입력된 데이터 정제하는데 유용하게 씀
        -- 데이터에 쓸데 없는 공백이 글자 앞뒤로 포함돼서 보이지 않을 때
   from dual; 

trim test

1) test table 생성

create table trim_test(
             col1 varchar2(5),
             col2 varchar2(5)
             );

2) test data 삽입

insert into trim_test values('a', 'a');

--2-1) 잘못 입력한 데이터 수정

update trim_test
set col2 = 'a '
where col1 = 'a';

3) 삽입 data 저장

commit;

4) 출력

select * from trim_test
where col1 = 'a'; 

select * from trim_test
where col2 = 'a'; -- 정상적으로 보이는 데이터가 조회가 안됨 --> 공백 포함가능성

select * from trim_test
where trim(col2) = 'a'; --> 공백 제거한 채 조회

치환함수

1) replace(원본문자열, 찾을단어, 바꿀 단어)
2) translate(원본문자열, 찾을 글자, 매칭된 바꿀글자)

select 'abcda',
        replace('abcda', 'a', 'A'),
        replace('abcda', 'ab', 'AB'), 
        -- 리플레이스는 단어대 단어 치환 : AB라는 단어만 치환
        translate('abcda', 'a', 'A'),
        translate('abcda', 'ab', 'AB')
         -- 트랜슬레이트는 글자대 글자 치환 : A와 B 모두 치환
                                       -- 한글자씩 매칭한다. a-A, b-B 'ABC'의 경우 C는 무시
from dual;

참고로 replace 함수는 R과 파이썬에서도 공용이다.

select 'abcda',
        replace('abcda','a',''),
        translate('1abcda', '1a', '1'),
        --항상 남기고 싶은 글자는 양쪽 모두에 넣어주면 남는다
        translate('abcda', 'a','') --모두 삭제
from dual;
--abcda    bcd    1bcd    

예제 Professor 테이블에서 교수 아이디에서 특수문자 모두 제거 후 출력

select replace(replace(ID, '-', ''),'*',''),
       translate(ID, '1!@#$%^&*-','1')   
  from professor;
--결과
captain    captain
sweety    sweety
powerman    powerman
lamb1    lamb1
number1    number1
bluedragon    bluedragon
angel1004    angel1004
naone10    naone10
onlyu    onlyu
simson    simson
gogogo    gogogo
mypride    mypride
ironman    ironman
standkang    standkang
napeople    napeople
silverher    silverher

연습문제 ) emp 테이블에서 급여를 모두 동일한 자리수로 출력

select lpad(SAL,4,0)
  from emp;

연습문제) student 테이블에서 이름의 두번째 글자를 # 처리

select substr(name, 1, 1) || '#' || substr(name, 3, 1)
  from student;   --sol1

select replace(name, substr(name, 2, 1), '#')
  from student;   --sol2

연습문제) student 테이블에서 전화번호를 다음과 같은 형식으로 변경
055 381 2158

select translate(tel, ')-', '  ')
  from student;

숫자함수

1. round(숫자[,자리수])

select 1234.567,
       round(1234.567), -- 정수리턴
       round(1234.567,1)
       ,round(1234.567,-1)
       -- -n 은 정수 n번째 자리에서 반올림
from dual;

2. trunc(숫자[,m]) -- 버림

select 1234.567,
       trunc(1234.567),
       trunc(1234.567,1)
  from dual;

3. mod(숫자1, 숫자2) -- 숫자1을 숫자2로 나눈 나머지 출력

select mod(1071, 3)
  from dual;  

4. ceil(숫자) : 정수 자리로 올림

select ceil(-1080.3) 
  from dual;  -- -1080

5. floor : 정수자리로 내림

select floor(-1080.3)
  from dual;   -- -1081

6. abs (absolute) : 절대값 출력

select abs(-3)
  from dual;

7. sign : 양수면 1, 음수면 -1, 0이면 0 리턴

select sign(1820),
       sign(-238),
       sign(0)
  from dual;
-- decode 함수시 유용하게 활용 가능 

select decode(sign(sal-3000), 1, 'A', 'B')
from emp;

날짜함수.

1. sysdate

select sysdate, sysdate + 100 -- 일(day)단위로 더한다. 빼기도 일단위로 표기됨
  from dual;

select ename, 
       floor(sysdate - hiredate) 근속일수, -- 뺀 날짜가 일단위로 표기
       trunc(floor(sysdate - hiredate) / 365, 1) 근속년수
  from emp;

2. add_months(날짜, n) : n 개월 후 리턴

예제 )현재날짜로부터 3개월 후 날짜 출력

select add_months(sysdate, 3),
       sysdate,
       sysdate + 3*30
  from dual;

두 결과값이 다를 것이다.
월이 균일하지 않기 때문에
add_month는 정확한 월을 다룰 때 좋은 함수.

3. months_between(날짜1, 날짜2) : 두 날짜 사이 계산 후 리턴

예제) 현재 날짜로부터 5개월 후 날짜 출력

select ename, floor(months_between(sysdate, hiredate)) 근속개월수
  from emp;

예제) emp 테이블에서 각 직원의 근무개월수 출력

select ename, 
       trunc(floor(sysdate - hiredate)/365) as 근속년수,-- 
       trunc(mod(months_between(sysdate, hiredate), 12)) as 근속개월수,
       trunc((months_between(sysdate, hiredate) - trunc(months_between(sysdate, hiredate)))*30) as 근속일수
from emp;

4. next_day(날짜, 요일) : 다음특정 요일 일자 리턴

1 : 일요일, 2 : 월요일 ...

select next_day(sysdate,1) -- 다음 일요일 날짜
  from dual;

select next_day(sysdate, '월') -- 현시스템의 날짜 형식에 따름. 시스템 상관없이는 숫자를 쓰면 됨
  from dual;

현시스템 날짜 형식 변경 하는 법

alter session  set nls_date_language = 'korean';

select next_day(sysdate, '월')--> 안됨
  from dual;
select next_day(sysdate, 'mon') --> 됨
  from dual;

실습문제1) emp 테이블에서 커미션을 받지 않는 사원의 급여를 10% 인상하여 표시하되,
다음의 형식으로 출력하여라.
The salary of SMITH after a 10% raise is 880

select 'the salary of ' || ename || ' after a 10% raise is ' || sal*1.1
  from emp
 where not comm is null;

실습문제2) Professor 테이블에서 상반기에 입사한 교수의
이름, pay, email_id 출력 (email 컬럼에서 추출)

 select name, hiredate, pay, substr(email, 1, (instr(email, '@')-1)) email_id
 from professor
 where substr(hiredate, -5, 2) <= 06;

실습문제3) student 테이블에서 각 학생의 지역번호 추출

 select studno, 
        name, 
        substr(tel, 1, (instr(tel, ')')-1)) 지역번호
 from student;

실습문제4) student 테이블에서 성이 'ㅅ'인 학생의 학번, 이름, 학년을 출력

 select studno, name, grade
   from student
  where substr(name, 1, 1) >= '사' 
    and substr(name, 1, 1) < '아'; 

실습문제 5) employees 테이블에서 대소를 구분하지 않고
email에 last_name이 포함되어있지 않은 사람의
employee_id, first_name, email을 출력하여라.

 select employee_id, first_name, last_name, email
 from employees
 where instr(email, substr(translate(upper(last_name), '! ', '!'), 1, 5)) = 0;

select *
from employees
where email 
not like '%' || upper(replace(last_name,' ', '')) || '%';
-- like 연산자로 함수의 결과를 전달하는 방법

추가실습문제 1) emp 테이블 사원의 근속주. 일

 select ename, 
        trunc((sysdate - hiredate)/7) || '주 ' || trunc(mod(sysdate - hiredate, 7)) || '일' as 주일
 from EMP
 order by sysdate - hiredate desc;

추가 실습문제 2) 10번 부서원의 근속월수

```sql
select ename, trunc(months_between(sysdate, hiredate)) as "10번 부서원 근무 월수"
from emp
where deptno = 10;