[SQL] 데이터 조인 - OUTER JOIN

문제1. dept 테이블의 모든 컬럼과 데이터를 출력하시오

select * from dept;

dept 테이블에 있는 deptno가 40번인 컬럼이
emp 테이블에는 없음

---

문제2. EMP 테이블과 DEPT 테이블을 조인해서 이름과 부서위치를 출력하시오

emp 테이블에 deptno가 40번인 컬럼이 없기 때문에
deptno가 40번인 loc가 BOSTON인 컬럼이 출력되지 않음

select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno;

답:

select e.ename, d.loc
 from emp e, dept d
 where e.deptno(+) = d.deptno;

💡 OUTER JOIN 싸인(+)은 데이터가 모자라게 나오는 쪽에 붙여줌
ㄴ 데이터가 없는 쪽에 붙여줌

---

문제4. 아래의 데이터를 사원 테이블에 입력하시오

insert into emp(empno, ename, sal, deptno)
  values(3921, 'JACK', 3400, 70);
  
commit;

---

문제5. emp 테이블과 dept 테이블을 조인해서 이름과 부서위치를 출력하는데 JACK 도 나오게 하시오

select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno(+);

---

문제6. 부서위치, 부서위치별 토탈월급을 출력하시오

select d.loc, sum(e.sal)
 from emp e, dept d
 where e.deptno = d.deptno
 group by d.loc;

---

문제7. 위의 결과를 다시 출력하는데 BOSTON도 같이 나오게 하시오

select d.loc, sum(e.sal)
 from emp e, dept d
 where e.deptno(+) = d.deptno
 group by d.loc;

---

문제8. 현업에서 사용하는 테이블 형태를 구성하시오

cmd에 붙여넣기

@demo
alter table emp add emp_kind varchar2(1) default 1 not null;

update emp set emp_kind = case when mod(empno, 2) = 1 then 1 else 2 end;

create table emp_kind1 as
select empno, ename, sal + 200 as office_sal
from emp where emp_kind = '1';

create table emp_kind2 as
select empno, ename, sal + 200 as sal
from emp where emp_kind = '2';

alter table emp_kind1 add constraint pk_emp_kind1 primary key(empno);
alter table emp_kind2 add constraint pk_emp_kind2 primary key(empno);

alter table emp drop column sal;

select * from emp;
select * from emp_kind1;
select * from emp_kind2;

문제9. emp 와 emp_kind1 과 emp_kind2 를 조인해서 아래의 결과를 출력하시오

테이블 3개 outer join

select e.empno, e.ename, ek1.office_sal, ek2.sal
 from emp e, emp_kind1 ek1, emp_kind2 ek2
 where e.empno = ek1.empno(+)
  and e.empno = ek2.empno(+);

---

복습문제1. 부서번호, 부서번호별 토탈월급을 세로로 출력하시오

select deptno, sum(sal)
 from emp
 group by deptno;

복습문제2. 위의 결과를 다시 출력하는데 맨 아래쪽에 전체 토탈월급이 출력되게하시오

select deptno, sum(sal)
 from emp
 group by rollup(deptno);

복습문제3. 부서위치, 부서위치별 토탈월급을 출력하는데 맨 아래쪽에 전체토탈월급을 출력하시오

select d.loc, sum(e.sal)
 from emp e, dept d
 where e.deptno = d.deptno
 group by rollup(d.loc);

복습문제4. 부서위치, 부서위치별로 속한 사원들의 이름이 가로로 출력되게 하시오

select d.loc, listagg(e.ename,',') within group (order by e.ename asc) 
 from emp e, dept  d
 where e.deptno = d.deptno
 group by d.loc;