[easyupclass] 초심자를 위한 데이터베이스 및 빅데이터 플랫폼 구축과정(SQL, 하둡) 9 - 13차시

-- non equi join
select ename, sal, grade
from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;

select e.ename, e.sal
from emp e join salgrade s on e.sal between s.losal and s.hisal
where s.grade = 4
order by e.sal desc;

-- outer join
select e.ename, d.loc
from dept d left join emp e on d.deptno = e.deptno;

insert into emp(empno, ename, sal, deptno)
values (7122, 'JACK', 3000, 70);

commit;

select e.ename, d.loc
from emp e left join dept d on e.deptno = d.deptno;

-- self join
select e1.ename, e1.job, e2.ename, e2.job
from emp e1 join emp e2 on e1.mgr = e2.empno
where e1.sal > e2.sal;

-- using 문법 : 조인절에 테이블 별칭 X
select e.ename, e.job, e.sal, d.loc
from emp e join dept d
using (deptno)
where d.loc='DALLAS';

-- natural join : 알아서 조인 컬럼 찾아서 조인
select e.ename, e.job, e.sal, d.loc
from emp e natural join dept d;

-- full outer join
select e.ename, e.job, e.sal, d.loc
from emp e full outer join dept d 
on e.deptno = d.deptno;

-- JACK 삭제
delete from emp
where deptno = 70;

commit;

-- union all : union할 테이블의 컬럼 갯수와 데이터 타입이 같아야 함
-- 컬럼명도 같게 하는게 좋음
--select deptno, sum(sal)
--from emp
--group by rollup(deptno)
--order by deptno;

select job, sum(sal)
from emp
group by job
union all
select to_char(null) job, sum(sal)
from emp
order by job;

-- union : 위 아래 데이터를 정렬해서 연결
--? 난 정렬 안되는디
select deptno, sum(sal)
from emp
group by deptno
union
select to_number(null) deptno, sum(sal)
from emp;

-- 교집합 : 첫번째 컬럼 기준으로 정렬
-- 하지만 난 안 됨
select ename, sal, job, deptno
from emp
where deptno in (10, 20)
intersect
select ename, sal, job, deptno
from emp
where deptno in (20, 30);

-- 차집합 : 앞 테이블에서 뒤 테이블과의 교집합 뺀 나머지
select ename, sal, job, deptno
from emp
where deptno in (10, 20)
minus
select ename, sal, job, deptno
from emp
where deptno in (20, 30);

-- 단일행 서브쿼리
-- = / !=, ^= <> / >(=) / <(=)
select ename, sal
from emp
where sal > (
    select sal
    from emp
    where ename='JONES' );

select ename, sal
from emp
where hiredate > (
    select hiredate
    from emp
    where ename = 'ALLEN' );
    
-- 다중행 서브쿼리 : return 값이 여러 개
-- in / not in / >all / <all / >any / <any
select ename, sal
from emp
where sal in (
    select sal
    from emp
    where job = 'SALESMAN' );

-- 반드시 null처리 해야 함   
select empno, ename
from emp
where empno not in 
        (select mgr
        from emp
        where mgr is not null);

-- exist와 not exist
-- exists 함수는 메인쿼리 먼저 실행 -> 서브쿼리 실행
select *
from dept
where exists (select *
            from emp
            where emp.deptno = dept.deptno);
            
-- having절 서브쿼리
select job, sum(sal)
from emp
group by job
having sum(sal) > (select sum(sal)
                    from emp
                    where job = 'SALESMAN');
                   
-- from절 서브쿼리
select *
from(
    select ename, sal
        , rank() over (order by sal desc) rnk
    from emp)
where rnk = 1;

-- select절 서브쿼리
select ename, sal
        , ( select max(sal)
        from emp
        where job = 'SALESMAN' ) max
        , ( select min(sal)
        from emp
        where job = 'SALESMAN' ) min
from emp
where job = 'SALESMAN';

-- 실행 순서 : from -> where -> group by -> having -> select -> order by
--            group by 빼고 서브쿼리 쓸 수 있음