-- 문자열 함수
-- lower (소문자), initcap(앞글자만 대문자), upper(대문자)
select 'abcDEF', lower('abcDEF'), upper('abcDEF'), initcap('abcDEF') from dual;
-- 결과: abcDEF, abcdef, ABCDEF, Abcdef
select last_name, lower(last_name), initcap(last_name), upper(last_name) from employees; -- table, alias 다 가능
select * from employees where lower(last_name) = 'k ing'; -- 소문자로 바뀌었을 때 나오는 값을 입력해야 함
-- length(길이), instr(문자 찾기), 인덱스는 1부터 시작
select first_name, length(first_name), instr(first_name, 'a') from employees;
-- substr(문자열 자르기-절삭/추출), concat(문자 연결)
select 'abcdef', substr('abcdef', 3) , substr('abcdef', 1, 3) from dual;
select first_name, substr(first_name, 1, 3) from employees;
--
select 'abc' || 'def' from dual;
select concat('abc', 'def') from dual;
select concat(first_name || ' ', last_name) as name from employees; -- || 문자열 붙이기 사용
select concat( concat(first_name,' '), last_name ) as name from employees; -- 함수 중첩
-- LTRIM, RTRIM, TRIM (공백제거)
select ltrim(' javascript_java') from dual; -- 좌측 공백제거
select ltrim('javascript_java', 'java') from dual; -- 좌측 제거
select rtrim('javascript_java ') from dual; -- 우측 공백제거
select rtrim('javascript_java', 'java') from dual; -- 우측 제거
select trim(' javascript_java ') from dual; -- 양쪽 공백제거
-- replace
select replace ('my dream is a president', 'president', 'doctor') from dual;
select replace ('my dream is a president', ' ', '') from dual;
-- nested(중첩) 방식 사용
select replace( replace ('my dream is a president', 'president', 'doctor'), ' ', '' ) from dual;
-- 문제1
select concat(first_name || ' ', last_name) as 이름, replace(hire_date, '/', '') as 입사일자 from employees order by 이름 asc;
-- 문제2
select '(02)' || substr(phone_number, 4) from employees;
select concat( '(02) ' , substr( phone_number, 4, length(phone_number) ) ) from employees;
-- 문제3
select rpad(substr(first_name, 1, 3), length(first_name), '*') as name, lpad(salary, 10, '*') as salary from employees where lower(job_id) = 'it_prog';
--문제 1.
--EMPLOYEES 테이블 에서 이름, 입사일자 컬럼으로 변경해서 이름순으로 오름차순 출력 합니다.
--조건 1) 이름 컬럼은 first_name, last_name을 붙여서 출력합니다.
--조건 2) 입사일자 컬럼은 xx/xx/xx로 저장되어 있습니다. xxxxxx형태로 변경해서 출력합니다.
select first_name || last_name as 이름, replace(hire_date, '/', '')as 입사일자 from employees
order by first_name asc;
--문제 2.
--EMPLOYEES 테이블 에서 phone_numbe컬럼은 ###.###.####형태로 저장되어 있다
--여기서 처음 세 자리 숫자 대신 서울 지역변호 (02)를 붙여 전화 번호를 출력하도록 쿼리를 작성하세요
select phone_number from employees;
select concat('02', substr(phone_number, 4)) from employees;
--문제 3.
--EMPLOYEES 테이블에서 JOB_ID가 it_prog인 사원의 이름(first_name)과 급여(salary)를 출력하세요.
--조건 1) 비교하기 위한 값은 소문자로 입력해야 합니다.(힌트 : lower 이용)
--조건 2) 이름은 앞 3문자까지 출력하고 나머지는 *로 출력합니다.
--이 열의 열 별칭은 name입니다.(힌트 : rpad와 substr 또는 substr 그리고 length 이용)
--조건 3) 급여는 전체 10자리로 출력하되 나머지 자리는 *로 출력합니다.
--이 열의 열 별칭은 salary입니다.(힌트 : lpad 이용)
select rpad(substr(first_name, 1, 3), length(first_name), '*') as name,
lpad(salary, 10, '*') as salary
from employees where lower(job_id) = 'it_prog';
-- 숫자함수
-- round (반올림)
select round(45.923, 1), round(45.923, 0), round(45.923, -1) from dual;
-- trunc (절삭)
select trunc(45.923, 1), trunc(45.923, 0), trunc(45.923, -1) from dual;
-- abs, ceil, floor
select abs(-24) from dual; -- 절댓값
select ceil(3.14) from dual; -- 올림
select floor(3.14) from dual; -- 내림
-- mod
select mod(10, 3) as 나머지, 10 / 3 as 몫 from dual;
-- 날짜함수
select sysdate from dual; -- 년월일
select systimestamp from dual; -- 년월일시분초
-- 날짜연산 (결과는 일수)
select first_name, sysdate - hire_date from employees;
select first_name, (sysdate - hire_date) / 7 from employees;
select first_name, (sysdate - hire_date) / 365 from employees;
-- 날짜 trunc, round
select round(sysdate) from dual; -- 12시가 지나서 다음날로 나옴
select round(sysdate, 'year') from dual; -- 년 기준 반올림
select round(sysdate, 'month') from dual; -- 월 기준 반올림
select round(sysdate, 'day') from dual; -- 해당 주의 일요일
select trunc(sysdate) from dual;
select trunc(sysdate, 'year') from dual; -- 년 기준 절삭
select trunc(sysdate, 'month') from dual;
select trunc(sysdate, 'day') from dual; -- 전 주의 일요일
select * from employees where salary >= '5000';
select department_id, decode( grouping(job_id), 1, '소계', job_id ), avg(salary), count(salary), grouping(job_id), grouping(department_id)
from employees
group by rollup(department_id, job_id)
order by department_id, job_id;
-- 형변환함수
select to_char (sysdate, 'yyyy-mm-dd') from dual;
select to_char (sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;
select to_char (sysdate, 'yyyy-mm-dd month hh:mi:ss') from dual;
select first_name, to_char (hire_date, 'yyyy-mm-dd hh:mi:ss') from employees;
select first_name, to_char (hire_date, ' yyyy"년"mm"월"dd"일" ') from employees;
-- 숫자를 문자로
select to_char(20000, '99999') || '원' from dual; -- 자리수
select to_char(20000, '9999') || '원' from dual; -- 자리수 부족
select to_char(20000.14, '99999.99') from dual; -- 소수점
select to_char(20000.14, '99999') from dual; -- 소수점 자리를 빼버리면 다섯자리만 나옴
select to_char(20000.14, '999,999,999') from dual; -- 총 9자리, 3자리마다 콤마
select to_char( salary, '$999,999') as salary from employees;
select to_char( salary, 'L999,999') as salary from employees;
-- salary를 현재 환율에 맞춰서 한국돈으로 표현, 환율: 1364원
select to_char( salary * 1364, 'L999,999,999') as salary from employees;
-- to_number (문자를 숫자로)
select '2000' + 2000 from dual; -- 출력: 4000, '2000'이 자동형변환 됨
select to_number('2000') + 2000 from dual; -- 명시적변환
select '$3,000' + 3000 from dual; -- 앞의 문자가 형변환 되지 않았음
select to_number ('$3,000', '$9,999') + 3000 from dual;
-- to_date (문자를 날짜로)
select sysdate - '2022-08-01' from dual;
select sysdate - to_date('2022-08-01') from dual; -- ok;
select sysdate - to_date('2022-08-01', 'yyyy-mm-dd') from dual; -- ok
select to_date('2020-03-31 12:23:03', 'YYYY-MM-DD hh:mi:ss') from dual;
-- xxxx년 xx월 xx일 문자열 형식으로 변환하세요
select to_char (to_date('20050102'), ' yyyy"년"mm"월"dd"일" ') from dual;
-- NVL (null에 대한 처리)
select nvl(null, 0), nvl(3.14, 0) from dual;
select nvl(commission_pct, 0) from employees;
-- NVL2 (컬럼, null아닌 경우 값, null일 경우 값)
select nvl2(null, '널 아님', '널') from dual;
select first_name,
salary,
commission_pct,
nvl2(commission_pct, salary+(salary*commission_pct), salary) from employees;
-- DECODE ( if~ )
select decode('c', 'a', 'a입니다',
'b', 'b입니다',
'c', 'c입니다',
'전부 아닙니다') from dual;
select job_id, salary,
decode(job_id, 'IT_PROG', salary * 0.1,
'FI_MGR', salary * 0.2,
'AD_VP', salary * 0.3,
salary) -- 마지막 디폴트 값
from employees;
-- case when then else end
select job_id, salary,
case job_id when 'IT_PROG' then salary*1.1
when 'FI_MGE' then salary*1.2
when 'AD_VP' then salary*1.3
else salary
end as result
from employees;
select job_id, salary,
case when job_id = 'IT_PROG' then salary*1.1
when job_id = 'FI_MGE' then salary*1.2
when job_id = 'AD_VP' then salary*1.3
else salary
end as result
from employees;
-- 문제1
select employee_id as 사원번호,
concat(first_name, last_name) as 사원명,
hire_date as 입사일자,
trunc((sysdate - hire_date) / 365) as 근속년수
from employees where(sysdate - hire_date) / 365 >= 10 order by hire_date asc;
-- 문제2 (case-when-then / decode)
select first_name, manager_id,
case manager_id when 100 then '사원'
when 120 then '주임'
when 121 then '대리'
when 122 then '과장'
else '임원'
end as 직급
from employees where department_id = 50;
select first_name, manager_id,
decode( manager_id,100, '사원',
120, '주임',
121, '대리',
122, '과장',
'임원') as 직급
from employees where department_id = 50;
-- 집합연산자 (위 아래 column 개수가 정확히 일치해야 함)
-- union(합집합 중복x), union all(합집합 중복o), intersect(교집합), minus(차집합)
select employee_id, first_name from employees where hire_date like'04%'
union
select employee_id, first_name from employees where department_id = 20;
select employee_id, first_name from employees where hire_date like'04%'
union all
select employee_id, first_name from employees where department_id = 20;
select employee_id, first_name from employees where hire_date like'04%'
intersect
select employee_id, first_name from employees where department_id = 20;
select employee_id, first_name from employees where hire_date like'04%'
minus
select employee_id, first_name from employees where department_id = 20;
---------
select employee_id, first_name, salary from employees where department_id = 20
union all
select employee_id, first_name, salary from employees where salary > 10000
union all
select 300, 'hong', 20000 from dual;
----------------------------------------------------------------------
-- 분석함수( ) over(조건)
select employee_id,
department_id,
salary,
rank() over(order by salary desc) as 중복순위o,
dense_rank() over(order by salary desc) as 중복순위x,
row_number() over(order by salary desc) as 일련번호,
rownum -- 조회된 순서
from employees
order by salary desc;
