[ORACLE] 2022.08.17 - 복수행 함수, 그룹함수

1. 학과별로 평균 몸무게와 학생 수를 출력하되 평균 몸무게의 내림차순으로 정렬하여라.

[결과]

학과코드 평균몸무게 학생수

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

    201            67             6

    102            64.25        4

    202            62.5          2

    101            60             4

    301            60             2

   103             51.5          2

select deptno1 학과코드, avg(weight) 평균몸무게, count(*)학생수 from student
group by deptno1
order by 평균몸무게 desc

2. 학생테이블의 birthday를 기준으로 월별로 태어난 인원수 출력하기

[결과]

합계 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월

---— ---- ----- ----- ----- ----- ---- ----  ----- ----- ------ ------ -------

  20    3    3     2    2     0     1    0     2     2      2      1       2

select count(*) 합계,
    sum(decode(substr(birthday,4,2),'01',1,0)) "1월",
    sum(decode(substr(birthday,4,2),'02',1,0)) "2월",
    sum(decode(substr(birthday,4,2),'03',1,0)) "3월",
    sum(decode(substr(birthday,4,2),'04',1,0)) "4월",
    sum(decode(substr(birthday,4,2),'05',1,0)) "5월",
    sum(decode(substr(birthday,4,2),'06',1,0)) "6월",
    sum(decode(substr(birthday,4,2),'07',1,0)) "7월",
    sum(decode(substr(birthday,4,2),'08',1,0)) "8월",
    sum(decode(substr(birthday,4,2),'09',1,0)) "9월",
    sum(decode(substr(birthday,4,2),'10',1,0)) "10월",
    sum(decode(substr(birthday,4,2),'11',1,0)) "11월",
    sum(decode(substr(birthday,4,2),'12',1,0)) "12월"
from student

select count(*) 합계,
    count(decode(substr(birthday,4,2),'01',1)) "1월",
    count(decode(substr(birthday,4,2),'02',1)) "2월",
    count(decode(substr(birthday,4,2),'03',1)) "3월",
    count(decode(substr(birthday,4,2),'04',1)) "4월",
    count(decode(substr(birthday,4,2),'05',1)) "5월",
    count(decode(substr(birthday,4,2),'06',1)) "6월",
    count(decode(substr(birthday,4,2),'07',1)) "7월",
    count(decode(substr(birthday,4,2),'08',1)) "8월",
    count(decode(substr(birthday,4,2),'09',1)) "9월",
    count(decode(substr(birthday,4,2),'10',1)) "10월",
    count(decode(substr(birthday,4,2),'11',1)) "11월",
    count(decode(substr(birthday,4,2),'12',1)) "12월"
from student
--카운트를 쓰고, 숫자가 맞는경우 1, 
--아닌경우 0을 넣지 않았음. 카운트는 null인 값을 계산하지않는다.

3. 학과별 교수 수가 2명 이하인 학과 번호, 교수 수를 출력하여라 

 [결과]

DEPTNO COUNT(*)

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

   201         2

   301         2

   202         2

   203         1

select nvl(deptno,0) 학과번호,
       count(*) 교수인원수
from professor
group by deptno 
having count(*) <= 2

4. 직급별로 평균 급여가 320보다 크면 '우수', 작거나 같으면 '보통'을 출력하여라

[결과]

POSITION    급여

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

   정교수        우수

   전임강사     보통

   조교수        우수

select position 직급, 
       case when avg(pay) > 320 then '우수'
            when avg(pay) <= 320 then '보통'
       end 급여 
from professor
group by position

5. 교수테이블에서 부서중 최대인원을 가진 부서의 인원수와 최소인원을 가진 부서의 인원수 출력하기

[결과]

최대인원   최소인원

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

       3                 1

select max(count(profno)) 최대인원,
       min(count(profno)) 최소인원
from professor
group by deptno

6. 교수테이블에서 평균 급여가 350이상인 부서의 부서코드, 평균급여, 급여합계를 출력하기

[결과]

부서코드   평균급여      급여합계

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

     102       363.333333   1090

     201       450                 900

     101       400                 1200

      203      500                  500

      103      383.333333    1150

select deptno 부서코드, 
       trunc(avg(pay),6) 평균급여, 
       sum(pay) 급여합계
from professor
group by deptno

7. 4학년 학생의 이름 학과번호, 학과이름 출력하기

select s.name 이름,
       s.deptno1 학과번호,
       d.dname 학과이름
from student s,department d
where s.deptno1= d.deptno 
    and grade = 4

--Ansi
select s.name 이름,
       s.deptno1 학과번호,
       d.dname 학과이름
from student s join department d
on s.deptno1 = d.deptno 
where grade = 4--where 안쓰고 and 써도 실행가능

8. 오나라 학생의 이름, 학과코드1,학과이름,학과위치 출력하기

select s.name 이름,
       s.deptno1 학과번호,
       d.dname 학과이름,
       d.build 학과위치
from student s,department d
where s.deptno1 = d.deptno 
    and s.name = '오나라'

--Ansi
select s.name 이름,
       s.deptno1 학과번호,
       d.dname 학과이름,
       d.build 학과위치
from student s join department d
on s.deptno1 = d.deptno 
where s.name = '오나라'

9. 학번과 학생 이름과 소속학과이름을 학생 이름순으로 정렬하여 출력

select s.studno 학번,
       s.name 이름,
       d.dname 학과이름,  
       s.deptno1 학과번호
from student s,department d
where s.deptno1= d.deptno
order by s.name

10. 교수별로 교수 이름과 지도 학생 수를 출력하기.

select p.name 교수이름,
       count(s.profno) 지도학생수
from student s,professor p
where s.profno = p.profno  
group by p.name

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

select p.name 교수이름,
       count(p.profno) 지도학생수
from student s,professor p
where s.profno = p.profno  
group by p.name

11. 성이 김씨인 학생들의 이름, 학과이름 학과위치 출력하기

select s.name 이름,
       d.dname 학과이름,  
       d.build 학과위치
from student s,department d
where s.deptno1= d.deptno and s.name like '김%'

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

select s.name 이름,
       d.dname 학과이름,  
       d.build 학과위치
from student s,department d
where s.deptno1= d.deptno and substr(s.name,1,1) = '김'