오라클 - 3

-- 크로스 조인 (데카르트 콥, 카테시안 곱)
SELECT *
FROM EMP, DEPT
ORDER BY EMPNO;


-- 등가 조인. 내부 조인 (inner join). 단순 조인.
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMPNO;


SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO;


SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR,
E.HIREDATE, E.SAL, E.COMM, E.DEPTNO,
D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO -- 조인 조건
AND SAL >= 3000;



-- 비등가 조인
SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;


-- 자체 조인
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;


-- 왼쪽 외부 조인
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;


-- 오른쪽 외부 조인
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;



-- NATURAL JOIN (등가 조인)
-- : 자동으로 컬럼명과 자료형이 같은 컬럼을 찾아서 조인 기준으로 사용함.
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, DEPTNO,
D.DNAME, D.LOC
FROM EMP E NATURAL JOIN DEPT D
ORDER BY DEPTNO, E.EMPNO;


-- JOIN ~ USING (등가 조인)
-- : USING (조인에 사용할 기준열을 명함)
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, DEPTNO,
D.DNAME, D.LOC
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE E.SAL >= 3000
ORDER BY DEPTNO, E.EMPNO;


-- JOIN ~ ON

-- JOIN 키워드는 내부 조인
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO,
D.DNAME, D.LOC
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE E.SAL >= 3000
ORDER BY E.DEPTNO, E.EMPNO;


-- 오라클 전용 테이블 3개 외부조인 문법
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME,
D.DNAME
FROM EMP E1, EMP E2, DEPT D
WHERE E1.MGR = E2.EMPNO(+)
AND E1.DEPTNO = D.DEPTNO(+)
ORDER BY E1.EMPNO;


-- 테이블 3개 외부조인 표준 문법
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME,
D.DNAME
FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
LEFT OUTER JOIN DEPT D ON (E1.DEPTNO = D.DEPTNO)
ORDER BY E1.EMPNO;



-- 서브쿼리

-- ★요구사항: EMP 테이블에서 JONES보다 급여가 높은 사원을 조회하기

-- 사원 이름이 JONES인 사원의 급여 출력하기
SELECT SAL -- 2975
FROM EMP
WHERE ENAME = 'JONES';

-- 급여가 2975보다 높은 사원정보 출력하기
SELECT *
FROM EMP
WHERE SAL > 2975;


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


-- 스칼라 서브쿼리 : 행도 1개 열도 1개인 단일값을 가져오는 서브쿼리


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



SELECT AVG(SAL)
FROM EMP;


SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, E.DEPTNO,
D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 20
AND E.SAL > (SELECT AVG(SAL)
FROM EMP);



SELECT *
FROM EMP
WHERE DEPTNO IN (20, 30);


-- 각 부서별 최고 급여와 동일한 급여를 받는 사원정보 출력하기

SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);


-- ANY, SOME 연산자는 동일한 기능
SELECT *
FROM EMP
WHERE SAL = ANY (SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);


-- 30번 부서 사원들의 최대 급여보다 적은 급여를 받는 사원정보 출력하기
SELECT *
FROM EMP
WHERE SAL < (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 30);


SELECT *
FROM EMP
WHERE SAL < ANY (SELECT SAL
FROM EMP
WHERE DEPTNO = 30);


SELECT *
FROM EMP
WHERE SAL < ANY (1600, 1250, 1250, 2850, 1500, 950);


-- 30번 부서 사원들의 최소 급여보다 많은 급여를 받는 사원정보 출력하기
SELECT *
FROM EMP
WHERE SAL > (SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 30)
ORDER BY EMPNO;


SELECT *
FROM EMP
WHERE SAL > ANY (SELECT SAL
FROM EMP
WHERE DEPTNO = 30)
ORDER BY EMPNO;


-- 30번 부서 사원들의 최소 급여보다 적은 급여를 받는 사원정보 출력하기
SELECT *
FROM EMP
WHERE SAL < (SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 30)
ORDER BY EMPNO;


SELECT *
FROM EMP
WHERE SAL < ALL (SELECT SAL -- (1600, 1250, 1250, 2850, 1500, 950)
FROM EMP
WHERE DEPTNO = 30)
ORDER BY EMPNO;



SELECT *
FROM EMP
WHERE EXISTS (SELECT DNAME
FROM DEPT
WHERE DEPTNO = 10);




SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO);





SELECT E10.EMPNO, E10.ENAME, E10.JOB, E10.SAL, E10.DEPTNO,
D.DNAME, D.LOC
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,
(SELECT * FROM DEPT) D
WHERE E10.DEPTNO = D.DEPTNO;


WITH
E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.JOB, E10.SAL, E10.DEPTNO,
D.DNAME, D.LOC
FROM E10, D
WHERE E10.DEPTNO = D.DEPTNO;



SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL,
(SELECT S.GRADE
FROM SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL) AS SALGRADE,
E.DEPTNO,
(SELECT D.DNAME
FROM DEPT D
WHERE E.DEPTNO = D.DEPTNO) AS DNAME
FROM EMP E;



INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
FROM EMP E;


UPDATE DEPT_TEMP2
SET (DNAME, LOC) = (SELECT DNAME, LOC
FROM DEPT
WHERE DEPTNO = 40)
WHERE DEPTNO = 40;


DELETE
FROM EMP_TEMP
WHERE EMPNO IN (SELECT EMPNO
FROM EMP
WHERE DEPTNO = 30);