[오라클] JOIN 함수 & SQL-99 & 서브쿼리

Natural Join :

  조인 대상이 되는 두 테이블에 이름과 자료형이 같은 열을 찾은 후 그 열을 기준으로 등가 조인을 해주는 방법

 사용하는 테이블간에 동일한 이름과 형식의 컬럼이 둘 이상인 경우 자연 조인을 사용할 수 없음

 

Join ~ using :

 using 키워드에 조인 기준으로 사용할 열을 지정할 수 있음 

 using 절에는 조인 컬럼을 기술해야 하며 괄호로 묶어 표현! 

 

Join On : 

 조인 조건 직접 명시

 

Outer Join : 조인 조건 null 데이터도 출력

Full Outer Join : Using, on 조건절을 필수적으로 사용

★★ + 없는 쪽이 기준이 된다. 짝꿍이 없으면 양쪽 다 기준이 된다. 

 

--1. 부서번호가 20이고 급여가 3000이하인 사원들의 이름,급여,부서번호를 출력

SELECT ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO=20 AND SAL<=3000;

--2. 급여가 3000이하인 사원들의 사번,이름,부서명, 부서번호 (테이블 2개 사용) 소속을 밝히면 틀림

SELECT EMPNO,ENAME,DNAME,DEPTNO FROM EMP NATURAL JOIN DEPT WHERE SAL<=3000;

--ORDER BY 추가해보기

SELECT EMPNO,ENAME,DNAME,DEPTNO    FROM EMP NATURAL JOIN DEPT    WHERE SAL<=3000 ORDER BY DEPTNO,EMPNO;

--2.2 2번과 같은 문제 (JOIN USING사용:공통속성은 속성을 밝힐 필요가 없음)

SELECT EMPNO,ENAME,DNAME,DEPTNO FROM EMP JOIN DEPT USING(DEPTNO) WHERE SAL<=3000;

--ORDER BY 추가하기

SELECT EMPNO,ENAME,DNAME,DEPTNO     FROM EMP JOIN DEPT USING(DEPTNO)     WHERE SAL<=3000     ORDER BY DEPTNO,EMPNO;

--2.3 (JOIN ON) 소속을 밝혀야 함

SELECT EMPNO,ENAME,DNAME,E.DEPTNO FROM EMP E JOIN DEPT D ON(E.DEPTNO=D.DEPTNO) WHERE SAL<=3000;

--ORDER BY 추가하기

SELECT EMPNO,ENAME,DNAME,E.DEPTNO FROM EMP E JOIN DEPT D ON(E.DEPTNO=D.DEPTNO) WHERE SAL<=3000    ORDER BY DEPTNO,EMPNO;

--2.4 WHERE절 사용, 소속 밝히기

SELECT EMPNO,ENAME,DNAME,E.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND SAL<=3000;

--ORDER BY추가하기

SELECT EMPNO,ENAME,DNAME,E.DEPTNO   FROM EMP E, DEPT D   WHERE E.DEPTNO=D.DEPTNO AND SAL<=3000   ORDER BY DEPTNO,EMPNO;

--2.5부서번호순, 사원번호 순

ORDER BY DEPTNO,DEMPNO;

--3.사원번호, 사원명, 사원의 관리자번호, 관리자의 사원번호, 관리자 이름

SELECT E.EMPNO,E.ENAME,E.MGR,M.DEPTNO,M.ENAME FROM EMP E,EMP M WHERE E.MGR=M.EMPNO;

--4.사원번호, 사원명, 사원의 관리자번호, 관리자의 사원번호, 관리자의 이름, 단 모든 사원의 정보는 다 나와야 함     (LEFT OUTER JOIN사용)

SELECT E.EMPNO,E.ENAME,E.MGR,M.EMPNO,M.ENAME FROM EMP E LEFT OUTER JOIN EMP M ON(E.MGR=M.EMPNO);

SELECT E.EMPNO,E.ENAME,E.MGR,M.EMPNO,M.ENAME FROM EMP E, EMP M WHERE E.MGR=M.EMPNO(+);

--사원번호, 사원명, 사원의 관리자번호, 관리자의 사원번호, 관리자의 이름, 단 모든 사원의 정보는 다 나와야 함   (RIGHT OUTER JOIN사용)

SELECT E.EMPNO,E.ENAME,E.MGR,M.DEPTNO,M.ENAME   FROM EMP M RIGHT OUTER JOIN EMP E ON(E.MGR=M.EMPNO);

--5. 4번 문제에서 부하사원이 없더라도 나오도록 하고 사원번호, 사원이름, 관리자 번호, 관리자의 사원번호, 관리자명 출력

SELECT E.EMPNO,E.ENAME,E.MGR,M.DEPTNO,M.ENAME    FROM EMP E RIGHT OUTER JOIN EMP M ON(E.MGR=M.EMPNO);

SELECT E.EMPNO,E.ENAME,E.MGR,M.EMPNO,M.ENAME FROM EMP E,EMP M WHERE E.MGR(+) = M.EMPNO;

--6. 모든 사원의 사원번호, 사원이름, 관리자 번호, 관지라의 사원번호, 관리자명 출력하되 관리자가 없어도 부하직원이 없어도 나오도록

SELECT E.EMPNO,E.ENAME,E.MGR,M.EMPNO,M.ENAME FROM EMP E FULL OUTER JOIN EMP M ON(E.MGR=M.EMPNO);

--7. 급여가 2000초과인 사원들의 부서번호, 부서명,사원번호,사원명, 급여 출력 WHERE 사용    (D.DEPTNO 소속밝힌 이유: 중복되는부분이DEPTNO밖에 없어서)

SELECT D.DEPTNO,DNAME,EMPNO,ENAME,SAL FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND SAL>2000;

--7.2 NATURAL JOIN 사용

SELECT DEPTNO,DNAME,EMPNO,ENAME,SAL FROM EMP E NATURAL JOIN DEPT D WHERE SAL>=2000;

--8. 부서별, 부서명별 부서번호, 부서명, 부서평균 급여(소수점이하버림) 최대급여(MAX_SAL), 최소급여(MIN_SAL) 인원수(CNT)

SELECT E.DEPTNO,DNAME,AVG(SAL) AS AVG_SAL, MAX(SAL) AS MAX_SAL, MIN(SAL) AS MIN_SAL, COUNT(*) AS CNT    FROM EMP E, DEPT D   WHERE E.DEPTNO=D.DEPTNO   GROUP BY E.DEPTNO,DNAME;

--9. 8번을 JOIN USING 으로 변경(FROM에 들어가)

SELECT DEPTNO, DNAME, TRUNC(AVG(SAL)) AS AVG_SAL, MAX(SAL) AS MAX_SAL, MIN(SAL) AS MIN_SAL, COUNT(*) AS    CNT    FROM EMP E JOIN DEPT D  USING(DEPTNO)   GROUP BY DEPTNO,DNAME;

--10. 부서번호, 부서명, 사원번호, 사원명, 사원의 직무, 급여를 출력하되 모든 부서의 정보는 다 나오도록 즉 사원이 없는       부서도 포함 부서번호순, 사원명순 WHERE절 이용

SELECT D.DEPTNO,DNAME,EMPNO,ENAME,JOB,SAL    FROM EMP E,DEPT D    WHERE E.DEPTNO(+)=D.DEPTNO    ORDER BY D.DEPTNO,E.ENAME;

--10.2 RIGHT OUTER이용

SELECT D.DEPTNO,DNAME,EMPNO,ENAME,JOB,SAL   FROM EMP E RIGHT OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO)   ORDER BY D.DEPTNO,E.ENAME;

--11. 사원번호, 사원명, 급여, 최저급여, 최고급여, 급여등급

SELECT EMPNO,ENAME,SAL,LOSAL,HISAL,GRADE   FROM EMP,SALGRADE   WHERE SAL BETWEEN LOSAL AND HISAL;

--12.(EMP E,DEPT,SALGRADE, EMP M:4개의 테이블이 필요)     부서테이블의 부서번호, 부서테이블의 부서명, 사원번호,사원명, 관리자번호,사원의 급여, 사원의 부서번호    동급 최저급여, 동급 최고급여, 급여등급, 관리자의 사원번호(MGR_EMPNO), 관리자이름(MGR_ENAME)단 모든 부서    포함, 관리자가 없는 포함부서 테이블의 부서번호순, 사원번호순

SELECT D.DEPTNO,D.DNAME,E.EMPNO,E.ENAME,E.MGR,E.SAL,E.DEPTNO,S.LOSAL,S.HISAL,S.GRADE, M.EMPNO AS MGR_E     MPNO, M.ENAME AS MGR_ENAME    FROM EMP E,DEPT D,SALGRADE S, EMP M     WHERE E.DEPTNO(+)=D.DEPTNO AND E.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+) AND E.MGR=M.EMPNO(+)    ORDER BY D.DEPTNO,E.EMPNO;

--SQL-99방식으로

SELECT D.DEPTNO,D.DNAME,E.EMPNO,E.ENAME,E.MGR,E.SAL,E.DEPTNO,S.LOSAL,S.HISAL,S.GRADE,  M.EMPNO AS MGR_EMPNO, M.ENAME AS MGR_ENAME FROM EMP E RIGHT OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO)  LEFT OUTER JOIN SALGRADE S ON(E.SAL BETWEEN S.LOSAL AND S.HISAL) LEFT OUTER JOIN EMP M ON(E.MGR =M.EMPNO) ORDER BY D.DEPTNO,E.EMPNO;

서브쿼리란?

▶ 단일행 서브쿼리 : 실행 결과가 단 하나의 행으로 나오는 서브쿼리

단일행 연산자 사용

▶ 다중행 서브쿼리 : 실행결과가 여러 개의 행으로 나오는 서브쿼리

다중행 연산자 사용

▶ 다중열 서브쿼리 : 서브쿼리에 여러 열 지정

  메일쿼리의 비교 열은 동일한 개수, 동일한 자료형을 괄호로 묶어서 지정

 

--13.사원명이 JONES인 사원의 급여

SELECT SAL FROM EMP WHERE ENAME='JONES';

--14 급여가 2975 초과인 사원의 정보

SELECT * FROM EMP WHERE SAL>2975;

--15.JONES인 사원의 급여보다 더 많이 받는 사원의 정보 SUB QUERY 사용

SELECT * FROM EMP WHERE SAL  >(SELECT SAL FROM EMP WHERE ENAME='JONES');

--16.SCOTT의 입사일

SELECT HIREDATE FROM EMP WHERE ENAME='SCOTT';

--17.SCOTT의 입사일 이전에 입사한 사원의 정보

SELECT * FROM EMP WHERE HIREDATE <(SELECT HIREDATE FROM EMP WHERE ENAME='SCOTT');

--18.SCOTT의 급여보다 더 적게 받는 사원의 정보

SELECT * FROM EMP WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME='SCOTT');