Coding Summary

서브쿼리와 오라클조인

 

오라클 조인

1. left  outer 조인은 left 로 지정된 테이블1의 데이터를
   테이블2의 조인조건 일치여부와 상관없이 모두 출력한다는 의미
   
   
2. right  outer 조인은 right 로 지정된 테이블1의 데이터를
   테이블2의 조인조건 일치여부와 상관없이 모두 출력한다는 의미
   
   
3. full  outer 조인은 right outer 조인 결과와 left outer 조인 결과를 합친 결과로서
   양쪽 테이블1 과 테이블2 의 데이터를 조인조건 일치여부와 상관없이 모두 출력한다는 의미
   
   select *from emp left join on emp.deptno = dept.deptno;
   
   select count(8) 총인원수, 
   sum (decode( to_char(hiredate, 'YYYY'), 1980, 1, 0)) "1980",
   sum (decode( to_char(hiredate, 'YYYY'), 1981, 1, 0)) "1981",
   sum (decode( to_char(hiredate, 'YYYY'), 1982, 1, 0)) "1982"
   from emp;
   
   일치하면 1 아니면 0 을 반환받아 스미스부터 계산을 함
   따라서 사원의 각자 해당연도에 따라 1씩 부여받고 계산을 더함.
   
   select empno, ename, sal, dname
   from emp e join d on e.deptno = d.deptno
   where sal >= 2000 order by 3 desc;
   
   실습문제 4번
   
   select empno, ename, salm grade from
   rmp e inner join dept d on e.deptno = d.deptno
   inner join salgrade g 
   on e.sal betwwen g.losal and g.hisal
   order by 4 desc;
   
   사원테이블에서 사원명과 해당 사원의 관리자
   select e.ename , m.ename, p.ename from
   emp e left outer join emp m on e.mgr = m. empno 
   left join emp p on m.mggr = p.empno;
   
   13. select e.ename , e.sal from emp e join emp m on e.mgr = m.empno
   where e.sal > m.sal;
   
   select d.dname, count(*) 전체인원
   from emp e join dept d on e.deptno = d.deptno
   where TO_CHAR(HIREDATE, 'YYYY') < 1982 group by d.dname;
   =
   select dname, count(*) 전체인원
   from emp join dept using(deptno) where to_char(hiredate, 'yyyy') < '1982'
   group by dname;
   
   

서브쿼리 중요!!!!!!!!

서브쿼리는 여러개의 select 문을 수행해야 얻을 수 있는 결과를 하나의 slect 문으로 해결

select ename, sal from emp where sal(select sal from emp where ename ='ALLEN');

 

1. 단일행 서브쿼리
   
   서브쿼리의 실행결과가 한개의 행을 반환
   
   select ename , sal
   from emp where sal >= (select avg(sal)from emp);
   -> 평균보다 높은 월금.. 하나의 결과에 대응
   
   select ename , hiredate 
   from emp where hiredate >=
   (select hiredate from emp where ename = 'CLARK');
   ->클락씨보다 늦게 입사한 사람
   
   select empno 사원번호, ename 이름, sal 월급
   from emp where sal < (select avg(sal) from emp
   join salgrade on sal between losal and hisal where grade = 2);
   →→ 세일즈부서의 인원수보다 사원인원수가 적은 부서으 이름과 인원
   
   select dname , count() from emp e join dept d
   on e.deptno = d.deptno group by dname having
   count()< (select count(*) from emp e
   join dept d on e.deptno = d.deptno where dname = 'SALES');
   → 세일즈부서의 인원수보다 사원인원수가 적은 부서으 이름과 인원
   
   select dname loc from emp e join dept d on e.deptno = d.deptno
   where ename = 'SMITH';
   -스미스가 속한 부서명 지역 / 서브쿼리X
   
   
2. 복수행 서브쿼리
   
   동등비교 = 안쓰고in
   
   하나라도 일치라면 참 ANY
   15> 10, 20, 30 이라면 10 하나라도 15보다 작으므로 참 
   즉, 최소값
   
   ALL 은 모든값이 일치
   15> 10, 20, 30 이라면 10 하나만 돼서 거짓.
   즉. 최대값
   
   1) IN 연산자
   
   서브쿼리 반환값이 복수.
   메인쿼리와 동등연산자로 비교할 때 사용하는 연산자
   
   select ename , deptno
   from emp where deptno in(select deptno from emp where sal>= 3000);
   
   2) ALL
   
   select  ename sal from emp where sal <all
   (select sal from emp where deptno ='30');
   -> 30번 부서 모든 월급보다 작은 사원의 이름 월급
   
   select ename sal from emp where sal > all
   (select sal from emp where deptno ='30');
   -> 30번 부서 모든 월급보다 월급이 큰 사원의 이름 월급
   
   3) ANY
   
   select * from emp where sal < any
   (select sal from emp where deptno = '30');
   -> 30번 부서의 월급이 2850 이 최대인데 이보다 작은 값
   
   4) EXIST
   
   select ename , deptno , sal from emp where exists
   (select empno from emp where comm is not null);
   
   →만일 사원들 중에서 커미션을 받는 사원이한명이라도 있으면 모든 사원 정보를 출력 사원번호가 7369이면서 이름이 스미스인사람 셀렉
   
   
3. 다중컬럼 서브쿼리
   
   서브ㅝ리에서 여러개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교
   메인쿼리의 조건절은 서브쿼리의 컬럼과 일대일 매칭이 되어야
   
   select * from emp where job = 'SALESMAN' and sal = 1250;
   =
   select * from emp where (job, sal) in (('SALESMAN' , 1250'));
   
   
   select ename, deptno, sal from emp
   where (deptno, sal ) in 
   (select deptno, max(sal) from emp group by deptno);
   -> 10번부서 월급, 20번... 30번..
   
   select empno, ename, job , hiredate, sal from emp
   where job = ( select job from emp where empno = 7521)
   and sal >(select sal from emp where empno = 7934);
   
   -> EMP 테이블에서 산원번호가 7521 의 업무와 같고
   급여가 사원번호 7934보다 많은 사원의 사원번호 이름 담당업무와 입사일자 급여를 출력.
   
   
4. 인라인 뷰
   
   select select_list 
   from (서브쿼리)alis
   where 조건식;
   
   select e.deptno , 합계 , 평균, 인원수
   from (select deptno , sum(sal) 합계 , avg(sal) 평균, count(*) 인원수
   from emp group by deptno) e, dept d 
   where e.deptno = d.deptno order by 1;
   -> 그룹핑으로 부서 3개를 3개와 조인
   먼저 서브쿼리에서 부서번호를 사용하여 그룹핑을 하고 월급의 합계 평균 부서별 인원수를 구한다
   사원테이블과 부서테이블에서 부서별 월급총합과 평균 그리고 부서별 인원수 출력
   
   select ename, deptno , sal from emp
   where sal in (select max (sal) from emp group by deptno);
   => 다중컬럼사용
   
    select ename, deptno, sal from emp where (deptno ,sal)
   in ((select deptno,max(sal) from emp group by deptno ));
   => and사용
   
   select ename, deptno, sal from emp where deptno
   in (select deptno from emp group by deptno) and
   sal in (select Max(sal) from emp group by deptno);
   → 부서번호로 모으고 sal max 를 구하기
   
   select ename 사원명, deptno 부서번호, dname 부서명, sal 월급
   from emp join dept using(deptno) where (deptno, sal)
   in (select deptno, max(sal) from emp group by deptno) order by 2;
   
   서브쿼리에서 검색된 department_id와 MAX(salary)값과
   메인쿼리의 department_id와 salary 컬럼을 pairwise 방식으로 비교하여
   동시에 만족하는 경우에만 출력하는 방법이다
   
   select dname, loc from dept where deptno
   = (select deptno from emp where ename = 'SMITH');
   →smith 가 속한 부서 명, 지역 /서브쿼리 O