그룹함수

Q1. spool 과 save의 차이점

1) spool 기능

작업한 내용 모두를 파일로 저장(인쇄목적, 책 교재 등을 위한 출력)

* SQL plus 명령어 : 오라클에서만 사용하는 명령어로 메모리(SQL Buffer) 기록

2) save 기능

save C:\~~.txt 저장 : 현재 SQL 명령어만 저장 -> 실행 목적

Q2. [과제] 2.오라클 SQL실습과제2 풀이_문제1

조건식 case ~end

형식) case 적용시킬 필드명(표현식) when 조건식 then 실행문장

                                                         when 조건식2 then 실행문장2

                                                         when 조건식3 then 실행문장3

                                                         ,,,, else 조건에 해당 안되는 실행n

          end

EX)

select empno, ename, job, sal, case job when 'ANALYST' then sal*1.1

when 'CLERK' then sal*1.15

when 'MANAGER' then sal*1.2

else sal

end "최종 금액"

from emp

order by sal desc;

출력)

QL> select empno, ename, job, sal, case job when 'ANALYST' then sal*1.1
2 when 'CLERK' then sal*1.15
3 when 'MANAGER' then sal*1.2
4 else sal
5 end "최종 금액"
6 from emp
7 order by sal desc;

EMPNO ENAME JOB SAL 최종 금액
--------- ---------- --------- ---------- ----------
7839 KING PRESIDENT 5000 5000
7902 FORD ANALYST 3000 3300
7788 SCOTT ANALYST 3000 3300
7566 JONES MANAGER 2975 3570
7698 BLAKE MANAGER 2850 3420
7782 CLARK MANAGER 2450 2940
7499 ALLEN SALESMAN 1600 1600
7844 TURNER SALESMAN 1500 1500
7934 MILLER CLERK 1300 1495
7521 WARD SALESMAN 1250 1250
7654 MARTIN SALESMAN 1250 1250

EMPNO ENAME JOB SAL 최종 금액
--------- ---------- --------- ---------- ----------
7876 ADAMS CLERK 1100 1265
7900 JAMES CLERK 950 1092.5
7369 SMITH SALES MAN 800 800

14 개의 행이 선택되었습니다.

Q3. [과제] 2.오라클 SQL실습과제2 풀이_문제2

<문제2>emp테이블에서 이름에 L자가 두개가 있고(and),

부서가30이거나 또는(or) 관리자(mgr)가 7782인 사원의 모든 정보(*)를 출력하는 select구문을 작성하시오.

1) 문자 L의 연속되는지 알 수 없을 경우

select * from emp

    where ename like '%L%L%' and (deptno=30 or mgr=7782);

2) 문자 L이 연속된다는 것을 알고 있는 경우

 select * from emp

    where ename like '%LL%' and (deptno=30 or mgr=7782);

---

Q4. 그룹함수의 성격

통계 처리 함수 [ 그룹 함수 ] :레코드 개수 처리 

1) count(*) : null값까지 계산

2) count(필드명) : 필드에 들어가 있는 개수(필드에 null은 배제, 개수에 포함되지 않음)

3) min(필드명) : 최소값

4) max(필드명) : 최대값

5) avg(필드명) : 평균값

6) sum(필드명) : 합계

EX)

select count(*), trunc(avg(sal)), max(sal), min(sal), to_char(sum(sal),'$999,999')

from emp;

COUNT(*) TRUNC(AVG(SAL)) MAX(SAL) MIN(SAL) TO_CHAR(S

----------     -------------                 ----------      ----------     ---------
14             2073                             5000         800         $29,025

EX2) 어떤 사원이 급여를 많이 받고 적게 받는지 알아보기(일반필드와 그룹함수 함께 사용하기)

select ename, count(*), trunc(avg(sal)), max(sal), min(sal), to_char(sum(sal),'$999,999')
*
1행에 오류:
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

-> 그룹함수를 select와 from 사이에 쓸 때, 기존의 필드와 함께 사용하지 못함

-> 일반 필드와 그룹함수를 같이 select ~ from 사이에 사용 가능한가?

형식) 일반필드와 그룹함수 함께 사용하기

select 필드,,,그룹함수 <-일반필드를 그룹함수처럼 묶어주어야 함

from 테이블명

where

group by 그룹을 지어줄 필드명

order by

출력) 
SQL> ed
file afiedt.buf(이)가 기록되었습니다

1 select ename, count(*), trunc(avg(sal)), max(sal), min(sal), to_char(sum(sal),
99')
2 from emp
3* group by ename
SQL> /

ENAME COUNT(*) TRUNC(AVG(SAL)) MAX(SAL) MIN(SAL) TO_CHAR(S
---------- ---------- --------------- ---------- ---------- ---------
ALLEN 1 1600 1600 1600 $1,600
JONES 1 2975 2975 2975 $2,975
FORD 1 3000 3000 3000 $3,000
CLARK 1 2450 2450 2450 $2,450
MILLER 1 1300 1300 1300 $1,300
SMITH 1 800 800 800 $800
WARD 1 1250 1250 1250 $1,250
MARTIN 1 1250 1250 1250 $1,250
SCOTT 1 3000 3000 3000 $3,000
TURNER 1 1500 1500 1500 $1,500
ADAMS 1 1100 1100 1100 $1,100

ENAME COUNT(*) TRUNC(AVG(SAL)) MAX(SAL) MIN(SAL) TO_CHAR(S
---------- ---------- --------------- ---------- ---------- ---------
BLAKE 1 2850 2850 2850 $2,850
KING 1 5000 5000 5000 $5,000
JAMES 1 950 950 950 $950

---

Q5. emp테이블에서 급여가 3000이상인 사원의 수(개수, count)를 구하시오.

1) select count(*) or count(ename)

from emp

where sal>=3000;

2) 급여 3000이상인 사원 목록

select ename, count(*) or count(ename)

from emp

where sal>=3000

group by ename;

SQL> ed
file afiedt.buf(이)가 기록되었습니다

1 select ename, count(ename)
2 from emp
3 where sal>=3000
4* group by ename
SQL> /

ENAME COUNT(ENAME)
---------- ------------
FORD 1
SCOTT 1
KING 1

3) 급여 3000이상 사원의 목록을 나타나되, 급여도 출력

SQL> ed
file afiedt.buf(이)가 기록되었습니다

1 select ename, count(ename), sal
2 from emp
3 where sal>=3000
4* group by ename, sal
SQL> /

ENAME COUNT(ENAME) SAL
---------- ------------ ----------
SCOTT 1 3000
KING 1 5000
FORD 1 3000

Q6. 문자, 데이터 그룹함수

* count(*) sum, min, max, avg(숫자와 관련된 함수)

     -> 숫자화된 데이터만 사용이 가능하다 편견을 가질 수 있음

    but 날짜, 문자 데이터도 사용이 가능하다.

EX) select min(ename), max(ename), min(hiredate), max(hiredate), min(sal), max(sal)

from emp;

MIN(ENAME) MAX(ENAME) MIN(HIRE MAX(HIRE MIN(SAL) MAX(SAL)
---------- ---------- -------- -------- ---------- ----------
ADAMS WARD 80/12/17 87/05/23 800 5000

1) 문자 데이터의 최소값, 최대값 : 영문자(A~Z) 순서로 따짐. A에 가까우면 적다, Z에 가까우면 크다.

2) 날짜 데이터 최소값, 최대값 : 날짜가 현재에서 오래되면 최소, 날짜가 현재와 가까우면 최대

[ HAVING 조건 ]

Q7. 부서별(그룹화)로 사원의 수가 4명 이상인 사원의 정보를 찾아서 출력하되, 부서번호, 급여의 합계를 구하시오.

EX) 일반 조건식 사용했을 경우

select deptno, sum(sal) "급여합계"

from emp

where count(*) >=4

group by detpno;

SQL> select deptno, sum(sal) "급여합계"
2 from emp
3 where count(*) >=4
4 group by detpno;
where count(*) >=4
*
3행에 오류:
ORA-00934: 그룹 함수는 허가되지 않습니다

-> where 조건식에서 그룹함수를 사용하는 구문을 사용불가

[ 조건식의 종류 ]

1) 일반 조건식 : where 조건식(일반적인 검색(default))

2) 그룹의 조건식 : Having 조건식

* 구문 순서

select

from

where

group by

having

order by

EX2) Having 조건식 사용하기

1 select deptno, sum(sal) "급여합계"
2 from emp
3 group by deptno
4* having count(*)>=4
SQL> /

DEPTNO 급여합계
---------- ----------
30 9400
20 10875

---

Q8. [ 그룹함수의 중첩] : 2단계까지

1) 부서별로 평균값(1) 중에서도 최대 평균 급여(2)를 구하시오 -> max(avg(sal))

2) 부서별로 합계 중에서도 최대 급여(인건비)? max(sum(sal))

3) 부서별로 합계 중, 최소 급여? min(sum(sal))

select max(avg(sal)), max(sum(sal)), min(min(sal)), max(max(sal))

from emp

group by deptno;

MAX(AVG(SAL)) MAX(SUM(SAL)) MIN(MIN(SAL)) MAX(MAX(SAL))
------------- ------------- ------------- -------------
2916.66667 10875 800 5000

Q9. emp테이블에서 업무가 MANAGER가 아닌(where) 사원에 대한 부서별(group by)로 급여의 합이 2500 이상인(having) 부서의 정보(select)를 출력하시오. (단, 부서번호, 급여의 합계를 급여 합계가 높은 순으로 출력)

1) 부정 연산자 사용

select deptno, sum(sal) "부서별 급여합계"

from emp

where job <>upper('manager')

group by deptno

having sum(sal) >=2500

order by sum(sal) desc;

DEPTNO 부서별 급여합계
---------- ---------------
20 7900
30 6550
10 6300

2) not 논리 사용

select deptno, sum(sal) "부서별 급여합계"

from emp

where not job=upper('manager')

group by deptno

having sum(sal) >=2500

order by sum(sal) desc;

* ERROR:
ORA-01740: 이중 인용부를 지정해 주십시오 : " 사용하라는 의미

-> 하나의 테이블을 활용하여 검색하는 모든 방법을 다 배움

실무 : 한 개 이상의 테이블을 검색

ex) emp 테이블에 부서번호는 있으나 부서명은 없음, 부서명을 찾으려면 한개 이상의 테이블을 검색

-> 얼마나 효율적으로 데이터를 저장하는지가 관점(테이블을 생성 -> 설계 목적)

모델링 -> exerd(설계)

---