6월14일 수업내용

문제 14 해설
select
job,
sum(decode(deptno,10,sal,20,0,30,0)) "DEPT 10",
sum(decode(deptno,10,0,20,sal,30,0)) "DEPT 20",
sum(decode(deptno,10,0,20,0,30,sal)) "DEPT 30",
sum(sal) "TOTAL"
from emp group by job;

SELECT
select count(*), count(decode(to_char(hiredate,'yy'),'80',job)) "1980", count(decode(to_char(hiredate,'yy'),'81',job)) "1981", count(decode(to_char(hiredate,'yy'),'82',job)) "1982", count(decode(to_char(hiredate,'yy'),'87',job)) "1987" from emp;


equi-join 1:1 대응
outer-join 조인되지 않은 데이터도 내보냄
left-outer-join 좌측을 다 내보냄
right-outer-join 우측을 다 내보냄
non-equi-join 조인범위에 맞춰 데이터를 내보냄
//78 = 70-80
self-join


select * from emp a, dept b;
56행
select * from emp a, dept b where a.deptno=b.deptno;
14행
select ename, dname, emp.deptno from emp, dept where emp.deptno=dept.deptno;
//조인
select ename, dname, e.deptno from emp e, dept d where e.deptno=d.deptno;
//알리아스를 이용(떠올리기 쉽게)
select empno, ename, job, sal, dname, loc from emp e, dept d where e.deptno=d.deptno;
//사번 이름 업무 급여 부서명 위치

select empno, ename, job, sal, dname, loc from emp e, dept d where e.deptno=d.deptno;
//사번 이름 업무 급여 부서명 위치

select empno, ename, job, sal, d.deptno, dname, loc from emp e, dept d where e.deptno(+)=d.deptno;
//(+)의 반대쪽 테이블이 다나옴 /left-outer-join
select empno, ename, job, sal, d.deptno, dname, loc from emp e, dept d where e.deptno=d.deptno(+);
//(+)의 반대쪽 테이블이 다나옴/right-outer-join

select ename, sal, grade from emp e, salgrade s where sal between losal and hisal;
//non-equi-join

select e.job||' '||e.ename "시다바리", m.job||' '||m.ename "왕초", e.sal, m.sal, m.sal-e.sal "급여차" from emp e, emp m where e.mgr=m.empno(+);

select b.job||' '||b.ename, b.sal from emp a, emp b where b.sal

01
select empno, ename, job, deptno, dname, loc from emp natural join dept;
02
select e.empno, e.ename, e.job, e.deptno, d.deptno, d.dname, d.loc from emp e join dept d on e.deptno=d.deptno;
03
select empno, ename, sal, dname, loc from emp join dept on emp.deptno=dept.deptno and lower(job)=lower('SALESMAN');
04
05
select empno, ename, job, sal, grade, losal, hisal from emp e join salgrade s on sal between losal and hisal;
06
select empno, ename, job, e.deptno, d.deptno, dname, loc from emp e right outer join dept d on e.deptno= d.deptno;
07
select worker.ename||'의 관리자는 '||manager.ename from emp worker join emp manager on worker.mgr=manager.empno;


연습문제
1.
select ename, deptno, dname from emp natual join dept;
2
select ename, job, sal, dname from emp natural join dept where upper(loc)=upper('new york');
select ename, job, sal, dname from emp e join dept d on e.deptno=d.deptno and upper(loc)=upper('new york');
3
select ename, comm, dname, loc from emp e join dept d on e.deptno=d.deptno and comm is not NULL;
4
select ename, job, dname, loc from emp natural join dept where ename like '%L%';
select ename, job, dname, loc from emp e join dept d on e.deptno=d.deptno and ename like '%L%';
5
select e.ename, e.empno, m.ename, m. empno from emp e left outer join emp m on e.mgr=m.empno;
6
select e.ename, e.hiredate, m.ename, m.hiredate from emp e join emp m on e.mgr=m.empno and e.hiredate < m.hiredate;
7
select RPAD(RPAD(ename,10,' '),10+sal/100,'*') "Employee and their salary" from emp;