그룹함수의 사용(57쪽)
=====================
그룹함수란?
---> 여러개의 경로가 행(row)에 대한 집계함수. 합계, 평균, 개수, 최대최소 등
다음을 수행해보고 각 함수의 의미를 알아보자
SELECT AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL)
//여러개의 값들을 구분해서 보기
FROM EMP
WHERE JOB LIKE 'SALES%';
COUNT함수 사용 시 주의 사항!
SELECT COUNT(*)
FROM EMP;
SELECT COUNT(comm)
FROM EMP;
----> 무엇이 다른가?
TAEHO>SELECT COUNT(*)
2 FROM EMP;
COUNT(*)
----------
15
TAEHO>SELECT COUNT(comm)
2 FROM EMP;
COUNT(COMM)
-----------
5
SELECT comm
FROM EMP
WHERE comm IS NOT NULL;
--->count 함수는 null이 아닌 행의 수를 반환
TAEHO>SELECT comm
2 FROM EMP
3 WHERE comm IS NOT NULL;
COMM
----------
300
500
1400
0
2000
** 그룹함수에서 NULL은 제외됨!!
다음 두 명령문을 비교해보라
SELECT AVG(COMM)
FROM EMP;
SELECT AVG(NVL(COMM,0))
FROM EMP;
---> 무엇이 다른가?
TAEHO>SELECT AVG(COMM)
2 FROM EMP;
AVG(COMM)
----------
840
TAEHO>SELECT AVG(NVL(COMM,0))
2 FROM EMP;
AVG(NVL(COMM,0))
----------------
280
--> 커미션 안받는사람은 0으로 간주해서 계산함. null값을 0으로 치환한다음에 평균을 구하면 값이 다름.
*******************
GROUP BY의 사용
*******************
SELECT COUNT(empno) "인원수", AVG(sal) "평균급여액",
MAX(sal) "최고급여액", MIN(sal) "최저급여액",
SUM(sal) "급여합계"
FROM emp;
TAEHO>SELECT COUNT(empno)"인원수",AVG(sal)"평균급여액",MAX(sal)"최고급여액",MIN(sal)"최저급여액",SUM(sal)"급여합계"
2 FROM emp;
인원수 평균급여액 최고급여액 최저급여액 급여합계
---------- ---------- ---------- ---------- ----------
15 2098.33333 5000 800 31475
----> 자 이제 다음을 생각해보자
특정 컬럼에 대하여 동일 집단에 대해서 각각 집계함수를 구할 수는 없을까?
직책별 평균급여액 등
SELECT job 직책, COUNT(empno) "인원수", AVG(sal) "평균급여액",
MAX(sal) "최고급여액", MIN(sal) "최저급여액",
SUM(sal) "급여합계"
FROM emp
GROUP BY job;
TAEHO>SELECT job"직책" ,COUNT(empno)"인원수",AVG(sal)"평균급여액",MAX(sal)"최고급여액",MIN(sal)"최저급여액",SUM(sal)"급여합계"
2 FROM emp
3 GROUP BY job;
직책 인원수 평균급여액 최고급여액 최저급여액 급여합계
--------- ---------- ---------- ---------- ---------- ----------
CLERK 4 1037.5 1300 800 4150
SALESMAN 4 1400 1600 1250 5600
PRESIDENT 1 5000 5000 5000 5000
MANAGER 4 2681.25 2975 2450 10725
ANALYST 2 3000 3000 3000 6000
----> 이번에는 부서별로 해보자
(각자 위문장을 참고하여 작성하기)
TAEHO>SELECT deptno 부서번호 ,COUNT(empno)"인원수",AVG(sal)"평균급여액",MAX(sal)"최고급여액",MIN(sal)"최저급여액",SUM(sal)"급여합계"
2 FROM emp
3 GROUP BY deptno;
부서번호 인원수 평균급여액 최고급여액 최저급여액 급여합계
---------- ---------- ---------- ---------- ---------- ----------
30 6 1566.66667 2850 950 9400
20 5 2175 3000 800 10875
10 4 2800 5000 1300 11200
---> 부서번호에 대하여 정렬까지 해보자
(각자 위문장을 참고하여 작성하기)
----> 두개 이상의 칼럼에 대하여 집계함수를 구할 수는 없을까?
SELECT DEPTNO 부서번호, JOB 직책 , COUNT(*) 인원수, ROUND(AVG(sal),1) 평균급여
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;
TAEHO>SELECT DEPTNO 부서번호 , JOB 직책, COUNT(*) 인원수 , ROUND(AVG(sal),1)평균급여
2 FROM EMP
3 GROUP BY DEPTNO,JOB
4 ORDER BY DEPTNO,JOB;
부서번호 직책 인원수 평균급여
---------- --------- ---------- ----------
10 CLERK 1 1300
10 MANAGER 2 2450
10 PRESIDENT 1 5000
20 ANALYST 2 3000
20 CLERK 2 950
20 MANAGER 1 2975
30 CLERK 1 950
30 MANAGER 1 2850
30 SALESMAN 4 1400
9 개의 행이 선택되었습니다.
**** 다음의 두 질의를 비교해보라*
최고급여가 3000보다 큰 부서에 대한 평균급여를 구하려면???
다음과 같이 하는 함정에 빠지지 말자!!!
SELECT DEPTNO, AVG(SAL)
FROM EMP
WHERE MAX(SAL) >=3000 // 그룹핑 하기 전에 모든 제약조건을 건다. 그룹함수로서 그룹핑이 된 다음에 주어지는 제약조건.
GROUP BY DEPTNO;
---->집계함수에 대한 제약조건은 HAVING절을 쓰자!
SELECT DEPTNO,MAX(SAL), AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MAX(SAL) >=3000;
TAEHO>SELECT DEPTNO, MAX(SAL),AVG(SAL)
2 FROM EMP
3 GROUP BY DEPTNO
4 HAVING MAX(SAL)>=3000;
DEPTNO MAX(SAL) AVG(SAL)
---------- ---------- ----------
20 3000 2175
10 5000 2800
TAEHO>
*****중첩 그룹함수
평균급여가 최고인 부서의 급여는?
SELECT MAX(AVG(SAL))
FROM EMP
GROUP BY DEPTNO;
MAX(AVG(SAL))
-------------
2800
**** 평균급여가 최고인 부서의 급여와 부서번호를 알고 싶다****
어찌할까?
SELECT DEPTNO, MAX(AVG(SAL))
FROM EMP
GROUP BY DEPTNO;
===>안된다! 어떻게 해결할까?
다음 서브쿼리를 배우고 해결해보자
+++++++++++++++++++++
서브쿼리 61쪽
+++++++++++++++++++++
질의시 내에 또 다른 질의식을 내포함
예를 들어 7369사원과 같은 부서에 근무하는 사원의 이름과 부서를 출력하려면
----> 7369사원이 근무하는 부서를 먼저 알아낸 뒤 이와 비교하여 추출
SELECT ename, deptno
FROM emp
WHERE deptno = (SELECT deptno
FROM emp
WHERE empno = 7369);
부서번호가 10번인 사원의 직책과 같은 직책을 가지는 사원의 이름과 직책을 구해보자
SELECT ename, job
FROM emp
WHERE job = (SELECT job
FROM emp
WHERE deptno = 10);
오류!!!
오류의 원인을 알아보자
우선 부질의만 실행해보자
SELECT job
FROM emp
WHERE deptno = 10;
결과 값이 여러개인데 이것들과 같은값을 찾을 수는 없다
다음을 수행해보자
SELECT ename, job
FROM emp
WHERE job = any (SELECT job
FROM emp
WHERE deptno = 10);
TAEHO>SELECT ename, deptno
2 FROM emp
3 WHERE deptno = (SELECT deptno
4 FROM emp
5 WHERE empno = 7369);
ENAME DEPTNO
---------- ----------
SMITH 20
JONES 20
SCOTT 20
ADAMS 20
FORD 20
TAEHO>SELECT ename, job
2 FROM emp
3 WHERE job = any(SELECT job
4 FROM emp
5 WHERE deptno = 10);
ENAME JOB
---------- ---------
LEE_HK MANAGER
CLARK MANAGER
BLAKE MANAGER
JONES MANAGER
KING PRESIDENT
MILLER CLERK
JAMES CLERK
ADAMS CLERK
SMITH CLERK
9 개의 행이 선택되었습니다.
============================
*** ANY와 ALL의 사용
============================
"영업사원이 아닌 직원 중에 영업사원들의 급여보다 적은 급여를 받는 직원 찾기
먼저 영업사원들의 급여를 알아보자
SELECT SAL
FROM EMP
WHERE JOB='SALESMAN';
any문의 사용
SELECT EMPNO, ENAME, JOB, sal
FROM EMP
WHERE SAL <any (SELECT SAL
FROM EMP
WHERE JOB='SALESMAN')
AND JOB <> 'SALESMAN';
-----> < any (1600, 1250, 1250, 1500) 의 의미는 어느 하나만이라도 해당 되는 것을 찾기
즉, 'SALESMAN' 사원들 중 "어느 한명"이라도 본인보다 높은 급여가 있는 사원 정보
따라서 최대 값 1600보다 작으면 만족
TAEHO>SELECT EMPNO, ENAME, JOB, sal
2 FROM EMP
3 WHERE SAL <any (SELECT SAL
4 FROM EMP
5 WHERE JOB='SALESMAN')
6 AND JOB<>'SALESMAN';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300
all문의 사용
SELECT EMPNO, ENAME, JOB, sal
FROM EMP
WHERE SAL <all (SELECT SAL
FROM EMP
WHERE JOB='SALESMAN')
AND JOB <> 'SALESMAN';
-----> < all (1600, 1250, 1250, 1500) 의 의미는 무든 값에 대하여 만족되는 것을 찾기
즉, "모든" 'SALESMAN' 사원들이 본인보다 높은 급여를 받는 사원 정보
따라서 최소 값 1250 보다 작아야 만족
TAEHO>SELECT EMPNO, ENAME, JOB, sal
2 FROM EMP
3 WHERE SAL<all(SELECT SAL
4 FROM EMP
5 WHERE JOB='SALESMAN')
6 AND JOB<>'SALESMAN';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7369 SMITH CLERK 800
TAEHO>
=============================
EXISTS 연산자의 사용 예제 64쪽
=============================
부하직원이 있는 사원의 정보, 즉, 다른 사람의 상사가 되는 사람 찾기
**** 우선 emp의 상황을 보자
SELECT empno, ename,mgr
FROM emp;
우선 7369가 다른 사람의 상사가 될 수 있는지 알아보자
SELECT empno
FROM emp
WHERE mgr= 7369;
---> 만약 하나라도 출력이 되면 7369는 다른 사람의 상사가 될 수 있음
TAEHO>SELECT empno, ename, mgr
2 FROM emp;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
7950 LEE_HK 7839
15 개의 행이 선택되었습니다.
TAEHO>SELECT empno
2 FROM emp
3 WHERE mgr=7369;
선택된 레코드가 없습니다.
이번에는 7902를 알아보자
SELECT empno
FROM emp
WHERE mgr= 7902;
TAEHO>SELECT empno
2 FROM emp
3 WHERE mgr=7902;
EMPNO
----------
7369
TAEHO>
모든 사원에 대하여 일괄적으로 조회할 수 있을까?
---> 나의 사원 번호가 다른 사람들의 mgr값과 같으면 나는 다른 사람의 상사가 된다
이렇게 해보자!
내 사원번호를 mgr 값으로 가지는 직원 찾기
SELECT empno, ename
FROM emp
WHERE (SELECT count (empno)
FROM emp
WHERE mgr= empno) >0;
원하는 결과인가?
TAEHO>SELECT EMPNO,ENAME
2 FROM EMP
3 WHERE (SELECT COUNT(EMPNO)
4 FROM EMP
5 WHERE MGR=EMPNO)>0;
선택된 레코드가 없습니다.
TAEHO>
=============================
Alias(Rename) 의 사용 42쪽
=============================
select e.ename 이름, e.sal 급여
from emp e;
테이블에 대해서도 별명을 사용할 수 있다!!!
다음과 같이 수정해보자!
SELECT empno, ename
FROM emp e
WHERE (SELECT count (empno)
FROM emp
WHERE mgr= e.empno) >0;
TAEHO>SELECT EMPNO, ENAME
2 FROM emp e
3 WHERE (SELECT count(empno)
4 FROM emp
5 WHERE mgr=e.empno)>0;
EMPNO ENAME
---------- ----------
7566 JONES
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7902 FORD
6 개의 행이 선택되었습니다.
TAEHO>
count가 0보다 크면? ----> 좀더 세렴된 함수?
SELECT empno, ename, sal
FROM emp e
WHERE EXISTS (SELECT empno
FROM emp
WHERE mgr= e.empno);
---> EXISTS함수는 질의식의 결과가 한 행이상 출력되면 참
TAEHO>SELECT empno, ename, sal
2 FROM emp e
3 WHERE EXISTS (SELECT empno
4 FROM emp
5 WHERE mgr=e.empno);
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
6 개의 행이 선택되었습니다.
TAEHO>
*****************************************************
실습 과제 :
1. 상사직원을 가지지 않는 직원(BOSS)를 찾아라
TAEHO>SELECT deptno , ename, sal
2 from emp
3 where empno in(select mgr from emp);
DEPTNO ENAME SAL
---------- ---------- ----------
20 JONES 2975
30 BLAKE 2850
10 CLARK 2450
20 SCOTT 3000
10 KING 5000
20 FORD 3000
6 개의 행이 선택되었습니다.
2. 업무추진비를 1,000 이하로 받는 직원의 이름과 업무추진비를 구하시오.
주의!! (다음 질의는 잘못되었다. 이를 바로 잡고 정확한 질의식을 작성할 것!!)
select ename, comm
from emp
where comm<=1000;
TAEHO>select ename, comm
2 from emp
3 where NVL(comm, 0)<=1000;
ENAME COMM
---------- ----------
SMITH
ALLEN 300
WARD 500
JONES
BLAKE
CLARK
SCOTT
KING
TURNER 0
ADAMS
JAMES
FORD
MILLER
13 개의 행이 선택되었습니다.
3. 급여 등급(테이블salgrade에 있음)이 1등급인 직원의 이름과 급여를 구하라
TAEHO>select ename, sal
2 from emp
3 where sal>=(select LOSAL
4 from salgrade
5 where grade=1)
6 and sal<=(select HISAL from salgrade
7 where grade=1);
ENAME SAL
---------- ----------
SMITH 800
ADAMS 1100
JAMES 950
TAEHO>
4. 평균급여가 최고인 부서의 번호와 평균 급여는?
TAEHO>SELECT DEPTNO, AVG(sal) FROM EMP GROUP BY DEPTNO HAVING AVG(sal) = (select MAX(AVG(sal))FROM EMP GROUP BY DEPTNO);
DEPTNO AVG(SAL)
---------- ----------
10 2800
TAEHO>
5. 부하직원이 있는 사원의 정보(부서번호, 이름, 급여)를 구하라
단, exists 함수를 쓰지 말고 in함수를 이용하라!
TAEHO>SELECT DEPTNO, ENAME, SAL
2 FROM EMP
3 WHERE EMPNO IN (SELECT MGR FROM EMP);
DEPTNO ENAME SAL
---------- ---------- ----------
20 JONES 2975
30 BLAKE 2850
10 CLARK 2450
20 SCOTT 3000
10 KING 5000
20 FORD 3000
6 개의 행이 선택되었습니다.
TAEHO>
*****************************************************
'_스타디' 카테고리의 다른 글
| 5주차 (1) | 2022.09.30 |
|---|---|
| 4주차-쌍비교와 상관관계질의 (1) | 2022.09.20 |
| 정처기실기_2회차 (0) | 2022.09.12 |
| 실무DB_2주차 (0) | 2022.09.06 |
| 실무DB_테이블초기화 (0) | 2022.09.06 |
