학점 테이블 연산 쿼리

--모든 학점이 A0인 학생을 검색
select sid
from (
  select sid, count(*)  tcnt, count(case when grade='A0' then 1 end ) a0_cnt
  from exam
  group by sid
)
where tcnt = a0_cnt;

--모든 학점의 평균값 구하는 sql 문
select  cid,
(((ap_cnt*4.5) +
(az_cnt*4.0) +
(bp_cnt*3.5) +
(bz_cnt*3.0) +
(cp_cnt*2.5) +
(cz_cnt*2.0) +
(d_cnt*1.5) )/ all_cnt) as avr
from (
select   cid,
  count(*) as all_cnt,
  count(case when grade='A+' then 1 end) ap_cnt,
  count(case when grade='A0' then 1 end) az_cnt,
  count(case when grade='B+' then 1 end) bp_cnt,
  count(case when grade='B0' then 1 end) bz_cnt,
  count(case when grade='C+' then 1 end) cp_cnt,
  count(case when grade='C0' then 1 end) cz_cnt,
  count(case when grade='D' then 1 end) d_cnt,
  count(case when grade='F' then 1 end) f_cnt
from exam
group by cid);