35일차 22.11.09

**문자함수

--문자열 함수
--lower(소문자), initcap(앞글자만 대문자), upper(대문자)

select 'abcDEF', lower('abcDEF'), upper('abcDEF'), initcap('abcDEF') from dual;

select last_name, lower(last_name), upper(last_name), initcap(last_name) from employees;

select * from employees where lower(last_name) = 'king';

--length(길이) , instr(문자 찾기)
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(first_name,concat(' ' , last_name)) as name from employees;

--LPAD, RPAD
select lpad('abc', 10, '*') from dual;
select rpad('abc', 10, '*') from dual;
select lpad(first_name, 10, '-') 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(replace('my dream is a president' , ' ', ''),'president' , 'doctor') from dual;

--1번
select first_name || ' ' || last_name as 이름,
replace(hire_date,'/','') as 입사일자
from employees
order by 이름 asc;

--2번
select replace(phone_number,substr(phone_number,1,3),'02') as 전화번호 
from employees;

--3번
select rpad(substr(first_name,1,3),length(first_name),'*') as 이름,
lpad(salary,10,'*') as 급여
from employees 
where lower(job_id) = 'it_prog';

LPAD - 왼쪽에 남은 부분을 주어진 문자로 채움 
RPAD - 오른쪽에 남은 부분을 주어진 문자로 채움

 

LTRIM - 특정 문자열의 왼쪽부터 지정된 단어가 발견되면 제거, 문자 지정하지 않을 경우  왼쪽 공백을 제거
RTRIM - 특정 문자열의 오른쪽부터 지정된 단어가 발견되면 제거, 문자 지정하지 않을 경우 오른쪽 공백을 제거

 

 

*****REPLACE*****
- 지정 문자열을 새로운 문자열로 대체
- 공백 또한 제거 가능

 

문제
1. EMPLOYEES 테이블에서 JOB_ID 가 it_prog 인 사원의 이름 first_name 과 급여 ( 를 출력하세요
조건
1) 비교하기 위한 값은 소문자로 입력해야 합니다 힌트 : lower 이용
조건
2) 이름은 앞 3 문자까지 출력하고 나머지는 로 출력합니다 .
이
열의 열 별칭은 name 입니다 힌트 : rpad 와 substr 또는 substr 그리고 length 이용
조건
3) 급여는 전체 10 자리로 출력하되 나머지 자리는 로 출력합니다 .
이
열의 열 별칭은 salary 입니다 힌트 : lpad 이용

 

문제
1.
EMPLOYEES
테이블 에서 이름 , 입사일자 컬럼으로 변경해서 이름순으로 오름차순 출력 합니다
조건
1) 이름 컬럼은 first_name , last_name 을 붙여서 출력합니다
조건
2) 입사일자 컬럼은 xx/xx/xx 로 저장되어 있습니다 xxxxxx 형태로 변경해서 출력합니다
문제
2.
EMPLOYEES
테이블 에서 phone_numbe 컬럼은 형태로 저장되어 있다
여기서
처음 세 자리 숫자 대신 서울 지역변호 ( 를 붙여 전화 번호를 출력하도록 쿼리를 작성하세요
문제
3.
EMPLOYEES
테이블에서 JOB_ID 가 it_prog 인 사원의 이름 first_name 과 급여 ( 를 출력하세요
조건
1) 비교하기 위한 값은 소문자로 입력해야 합니다 힌트 : lower 이용
조건
2) 이름은 앞 3 문자까지 출력하고 나머지는 로 출력합니다 .
이
열의 열 별칭은 name 입니다 힌트 : rpad 와 substr 또는 substr 그리고 length 이용
조건
3) 급여는 전체 10 자리로 출력하되 나머지 자리는 로 출력합니다 .
이
열의 열 별칭은 salary 입니다 힌트 : lpad 이용

 

--문자열 함수
--lower(소문자), initcap(앞글자만 대문자), upper(대문자)

select 'abcDEF', lower('abcDEF'), upper('abcDEF'), initcap('abcDEF') from dual;

select last_name, lower(last_name), upper(last_name), initcap(last_name) from employees;

select * from employees where lower(last_name) = 'king';

--length(길이) , instr(문자 찾기)
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(first_name,concat(' ' , last_name)) as name from employees;

--LPAD, RPAD
select lpad('abc', 10, '*') from dual;
select rpad('abc', 10, '*') from dual;
select lpad(first_name, 10, '-') 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(replace('my dream is a president' , ' ', ''),'president' , 'doctor') from dual;

--1번
select first_name || ' ' || last_name as 이름,
replace(hire_date,'/','') as 입사일자
from employees
order by 이름 asc;

--2번
select replace(phone_number,substr(phone_number,1,3),'02') as 전화번호 
from employees;

--3번
select rpad(substr(first_name,1,3),length(first_name),'*') as 이름,
lpad(salary,10,'*') as 급여
from employees 
where lower(job_id) = 'it_prog';

**숫자함수

*ROUND 
- 지정 소수점 위치까지 반올림
- 인자 값이 하나만 들어간다면 정수 부분까지 반올림 한다고 생각 ex) round(45.923) -> 46

 

*TRUNC
- 지정 소수점 위치까지 절삭
- 반올림이 아닌 절삭!~!
- 인자가 하나만 있으면 정수부분만 나온다
- -1이 들어가면 정수부분 절삭 ex) trunc(45.966, -1) -> 40

 

*CEIL
- 올림
*FLOOR
- 내림
*MOD
- 나머지 (자바에서 %랑 똑같다)

 

*****날짜함수*****

 

 

- 날짜함수는 연산이 가능하다
-> 날짜 - 날짜 = 일수 출력
-> 날짜 - 숫자 = 날짜 출력(날짜에서 일수를 뺀다)
- round, trunc 사용도 가능 (많이는 안쓰임)


***********sysdate********** 

- 현재 날짜를 반환하는 함수

 

**********systimestamp**********

 

- 현재의 날짜 & 시간까지 반환하는 함수
- UTC  기준으로 시간 출력 ex) 한국 -> +09:00


***********************************변환함수***********************************
★★★★★★어어어어엄청 중요★★★★★★★
- 형변환 함수

DB의 데이터 타입
1. 문자 (VARCHAR2)
2. 숫자(NUMBER)
3. 날짜(DATE)

각 타입을 서로 변환할 때 쓰는 함수 -> 형변환함수

문자 <-> 숫자 ==> 자동 형변환
문자 <-> 날짜 ==> 자동 형변환

but 안되는 경우 따로 함수를 이용해서 변환해줘야 한다
- 항상 문자를 기준으로 한다
- 문자를 기준으로 숫자로 바꿀 때는 to_number 사용(숫자타입으로 형변환),  숫자에서 문자열로 형변환할 때는 to_char사용(문자타입으로 형변환)
- 문자를 기준으로 날짜로 바꿀 때는 to_date 사용(날짜타입으로 형변환), 날짜에서 문자열로 형변환할 때는 to_char사용(문자타입으로 형변환)

- fmt : format 형식이라고 생각하면 된다
-> 년월일시분초 정도만 암기
☆년: YYYY or YY
☆월: MM
☆일: DDD or DD or D
☆시: HH
☆분: MI(자바와 다름!)
☆초: SS 

포맷문자랑 문자를 같이 쓰고 싶으면 "" 을 사용한다
ex) 'YYYY"년" MM"월" DD"일"'

근데 그냥 - 사용하면 된다
'YYYY-MM-DD'  요렇게 더 자주 사용

숫자 포맷
9 - 자리수를 나타낸다 ex) 999999 -> 6자리

*to_number 함수
- 문자스트링을 숫자형식으로 변환
- 포맷형식 지정해주면 된다


************to_date함수************
- 왕중요
- 문자열을 날짜 형식으로 변환
- 날짜 차이를 구하고 싶을 때 사용

*NVL 함수
- Null 값을 실제값으로 변환하기 위해서 사용
- null일 때 어떤 값으로 변환할건지 정해준다

 

*NVL2 함수
- Null값이 아닐 경우 표현하는 값을 두번째 인자에 넣음
- null값일 경우 출력하는 값을 세번째 인자에 넣음

 

*****Decode 함수*****

- if 구문이랑 같은 방법
- 컬럼이 search1과 같으면 result1 이 출력, search2와 같으면 result2 출력, 마지막에는 default값이 있다

*case~ when~ then 구문
- if~else , switch~ case 구문이랑 같은 기능

문제
1.
현재일자를
기준으로 EMPLOYEE 테이블의 입사일자 hire_date 를 참조해서 근속년수가 10 년 이상 인
사원을 다음과 같은 형태의 결과를 출력하도록 쿼리를 작성해 보세요 .
조건
1) 근속년수가 높은 사원 순서대로 결과가 나오도록 합니다
문제
2.
EMPLOYEE
테이블의 manager_id 컬럼을 확인하여 first_name , manager_id , 직급을 출력합니다
100
이라면 사원 ’,
120
이라면 주임
121
이라면 대리
122
라면 과장
나머지는
임원 ’ 으로 출력합니다
조건
1) manager_id 가 50 인 사람들을 대상으로만 조회합니다

 

--형변환함수
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 HH:MI:SS Month') 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; -- 소수점
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; -- 자동 형변환
select to_number('2000') + 2000 from dual; --명시적 변환

select '$3000' + 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; 
select sysdate - to_date('2022-08-01', 'YYYY-MM-DD') from dual; 
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'),'YYYY"년" MM"월" DD"일"') from dual;

select substr('20050102',1,4) ||'년 ' || substr('20050102',5,2) || '월 ' || substr('20050102',7,2) || '일' 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 to_char(NVL2(commission_pct, salary + (salary * commission_pct), salary),'$999,999,999') as 총급여 from employees;
select first_name as 이름,
salary as 급여,
commission_pct as 성과급율,
NVL2(commission_pct, salary + (salary * commission_pct), salary)as 총급여 
from employees;

--DECODE(if~~)
select decode('a', 'a', 'a입니다', 'a가 아닙니다')from dual;
select decode('c', 'a', 'a입니다',     
                   'b', 'b입니다',
                   'c', 'c입니다',
                   '전부 아닙니다')from dual;
                   
--case when then else end
select job_id,
       salary,
       case job_id when 'IT_PROG' then salary *1.1
                   when 'FI_MGR' 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_MGR' 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 사원번호,
first_name||' ' || last_name as 사원명,
hire_date as 입사일자,
floor((sysdate - hire_date)/365)as 근속년수 
from employees 
where floor((sysdate - hire_date)/365) >= 10
order by 근속년수 desc; 

select *from employees;
--문제 2
select first_name as 이름,
       department_id as 부서,
       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 as 이름,
department_id as 부서,
decode(manager_id, 100, '사원',     
                   120, '주임',
                   121, '대리',
                   122, '과장',
                        '임원')
from employees
where department_id = 50;

***집합연산자*** 어렵다규 함

-  row 와 row를 합치는 것, 컬럼 개수가 정확하게 일치해야 한다
- 집합연산자를 이용해서 여러 select 구문을 붙일 수 있다

*union
- 조회된 결과들의 합집합
- 중복은 포함하지 않는다
-  select구문과 select구문 사이에 넣어준다

 

*union all
- 합집합인데 중복도 다 포함해서 출력한다
- 마찬가지로 select구문과 select구문 사이에 넣어준다

 

 

*intersect
- 교집합만 출력된다

 

 

*minus
- 차집합을 출력한다
- 위의 select 구문에서 아래 select 집합 데이터를 뺀 값

 

**분석함수 - sqld 시험에 간혹 나와서 공부하는게 죠음, 외우지않아듀 됨

- over 함수랑 같이 쓰인다 
- over(순서를 지정해줄 조건) ex =>  over(order by salary desc)

*rank
- 조회한 값에 대한 순서를 매겨준다 (중복순위 계산)
-> 공동 순위를 계산한다
ex) 1 2 2 4

*dense_rank
- 조회한 값에 대한 순서를 매겨준다 (중복순위 계산하지 않음)
-> 공동 순위 계산 노노
ex) 1 2 2 3

*row_number
- 조건을 만족하는 모든 행 번호를 제공, 일련번호 생성
 

 

--집합 연산자(위 아래 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 as 조회된순서
from employees
order by salary desc;