오라클 Day 4

Hyunsu·2023년 4월 16일

oracle

Today I Learned

목록 보기

27/37

📝 목차

Chapter 08 조인

Chapter 08 조인

📌 문제 1

-- 오라클 전용 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND SAL > 2000
ORDER BY DEPTNO, DNAME;

-- ANSI 표준 방식
SELECT DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E NATURAL JOIN DEPT D
WHERE E.SAL > 2000
ORDER BY DEPTNO, DNAME;

📌 문제 2

-- 오라클 전용 방식
SELECT D.DEPTNO, D.DNAME, TRUNC(AVG(SAL)) AS AVG_SAL,
	   MAX(SAL) AS MAX_SAL, MIN(SAL) AS MIN_SAL, COUNT(*) AS CNT
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
GROUP BY D.DEPTNO, D.DNAME;

-- ANSI 표준 방식
SELECT DEPTNO, D.DNAME, TRUNC(AVG(SAL)) AS AVG_SAL,
	   MAX(SAL) AS MAX_SAL, MIN(SAL) AS MIN_SAL, COUNT(*) AS CNT
FROM DEPT D NATURAL JOIN EMP E
GROUP BY DEPTNO, D.DNAME;

📌 문제 3

-- 오라클 전용 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO(+)
ORDER BY D.DEPTNO, E.ENAME;

-- ANSI 표준 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM DEPT D LEFT OUTER JOIN EMP E ON(D.DEPTNO = E.DEPTNO)
ORDER BY D.DEPTNO, E.ENAME;

📌 문제 4

-- 오라클 전용 방식
SELECT D.DEPTNO, D.DNAME,
	   E1.EMPNO, E1.ENAME, E1.MGR, E1.SAL, E1.DEPTNO AS DEPTNO_1,
	   S.LOSAL, S.HISAL, S.GRADE,
	   E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM DEPT D, EMP E1, SALGRADE S, EMP E2
WHERE D.DEPTNO = E1.DEPTNO(+)
	  AND E1.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
	  AND E1.MGR = E2.EMPNO(+)
ORDER BY D.DEPTNO, E1.EMPNO;

-- ANSI 표준 방식
SELECT D.DEPTNO, D.DNAME,
	   E1.EMPNO, E1.ENAME, E1.MGR, E1.SAL, E1.DEPTNO AS DEPTNO_1,
	   S.LOSAL, S.HISAL, S.GRADE,
	   E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM DEPT D LEFT OUTER JOIN EMP E1 ON(D.DEPTNO = E1.DEPTNO)
	 LEFT OUTER JOIN SALGRADE S ON(E1.SAL BETWEEN S.LOSAL AND S.HISAL)
	 LEFT OUTER JOIN EMP E2 ON(E1.MGR = E2.EMPNO)
ORDER BY D.DEPTNO, E1.EMPNO;