데이터베이스 SQL 함수

   - 복습문제

문제: 매니저가 없는 사람들의 이름 및 직종을 출력하시오
select last_name, job_id from employees where manager_id is null;

문제: 매니저가 있는 사람들의 이름 및 직종, 매니저 번호를 조회하라
select last_name, job_id,manager_id from employees where manager_id is not null;

문제: 커미션을 받는 모든 사원들의 이름, 연봉 및 커미션을 출력하시오 -연봉을 역순으로 정렬, 연봉은 ANNSAL로 출력하시오
select last_name, salary*12 "ANNSAL", commission_pct from employees
where commission_pct is not null order by ANNSAL desc;

문제: 이름의 네번째 글자가 h인 사원의 이름을 조회하시오
select last_name from employees where last_name like '___h%';

문제: 이름에 a와 e글자가 있는 사원의 이름을 조회하시오
select last_name from employees where last_name like '%a%e%' or last_name like '%e%a%';
                                                          a~e or e~a 인사람
select last_name from employees where last_name like '%a%' and last_name like '%e%';
                                                      a가들어가고 and e가 들어가는사람


select + from + where + group by/ having/ order by          
  -> from 절부터 1번  where 2번 group by 3번 having 4번 select절 5번 order by 6번 순서로 컴퓨터는 실행한다.
-select절, from절 제외하면 생략이 가능하다
  
  

--함수: 입력값이 있으면 결과값이 나오는것
-- 단일행 함수, 문자함수, 숫자함수

--단일행 함수: 문자 단일행함수, 숫자 단일행함수, 날짜 단일행함수, 변화 단일행함수
   -문자함수: 문자를 입력 받아서 문자, 숫자를 반환한다.
   -숫자함수: 숫자를 입력 받아서 숫자를 반환한다.

-- 문자함수
lower(column) 알파벳 값을 소문자로 변환
upper(column) 알파벳 값을 대문자로 변환
initcap(column) 알파벳 값을 각 단어의 첫문자는 대문자 나머지는 소문자로
concat (column1,column2) 첫번째 문자값을 두번째 문자값에 연결한다. (연결연산자 ||와 동일하다)


select lower(last_name) low_name, upper(last_name) upr_name, initcap(last_name) int_name
 from employees;
 
 
 length(column) 표현식의 문자수를 반환한다
 instr(column, 'string') 'string' string(문자)가 들어가는 위치가 어딘가? 
                         ex)  instr(last_name, 'e') 이름에 e가 첫번째로 나오는 몇번째에 들어가나?
 
select
  instr('010-1254-5674', '-')             --> 4    '- '가 처음으로 나오는 위치의 숫자
  instr('010-1254-5674', '-',1,2)      -->9     '-' 가 1번위치인 0부터 시작해서 2번째로 나오는 위치(숫자로)는?
  instr('010-12-54-5674', '-',5,2)     -->10
  
 substr(column,m,[n]) 문자 값의 위치 m에서 n까지 지정된 문자를 반환한다
 
 
 select last_name, length(last_name) name_len, instr(last_name, 'e') name_ins, 
 substr(last_name,2,3) name_sub from employees;
 substr('010-1254-5674', 5,4)   -> 1254   5번째(1부터)부터 4개의 숫자를 출력.
 
 substr('010-1254-5674', instr('010-1254-5674', '-')+1,4)  -> 1254 출력
 
  instr('010-1254-5674', '-')+1  ->  instr('010-1254-5674', '-') 4가 나오고 +1해서 5  
  따라서 substr('010-1254-5674', 5,4) 가 된다.
  왜 고정으로 5라고 안쓰고 instr을 쓰냐면 substr으로 원하는 문자열을 뽑을때
   문자열의 수가 가변적 (ex 메일의 아이디의 길이가 다 다를때)일때가 있기때문
  
  -shinho@portal.com   jyp@portal.com 의 아이디만 출력해봐라
  substr(mail,1,instr(mail,'@')-1);
  ->instr(mail,'@')는 @의 위치가 몇번째에오는지 구할 수 있는데 아이디는 @앞에까지이므로 -1해주면
  substr을 이용해 메일의 아이디 길이가 달라도 아이디만 출력 할 수 있다. (두가지 함수를 사용해서)
  
 
 
 -- job_id 에서 _뒤에 문자만 나타내봐라
 
 select job_id, substr(job_id, instr(job_id, '_')+1, length(job_id)-instr(job_id,'_')) 
  from employees;
 
-> instr(job_id, '_')+1 -> _의 뒤에 문자만 나타낼 꺼니까 +1
-> substr으로 몇글자를 추출해야하나?를    job_id의 전체길이-job_id에서 _까지 글자수를 빼면 추출할 글자수를 구할수 있다.
                                    ->  length(job_id)-instr(job_id,'_')
 
 
 select instr(phone_number,'.') phone_sep from employees;
 
 
 --명시적, 암시적 형변환
 select 2+ '3' from dual; -> 암시적 유형변환 (자동변환)
 select 2 + to_number('3') from dual; -> 명시적 유형변환(강제변환)
 
 select '3'+5 from dual;
 
 select last_name, salary from employees; -> 문자는 왼쪽에서부터 정렬 숫자는 오른쪽에서부터 정렬
 select to_char(20) from dual; -> 문자로 형변환 되어 왼쪽으로 정렬됨
 정렬 위치 결과를 보고 문자형 숫자형을 알수있다.
 select '20' num from dual; ->20은 문자형
 
 -- 숫자 함수
 select trunc(45.5932) trn1, trunc(45.5932,2) trn2, trunc(45.5932,-1) trn3
         ->45                     45.59            -1을 10의1승으로생각해서 10의자리까지 남기고->40   
                                            일의자리에서 버림해서 -> 40
trunc -> 지정한 소수점 자리까지 남기고 버림    

        round(45.5932,2) -> 45.59 소수점3번째에서 2번째로 반올림
round(45.5932,0) -> 46  소수점1번째에서 소수점0번째로(정수 첫자리로 반올림) 반올림
round(45.5932,-1) -> 50 

round -> 지정한 소수점 자리로 값을 반올림한다
        


mod (45.5932)
        mod -> 나눗셈의 나머지를 반환한다.



 -- 날짜 함수
 select sysdate from dual;   -> 오늘 날짜 출력
 
 select sysdate, sysdate+7 이나 sysdate-7 from dual; -> 오늘 날짜에서 +7일 이나 -7일
 
 select last_name, hire_date, (sysdate - hire_date)/7 wkday from employees;
 
 (sysdate - hire_date)/7 -> 여태 근무한 주의 수 ( 몇주 근무했냐? )
 
 select last_name, hire_date, 
 round((sysdate - hire_date)/7) wkday from employees;  -> 몇주 근무했는지 소수점을 반올림 시킴.
 
 --각각의 달 사이에 달수
  select last_name, hire_date, 
 months_between(sysdate,hire_date) wrkmon from employees;
 -> 현재 날짜와 근무시작 날짜 사이의 달 수를 구한것.
 
 
 select last_name, hire_date,
  to_char(hire_date, 'yyyy-mm-dd') hdate from employees;
  -> to_char를 이용해 날짜의 표기 방식을 변환(포맷)시킴
  
  -- 직원의 급여에 성과금을 더한 급여는 얼마인가? 직원이름, 급여,성과금, 성과금을 포함한 급여를 출력하라
  --> null인 값이 연산에 참여하면 그대로 null이기 때문에 nvl을 쓴다.
  
  select last_name, salary, commission_pct, salary+salary*commission_pct
  from employees;
  
  select last_name,salary+salary*nvl(commission_pct,0) from employees;
  
   
  nvl(commission_pct,0) -> null값이 있는 즉 커미션이 없는 사람들의 커미션을 0으로 설정해주는함수
  
  nvl(column,null일경우 반환값)
  nvl(column,0) : 컬럼의 값이 null일 경우 0으로 치환
  nvl(column,'') : 컬럼의 값이 null일 경우 ''으로 치환
  
   select last_name nvl(manager_id, '상관이 없음') null값인 사람들을 상관이 없음 이라고 나타내고 싶은데
       manager_id는 숫자 상관이없음은 문자라 오류가 난다.
 to_char(manager_id,'0')로 문자형으로 바꾼다 -> nvl(to_char(manager_id,'0') '상관이 없음')
  
  select last_name, to_char((salary+salary*nvl(commission_pct,0)),'99,999')
  from employees;
    ->  to_char를 활용해서 성과금 포함 급여의 표기 방식을 변환시킴
 select 절에 오는 컬럼 앞에 to_char 사용..
 
 

-- 직종에 따라 급여의 보너스를 차등 지급 하려고 할때
--IT_PROG는 보너스 1.1,     ST_CLERK은 보너스 1.15,       SA_REP는 보너스 1.20
-> (case when then when then ... else end)를 select절에

select last_name, job_id, salary, (case job_id when 'IT_PROG' then 1.1*salary
when 'ST_CLERK' then 1.15*salary when 'SA_REP' then 1.2*salary else salary end) sal_bonus
from employees;


문제: 급여가 2500,3500,7000이 아니며 직업이 SA_REP나 ST_CLERK인 사원의 이름과 급여, 직업을 출력하시오
select last_name, salary, job_id from employees where salary not in(2500,3500,7000) and 
job_id in('SA_REP','ST_CLERK');

문제: 30번 부서내의 모든 직업들을 유일한 값으로 출력하시오. 90번 부서 또한 포함하고, 직업을 오름차순으로 출력하시오
select distinct department_id,job_id from employees where department_id in(30,90)
order by job_id;