[오라클] 보너스구하기 nvl case, decodem groupby having

보너스가 0이면 보너스없음. 널이면 널임, 아니면 보너스 : 보너스가 나오게 하여라 세가지방법

SELECT eno,
       ename,
       CASE nvl(comm,-1)
           WHEN 0 THEN '보너스없음'
           ----왜안돼냐
           when -1 then '널임'
           ELSE '보너스 :' || comm
           END AS com_txt
FROM emp;

SELECT eno,
       ename,
       nvl(CASE comm
           WHEN 0 THEN '보너스없음'
           ELSE '보너스 :' || comm
           END,'널') AS com_txt
FROM emp;


SELECT eno,
       ename,
       CASE
           WHEN comm = 0 THEN '보너스없음'
           when comm is null then '널 이다' ----왜안돼냐
           ELSE '보너스 :' || comm
           END AS com_txt
FROM emp;

SELECT eno,
       ename,
       nvl(CASE
               WHEN comm = 0 THEN '보너스없음'
               ELSE '보너스 :' || comm
               END,'널',-1) AS com_txt
FROM emp;

-- 그룹함수
-- 맥스
-- 전체 학생 중 최대 평점
select max(avr)
from student;

--전공별 최대 평점
select max(avr),MAJOR
from STUDENT
group by major;


--전공에 따른 학년별

select max(student.avr),student.MAJOR, student.SYEAR, (select sname from student where AVR = max(student.avr))
from student student
group by major,SYEAR;


--- 화학과중 학년별로 최대평점 조사. 최대평점이 2.5 이사잉어야 한다.
select st.sno, st.sname, maxavr.avr, st.MAJOR, st.SYEAR from student st join(

    select max(avr) as avr,MAJOR, SYEAR
    from student
    group by major, SYEAR
having major = '화학'
and max(avr)>2.5

) maxavr
on  st.avr = maxavr.avr
and st.major = maxavr.MAJOR
and st.syear = maxavr.syear;


--기말고사 성적 중 과목별로 가장 높은 점수 조회

select course.cname, course.cno, a.maxscore
from course course
join(


select max(RESULT) as maxscore, cno
from SCORE
group by cno) a
on a.cno = course.cno;