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

-- JOIN
-- 등가조인
select * from emp, dept order by empno;

select
    e.empno, e.ename, e.sal, d.dname
from emp e, dept d  -- 별칭을 주면 어디서 가져온 것인지 더 명확해진다.
where e.deptno = d.deptno  
order by e.empno;  

-- 위와 아래는 같다.

select
    empno, ename, sal, dname
from emp
inner join dept on emp.deptno = dept.deptno
order by empno;

select
    e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc
from emp e, dept d
where
    e.deptno = d.deptno
    and sal >= 3000;  -- 조건을 추가할 수도 있다.
    
select * from salgrade;

-- 비등가 조인(범위)..... 잘 안씀....
select *
from emp e, salgrade s
where e.sal between s.losal and s.hisal;  -- s.losal and s.hisal의 범위안에 있는 e.sal

-- 외부 조인
-- LEFT 조인 (Oracle 표기)
select e1.empno, e1.ename, e1.mgr,
    e2.empno as mgr_empno,
    e2.ename as mgr_ename
from emp e1, emp e2
where e1.mgr = e2.empno (+)
order by e1.empno;

-- RIGHT 조인 (Oracle 표기)
select e1.empno, e1.ename, e1.mgr,
    e2.empno as mgr_empno,
    e2.ename as mgr_ename
from emp e1, emp e2
where e1.mgr (+) = e2.empno 
order by e1.empno;

-- ANSI 표기 (10g ver 이상)
-- LEFR 조인
select e1.empno, e1.ename, e1.mgr,
    e2.empno as mgr_empno,
    e2.ename as mgr_ename
from 
    emp e1 left join emp e2
    on e1.mgr = e2.empno
order by
    e1.empno;
    
-- ANSI 표기 (10g ver 이상)
-- RIGHT 조인
select e1.empno, e1.ename, e1.mgr,
    e2.empno as mgr_empno,
    e2.ename as mgr_ename
from 
    emp e1 right join emp e2
    on e1.mgr = e2.empno
order by
    e1.empno, mgr_empno;
    
-- 서브 쿼리
select sal from emp
where ename = upper('jones');

select * from emp
where sal > 2975;

-- emp 테이블에 있는 사람들 중에서 jones 보다 많이 받는 사람을 구하자.
select * from emp
where sal > (select sal from emp
             where ename = upper('jones'));
             
select * from emp
where hiredate < (select hiredate from emp
                  where ename = 'CLARK');
                  
select * from emp
where sal in (select max(sal) from emp
              group by deptno);
              
-- 비교할 열이 여러 개인 다중 열 서브쿼리
select * from emp
where (deptno, sal) in (select deptno, max(sal) from emp
                        group by deptno);

select e10.empno, e10.ename, e10.deptno, d.dname, d.loc
from (select * from emp where deptno = 10) e10,
     (select * from dept) d
where e10.deptno = d.deptno;

-- DDL create
-- 1. 테이블 직접 만들기
create table kd1Test (
    name    varchar2(30)    not null,
    age     number
);  

select * from kd1test;

-- 2. 테이블 복사
create table dept_temp
    as select * from dept; 
    
select * from dept_temp;

desc dept_temp;
desc dept;  -- 제약 조건은 복사가 되지 않는다.

-- DML insert
-- 1.
insert into dept_temp(deptno, dname, loc)
    values(50, 'DATABASE', 'SEOUL');
    
select * from dept_temp;    

-- 2.
insert into dept_temp values(60, 'ORACLE', 'GWANGJU');

select * from dept_temp;

delete dept_temp where deptno = 60;

select * from dept_temp;

insert into dept_temp values(60, 'NETWORK', 'BUSAN');

insert into dept_temp(deptno, dname, loc)
    values(70, 'WEB', null);  -- 제약조건에 null이 없으므로 null 가능
    
insert into dept_temp(deptno, dname, loc)
    values(80, 'MOBILE', '');  -- 빈 문자열도 null로 인식한다. 
    
 insert into dept_temp(deptno, loc)
    values(90, 'INCHEON');  -- null의 암시적 입력 
    
select * from dept_temp;

-- 빈 껍데기만 만들기
create table emp_temp
    as select * from emp where 1 <> 1;  -- 조건이 없으므로(1 <> 1는 false이므로) 구조만 복사해온다.... 껍데기만....
    
SELECT * FROM emp_temp;

-- insert 문으로 날짜 데이터 입력하기
insert into emp_temp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values(9999, '피카츄', 'PRESIDENT', null, '2001/01/01', 5000, 1000, 10);  -- 문자열로 넣어도 자동 형변환으로 날짜로 입력됨
    
insert into emp_temp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values(1111, '라이츄', 'MANAGER', 9999, '2001-01-05', 4000, null, 20);  -- 문자열로 넣어도 자동 형변환으로 날짜로 입력됨  
    
-- TO_DATE()를 사용하여 날짜 데이터 입력하기
insert into emp_temp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values(2111, '파이리', 'MANAGER', 9999, to_date('07/01/2001', 'DD/MM/YYYY'), 4000, null, 20);

-- SYSDATE를 사용하여 날짜 데이터 입력하기
insert into emp_temp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values(3111, '꼬부기', 'MANAGER', 9999, sysdate, 4000, null, 30);

-- 서브쿼리로 insert 하기    
insert into emp_temp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno
    from emp e, salgrade s
    where e.sal between s.losal and s.hisal and s.grade = 1;
    
SELECT * FROM emp_temp;  

-- update
create table dept_temp2
    as select * from dept;
    
select * from dept_temp2;

update dept_temp2
set loc = 'SEOUL';  -- 주의!! 조건을 따로 설정하지 않으면 원치 않은 데이터까지 다 수정됨

-- DML은 update를 저장(commit)하지 않으면 트랜잭션이 끝나지 않아서 무효화가 가능해진다.
rollback;

select * from dept_temp2;

-- 데이터의 일부분을 수정하기
update dept_temp2
set dname = 'DATABASE', loc = 'SEOUL'
where deptno = 40;

select * from dept_temp2;

-- 이대로는 저장이 되지 않는다. 커밋을 해줘야됨  --> 커밋하고 나서는 롤백 불가능
commit;

-- 서브쿼리를 사용하여 데이터 수정하기
update dept_temp2
set (dname, loc) = (select dname, loc from dept where deptno = 40)  -- 원본 데이터를 다시 넣어줌
where deptno = 40;

select * from dept_temp2;

-- delete
delete dept_temp2;
delete from dept_temp2;  -- 둘 다 가능

select * from dept_temp2;

-- 하지만 트랜잭션이 커밋이 안되었으므로 롤백 가능
rollback;

delete from dept_temp2 where deptno = 30;  -- 조건 설정도 가능

commit;

select * from employees;

-- 자체 조인
select e1.employee_id, e1.last_name, e1.manager_id,
    e2.employee_id as mgr, e2.last_name as mgr_name
from employees e1, employees e2  -- 같은 테이블에서 두번 참조
where e1.manager_id = e2.employee_id;  

-- Q. 사원번호, 사원의 full name, 부서명, 사는 도시 를 출력하시오.
SELECT * FROM locations;
SELECT * FROM departments;
SELECT * FROM employees;

-- A. 1명의 누락이 발생함
select 
    e.employee_id as "사원번호", 
    e.first_name || ' ' ||  e.last_name as "FULL NAME",
    d.department_name as "부서명", 
    l.city  as "사는 도시"
from locations l, departments d, employees e 
where 
    e.department_id = d.department_id
    and
    l.location_id = d.location_id;

-- 누락 없이 하려면 이렇게 하기    
select 
    e.employee_id as "사원번호", 
    e.first_name || ' ' ||  e.last_name as "FULL NAME",
    d.department_name as "부서명", 
    l.city  as "사는 도시"
from 
    employees e
    left join departments d on e.department_id = d.department_id
    left join locations l on d.location_id = l.location_id 
order by e.employee_id;

-- 예제2
select tname from tab;
select * from employees;

-- 1. 직업이 AD_PRESS 인 사람은 A 등급을, ST_MAN 인 사람은 B 등급을, IT_PROG 인 사람은 C 등급을,
--  SA_REP인 사람은 D 등급을, ST_CLERK 인 사람은 E 등급을, 기타는 0을 부여하여 조회한다.
--  조회할 컬럼 : FIRST_NAME, LAST_NAME, JOB_GRADE
select 
    FIRST_NAME || ' ' || LAST_NAME as "FULL_NAMe", job_id,
    decode(job_id,
        'AD_PRESS', 'A',
        'ST_MAN', 'B',
        'IT_PROG', 'C',
        'SA_REP', 'D',
        'ST_CLERK', 'E',
        0) as JOB_GRADE
from employees; 

-- 2. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총합 및 평균 연봉을 자연수로 포맷(반올림)하여 조회한다.
select max(salary) as "최대 연봉", min(salary) as "최소 연봉", sum(salary) as "연봉 총합", round(avg(salary)) as "평균 연봉"
from employees;

-- 3. 매니저로 근무하는 사원들의 총 수를 조회한다.
select count(distinct manager_id)  -- 같은 매니저를 가지고 있으면 count에서 제외하기 위하여
from employees;

-- 4. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.
select (max(salary)-min(salary)) as "연봉 차이"
from employees;

-- 5. 핸드폰 번호가 515로 시작하는 사원들의 사번, FULL_NAME, 핸드폰 번호를 이름(알파벳)순으로 조회한다.
select employee_id, first_name || ' ' || last_name as "FULL_NMAE", phone_number
from employees
where phone_number LIKE '515%'
order by "FULL_NMAE";

-- 6. 동일한 직업을 가진 사원들의 총 수를 조회한다.
select job_id, count(job_id)
from employees
group by job_id;

select * from employees;
-- 7. 부서번호가 있는 사원들 중 부서별 연봉의 총 합계를 조회한다.
--  (부서별 연봉의 합계는 10000 이상이며, 부서번호의 오름차순으로 정렬)
select department_id, sum(salary) as "부서별 연봉 총 합계"
from employees
where department_id is not null
group by department_id
having sum(salary) >= 10000
order by department_id ASC;

-- 8. 사원번호, 이름, 부서번호, 급여, 직급(job_id)을 급여별로 내림차순 조회
SELECT
    employee_id, first_name || ' ' || last_name as "FULL_NAME", department_id, salary as "급여", job_id
FROM employees
order by "급여" desc;

-- 9. 급여가 10000 이상인 사람의 이름, 급여, 부서번호 조회
SELECT
    first_name || ' ' || last_name as "FULL_NAME", department_id, salary as "급여"
FROM employees
where salary >= 10000;

-- 10. 부서에 소속되지 않은 사람의 이름, 입사일, 급여, 부서번호를 조회
SELECT
    first_name || ' ' || last_name as "FULL_NAME", hire_date as "입사일", department_id, salary as "급여"
FROM employees
where department_id is null;

-- 11. EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라
SELECT
    department_id, count(*), sum(salary)  -- count(department_id) 와 같다.
FROM employees
group by department_id
having count(*) > 4;

-- 12. EMPLOYEES 테이블에서 최고 급여가 10000 이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
select department_id, round(avg(salary)), sum(salary)
from employees
group by department_id
having max(salary) >= 10000;

-- 서브쿼리 예제
select * from employees;
-- 1. 회사 전체 평균 연봉보다 더 받는 사원들의 사번 및 LAST_NAME을 조회한다.
select employee_id, last_name
from employees
where salary > (select round(avg(salary)) from employees);

-- 2. LAST_NAME에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME을 조회한다.
select employee_id, last_name
from employees
where department_id in (select distinct department_id from employees
                        where last_name like ('%u%'));
                        
-- JOIN 예제
select *  from employees;
select *  from departments;
select * from locations;

-- 1. 사원들의 이름, 부서번호 /, 부서명을 출력하라
select e.first_name || ' ' || e.last_name as "name", e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id (+);

-- 2. 30번 부서의 사원들의 이름, 직업, / 부서명을 출력하라
select e.first_name || ' ' || e.last_name as "name", e.job_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id (+) and e.department_id = 30;

-- 3. 커미션을 받는 사원의 이름, 직업, 부서번호, / 부서명을 출력하라
select e.first_name || ' ' || e.last_name as "name", e.job_id, d.department_id ,d.department_name
from employees e, departments d
where e.department_id = d.department_id (+) and e.commission_pct is not null;

-- 4. 지역번호 2500에서 근무하는 사원의 이름, 직업, 부서번호, / 부서명을 출력하라
select e.first_name || ' ' || e.last_name as "name", e.job_id, d.department_id ,d.department_name
from employees e, departments d
where e.department_id = d.department_id (+) and d.location_id = 2500;

-- 5. 이름에 A가 들어가는 사원들의 이름과 / 부서이름을 출력하라
select e.first_name || ' ' || e.last_name as "name", d.department_name
from employees e, departments d
where e.department_id = d.department_id (+) and (e.first_name || ' ' || e.last_name) like '%A%';

-- 6. 사원이름과 그 사원의 관리자 이름을 출력하라
select e1.last_name,
    e2.employee_id as mgr, e2.last_name as mgr_name
from employees e1, employees e2  -- 같은 테이블에서 두번 참조 (자체조인)
where e1.manager_id = e2.employee_id;

-- 7. 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라
select e.first_name || ' ' || e.last_name as "name", d.department_name, e.salary
from employees e, departments d
where e.department_id = d.department_id (+) and e.salary >= 3000
order by salary desc;

-- 8 . 급여가 3000에서 5000 사이인 사원의 이름과 소속부서명을 출력하라
select e.first_name || ' ' || e.last_name as "name", d.department_name
from employees e, departments d
where e.department_id = d.department_id (+) and e.salary between 3000 and 5000
order by salary desc;

-- 9 . ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력하라
select  e.first_name || ' ' || e.last_name as "name", e.hire_date
from employees e, departments d 
where e.department_id = d.department_id (+) and e.department_id = 110;  
-- 선생님 답 : d.department_name = initcap('ACCOUNTING') : 맨 앞 글자만 대문자로 출력....

-- 10. 급여가 3000 이하인 사원의 이름과 급여, 근무지를 출력하라.
select e.first_name || ' ' || e.last_name as "name", e.salary, l.city
from employees e 
     left join departments d on e.department_id = d.department_id
     left join locations l on d.location_id = l.location_id 
where e.salary <= 3000;

-- 선생님 답
select e.first_name || ' ' || e.last_name as "name", e.salary, l.city
from employees e, departments d, locations l 
where e.department_id = d.department_id
and d.location_id = l.location_id 
and e.salary <= 3000;