SQL JOIN , 서브쿼리

조인

• 오라클 조인 문법

1. Equi join : 조인 조건이 = 인 경우
2. Non equi join : 조인 조건이 = 아닌 경우
3. Outer join
4. Self join

• 1999 ansi 조인 문법

1. On 절을 사용한 조인
2. Using 절을 사용한 조인
3. Natural 조인
4. Left/ right/ full outer join
5. Cross join

---

• Outer join

   : equi join으로는 볼수 없는 결과를 볼 때 사용하는 조인 문법

 

Ex) select * from dept;

 

Ex) 이름과 부서위치 출력

 select e.ename, d.loc

 from emp e, dept d

 where e.deptno=d.deptno;

보스턴 출력이 안 된 이유

-> emp 테이블에 40번 부서번호를 갖는 사원이 없음, 데이터가 덜 나오는 쪽에 (+)= 을 쓴다.

select e.ename, d.loc

from emp e, dept d

where e.deptno (+)= d.deptno;

---

• Self 조인 : 자기 자신의 테이블과 조인하는 조인 문법

self join이 필요한 이유

: 관리자보다 먼저 입사한 사원 출력, 관리자의 월급과 그 사원의 월급 같이 출력

 

사원 테이블 조직도 출력

-> select empno, ename, mgr from emp;

Ex) 사원이름, 관리자의 이름 출력

select e.ename 사원, m.ename 관리자

from emp e, emp m

where e.mgr = m.empno;

---

• On절을 사용한 조인 문법

Ex) select e.ename, d.loc from emp e join dept d on (e.deptno = d.deptno)

Where d.loc = 'DALLAS';

@ 위치 => ' ,' -> join // where -> on(조인 조건)

 

• Using 절을 사용한 조인

Ex) select e.ename, d.loc from emp e join dept d using (deptno);

using으로 테이블 조인 조건 선언

 

• Natural join

Ex) select e.ename, d.loc from emp e natural join dept d;

 

---

• Left / right / full outer join

Ex)  오라클 조인 문법 vs 1999 ansi 조인 문법

@oracle join

Select e.ename, d.loc

from emp e, dept d

where e.deptno(+)= d.deptno;

 

@ansi - right outer join

Select e.ename, d.loc

from emp e right outer join dept d

on (e.deptno = d.deptno);

 

@oracle join

Select e.ename, d.loc

from emp e, dept d

where e.deptno= d.deptno(+);

 

@ansi - left outer join

Select e.ename, d.loc

from emp e left outer join dept d

on (e.deptno = d.deptno);

 

Ansi - full outer join

Select e.ename, d.loc

from emp e full outer join dept d

on (e.deptno = d.deptno);

 

• Cross join

Equi join에 조인 조건이 없는 조인의 결과와 같은 조인

 

Ex) select e.ename, d.loc from emp e, dept d;

전부 다 조인해서 60개 행 출력된다.

 

Select e.ename, d.loc from emp e cross join dept d;

---

• 서브쿼리문

다음과 같은 질문에 답하려면 서브쿼리문을 알아야한다.

• 서울시 물가 데이터에서 가장 비싼 물품이 무엇인가?

-> 쉽게 이해하자면 조건을 만족하기 위해 조건에 해당되는 서브 쿼리를 작성하는 것이다.

 

Ex) Jones의 월급보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력

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

 메인 쿼리 문                                       서브쿼리문

 

• 서브쿼리문 종류 3가지

1. 단일행 서브쿼리 : 서브쿼리에서 메인쿼리로 하나의 값 리턴되는 문장
2. 다중행 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 문장

             연산자 : in, not in, >all, <all, >any, <any

 

 3. 다중 컬럼 서브쿼리 : 서브쿼리에서 메인쿼리로

                            여러개의 컬럼값이 리턴되는 문장

             연산자 : in, not in

---

- Outer Join 문제

 

문제 194. 부서위치, 부서위치 별 토탈 월급 출력하세요

-> select d.loc, sum(sal)

   from emp e, dept d

   where e.deptno (+)= d.deptno group by d.loc;

 

문제 195. 아래 결과에서 null 나오는 boston의 토탈 월급이 0으로 출력되게 하세요

-> select d.loc, nvl(sum(e.sal),0)

    from emp e, dept d

    where e.deptno(+)=d.deptno

    group by d.loc;

문제 196. 위의 문제에서 금액 단위 부여하세요

-> select d.loc, to_char(nvl(sum(e.sal),0),'$999,999')

    from emp e, dept d

    where e.deptno(+) = d.deptno

    group by d.loc;

 

추가 데이터 삽입

-> insert into emp(empno, ename, sal, job, deptno) values (7881,'JACK',3200,'ANALYST',70);

    commit;

 

문제 197. 직업, 직업별 최대월급, 직업별 최소월급, 직업별 평균 월급을 출력

-> select job, max(sal), min(sal), avg(sal) from emp group by job;

 

문제 199. 직업, 이름, 월급, 월급에 대한 순위를 출력, 월급에 대한 순위가 직업별로 각각 파티션해서 출력되게 하세요

-> select e.ename, e.job, e.sal, dense_rank() over(partition by d.loc order by e.sal desc)  from emp e, dept d;

 

문제 200. 부서위치, 이름, 월급, 월급에 대한 순위를 출력, 월급에 대한 순위가

부서위치별로 각각 파티션해서 출력되게 하시오

-> select d.loc, e.ename, e.sal, dense_rank() over(partition by d.loc order by sal desc) from emp e, dept d;

 

문제 201. 부서위치, 부서위치별 토탈 월급을 출력하는데 가로로 출력

-> select * from 

    (select d.loc as location, e.sal from emp e, dept d where e.deptno(+)= d.deptno)

    pivot(sum(sal) for location in ('NEW YORK','DALLAS','CHICAGO','BOSTON'));

 

   

   select sum(decode(d.loc, 'NEW YORK', sal, null)) as "NEW YORK",

          sum(decode(d.loc, 'DALLAS', sal, null)) as "DALLAS",

          sum(decode(d.loc, 'CHICAGO', sal, null)) as "CHICAGO",

          sum(decode(d.loc, 'BOSTON', sal, null)) as "BOSTON"

    from emp e, dept d

    where e.deptno(+)= d.deptno;

 

 

 

- Self Join 문제

 

문제 202. 위의 결과를 다시 출력하는 데, 사원이름, 사원의 월급, 관리자 이름, 관리자 월급 출력

-> select e.ename, e.sal, m.ename, m.sal from emp e, emp m

    where e.mgr = m.empno;

 

문제 203. 위의 결과에서 관리자보다 월급이 많은 사원

-> select e.ename, e.sal, m.ename, m.sal from emp e, emp m

    where e.mgr = m.empno and e.sal > m.sal;

 

문제 204. 관리자보다 먼저 입사한 사원들의 이름과 입사일을 출력

-> select e.ename, e.hiredate, m.ename, m.hiredate from emp e, emp m

    where e.mgr = m.empno and e.hiredate<m.hiredate;

문제 205. 관리자 이름, 해당 관라자에 속한 사원들의 이름을 가로로 출력

-> select m.ename, listagg(e.ename,',') within group (order by e.ename) 사원

     from emp e, emp m

     where e.mgr = m.empno

     group by m.ename;

 

문제 206. 관리자보다 먼저 입사했는데 월급이 적은 사원들이 퇴사할 가능성이 높다고 가정,

             이 사원들이 누구인지 이름과 월급과 입사일 출력하세요

-> select e.ename, e.hiredate from emp e, emp m where e.mgr = m.empno and e.hiredate<m.hiredate;

 

문제 207. 이름, 입사일, 월급, retire_exp라는 컬럼 이름을 출력

             retire_exp 가 관리자보다 먼저 입사했는데

             월급이 작은 사원들은 1로  출력 나머지 사원들은 0

 

-> select e.ename, e.hiredate, e.sal,

   case when e.hiredate<m.hiredate then 1 else 0 end as retire_exp

    from emp e, emp m

    where e.mgr = m.empno;

 

 

 

- on 절 사용 문제

문제 208. 급여등급이 5등급인 사원들의 이름과 월급과 급여등급 출력

-> select e.ename, s.grade
    from emp e join salgrade s on (e.sal between s.losal and s.hisal)
    where s.grade= 5;

 

문제 210. 직업이 SALESMAN, ANALYST인 사원들의 이름과 직업과 부서위치를 출력한다.

             on절을 이용한 조인 문법으로 구현하시오

-> select e.ename, e.job, d.loc from emp e join dept d on (e.deptno = d.deptno)

    where e.job in ('SALESMAN', 'ANALYST');

- using 절 사용 문제

 

문제 211. 직업이 salesman인 사원들의 이름과 월급과 직업과 부서위치를 using 절을 사용해서 출력

-> select e.ename, e.sal, e.job, d.loc from emp e join dept d using (deptno)

 

- Left, right, outer join 사용 문제

문제 212. 직업이 salesman이고 월급이 1200이상인 사원들의 이름과 월급과 직업과 부서위치 출력

             on 절 사용한 조인 문법으로 수행

-> select e.ename, e.sal, e.job, d.loc from emp e join dept d on (e.deptno = d.deptno) where e.sal>=1200 and

    job = 'SALESMAN';

 

-서브 쿼리문 문제

문제 213. scott과 같은 월급을 받는 사원들의 이름과 월급 출력, scott 제외하고 출력

-> select ename, sal from emp where sal = (select sal from emp where ename = 'SCOTT') and ename != 'SCOTT';

 

문제 214. ALLEN보다 늦게 입사한 사원들의 이름과 입사일을 출력하세요

-> select ename, hiredate
    from emp
    where hiredate > (select hiredate from emp where ename = 'ALLEN');

문제 215. 직업이 salesman인 사원들 중에서의 최대월급보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하세요

-> select ename, sal from emp where sal> (

 select max(sal) from emp where job = 'SALESMAN');

문제 216. 최대월급을 받는 사람의 이름과 월급을 출력

-> select ename, sal from emp where sal= (

 select max(sal) from emp);

 

문제 217. 서울시 물가 데이터에서 가장 비싼 물품과 가격을 출력

-> Select m_name, A_Name, A_PRICE

    from price

    where A_PRICE = (select max(a_price) from price);

 

문제 219. 20번 부서번호에서 근무하는 사원들 중에서 최대월급보다 더 많은 월급을 받는 사원들의 이름과 월급

-> select ename, sal from emp where sal > (select max(sal) from emp where deptno =20);

문제 220. dallas에 있는 부서번호에서 근무하는 사원들의 이름과 월급

Select ename, sal from emp where deptno = (select deptno from dept where loc = 'DALLAS');

 

문제 221. king에게 보고하는 사원들의 이름 출력 (KING의 직속부하 사원 출력)

-> select e.ename from emp e join emp m on (m.empno = e.mgr) where e.deptno = (select empno from emp where ename = 'KING);

 

문제 222. 직업이 salesman인 사원들과 월급이 같은 사원들의 이름과 월급

Select ename, sal

from emp

where sal in (select sal from emp where job='SALESMAN');

 

문제 223. 직업이 SALESMAN인 사원들과 월급이 같지 않은 사원들의 이름과 월급과 직업을 출력하세요

Select ename, job from emp where sal not in (select sal from emp where job='SALESMAN');

 

문제 225. 관리자인 사원들의 이름을 출력하세요

(자기 밑에 직속 부하가 한명이라도 있는 사원들)

select ename

from emp

where empno in (select mgr from emp );

 

문제 226. 관리자가 아닌 사원들의 이름 출력

select ename from emp where empno not in (select nvl(mgr,-1) from emp)

@ null값 존재시 값 조회가 안된다.

@ not in은 and로 연결되있다.

@ 서브쿼리문에서 not in 사용시 반드시 null 값 처리 해주어야한다.

 

문제 227. 우리나라에서 대학등록금이 가장 비싼 학교는 어디인가?

select tuition,university

    from univ

     where tuition = (select max(tuition) from univ);

 

 

문제 229. 가정불화로 인해 생기는 가장 큰 범죄유형이 무엇인지 1위부터 3위

 

select * from(

select crime_type, cnt, rank() over(order by cnt desc) 등급

from crime_cause2

where term='가정불화'

and cnt in (select cnt from crime_cause2 where term='가정불화')

)where 등급 <=3;

 

 

select crime_type, 순위

    from ( select crime_type, 가정불화,

                rank() over (order by 가정불화 desc) as 순위

        from crime_cause

        )

 

    where 순위<=3;