SQL - JOIN

1. JOIN

-------- 여러 테이블 JOIN(조인)
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
order by emp.deptno asc, dept.deptno asc;
    -- 14   * 4  = 56
     
-- 양쪽 테이블에 겹치는 이름의 컬럼을 사용할 경우 반드시!!!    테이블명.컬럼  명확히 기재
-- 00918. 00000 -  "column ambiguously defined"     
--SELECT DNAME, DEPTNO, ENAME   -- 에러가 난다
SELECT DNAME, DEPT.DEPTNO, ENAME   
FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO AND
       DEPT.DEPTNO = 10
;
 
-- ANSI 
SELECT DNAME, DEPT.DEPTNO, ENAME   
FROM DEPT JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO
WHERE DEPT.DEPTNO = 10;
 
 
 
-- 사원기준 10번 부서 사람들의 사원번호 사원명  매니저번호,            매니저명 출력
SELECT e1.EMPNO, e1.ENAME, e1.MGR,  e2.ename, e1.deptno
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO 
     AND e1.deptno = 10
;
-- ANSI
SELECT E1.EMPNO, E1.ENAME, E1.MGR,  E2.ENAME, E1.DEPTNO
FROM EMP E1 JOIN EMP E2  ON  E1.MGR = E2.EMPNO 
WHERE e1.deptno = 10
;

 

2. OUTER JOIN

 -- outer join
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO 
;
   --10 20 30 null   10 20 30 40
 
SELECT *    --distinct 했을때 더 많은 쪽이 기준
FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
;
 
SELECT *    --distinct 했을때 더 많은 쪽이 기준
FROM DEPT D LEFT OUTER JOIN EMP E ON E.DEPTNO = D.DEPTNO
;
 
 
   --10 20 30 null   10 20 30 40
SELECT DISTINCT DEPTNO FROM EMP;
select distinct deptno from dept;

 

3. 문제

--1. 부서번호가 10번인 사람의 사원번호,사원명,급여 출력
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO=10;
 
--2. 사원번호가 7369인 사람의 사원명,입사일,부서번호 출력
SELECT ENAME, HIREDATE, DEPTNO
FROM EMP
WHERE EMPNO=7369
;
 
 
--3.입사일이 1983년 이상인 사원의 이름,급여 출력
SELECT ENAME, SAL
FROM EMP
--WHERE HIREDATE >= '1983-01-01'
WHERE HIREDATE >= TO_DATE('1983-01-01','YYYY-MM-DD')
; 
 
--4.직업이 MANAGER가 아닌 사원의 모든 정보 출력
SELECT *
FROM EMP
WHERE JOB != 'MANAGER'
--WHERE JOB <> 'MANAGER'
--WHERE JOB NOT IN ('MANAGER')
; 
 
--5.입사일이 81/04/02보다 늦고 82/12/29보다 빠른 사원의 모든 정보 출력
SELECT *
FROM EMP
--WHERE HIREDATE  BETWEEN  '1981-04-03' AND '1982-12-30'   -- 이상이하
--WHERE HIREDATE > '1981-04-02' AND HIREDATE < '1982-12-29'
WHERE HIREDATE > TO_DATE('1981-04-02','YYYY-MM-DD') AND 
      HIREDATE < TO_DATE('1982-12-29','YYYY-MM-DD')
;
 
 
--6.사원번호가 7654와 7782 사이 이외의 사원의 모든 정보 출력
SELECT *
FROM EMP
--WHERE EMPNO NOT IN (7654, 7782)
WHERE EMPNO NOT BETWEEN 7654 AND 7782
--WHERE EMPNO < 7654 OR EMPNO > 7782
;
 
--7.직업이 MANAGER와 SALESMAN인 사원의 모든 정보 출력 
SELECT *
FROM EMP
WHERE JOB IN ('MANAGER','SALESMAN')
--WHERE JOB = 'MANAGER' OR JOB='SALESMAN'
 ;
 
--8.입사일이 81년도인 사원의 모든 정보 출력
SELECT *
FROM EMP
WHERE HIREDATE BETWEEN '1981-01-01' AND '1981-12-31'
--WHERE HIREDATE >= '1981-01-01' AND HIREDATE <= '1981-12-31'
--WHERE HIREDATE >= TO_DATE('1981-01-01','YYYY-MM-DD') AND HIREDATE <= TO_DATE('1981-12-31','YYYY-MM-DD')
--WHERE TO_CHAR(HIREDATE,'YYYY') = '1981' --비추:: 조건절에서 원본컬럼을 변형했다
;
 
 
--9.커미션을 받는 사원의 모든 정보 출력
SELECT *
FROM EMP
WHERE COMM IS NOT NULL AND COMM>0
--WHERE NVL(COMM,0) > 0    --비추:: 조건절에서 원본컬럼을 변형했다
--WHERE COMM NOT IN (NULL, 0)  -------------- 에러
;
 
 
--10. 연봉이 20000 이상인 사원의 모든 정보 출력 (연봉에는 comm을 포함시킬것)
SELECT (SAL*12) + NVL(COMM,0)
FROM EMP
WHERE (SAL*12) + NVL(COMM,0) >= 20000
;
 
 
select (sal*12) + nvl(comm,0)
FROM EMP
WHERE (SAL*12) + NVL(COMM,0) >= 20000;
-------------------------------------------------------------- 조인없이 
--12_1. 이름에 'A'가 들어가는 사원들의 이름, 부서번호 출력
SELECT ENAME, DEPTNO
FROM EMP
WHERE ENAME LIKE '%A%'
;
--13_1. 직업이 'SALESMAN'인 사원 이름, 부서번호 출력
SELECT ENAME, DEPTNO
FROM EMP
WHERE JOB = 'SALESMAN'
;
 
--14_1. 부서번호가 10번, 20번인 사원들의 사원이름, 급여, 부서번호 출력
--      출력된 결과물을 부서번호가 낮은 순으로, 급여가 높은 순으로 정렬
 
SELECT ENAME, SAL, DEPTNO
FROM EMP 
WHERE DEPTNO  IN (10, 20) 
ORDER BY DEPTNO ASC, SAL DESC
--WHERE DEPTNO=10 OR DEPTNO=20
;
-------------------------------------------------------------- 조인이 있을 경우
 
--11. DALLAS에서 근무하는 사원의 이름, 직업, 부서번호, 부서이름 출력
SELECT dname, DEPTNO FROM DEPT
where loc = 'DALLAS';
 
SELECT ENAME,  JOB, DEPTNO
FROM EMP;
 --------
SELECT d.DNAME, d.DEPTNO  ,      E.ENAME,  E.JOB   --, DEPTNO
FROM DEPT  D,  EMP   E
WHERE LOC = 'DALLAS' AND D.DEPTNO = E.DEPTNO
;
 
 
--12. 이름에 'A'가 들어가는 사원들의 이름과 부서이름을 출력
SELECT e.ENAME, d.DNAME
FROM EMP E, DEPT D
WHERE e.ENAME LIKE '%A%' and e.deptno = d.deptno
;
 
--13. 직업이 'SALESMAN'인 사원들의 직업과 그 사원이름, 부서 이름 출력
SELECT e.JOB, e.ENAME , d.dname
FROM EMP E, DEPT D
WHERE e.JOB = 'SALESMAN' AND E.DEPTNO = D.DEPTNO
;
 
--14. 부서번호가 10번, 20번인 사원들의 부서번호, 부서이름, 사원이름, 급여 출력
      출력된 결과물을 부서번호가 낮은 순으로, 급여가 높은 순으로 정렬
SELECT d.DEPTNO, e.ENAME, e.SAL, d.dname      
FROM EMP E , DEPT D    
WHERE d.DEPTNO IN (10,20) AND E.DEPTNO = D.DEPTNO
;
 
------------- GROUP BY 관련 ---------------------
 
--1. EMP 테이블에서 10번부서 급여의 평균,최고,최저,급여를받는인원수 출력(조건 평균 급여가 많은 순으로 출력)
SELECT AVG(SAL),MAX(SAL),MIN(SAL),COUNT(SAL)
FROM EMP
WHERE DEPTNO=10
ORDER BY AVG(SAL) DESC;
 
--2. EMP 테이블에서 각 부서별 급여의 평균,최고,최저 출력(조건:부서 오름차순 정렬)
 
SELECT DEPTNO,AVG(SAL),MAX(SAL),MIN(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO ASC;
 
--3. EMP 테이블에서 같은업무를 하는 사람의 수가 4명 이상인 업무와 인원수 출력
SELECT JOB, COUNT(JOB)
FROM EMP
GROUP BY JOB
HAVING COUNT(JOB) >= 4;
 
--4. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하시오
 
SELECT DEPTNO,COUNT(DEPTNO), SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(DEPTNO)>4;
 
 
--5. EMP 테이블에서 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 부서번호,업무명,인원수출력
-- (조건 부서번호 오름차순, 업무 내림차순 정렬)
SELECT DEPTNO, JOB, COUNT(1) CNT
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO ASC, JOB DESC;