Oracle Join / SubQuery

★중요 !  1. Join 의 종류 6가지를 적고 각각 어떤 역할을 하는지 적으시오

 

- INNER JOIN : 외래키로 이어진 테이블들의 다른 컬럼값을 조회할때

 

 

- LEFT OUTER JOIN : 외래키로 이어진 테이블들 중 FROM 절에 있는 테이블의 외래키 컬럼값이 NULL이라도 조회할 경우

 

- RIGHT OUTER JOIN : 외래키로 이어진 테이블들 중 RIGHT JOIN 절에 있는 테이블의 외래키 컬럼값이 NULL이라도 조회할 경우

 

 

 

- OUTER JOIN : INNER JOIN과 반대

 

 

- CROSS JOIN : 공통된 컬럼의 값이 일치하든 안하든 각각의 테이블의 컬럼값을 조회하고 싶을 때

 

 

- SELF JOIN : 하나의 테이블에서 부모, 자식 구조로 컬럼값을 조회하고 싶을 때

 

 

★중요 !  2. Join 의 종류 6가지를 어떤 문법으로 사용하는지 적으시오

 

- INNER JOIN 

SELECT

테이블1.컬럼, 테이블2.컬럼

FROM

테이블1

INNER JOIN 

테이블2

ON

테이블1.공통컬럼 = 테이블2.공통컬럼

 

- LEFT OUTER 

SELECT

테이블1.컬럼, 테이블2.컬럼

FROM

테이블1

LEFT OUTER JOIN

테이블2

ON

테이블1.공통컬럼 = 테이블2.공통컬럼

 

 

- RIGHT OUTER

SELECT

테이블1.컬럼, 테이블2.컬럼

FROM

테이블1

RIGHT OUTER JOIN

테이블2

ON

테이블1.공통컬럼 = 테이블2.공통컬럼

 

- OUTER JOIN

SELECT

테이블1.컬럼, 테이블2.컬럼

FROM

테이블1

OUTER JOIN

테이블2

ON

테이블1.공통컬럼 <> 테이블2.공통컬럼

 

- CROSS JOIN

SELECT

테이블1.컬럼, 테이블2.컬럼

FROM

테이블1

CROSS JOIN

테이블2

 

 

- SELF JOIN

SELECT

테이블1.컬럼1, 테이블2.컬럼3

FROM

테이블 as 테이블1

SELF JOIN

테이블 as 테이블2

ON 

테이블1.컬럼1 = 테이블2.컬럼2

 

★중요 !  3. SELECT  (   )  FROM   (   )   WHERE  (   ) 

각 ( )위치에 해당하는 서브쿼리명칭과 역할, 결과를 적으시오.

( 보기 -  인라인 뷰,  스칼라 서브쿼리, 서브쿼리 )

 

- SELECT(스칼라 서브쿼리) : 서브쿼리의 결과를 하나의 컬럼처럼 사용하고 싶을 때 

- FROM(인라인 뷰) : 뷰테이블처럼 결과가 동적으로 생성된 테이블로 사용

- WHERE(서브쿼리) : 서브쿼리의 결과로 조건을 줘서 데이터를 조회하고 싶을 때

 

 

★중요 !  4. 단일행 서브쿼리와 다중 행 서브쿼리 비교연산자를 각각 적으시오.

 

- 단일행 서브쿼리 : =, <>, < , <=, >, >=

- 다중행 서브쿼리 : IN, ANY, ALL, EXISTS

 

★중요 !  5. 다중행 비교 연산자 각각의 설명과 사용법을 적어주세요

 

- IN : 하나의 컬럼이 여러개의 "=" 조건을 가지는 경우

SELECT

ename, sal, deptno

FROM

emp

WHERE

sal IN (select MIN(sal) from emp group by deptno)

 

- ANY : 여러 결과값 중 하나의 값만 만족하면 행 반환

select sal from emp where job = 'SALESMAN'

 

 

SELECT

*

FROM

emp

WHERE

sal > ANY (select sal from emp where job = 'SALESMAN')

 

 

설명)

SELECT

*

FROM

emp

WHERE

sal > ANY (select sal from emp where job = 'SALESMAN')

 

이거는

 

SELECT

*

FROM

emp

WHERE

sal > ANY (1600.00, 1250.00, 2850.00, 1500.00);

 

이거랑 같은 것

그러므로

SELECT

*

FROM

emp

WHERE

sal > 1600.00 OR
sal > 1250.00 OR

sal > 2850.00 OR

sal > 1500.00

이런 뜻이다.

 

 

 

 

 

- ALL : 서브쿼리 결과값 중 모든 결과를 만족하는 행을 반환

 

select *
from emp
where sal > all (select sal from emp where job="SALESMAN")

 

 

설명)

select *
from emp
where sal > all (select sal from emp where job="SALESMAN")

 

위 쿼리문은

 

select *
from emp
where sal > all (1600.00, 1250.00, 2850.00, 1500.00)

 

이러므로

결국

 

 

select *
from emp

where

sal > 1600.00 AND

sal > 1250.00 AND

sal > 2850.00 AND

sal > 1500.00

 

이랑 같은 뜻이다.

 

 

 

 

- EXISTS : 서브쿼리의 값이 있을 경우 메인쿼리 수행

select job from emp where job="DEV"

 

해당 쿼리의 결과값은 존재하지 않는다.

 

select *
from emp
where
 exists (select job from emp where job="DEV")

 

 

 

그러므로 메인쿼리는 실행되지 않는다.

 

 

★중요 !  6. 3개 종류의 서브쿼리 예제를 각각 한개씩 작성하고 결과를 보여주세요.

 

- 인라인 뷰

select dno.*
from (select * from emp where deptno=10) as dno

 

 

 

- 스칼라 서브쿼리

select empno, (select dname from dept d where d.deptno = e.deptno) dname, job
from emp e
where e.job="MANAGER"

 

 

 

- 중첩 서브쿼리

select *
from emp
where sal > (select sal from emp where ename="ALLEN")

 

 

 

★★중요7 . 다음 결과를 도출하도록 쿼리를 작성해서 제출하고 결과를 캡쳐해서 올려주세요.

 

-    부서별로 평균 임금보다 많이 받는 사원을 구하세요.     -

 

select
 empno,
 ename,
    sal as '급여',
 (select avg(sal) from emp e2 where e1.deptno=e2.deptno group by deptno) as "평균",
    deptno
from
 emp e1
where
 sal > ANY (
  select avg(sal) from emp e2 where e1.deptno=e2.deptno group by deptno
  )
order by
 deptno