너는 파도처럼 내게 밀려 ORACLE 003

select * from tabs;

select * from dept;

select * from emp;

-- 문자열 연산자 like, 와일드카드 _, %
select * from emp where ename like 'S%';  -- S로 시작하는 것 찾기

select * from emp where ename like '_L%';  -- 두번째 자리만 L이면 되는 것 찾기

select * from emp where ename not like '%AM';  -- AM이 포함되어 있지 않는 것 찾기

-- NULL 연산자
select ename, sal, sal*12+comm as annsal, comm from emp;  -- NULL값이 포함되어 있다.
select ename, sal, sal*12+comm as annsal, comm from emp where comm is not null;  -- NULL값이 포함되어 있지 않다.

-- 집합 연산자
select empno, ename, sal, deptno from emp where deptno = 10
union
select empno, ename, sal, deptno from emp where deptno = 20;

select empno, sal, ename, deptno from emp where deptno = 10
union
select empno, ename, sal, deptno from emp where deptno = 20;  -- Error 발생 : 데이터 타입이 일치하지 않음

select empno, ename, sal, deptno from emp
INTERSECT
select empno, ename, sal, deptno from emp where deptno = 10;  -- deptno = 10인 교집합만 가져옴

-- 문자열 함수
-- Upper, Lower
select ename, upper(ename), lower(ename), initcap(ename) from emp;

-- length
select ename, length(ename) from emp;

-- substr
select job, substr(job,1,2), substr(job,3,2), substr(job,5) from emp;  -- substr(job,5) : 5번째부터 끝까지 출력

-- instr
-- 파라미터
-- 1. 대상 문자열 데이터 (필수)
-- 2. 위치를 찾으려는 부분 문자(필수)
-- 3. 위치 찾기를 시작할 대상 무낮열 데이터 위치 (선택, 기본값 1)
-- 4. 시작 위치에서 찾으려는 문자가 몇 번째 인지 지정(선택, 기본값 1)
select
    instr('HELLO, ORACLE!', 'L') as instr_1,
    instr('HELLO, ORACLE!', 'L', 5) as instr_2,
    instr('HELLO, ORACLE!', 'L', 2, 2) as instr_3
from dual;  -- dual : 임시 테이블, 주로 계산이나 테스트할 떄 사용

-- replace
select '010-1234-5678' as replace_before,
    replace('010-2345-6789', '-', ' ') as replace_1,
    replace('010-3456-7890', '-') as replace_2
from dual;  

-- LPAD, RPAD
select 'Oracle',
    lpad('Oracle', 10, '#') as lpad_1,
    rpad('Oracle', 10, '*') as rpad_1,
    lpad('Oracle', 10) as lpad_2,
    rpad('Oracle', 10) as rpad_2
from dual;    

select
    rpad('971225-', 14, '*') as rpad_ssn,
    rpad('010-1234-', 13, '*') as rpad_phone
from dual;

-- Trim
select '[' || trim(' - - Oracle - - ') || ']' as trim from dual;

-- ROUND
select 
    round(1234.5678) as round,
    round(1234.5678, 0) as round_0,
    round(1234.5678, 1) as round_1,
    round(1234.5678, 2) as round_2,
    round(1234.5678, -1) as round_minus1,
    round(1234.5678, -2) as round_minus2
from dual; 

-- 형변환 함수 to_char()
select sal, 
    to_char(sal, '$999,999') as sal_$,
    to_char(sal, 'L999,999') as sal_L,
    to_char(sal, '999,999.00') as sal_1,
    to_char(sal, '000,999,999.00') as sal_2,
    to_char(sal, '000999999.99') as sal_3
from emp;    

-- 형변환 함수 to_char() : 날짜 -> 문자
select sysdate,
    to_char(sysdate, 'MM') as MM,
    to_char(sysdate, 'MON') as MON,
    to_char(sysdate, 'MONTH') as MONTH,
    to_char(sysdate, 'DD') as DD,
    to_char(sysdate, 'DY') as DY,
    to_char(sysdate, 'DAY') as DAY
from dual;

select sysdate,
    to_char(sysdate, 'HH24:MI:SS') as HH24MISS,
    to_char(sysdate, 'HH12:MI:SS AM') as HHMISS_AM,
    to_char(sysdate, 'HH24:MI:SS PM') as HHMISS_PM
from dual;    

-- NULL 관련 함수
-- NVL
select empno, ename, sal, comm, sal+comm, nvl(comm, 0), sal+nvl(comm, 0) from emp;

-- NVL2 : 값이 있는지 없는지를 따지는 함수
select empno, ename, sal, comm, nvl2(comm, 'O', 'X'), nvl2(comm, sal*12+comm, sal*12) as annsal from emp;

-- DECODE : 조건문처럼 사용 가능
select empno, ename, job, sal,
    decode(job, 
            'MANAGER', sal*1.1,
            'SALESMAN', sal*1.05,
            'ANALYST', sal,
            sal*1.03) as upsal
from emp;    

-- CASE문 : decode와 결과가 같다.
select empno, ename, job, sal,
    case job
        when 'MANAGER' then sal*1.1
        when 'SALESMAN' then sal*1.05
        when 'ANALYST' then sal
        else sal*1.03
    end as upsal
from emp;    

-- 집계 함수
select sum(sal) from emp;
select
    sum(DISTINCT sal),
    sum(all sal),
    sum(sal)
from emp;

select ename, sum(sal) from emp; -- 오류 나는 이유 : ename은 데이터를 여러개 출력하는데 sum(sal)은 하나의 데이터만 나와서 안 된다.
select ename, sum(sal) from emp group by ename;  -- 오류가 안나려면 이렇게 써야함

select count(*) from emp where deptno = 30;

-- max, min
select max(sal) from emp where deptno = 10;
select min(sal) from emp where deptno = 10;

select max(hiredate) from emp where deptno = 20;

-- avg
select round(avg(sal)) from emp where deptno = 30;

-- roll up : 소계 내는 법
select deptno, job, sum(sal) from emp group by rollup(deptno, job);

-- 그룹화
select deptno, job, avg(sal) from emp group by deptno, job order by 1, 2;

-- having
select deptno, job, avg(sal) from emp 
group by deptno, job 
having avg(sal) >= 2000 
order by 1, 2;

-- having + where
select deptno, job, avg(sal) from emp 
where sal <= 3000 
group by deptno, job 
having avg(sal) >= 2000 
order by 1, 2;

-- listagg : 하나의 컬럼에 데이터를 여러개 넣을 수 있음(행이 너무 많을 때 주로 사용)
-- listagg(나열할 열(필수), 데이터 구분자(선택))
-- within group(order by  정렬 기준 컬럼(선택))
select deptno,
    listagg(ename, ', ')
    within group(order by sal desc) as enames
from emp
group by deptno;

select * from employees;

-- 1. 연봉이 12000 이상되는 직원들의 LAST_NAME 및 연봉을 조회한다.
select LAST_NAME, SALARY from employees where SALARY >= 12000;

-- 2. 사원번호가 176인 사람의 LAST_NAME 과 부서 번호를 조회한다.
select LAST_NAME, department_id from employees where employee_id = 176;

-- 3. 연봉이 5000에서 12000의 범위 이외인 사람들의 LAST_NAME 및 연봉을 조회한다.
select LAST_NAME, SALARY from employees where SALARY < 5000 or SALARY > 12000;

-- 4. 2002/02/20 일부터 2005/05/01 사이에 고용된 사원들의 LAST_NAME, 사번, 고용일자를 조회한다. (고용일자 순으로 정렬)
select last_name, employee_id, hire_date from employees where hire_date >= '2002/02/20' and hire_date <= '2005/05/01';

-- 5. 20번 및 50번 부서에서 근무하는 모든 사원들의 LAST_NAME 및 부서 번호를 알파벳 순으로 조회한다.
select LAST_NAME, department_id from employees where department_id in (20, 50) order by last_name asc;

-- 6. 20번 및 50번 부서에서 근무하며, 연봉이 5000 ~ 12000 사이인 사원들의 LAST_NAME 및 연봉을 조회한다.
select LAST_NAME, department_id from employees where department_id in (20, 50) and (SALARY >=5000 AND SALARY <= 12000);

-- 7. 2002년도에 고용된 모든 사람들의 LAST_NAME 및 고용일을 조회한다.
select last_name, employee_id, hire_date from employees where hire_date like '02%';

-- 8. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID를 조회한다.
select last_name, job_id from employees where manager_id is null;

-- 9. 커미션을 버는 모든 사원들의 LAST_NAME, 연봉 및 커미션을 조회한다. (연봉 역순, 커미션 역순차로 정렬한다)
select LAST_NAME, salary, commission_pct from employees where commission_pct is not null order by salary desc,commission_pct desc;

-- 10. LAST_NAME의 네 번째 글자가 a 인 사원들의 LAST_NAME을 조회한다.
select LAST_NAME from employees where LAST_NAME LIKE '___a%';