그룹함수, HAVING, 단일 및 다중 서브쿼리

--그룹 함수
--; 검색된 여러 행을 이용하여 통계정보를 계산하는 함수

MAX  값들 중에 최대값을 반환
MIN  값들 중에 최소값을 반환
AVG  평균값을 계산
COUNT 반환된 행의 수를 계산
SUM  총합을 계산
STDDEV 표준편차를 계산
VARIANCE 분산을 계산

1) null 값은 무시된다
2) 반드시 1개의 값만을 반환한다
3) GROUP BY 없이 일반 컬럼과 기술될 수 없다

--1) 사원의 평균 급여를 검색한다
SELECT AVG(sal) 평균급여, ROUND(AVG(sal)) 평균급여
FROM emp;


--2) 사원들에게 지급된 보너스 총합과 보너스 평균을 검색한다
SELECT SUM(comm) "보너스 총합",
       ROUND(AVG(comm))  "보너스 평균",        --널값은 제외하고 평균낸 것이라 10명
       COUNT(comm)      "수령 인원",
       ROUND(AVG(NVL(comm,0))) "환산 평균",    --널값 치환하여 포함하면 14명 
       COUNT(*)         "전체 인원"            --EMP테이블의 전체 행의 개수/ 행 = row =record
FROM emp;

--보너스에서 NULL이 아닌 사람 수를 계산
SELECT COUNT(*)
FROM emp
WHERE comm IS NOT NULL;


--그룹 함수와 GROUP BY 절

--SELECT 컬럼 OR 그룹 함수...
--FROM 테이블
--WHERE 조건
--GROUP BY 그룹대상
--ORDER BY 정렬대상;

--1) 그룹함수와 함께 사용되는 일반 컬럼은 
--  반드시 GROUP BY 절에 기술되어야 한다
--2) GROUP BY 절에 기술되지 않으면
--   ORA-00937 에러가 발생한다



3) 업무별 평균 급여, 평균 연봉을 검색한다
--ORA-00937 : 카디널리티가 일치하지 않는다
--AVG 함수는 1개의 결과값
--job 은 행개수만큼 결과값

SELECT job 업무, ROUND(AVG(sal)) 평균급여,
      ROUND(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM emp;


SELECT job 업무       --14개
FROM emp;

SELECT ROUND(AVG(sal)) 평균급여,          --1개(그룹함수니까), 전체 행의 평균 계산해서 1개 결과
       ROUND(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM emp;


--4) 그룹 함수와 함께 사용되는 일반 컬럼은
--반드시 GROUP BY 에 기술되어야 한다 (그룹화시켜줘야 한다)

SELECT job 업무,                          --업무별로 평균구하기
      ROUND(AVG(sal)) 평균급여,      
      ROUND(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM emp
GROUP BY job;


--5) 부서별 평균 급여, 평균 연봉을 검색한다
SELECT d.dno 부서번호, dname 부서명,       --일반컬럼들
      ROUND(AVG(sal)) 평균급여,
      ROUND(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM dept d, emp e
WHERE d.dno=e.dno
GROUP BY d.dno, dname;


--TO_CHAR(AVG(result), '99.99') "기말 평균"
내용을 입력하세요.


*그룹 함수와 HAVING 절


그룹 함수와 Having 절
; 그룹 함수를 포함한 조건은 일반 조건과 계산하는 시점이 다르다.
일반 조건의 경우 컬럼의 값을 단지 조건과 비교하면 되지만
그룹 함수의 결과를 조건으로 하는 경우 GROUP BY 절의 
사용 유무에 따라 결과 값이 달라지므로 조건에 그룹 함수가 
포함된 경우 이것은 일반 조건과 동일한 시점에 처리할 수 없다
SQL 은 이를 위해 HAVING 절을 제공한다.

HAVING 절은 조건 중에 그룹 함수가 포함된 것만을 모아서
기술하는 구문이다.

SELECT 컬럼 or 그룹함수...
FROM 테이블
WHERE 일반 조건
GROUP BY 그룹대상
HAVING 그룹함수포함조건
ORDER BY 정렬 대상;
1) HAVING : 조건 중에 그룹함수를 포함하는 조건 기술 (그룹함수의 조건)
2) HAVING 절은 GROUP BY 절 뒤에 기술한다
3) HAVING 절의 해석은 WHERE 절과 동일하다
   다만 그룹 함수를 포함하는 조건은 
   HAVING 절에 해야만 한다
   
SELECT eno, ename   -- 3 (순서)
FROM emp            -- 1
WHERE dno='20';     -- 2

--부서별로 평균급여가 3000이상인 부서만 출력하세요
SELECT dno, ROUND(AVG(sal))   -- 3
FROM emp                      -- 1
GROUP BY dno;                 -- 2

--그룹함수 조건은 WHERE절에 사용할 수 없다.
SELECT dno, ROUND(AVG(sal))    --3 
FROM emp                       --1
GROUP BY dno                   --2
HAVING AVG(sal) >= 3000;       --4

--20번 부서가 아니면서 평균 급여가 3000 이상인 부서만 출력하세요.
SELECT dno, ROUND(AVG(sal))
FROM emp e
WHERE dno != 20                --일반 컬럼은 WHERE절에
GROUP BY dno
HAVING ROUND(AVG(sal))>=3000;


--1)GROUP BY 절에 따른 그룹 함수 결과 값의 변화



--2) 부서별 급여 평균이 3천 달러 미만인 부서의 부서번호와
--평균 급여를 검색한다
SELECT dno, ROUND(AVG(sal))  
FROM emp                      
GROUP BY dno                  
HAVING AVG(sal) < 3000;  


--3) 개발 업무가 아닌 부서별 인원수를 검색하세요
SELECT dno, job, COUNT(*) 인원수  
FROM emp    
WHERE job != '개발'
GROUP BY dno, job;    --처음에 dno때문에 1번 부서를 같이 묶었다가 JOB에 의해서 경영,지원 둘로 나뉘어 출력

4) 그룹함수의 조건인 아닌 것은 WHERE 절에 기술한다


5) HAVING 절은 그룹함수의 조건이나
GROUP BY 에 기술한 컬럼의 조건이 가능하다
그러나 HAVING 절은 그룹함수의 조건만 사용하고
GROUP BY 에 있는 컬럼이라도 WHERE 절에 사용하는 것을
권장한다.

내용을 입력하세요.


*서브쿼리


서브 쿼리 - 단일 행 서브쿼리
; 서브 쿼리는 두 개의 쿼리를 결합하여 하나의 문장으로
표현하는 것이다
1) 단일 행 서브쿼리
 ; 서브쿼리가 하나의 컬럼에서 하나의 행을 검색한다
2) 다중 행 서브쿼리
 ; 서브쿼리가 하나의 컬럼에서 여러 개의 행을 검색한다
3) 다중 열 서브쿼리
 ; 서브쿼리가 여러 개의 컬럼을 검색한다

4) 서브 쿼리는 WHERE 절, HAVING 절과 같이 조건절에 주로
 쓰인다. FROM 절에 쓰이는 경우도 있다.
 FROM 절에 서브쿼리를 쓰는 경우를 인라인뷰(Inline View)
 라고 한다

SELECT 컬럼, ...
FROM 테이블
WHERE 컬럼 <단일 행 연산자> (SELECT 문: Sub Query);
1) 단일 행 연산자가 사용됨으로 반드시 서브쿼리의 결과
 값은 1개만 검색돼야 한다
2) 서브 쿼리는 반드시 괄호로 묶는다
3) 서브 쿼리는 메인 쿼리 실행 전에 실행된다
4) 서브 쿼리의 검색된 결과값은 메인 쿼리에 사용된다
5) 단일 행 연산자 오른쪽에 기술한다
   (=, <, >, <=, >=, !=)
6) WHERE 절에 기술된 열의 숫자와 타입은
  SELECT 절과 1:1 대응관계가 되어야 한다.

1)김연아보다 급여를 많이 받는 사원을 검색한다
--1) 김연아의 급여를 검색한다
--2) 김연아의 급여와 비교하여 더 많이 받는 사원을 검색한다
SELECT eno, ename "김연아급여보다 많아"     --3
FROM emp                                  --1
WHERE sal > (SELECT sal                   --서브쿼리 실행 후 비교
             FROM emp
             WHERE ename='김연아');

             
2)노육과 평점이 동일한 학생의 정보를 검색하라
--노육이 3명이라서 단일 행 연산자(=)를 사용할 수가 없다
--그래서 Error 가 발생했다
SELECT sno, sname, avr
FROM student
WHERE avr = (SELECT avr 
             FROM student
             WHERE sname='노육');
             
             SELECT sno, sname, avr


--다중행 서브쿼리 (결과 값이 여러 개의 행이다)
SELECT sno, sname, avr
FROM student
WHERE avr IN (SELECT avr 
             FROM student
             WHERE sname='노육');

--예측하기 힘든 단일 행 서브쿼리를 수정하는 방법
--1) '=' 연산자는 'IN'연산자로 바꾼다 -다중행 서브쿼리로 전환 
--2) 부등호(<, >, <=, >=)는 any, all -다중행 서브쿼리로 전환 
--  연산자를 추가한다
--3) Max, Min 그룹 함수를 사용한다  - 여러개 중에 1개만 선택



--3) 김연아와 부서가 다르고 동일한 업무를 하는 사원의 정보를 검색하라
-- a) 김연아와 부서가 다르다 - sub query
-- b) 김연아와 동일한 업무를 한다 - sub query
-- c) 위 조건의 사원의 정보를 검색한다 - main query
SELECT eno, ename, dno, job
FROM emp
WHERE dno != (SELECT dno
              FROM emp
              WHERE ename='김연아')
AND job = (SELECT job
           FROM emp
           WHERE ename='김연아');
           
         
4) 부서 중 가장 급여를 많이 받는 부서를 검색하라
--1) 부서중 평균 최대급여 계산
--2) 일치하는 부서를 출력
SELECT dno "급여 최대 부서" , ROUND(AVG(sal))
FROM emp
GROUP BY dno
HAVING AVG(sal) = (SELECT MAX(AVG(sal))
                          FROM emp
                          GROUP BY dno);


5) 부산에서 근무하는 사원의 정보를 검색한다
 a) 부산에 근무하는 부서번호
 b) 해당 부서 번호와 일치하는 사원의 정보 검색
 
SELECT eno, ename "부산 근무 사원"
FROM emp
WHERE dno= (SELECT dno
            FROM dept
            WHERE loc='부산');


SELECT eno, ename "부산 근무 사원"
FROM emp e, dept d
WHERE d.dno=e.dno
AND loc='부산';
내용을 입력하세요.

*서브쿼리 다중행,다중열


--서브 쿼리 - 다중 행, 다중 열
--
--SELECT 컬럼, ...
--FROM 테이블
--WHERE 컬럼 <다중 행 연산자> (SELECT문 : Sub Query문);
--1) 서브 쿼리에 여러 개의 행이 검색되는 쿼리를
--   다중 행 서브 쿼리라고 한다
--2) 다중 행 서브 쿼리는 다중 행 연산자를 이용한다
--3) 다중 행 연산자의 종류
--  IN : 검색된 값중에 하나만 일치하면 참이다
--  ANY : 검색된 값중에 조건에 맞는 것이 하나 이상 있으면
--        참이다
--  ALL : 모든 검색된 값과 조건에 맞아야 참이다
  
1) 20번 부서원들과 동일한 관리자로부터 관리받는 사원을 검색한다
--1) 20번 부서원들의 관리자가 여러 명일 수 있다
--2) 서브쿼리의 결과가 몇 개일지 예측할 수 없는 경우
--   다중 행 연산자를 이용하여 다중 행 서브쿼리를 작성한다

SELECT eno, ename, mgr "관리자 번호", dno
FROM emp
WHERE mgr IN (SELECT mgr              --여러명의 관리자들 중 하나만 일치하면,
              FROM emp
              WHERE dno='20')
AND dno!='20';           --어감상 20번 부서 제외



2) 10번 부서원들보다 급여가 낮은 사원을 검색한다
SELECT eno, ename, dno, sal
FROM emp
WHERE sal < ALL(SELECT sal   --여러명이 나온다면 그들 모두 보다도 낮은 sal
                FROM emp
                WHERE dno='10')
AND dno!='10';


SELECT eno, ename, dno, sal
FROM emp
WHERE sal <  ( SELECT MIN(sal) 
                FROM emp
                WHERE dno='10')
AND dno!='10';

--다중 행 연산자와 그룹 함수
--다중 행 연산자인 ALL 이나 ANY 는 다음과 같이
--그룹 함수를 이용해서 표현할 수 있다.
--ALL : 모두 다 조건에 맞아야 함
--ANY : 모두 중에 하나라도
--컬럼 > ALL => 컬럼 > MAX : 가장 큰 값보다 크다
--컬럼 < ALL => 컬럼 < MIN : 가장 작은 값보다 작다
--컬럼 > ANY => 컬럼 > MIN : 가장 작은 값보다 크다
--컬럼 < ANY => 컬럼 < MAX : 가장 큰 값보다 작다       ==하나라도 작은거니까


--3) 20번 부서원들과 보너스가 같은 사원을 검색한다

--null을 0으로 치환해서 검색할지 말지는 판단해야 한다.
SELECT dno, eno, ename, comm
FROM emp
WHERE comm IN (SELECT NVL(comm,0)           --NULL값이 존재하기때문에 치환. null과 null을 같다고 보지 않음!!
               FROM emp
               WHERE dno='20')
AND dno!='20';

SELECT eno, comm
FROM emp
WHERE dno='20';

--다중 열 서브 쿼리
--SELECT 컬럼,...
--FROM 테이블
--WHERE (컬럼1, 컬럼2, ...) IN (SELECT 문 : Sub Query 문);
--1) 서브 쿼리의 SELECT 문에 여러 개의 컬럼을 검색한다
--2) 여러 개의 컬럼을 검색하는 서브 쿼리 문을 이용할 때는
--   반드시 비교 대상 컬럼과 1:1 대응되어야 한다
--3) 다중 열 서브 쿼리에서 서브 쿼리의 검색 결과가 단지 하나의
--  행이라면 '='연산자 사용이 가능하지만 되도록 'IN'연산자를
--  사용한다.

4) 손하늘과 동일한 관리자의 관리를 받으면서 업무도 같은 사원을
검색한다.

SELECT eno, ename, mgr, job
FROM emp
WHERE (mgr, job) IN (SELECT mgr, job
                     FROM emp
                     WHERE ename='손하늘')
AND ename!='손하늘';


--손하늘 사원이 1명만 존재한다면 단일 행 서브쿼리로 변경할
--수도 있다. 손하늘 사원이 여러 명이면 다중 열 서브쿼리로만
--가능하다.
SELECT eno, ename, mgr, job
FROM emp
WHERE mgr = (SELECT mgr
              FROM emp
              WHERE ename='손하늘')
AND job = (SELECT job
            FROM emp
            WHERE ename='손하늘')
AND ename!='손하늘';


--5) 각 부서별로 최소 급여를 받는 사원의 정보를 검색한다      -부서별,최소급여 그리고 사원
--(이름, 급여)
SELECT dno, eno, ename, sal "부서별 최소급여"
FROM emp
WHERE (dno, sal) IN (SELECT dno, MIN(sal)
                     FROM emp
                     GROUP BY dno);