<그룹 쿼리>
1. 그룹핑(그룹을 짓는다) : group by 절을 사용
(문제) emp 테이블에서 부서코드 단위로 부서코드 조회하기
그룹바이로 한줄로 변경 할 수 있다.
select count(*) , sum(sal), avg(sal) from emp
WHERE DEPTNO =10;
select count(*) , sum(sal), avg(sal) from emp
WHERE DEPTNO =20;
select count(*) , sum(sal), avg(sal) from emp
WHERE DEPTNO =30;
select deptno, count(*) , sum(sal), avg(sal) from emp
group by deptno;
=>출력결과
30 6 2040 340
20 5 2007 401.4
10 3 1475 491.666666666666666666666666666666666667
(문제) 학과별로 교수들의 평균 급여를 출력하시오.
select deptno, round(avg(pay)) from professor group by deptno;
=>출력
102 364
201 450
301 255
101 400
202 285
203 500
103 384
(문제) 학과별, 직급별로 교수들이 평균 급여를 출력하시오.
select deptno, position, avg(pay) from professor group by deptno, position;
|
103 전임강사 290 |
*** 조인과 그룹바이 *****
select deptno1, DNAME , count(*) from
STUDENT s inner join DEPARTMENT d
on s.DEPTNO1 =d.DEPTNO
group by deptno1, DNAME;
|
=>출력 결과 301 문헌정보학과 2 |
-- 학과별 직급별 평균급여
SELECT p.deptno, dname, position, round(avg(pay), 1)
from PROFESSOR p, DEPARTMENT d
where p.DEPTNO = d.DEPTNO
GROUP by p.DEPTNO , dname, position
ORDER by p.DEPTNO;
|
=>출력 101 컴퓨터공학과 전임강사 270 |
2. group by절과 having절
1) group by절
가. 특정 컬럼값이나 표현식을 단위로 집계성 데이터를 보기 위해서
집계함수와 함께 사용
나. select 절에 오는 컬럼 또는 값은 모두 group by 절에 명시행 함
(단, 집계함수는 제외)
다. groub by 절에는 한개 이상의 컬럼 또는 값을 가짐
라. order by 절은 group by 절 다음에 위치함
(주의) order by 절에 오는 컬럼 또는 값은 group by 절에 명시한 값 이외의
값은 사용할 수 없음.
마. where 절은 group by 절 앞에 위치함
2) having절
가. where 조건 이외에 집계함수의 결과로 조건을 주고자 할 때 사용.
group by 절과 함께 사용함
나. 일반적으로 집계함수난 상수가 사용된 조건을 명시하나 group by 절에 명시된 컬럼도 조건으로 사용 가능함
다. where절과 having절은 동시에 사용 가능
(문제) 교수의 평균 급여가 450 이상인 학과와 평균급여를 출력하시오.
-- where : 요약 전 (그룹바이 전)
-- having : 요약 후 (그룹아이 후)
-- where 절에는 집계함수를 사용할 수 없음.
-- from => where => group by => having => select => order by
select dname, avg(pay) from professor p, department d
where p.deptno=d.deptno group by dname having avg(pay) >= 450
orcer by avg(pay) desc ;
=> 출력 결과
전자공학과 450
화학공학과 500
<<실습문제>>
1. 교수 중에서 급여총액(급여+보너스)이 가장 높은 교수와 가장 낮은 교수,
급여총액의 평균금액을 출력하시오.(학과별 그룹핑)
-- nvl( A, B) A 가 null 이면 B, null 이 아니면 A
select d.DEPTNO, d.dname, max(pay+NVL(bonus, 0)), min(pay+NVL(bonus, 0)), round(avg(pay+ NVL(bonus, 0))) 평균금액
from PROFESSOR p inner join DEPARTMENT d
on p.DEPTNO =d.DEPTNO
GROUP by d.DEPTNO , d.DNAME ORDER by 평균금액 desc
;
|
203 화학공학과 580 580 580 |
2. Student 테이블의 birthday 컬럼을 사용하여 월별로 태어난 인원수를 출력 하시오.
-- decode(A,B,C,D) A=B => C A<>B =>D
select
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '01', 0)) "1월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '02', 0)) "2월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '03', 0)) "3월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '04', 0)) "4월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '05', 0)) "5월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '06', 0)) "6월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '07', 0)) "7월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '08', 0)) "8월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '09', 0)) "9월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '10', 0)) "10월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '11', 0)) "11월" ,
count(decode(TO_CHAR(BIRTHDAY, 'MM'), '12', 0)) "12월"
from student ;
|
=>출력 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월 |
select (
select count(*) from student
where to_char(birthday, 'MM')='01'
) "1월",
(
select count(*) from student
where to_char(birthday, 'MM')='02'
) "2월"
,
(
select count(*) from student
where to_char(birthday, 'MM')='03'
) "3월",
(
select count(*) from student
where to_char(birthday, 'MM')='04'
) "4월",
(
select count(*) from student
where to_char(birthday, 'MM')='05'
) "5월",
(
select count(*) from student
where to_char(birthday, 'MM')='06'
) "6월"
,
(
select count(*) from student
where to_char(birthday, 'MM')='07'
) "7월",
(
select count(*) from student
where to_char(birthday, 'MM')='08'
) "8월"
,
(
select count(*) from student
where to_char(birthday, 'MM')='09'
) "9월"
,
(
select count(*) from student
where to_char(birthday, 'MM')='10'
) "10월",
(
select count(*) from student
where to_char(birthday, 'MM')='11'
) "11월"
,
(
select count(*) from student
where to_char(birthday, 'MM')='12'
) "12월"
from dual;
=>출력
1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
3 3 2 2 0 1 0 2 2 2 1 2
댓글 ( 4)
댓글 남기기