보너스가 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;
[자바]문제모음4 (0) | 2023.04.14 |
---|---|
[자바] 문제모음3 (3) | 2023.04.14 |
[자바]문제모음2 (0) | 2023.04.14 |
[자바] 문제모음1 (0) | 2023.04.14 |
[람다] 문자열 두개 받아서 비교하는거 (0) | 2023.04.04 |
댓글 영역