decode의 활용법

with student as(
select '001' s_id, '김개똥' s_nm FROM dual
UNION ALL SELECT '002', '이말똥' FROM dual
UNION ALL SELECT '003', '박소똥' FROM dual
UNION ALL SELECT '004', '강닭똥' FROM dual
)
,course as(
select '001' c_id, 'Database' c_nm FROM dual
UNION ALL SELECT '002','Java' FROM dual
)
,study as(
select '001' s_id, '001' c_id, 1 chasu FROM dual
UNION ALL SELECT '001','001',3 FROM dual
UNION ALL SELECT '001','002',2 FROM dual
UNION ALL SELECT '002','001',1 FROM dual
UNION ALL SELECT '002','001',2 FROM dual
UNION ALL SELECT '002','001',3 FROM dual
UNION ALL SELECT '003','002',1 FROM dual
UNION ALL SELECT '003','002',2 FROM dual
UNION ALL SELECT '004','001',1 FROM dual
)
select a.s_id,a.s_nm,a.c_nm,
min(decode(chasu,1,'O')) as "1차",
min(decode(chasu,2,'O')) as "2차",
min(decode(chasu,3,'O')) as "3차",
nvl(sum(decode(chasu,1,1)),0)+nvl(sum(decode(chasu,2,1)),0)+nvl(sum(decode(chasu,3,1)),0) as "참여횟수"
from (
select a.s_id,a.s_nm,b.c_id,b.c_nm
from student a, course b
group by a.s_id,a.s_nm,b.c_id,b.c_nm
) a, study c
where a.s_id = c.s_id(+)
and a.c_id = c.c_id(+)
group by a.s_id,a.s_nm,a.c_id,a.c_nm
order by a.s_id,a.c_id;