디비1 - 4월6일월요일 - group 함수

문제를 내고 코드로 작성하는거

4월20일 월요일

5장. Group 함수

*함수의 종류

1.단일행 함수

2.복수행 함수 (=group함수)

*Group 함수의 종류

1.max 최대값 출력

2.min최소값 출력

3.agv 평균값 출력

4.count 건수를 세는 함수

5.sum 토탈값 출력

문제63.

최대월급을 출력하시오.

select max(sal)

from emp;

문제64.직업이 SALESMAN인 사원들 중에서의 최대월급을 출력하시오

select job,max(sal)

from emp

where job='SLAESMAN';

답이 아니네...

select max(sal)

from emp

where job='SLASMAN';

문제65. 20번 부서번호인 사원들중에서의 최대월급을 출력하시오

select max(sal)

from emp

where deptno = 20;

문제 66.위의 sql을 수정해서 아래와 같이 결과가 출력되게 하시오.

deptno   max(sal)

------    ------

20           3000

select deptno(20),max(sal)

from emp

where deptno = 20;

어쨌던 deptno 쓰게 될텐데, 왜 오류가 뜨냐? 부서번호는 여러개 나올려고

하는데 맥스값은 하나이기 때문에..에러...그래서 부서번호가 하나만 나오도록 

그룹핑을 해줘야되.

select deptno,max(sal)

from emp

where deptno = 20

group by deptno;

*group by절의 역할 ?

grouping을 하는 역할

문제67. 아래의 결과를 출력하시오.

select max(sal)

from emp

where job = 'SLAESMAN';

job    MAX(SAL)

---- ---------

SALESMAN    1600

4  select job,max(sal)

1  from emp

2  where job = 'SLAESMAN'

3  group by job;

안나오는데..?

문제68.위의 결과에서 where 절을 뺴면 어떠한 결과과 출력될까?

 3 select job,max(sal)

 1 from emp  

 2 group by job;

이렇게 하면 job별로 최대값이 나오게 되는 것.

문제69.부서번호,부서번호별 최대월급을 출력하시오.

select deptno,max(sal)

from emp

group by deptno;

문제70. 위의 결과를 다시 출력하는데 부서번호별 최대월급이 높은것부터 출력하시오. 

select deptno,max(sal)

from emp

group by deptno

order by asc;

이게 아니고...

select deptno,max(sal)

from emp

group by deptno

order by max(sal) desc;

문제71.직업,직업별 최소월급을 출력하는데 직업이 SALESMAN은 제외하고 출력하시오.

select job,max(sal)

from emp

where job =! 'SALESMAN'

group by job;

이게 아니고 != 이거다.....

select job,max(sal)

from emp

where job != 'SALESMAN'

group by job;

예를들면 중간고사 문제 이렇게 나온다.

문제72.커미션이 null인 사원들 중에서의 최소월급을 출력하시오.

select min(sal)

from emp

where comm is null;

select min(sal)

from emp

where comm = null;

이렇게 하면 틀린거다.

(시험 오픈북 아니고 컴퓨터 돌릴 수 없다.)

이런게 시험 문제다.

문제73.직업,직업별 최소월급을 출력하는데 직업별 최소월급이 2000이상인것만 출력하시오.

select job,min(sal)

from emp

where min(sal) >= 2000

group by job;

이거 에러나네.... 왜지??? 그리고또.. where이랑 group by 순서 바꿔쓰지마라

어떤 에러가 떴냐면 group function is not allowed here 

그니까 그룹함수랑 조건절을 같이 못쓴다는 얘기다.

*그룹함수를 이용한 검색조건은 where 절이 아니라 having절을 사용해야 한다.

select job,min(sal)

from emp

group by job

having min(sal) >= 2000;

*select 문의 6가지절

5 select

1 from

2 where

3 group by

4 having

5 order by

문제74.직업,직업별 최대월급을 출력하는데 직업이 SALESMAN은 제외하고 출력하고

직업별 최대월급이 3000이상인것만 출력하고 그리고 직업별 최대월급이 높은것부터 출력을 하는데 컬럼명이 한글로 직업,최대월급 이렇게 나오게 하시오.

select job "직업" ,max(sal)  "최대월급"

from emp

where job != 'SALESMAN'

group by job

having max(sal) >= 3000

order by max(sal) desc;

*컬럼명 

select job as 직업,max(sal) as 최대값

as 생략해도 되. 그리고 "직업" 이렇게 안해도되.

문제75.사원 테이블에서 평균월급을 출력하시오.소숫점이후는 안나오게 반올림하시오.

select round(avg(sal) )

from emp;

이게 중요하대!

문제76.커미션의 평균값을 출력하시오.

select avg(comm)

from emp;

결과과 550 나오는데

1400,300,500,0 인데 이게 다 더해서 14로 나누는게 아니라

4로 나눈 결과가 나온다.

select (1400 + 300 + 500 + 0 ) / 4

from dual;

어 근데, 내가 우려했던거는 이거 였거든 comm 할때 저번시간에 comm에 널을 0으로 만들어주는 함수를 썼던걸로 기억해서 그걸 할려고 했는데, 근데 group함수는 null값을 무시해서 출력하는게 특징

*group 함수의 특징

null값을 무시한다.

문제77. 커미션의 평균값을 출력하는데 4로 나누는게 아니라 14로 나누겠금 출력하시오

select avg(NVL(comm,0))

from emp;

문제78.아래의 2개의 sql중 어떠한 sql이 더 효율적인 sql 인가?

select sum(comm) from emp;

select sum(nvl(comm,0) ) from emp;

결과는 똑같이 나온대.근데 위에께 더 효율적이다! 왜?

그룹함수는 null 값을 무시해.

근데 nvl 쓰면 자꾸 쓸데없이 0을 더하니깐

굉장히 중요한 문제다.중간고사에 답이 나오면 위에꺼라고 답을 하면 된다.

문제79.부서번호,부서번호별 토탈월급을 출력하는데 부서번호별 토탈월급이 3000이상인것만 출력하시오.

select deptno,sum(sal)

from emp

group by deptno

having sum(sal) >= 3000;

문제80.입사한 년도(4자리) 입사한 년도별 토탈월급을 출력하는데

입사한 년도별 토탈월급이 높은것부터 출력하시오.

select to_char(hiredate,'YYYY'),sum(sal)

from emp

group by to_char(hiredate,'YYYY')

order by desc;

이게 아니고.........

select to_char(hiredate,'RRRR'),sum(sal)

from emp

group by to_char(hiredate,'RRRR')

order by desc;

이거 안나오네..? 다시해보기!!!

아.. order by 뒤에 뭘 할건지해야지...

order by sum(sal) desc;

count 함수 -->> 건수를 세는 함수

문제 81.사원 테이블의 전체 사원수가 몇명인지 확인하시오

select count(empno)

from emp;

근데 이렇게를 더 많이 써

select (*)

from emp;

@ *는 모든 컬럼을 다 가리키는거다. empno,ename,다~

*랑 사원번호 차이알겠지? 근데 이게 성능차이가 없다.성능의 여부가 아님

왜냐하면 회사의 컬럼수가 많아봤자 5000개 차이가 없대.

문제82. 직업,직업별 인원수를 출력하시오.

select count(job)

from emp

group by job;

문제83. 직업,직업별 토탈월급을 출력하는데 직업별 토탈월급을 출력할 때 천단위가 표시되게 하시오.

select job,to_char(sum(sal),'999,999')

from emp

group by job;