[오라클] JOIN 함수

 

조인함수란
: 한 개 이상의 테이블에서 데이터를 조회

 

복습 

--1. 부서별 급여 총계와 평균을 구하라
select deptno, sum(sal), trunc(avg(sal)) from emp group by deptno order by deptno;

--2.HIREDATE의 자료형 확인 -> DATE
DESC EMP;
--3.입사년도 출력
select to_char(hiredate,'yyyy') as 입사년도 from emp;

--4. 입사 년도별 인원수
select TO_CHAR(HIREDATE, 'YYYY') as 입사년도 , COUNT(*) as 인원수
from emp 
GROUP BY TO_CHAR(HIREDATE,'YYYY');

--5.직무별 인원수, 급여 평균
SELECT JOB,COUNT(*) AS 인원수 ,ROUND(AVG(SAL)) AS 급여평균 FROM EMP GROUP BY JOB;

--6.부서별 보너스 받는 사원의 수
SELECT DEPTNO,COUNT(*) AS 인원수 FROM EMP WHERE COMM>0 GROUP BY DEPTNO;

--7. 부서별 입사년도별 인원수, 최고급여, 급여합계,평균급여 출력하되 부서별 소계와 총계 출력하기
SELECT DEPTNO,TO_CHAR(HIREDATE,'YYYY'),COUNT(*),MAX(SAL),SUM(SAL),AVG(SAL) 
FROM EMP 
GROUP BY ROLLUP(DEPTNO,TO_CHAR(HIREDATE,'YYYY'));

--8. 사원수
SELECT COUNT(*) FROM EMP;

--9.부서수 DEPT(전체부서)
SELECT COUNT(*) FROM DEPT;

--10. 사원이 하나도 없는 부서번호
--전체부서
SELECT DEPTNO FROM DEPT
MINUS SELECT DISTINCT DEPTNO FROM EMP;

--11.부서별 사원의 이름
SELECT DEPTNO,ENAME FROM EMP GROUP BY DEPTNO,ENAME;

--12.부서별 사원의 이름을 출력하되, 급여순으로 출력 WITHIN -> 그 그룹안에서 어떻게 줄세울거니?
SELECT DEPTNO,LISTAGG(ENAME,',')WITHIN GROUP(ORDER BY SAL) FROM EMP GROUP BY DEPTNO;

--13. 직무별 사원의 이름을 출력하되, 입사순으로 출력
SELECT JOB,LISTAGG(ENAME,',') WITHIN GROUP(ORDER BY HIREDATE) FROM EMP GROUP BY JOB;

--14.부서별 사원의 사원번호를 출력하되, 사원번호순으로 
SELECT DEPTNO,LISTAGG(EMPNO,',') WITHIN GROUP(ORDER BY EMPNO) FROM EMP GROUP BY DEPTNO;

JOIN 함수 예제

--15.모든 사원과 부서의 정보를 출력 : CROSS JOIN,카디전 곱(CARTESIAN PRODUCT)
SELECT * FROM EMP,DEPT ORDER BY EMPNO;    --56개 EMP:14개 DEPT:4개 ==> 14*4=56개 한사람당 4개씩 나옴 한사람당 부서번호륻 다 조인해서 그럼 그럴려면 조인 기준을 정해주면 됨

--16.  '.'은 소속을 나타냄
SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO ORDER BY EMPNO;

--17. 16번과 동일하나 보이는 방식을 다르게 할 수 있음 FROM 절에서 EMP E=> EMP테이블 E라고 지칭
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;

--18. 사원번호,사원명, 부서번호, 부서명,부서위치 출력 ★ 소속을 밝히는 습관을 가지자!
SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO; 

--19.급여가 3000이상인 사원들의 번호,사원명,급여,부서번호,부서명,부서위치 출력  ★ E.DEPTNO
SELECT EMPNO,ENAME,SAL, E.DEPTNO,D.DNAME,LOC FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND SAL>=3000;

--20.급여등급 테이블 정보 확인 --SALGRADE테이블
SELECT * FROM SALGRADE;

--21. 모든 사원의 정보와 급여등급정보를 출력
SELECT * FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL ;

--22.SCOTT이라는 사원의 급여와 급여등급 구하기
SELECT SAL, GRADE FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.ENAME='SCOTT';
SELECT SAL,GRADE FROM EMP E, SALGRADE S WHERE ENAME='SCOTT' ,E.SAL BETWEEN S.LOSAL AND S.HISAL;  ->내가 한거 수정하기

--23.사원번호,사원명, 관리자번호,관리자의 사원번호, 관리자명 : 자체조인(SELF JOIN)
SELECT E.EMPNO, E.ENAME, E.MGR, M.EMPNO, M.ENAME FROM EMP E ,EMP M WHERE E.MGR = M.EMPNO;

--24.관리자가 없는 사원의 이름 (KING)
SELECT ENAME FROM EMP WHERE MGR IS NULL;

--25. CROSS JOIN  C(ROSS JOIN빼고 , 작성해도 값이 나옴)
SELECT * FROM EMP CROSS JOIN DEPT;

--26.★ 모든 사원의 사원번호, 사원명, 관리자번호,관리자이름 단 관리자가 없더라도 나오도록! 사장님도 포함(외부조인)
SELECT E.EMPNO AS 사원번호,E.ENAME AS 사원명 ,E.MGR AS 관지라번호,M.ENAME AS 관리자명 FROM EMP E,EMP M WHERE E.MGR = M.EMPNO(+);

--27. 사원번호,사원명,관리자 번호,관리자 이름, 관지라의 부서번호-->관리자 없는 사람 제외한 결과:13개
SELECT E.EMPNO,E.ENAME,E.MGR,M.ENAME,M.DEPTNO FROM EMP E,EMP M WHERE E.MGR=M.EMPNO;

--28. 모든 관리자의 정보를 출력하고 부하직원이 없더라도 포함 (없으면 NULL값으로 채워줘 = 부하직원이 없어도 채워줘)
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2 WHERE E1.MGR(+) = E2.EMPNO ORDER BY E1.EMPNO;

--29.NATURALJOIN(자연조인)
--사원번호, 사원명, 직무,관리자번호,입사일,급여,보너스,부서번호,부서명,부서위치 출력하되,부서번호순, 사원번호순
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,E.DEPTNO,DNAME,LOC 
FROM EMP E, DEPT D 
WHERE E.DEPTNO=D.DEPTNO ORDER BY E.DEPTNO,EMPNO; 
--다른 방법 사용해서 작성
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DNAME,LOC 
FROM EMP E NATURAL JOIN DEPT D
ORDER BY DEPTNO,EMPNO; 

--30. FROM A JOIN B USING(공통속성)사용해서 29번 풀기 : D.E.이렇게 소속을 정해주면 틀린거야
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DNAME,LOC 
FROM EMP E JOIN DEPT D USING(DEPTNO)
ORDER BY DEPTNO,EMPNO;

--31.사원번호,사원명,직무,관리자번호,입사일,급여,보너스,부서번호,부서명,부서위치를 출력하되 급여가 3000이상인 자
--대상으로 부서번호순, 사원번호순으로! JOIN USING 사용하여
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,DNAME,LOC 
FROM EMP E JOIN DEPT D USING(DEPTNO)
WHERE SAL>=3000
ORDER BY DEPTNO,EMPNO;