오라클로 배우는 데이터베이스 입문, 둘째 마당 08 - 1 ~ 4번

1. 급여가 2000 초과인 사원들의 부서 정보, 사원 정보를 출력

---

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

출력 결과

20	RESEARCH	7566	JONES	2975
30	SALES	7698	BLAKE	2850
10	ACCOUNTING	7782	CLARK	2450
20	RESEARCH	7788	SCOTT	3000
10	ACCOUNTING	7839	KING	5000
20	RESEARCH	7902	FORD	3000

2. 부서별 평균 급여, 최대 급여, 최소 급여, 사원수를 출력

---

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

출력 결과

10	ACCOUNTING	2916	5000	1300	3
20	RESEARCH	2175	3000	800	5
30	SALES	1566	2850	950	6

3. 모든 부서 정보와 사원 정보를 부서 번호, 사원 이름 순으로 정렬하여 출력

---

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

출력 결과

10	ACCOUNTING	7782	CLARK	MANAGER	2450
10	ACCOUNTING	7934	MILLER	CLERK	1300
10	ACCOUNTING	7839	KING	PRESIDENT	5000
20	RESEARCH	7566	JONES	MANAGER	2975
20	RESEARCH	7369	SMITH	CLERK	800
20	RESEARCH	7788	SCOTT	ANALYST	3000
20	RESEARCH	7902	FORD	ANALYST	3000
20	RESEARCH	7876	ADAMS	CLERK	1100
30	SALES	7521	WARD	SALESMAN	1250
30	SALES	7844	TURNER	SALESMAN	1500
30	SALES	7499	ALLEN	SALESMAN	1600
30	SALES	7900	JAMES	CLERK	950
30	SALES	7654	MARTIN	SALESMAN	1250
30	SALES	7698	BLAKE	MANAGER	2850
40	OPERATIONS				

4. 모든 부서 정보, 사원 정보, 급여 등급 정보, 각 사원의 직속 상관의 정보를 부서 번호, 사원 번호 순서로 정렬하여 출력

---

SELECT 
    D.DEPTNO, D.DNAME, E1.EMPNO, E1.ENAME, E1.MGR, E1.SAL, E1.DEPTNO, S.LOSAL,S.HISAL,S.GRADE, E1.EMPNO EGR_EMPNO, E2.MGR MGR_ENAME
FROM EMP E1, EMP E2, DEPT D, SALGRADE S
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 ;

출력 결과

10	ACCOUNTING	7782	CLARK	7839	2450	10	2001	3000	4	7782	
10	ACCOUNTING	7839	KING		5000	10	3001	9999	5	7839	
10	ACCOUNTING	7934	MILLER	7782	1300	10	1201	1400	2	7934	7839
20	RESEARCH	7369	SMITH	7902	800	20	700	1200	1	7369	7566
20	RESEARCH	7566	JONES	7839	2975	20	2001	3000	4	7566	
20	RESEARCH	7788	SCOTT	7566	3000	20	2001	3000	4	7788	7839
20	RESEARCH	7876	ADAMS	7788	1100	20	700	1200	1	7876	7566
20	RESEARCH	7902	FORD	7566	3000	20	2001	3000	4	7902	7839
30	SALES	7499	ALLEN	7698	1600	30	1401	2000	3	7499	7839
30	SALES	7521	WARD	7698	1250	30	1201	1400	2	7521	7839
30	SALES	7654	MARTIN	7698	1250	30	1201	1400	2	7654	7839
30	SALES	7698	BLAKE	7839	2850	30	2001	3000	4	7698	
30	SALES	7844	TURNER	7698	1500	30	1401	2000	3	7844	7839
30	SALES	7900	JAMES	7698	950	30	700	1200	1	7900	7839
40	OPERATIONS										

emp Table

empno ename job mgr hiredate sal comm deptno

7369	SMITH	CLERK	7902	1993-06-13	800.00	0.00	20
7499	ALLEN	SALESMAN	7698	1998-08-15	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1996-03-26	1250.00	500.00	30
7566	JONES	MANAGER	7839	1995-10-31	2975.00		20
7698	BLAKE	MANAGER	7839	1992-06-11	2850.00		30
7782	CLARK	MANAGER	7839	1993-05-14	2450.00		10
7788	SCOTT	ANALYST	7566	1996-03-05	3000.00		20
7839	KING	PRESIDENT		1990-06-09	5000.00	0.00	10
7844	TURNER	SALESMAN	7698	1995-06-04	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1999-06-04	1100.00		20
7900	JAMES	CLERK	7698	2000-06-23	950.00		30
7934	MILLER	CLERK	7782	2000-01-21	1300.00		10
7902	FORD	ANALYST	7566	1997-12-05	3000.00		20
7654	MARTIN	SALESMAN	7698	1998-12-05	1250.00	1400.00	30

dept Table

deptno dname location

10	Accounting	New York
20	Research	Dallas
30	Sales	Chicago
40	Operations	Boston

salgrade Table

grade losal hisal

1	700.00	1200.00
2	1201.00	1400.00
4	2001.00	3000.00
5	3001.00	99999.00
3	1401.00	2000.00