[ORACLE] 기본 문법 3

그룹 함수(= 복수행 함수, 다중행 함수)     (1) 정의 : 여러 행을 바탕으로 하나의 결과 값을 도출해내기 위해 사용하는 함수     (2) 자주 사용하는 그룹 함수         1) SUM      : 합         2) COUNT    : 개수         3) MAX      : 최대값         4) MIN      : 최소값         5) AVG      : 평균값

사용 예시

모든 사원들의 급여 합계 출력하기
SELECT SUM(SAL)
FROM EMP;

 

* 그룹 함수를 사용한 SELECT절에는 기본적으로 여러 행이 결과로 나올 수 있는 열을 함께 사용할 수 없다.
즉 다음과 같은 SELECT문은 실행되지 못하고 오류가 발생한다. 

 

SELECT ENAME, SUM(SAL)
FROM EMP;

 

전체 부서번호의 개수 출력하기(단, 중복 제거)
SELECT COUNT(DISTINCT DEPTNO)
FROM EMP;

 

부서 번호가 10번이 사원들 중 최대 급여 출력하기
SELECT MAX(SAL)
    FROM EMP
WHERE DEPTNO = 10;

부서 번호가 10번인 사원들 중 최소 급여 출력하기
SELECT MIN(SAL)
    FROM EMP
WHERE DEPTNO = 10;

 

* 오라클 데이터베이스에서는 날짜 및 문자 데이터 역시 크기 비교가 가능하다.
부서 번호가 20번인 사원들 중 제일 최근 입사일 출력하기
SELECT MAX(HIREDATE)
    FROM EMP
WHERE DEPTNO = 20;

 

사원 이름이 알파벳 순으로 가장 앞글자와 뒷글자 이름 출력하기 
SELECT MIN(ENAME), MAX(ENAME)
FROM EMP;

실행결과

 

---

GROUP BY 절     (1) 정의         여러 데이터에서 의미 있는 하나의 결과를         특정 열 값별로 묶어서 출력할 때 데이터를 '그룹화'한다고 표현한다.         예) 각 부서별 평균 급여 출력하기     (2) 기본 형식         SELECT   [조회할 열1 이름], [조회할 열2 이름], ..., [열N 이름]         FROM     [조회할 테이블 이름]         WHERE    [조회할 행을 선별하는 조건식]         GROUP BY [그룹화할 열을 지정(여러개 지정 가능)]         ORDER BY [정렬하려는 열 지정]            (3) 주의         * GROUP BY 절에는 별칭이 인식되지 않는다.

사용 예시

각 부서별 평균 급여 출력하기
SELECT 

    DEPTNO, AVG(SAL)
    FROM EMP
    GROUP BY DEPTNO;

 

부서 번호 및 직책별 평균 급여 출력하기 * 여러 개 지정 가능
SELECT DEPTNO, JOB, AVG(SAL) FROM EMP GROUP BY DEPTNO, JOB ORDER BY DEPTNO, JOB;

 

ENAME은 일반 열로 그룹화된 열의 데이터 수와 일치하지 않는다.(오류발생 : ORA-00937)

* 그룹 함수를 사용한 열 또는 GROUP BY 절에 명시한 열만 사용할 수 있다.
SELECT ENAME, DEPTNO, AVG(SAL)
    FROM EMP
GROUP BY DEPTNO;

 

 

---

HAVING 절     (1) 정의         GROUP BY절을 통해 그룹화된 결과 값의 범위를 제한하는 데 사용한다.     (2) 기본 형식         SELECT   [조회할 열1 이름], [조회할 열2 이름], ..., [열N 이름]         FROM     [조회할 테이블 이름]         WHERE    [조회할 행을 선별하는 조건식]         GROUP BY [그룹화할 열을 지정(여러개 지정 가능)]         HAVING   [출력 그룹을 제한하는 조건식]         ORDER BY [정렬하려는 열 지정]          (3) 주의         WHERE절은 출력 대상 행을 제한하고,         HAVING절은 그룹화된 대상을 출력에서 제한하기 위해 쓰인다.

사용 예시

평균 급여가 2000이 넘는 사원의 부서번호, 직책, 평균 급여 출력하기
SELECT 

DEPTNO, JOB, AVG(SAL) 

FROM EMP 

GROUP BY DEPTNO, JOB 

HAVING AVG(SAL) >= 2000 ORDER BY DEPTNO, JOB;

 

급여가 3000 이하인 사원들 중에 평균 급여가 2000이상인 사원의 부서번호, 직책, 평균 급여 출력하기

* WHERE 과 HAVING 모두 사용하기
SELECT DEPTNO, JOB, AVG(SAL) 
        FROM EMP 
        WHERE SAL <= 3000 
        GROUP BY DEPTNO, JOB 
        HAVING AVG(SAL) >= 2000
            ORDER BY DEPTNO, JOB;

 

사원들의 입사 연도를 기준으로 부서별로 몇 명이 입사했는지 출력하기
SELECT DEPTNO ,TO_CHAR(HIREDATE, 'YYYY'), COUNT(*) 

FROM EMP 

GROUP BY DEPTNO, TO_CHAR(HIREDATE, 'YYYY') 

ORDER BY DEPTNO;

 

추가 수당을 받는 사원 수와 받지 않는 사원 수 출력하기

★ 잘 봐두기
SELECT NVL2(COMM, 'O', 'X') AS EXIST_COMM, 
        COUNT(*) AS CNT FROM EMP
GROUP BY NVL2(COMM, 'O', 'X');

 

각 부서의 입사 연도별 사원 수, 최고 급여, 급여 합, 평균 급여를 출력
SELECT DEPTNO ,TO_CHAR(HIREDATE, 'YYYY'), COUNT(*), MAX(SAL), SUM(SAL), TRUNC(AVG(SAL))
FROM EMP
GROUP BY DEPTNO, TO_CHAR(HIREDATE, 'YYYY')
ORDER BY DEPTNO, TO_CHAR(HIREDATE, 'YYYY');

 

---

등가 조인(join)     (1) 정의         두 개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용     (2) FROM 절에는 여러 개 테이블을 지정하는 것이 가능하다.

사용 예시

 테이블의 모든 열을 출력할지라도 대부분 *을 사용하지 않고 열을 하나하나 직접 명시해준다.
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
ORDER BY DEPTNO;

 

 

---

조인의 종류     (1) 정의         두 개 이상의 테이블을 하나의 테이블처럼 가로로 늘어뜨려 출력하기 위해 사용하는 조인은         대상 데이터를 어떻게 연결하느냐에 따라 여러 종류로 나뉜다.     (2) 종류         1. 등가 조인(equi join) = 내부 조인(inner join) = 단순 조인(simple join) 전부 같은말이다.             특정 열 값이 일치한 출력 결과를 사용하는 방식         2. 비등가 조인         3. 자체 조인         4. 외부 조인

사용 예시

사원번호, 사원이름, 부서번호, 부서이름, 지역 함께 출력하기
SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC 
FROM EMP E, DEPT D 

WHERE E.DEPTNO = D.DEPTNO 

ORDER BY D.DEPTNO, E.EMPNO;

 

하나의 테이블을 여러개의 테이블처럼 활용하는 조인 (내부 조인)
-매니저 번호가 또 하나의 사원의 번호이므로 결국 자기 테이블을 2번검사해야한다. 
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;

 

외부조인: NULL 값도 결과에 출력할때 사용 
위 결과에서 KING 의 데이터가 출력되지않았다. 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(+);

 

*사원을 기준으로 LEFT OUTER JOIN

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;

* MANAGER 를 기준으로 RIGHT OUTER JOIN

---

JONES 의 급여보다 높은 급여를 받는 사원 정보 출력하기
SELECT * FROM EMP 
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');

 

SCOTT 보다 빨리 입사한 사원목록 
SELECT * FROM EMP

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

 

20번 부서에 속한 사원중 전체 사원의 평균급여보다 높은 급여를 받는 사원정보
SELECT * FROM EMP 

WHERE DEPTNO = 20 AND SAL > (SELECT AVG(SAL) FROM EMP);

 

부서 번호가 20 이거나 30인 사원의 정보
* IN 사용법  ( 여러가지중 하나라도 포함되면 TRUE)  
SELECT * FROM EMP WHERE DEPTNO IN(20, 30);

 

각 부서별 최고급여와 동일한 급여를 받는 사원 정보 

★ 이거 잘 이해 안됨ㅠㅠ
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
            FROM EMP
            GROUP BY DEPTNO);