join, 서브 쿼리 정리 및 예시

 

JOIN

 

-EMP 테이블의 모든 사원들의 이름,부서번호,부서명을 출력해 보세요.

SQL>SELECT ename, emp.deptno, dname

FROM emp,dept

WHERE emp.deptno = dept.deptno ;

 

- 급여가 3000 에서 5000 사이의 사원이름과 부서명을 출력해보세요.

SQL>SELECT ename, dname

FROM emp,dept

WHERE emp.deptno = dept.deptno

AND sal BETWEEN 3000 AND 5000 ;

 

SQL>SELECT ename, dname

FROM emp,dept

WHERE emp.deptno = dept.deptno

AND ( sal >= 3000 AND sal <= 5000 ) ;

 

- 부서명이 'ACCOUNTNG' 인 사원의 이름,입사일,부서번호,부서명을 출력해보세요.

SQL>SELECT ename, hiredate, emp.deptno, dname

FROM emp , dept

WHERE emp.deptno = dept.deptno

AND dname = 'ACCOUNTING' ;

 

테이블에 별칭(alias) 사용 표현식

 

- 부서명이 'ACCOUNTNG' 인 사원의 이름,입사일,부서번호,부서명을 출력해보세요.

SQL>SELECT ename, hiredate, e.deptno, dname

FROM emp e, dept d

WHERE e.deptno = d.deptno

AND dname = 'ACCOUNTING' ;

 

ANSI JOIN 표현식 

 

- 부서명이 'ACCOUNTNG' 인 사원의 이름,입사일,부서번호,부서명을 출력해보세요.

SQL>SELECT ename, hiredate, emp.deptno, dname

FROM emp

INNER JOIN dept ON emp.deptno = dept.deptno

WHERE dname = 'ACCOUNTING' ;

 

ANSI JOIN 표현식 USING 사용

 

- 부서명이 'ACCOUNTNG' 인 사원의 이름,입사일,부서번호,부서명을 출력해보세요.

SQL>SELECT ename, hiredate, deptno, dname

FROM emp

INNER JOIN dept USING(deptno)

WHERE dname = 'ACCOUNTING' ;

 

SELECT ename, deptno, dname, loc
FROM emp

INNER JOIN dept  USING(deptno)

ORDER BY ename ASC

 

SELF JOIN

참조해야할 칼럼이 자신의 테이블에 있는 경우에 사용하는 JOIN 방법

 

SELECT e1.empno, e1.ename, e1.mgr, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno

 

OUTER JOIN

 

SELECT ename, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno(+) = dept.deptno

 

RIGHT OUTER JOIN

 

SELECT ename, deptno, dname
FROM emp
RIGHT OUTER JOIN dept USING(deptno)

 

LEFT OUTER JOIN

 

SELECT deptno, dname, ename
FROM dept
LEFT OUTER JOIN emp USING(deptno)

 

 

서브 쿼리

하나의 SQL 문장절에 포함된 또다른 SELECT 문장, 따라서 두번 질의를 해야 얻을수 있는 결과를 한번의 질의로 해결이 가능하게 하는 쿼리

 

단일행 서브쿼리

서브쿼리의 실행결과가 하나의 칼럼과 하나의 행만을 리턴해주는 쿼리 (하나의 데이터만 리턴해주는 쿼리)

 

1. 'SMITH' 가 근무하는 부서명을 서브쿼리를 이용해서 출력해 보세요.

SELECT dname

FROM dept

WHERE deptno = (SELECT deptno FROM emp WHERE ename='SMITH');

 

2. 'ALLEN' 과 같은 부서에서 근무하는 사원의 이름과 부서의 번호를 출력해 보세요.

SELECT ename,deptno

FROM emp

WHERE deptno = (SELECT deptno FROM emp WHERE ename='ALLEN');

 

3. 'ALLEN' 과 동일한 직책(job) 을 가진 사원의 사번과 이름, 직책을 출력해 보세요.

SELECT empno, ename, job

FROM emp

WHERE job = (SELECT job FROM emp WHERE ename='ALLEN');

 

4. 'ALLEN' 의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여를 출력해 보세요. 

SELECT ename, sal

FROM emp

WHERE sal >= (SELECT sal FROM emp WHERE ename='ALLEN');

 

5. 'DALLAS' 에서 근무하는 사원의 이름, 부서번호를 출력해보세요.

SELECT ename, deptno

FROM emp

WHERE deptno = (SELECT deptno FROM dept WHERE loc='DALLAS');

 

6. 'SALES' 부서에서 근무하는 모든 사원의 이름과 급여를 출력해보세요.

SELECT ename, sal

FROM emp

WHERE deptno = (SELECT deptno FROM dept WHERE dname='SALES');

 

7. 자신의 직속 상관이 'KING' 인 사원의 이름과 급여를 출력해 보세요.

SELECT ename,sal

FROM emp

WHERE mgr = (SELECT empno FROM emp WHERE ename='KING');

 

다중행 서브쿼리

서브쿼리의 실행결과가 하나의 칼럼과 여러개의 행을 리턴해주는 쿼리 (여러개의 데이터만 리턴해주는 쿼리)

다중행 서브쿼리의 결과값을 조건절에서 사용할때는 반드시 다중행 연산자와 함께 사용해야한다. ( IN, ALL, ANY, EXIST) 

 

1. 급여를 3000 이상받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 이름과 급여, 부서번호를 출력해 보세요.

SELECT ename,sal,deptno

FROM emp

WHERE deptno IN(SELECT deptno FROM emp WHERE sal>=3000);

 

2. IN 연산자를 이용하여 부서별로 가장 급여를 많이 받는 사원의 사원번호, 급 여, 부서번호를 출력해보세요.

SELECT empno, sal, deptno

FROM emp

WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY deptno);

 

3. 직책이 MANAGER 인 사원이 속한 부서의 부서번호와 부서명과 부서의 위치를 출력해보세요.

SELECT deptno, dname, loc

FROM dept

WHERE deptno IN(SELECT deptno FROM emp WHERE job='MANAGER');

 

4. 30번 부서의 사원중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력해보세요.

 

- 단일 행 서브쿼리 -

SELECT ename,sal

FROM emp

WHERE sal > (SELECT MAX(sal) FROM emp GROUP BY deptno HAVING deptno=30);

 

- 다중 행 서브 쿼리 -

SELECT ename,sal

FROM emp

WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=30);

 

5. 직책이 'SALESMAN' 보다 급여를 많이 받는 사원들의 이름과 급여를 출력하라. (ANY 연산자 이용)

SELECT ename,sal

FROM emp

WHERE sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN');

 

6. 부서번호가 30번인 사원들의 급여중 최저 급여보다 높은 급여를 받는 사원의 이름, 급여를 출력해보세요.

 

- 단일행 서브 쿼리 -

SELECT ename,sal

FROM emp

WHERE sal > (SELECT MIN(sal) FROM emp GROUP BY deptno HAVING deptno=30);

 

- 다중행 서브 쿼리 - (ANY 연산자 사용)

SELECT ename,sal

FROM emp

WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=30);

 

7. 직책이 'SALESMAN' 인 사원의 최소 급여보다 많이 받는 사원들의 이름과 급여, 직책을 출력하되 'SALESMAN' 은 출력하지 않습니다. (ANY 연산자를 사용하세요)

SELECT ename, sal, job

FROM emp

WHERE sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN')

AND job != 'SALESMAN' ;

 

8. SMITH 와 동일한 직책을 가진 사원의 이름과 직책을 출력하세요

SELECT ename, job

FROM emp

WHERE job = (SELECT job FROM emp WHERE ename='SMITH');

 

9. 직책이 'SALESMAN' 인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들 의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다. (ALL 연산자 이용)

SELECT ename,sal

FROM emp

WHERE sal > ALL(SELECT sal FROM emp WHERE job='SALESMAN')

AND deptno != 20;

 

10. 직책이 'SALESMAN' 인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원 들의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다. (ANY 연산자 이용)

SELECT ename,sal

FROM emp

WHERE sal > ANY (SELECT sal FROM emp WHERE job='SALESMAN')

AND deptno != 20;