#DB. Join과 SubQuery 연습문제(3)

-- -----------------------------------------------------
-- 2020-10-14
-- -----------------------------------------------------

use workshop;

-- -----------------------------------------------------
-- MySQL 내장함수
-- -----------------------------------------------------
-- 날짜 함수
select DATE_ADD(NOW(), INTERVAL 1 day);
select DATE_SUB(NOW(), INTERVAL 1 day);

-- 단일행 함수
select sal, case when sal > 3000 then 'a'
		when sal> 2500 then 'b'
		else 'c'
		end as 급여
from emp;

-- 집계함수: sum, avg, max, min, count
-- 다중행 함수 -> 결과값을 한 건으로 바꾼다.
select sum(sal) from emp;

--  각 부서별 급여 합계
select distinct deptno from emp;
select sum(sal) from emp where deptno = 10;
select sum(sal) from emp where deptno = 20;
select sum(sal) from emp where deptno = 30;

-- group by절
-- 부서별 소그룹 적용
select deptno, sum(sal) '부서별 급여합' , avg(sal) '부서별 급여 평균'
from emp
group by deptno;

-- 업무별 그룹합
select job, count(job), sum(sal) '업무별 급여합' , avg(sal) '업무별 급여 평균'
from emp
group by job;

-- 부서별, 업무별 group by
select deptno, job, count(sal), sum(sal) '부서별 급여합' , avg(sal) '부서별 급여 평균'
from emp
group by deptno, job
order by deptno;

-- 부서의 급여 합이 1000원 이상인 부서 정보
select deptno, count(deptno), sum(sal)
from emp

-- where절은 group by 이전에 비교작업을 진행하기에 문제조건을 충족하지 못한다.
-- where sum(sal) > 1000
group by deptno
having sum(sal) > 1000
order by deptno;

-- -----------------------------------------------------
-- Join, SubQuery
-- -----------------------------------------------------
-- 사원의 이름, 급여 부서명
-- Cartesian Product: 모든 테이블의 곱 계산
-- 우리가 원하던 값이 아니다. 
select count(*)
from emp, dept
where emp.deptno;

-- inner join 방법 1.
select count(*)
from emp e, dept d
where e.deptno = d.deptno;

-- 방법 2.
select e.ename, e.sal, d.deptno, d.dname
from emp e join dept d
on e.deptno = d.deptno
order by d.deptno, e.ename;

-- 방법 3.
select e.ename, e.sal, d.deptno, d.dname
from emp e join dept d
using(deptno);

-- 기댓값 14개, 결과 값 13개
-- emp 멤버중 'king'은 deptno 값이 null이다.
select count(*)
from emp join dept
on emp.deptno = dept.no;

-- 문제해결을 위해 emp 테이블 값이 null이라도 emp 값은 다 나오도록
-- outer join을 실시한다.
select count(*)
from emp left join dept
on emp.deptno = dept.deptno;

-- SubQuery
-- 이름이 'smith'인 직원과 같은 부서에 근무하는 직원 조회
select *
from emp
where deptno = (select deptno
		from emp
		where ename = 'smith');
                
-- where                
-- 단일행
select *
from emp
where sal > (select sal
		from emp
		where ename = 'smith');
-- 다중행
select *
from emp
where sal > all ( select round(avg(sal))
		from emp
		group by deptno);
                
-- 다중 컬럼
select *
from emp
where (deptno, sal) = ( select deptno, sal
			from emp
			where ename = 'Smith');
                        
-- from
-- 이름, 급여, 소속 부서 급여 합
select a.ename, a.sal, b.sum
from emp a join (select deptno, sum(sal) sum
		from emp
            	group by deptno) b
on a.deptno = b.deptno;

-- column
-- scalar 서브쿼리는 결과가 무조건 한 건이여야 한다. 
select ename, sal, (select sum(sal) from emp)
from emp;


-- insert, update, delete, create
insert into test
(select max(id)+1 from test);

create table smaple
select *from test;

-- 테이블 구조만 가져오기
create table sample
select *from test
where 1=0;

-- rownum
select ename, rownum
from ( select ename, row_number() over(order by sal) rownum
	from emp) a
where rownum limit 0, 10;