SQL practice ："Case when" & "join"

1. Case when

select table_name,

(CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END) ownerDesc

from all_tables;

2. join

create table test1(
reason varchar(10);
volume number(5,0);
amount number (5,0)
)
create table test2(
reason varchar(10);
volume number(5,0);
amount number (5,0)
)

--left join
select * from test1 aa ,test2 bb
where  aa.reason =bb.reason(+)

select * from test1 aa left join test2 bb on aa.reason=bb.reason

--further left join
select aa.reason,nvl(aa.volume,0)+ nvl(bb.volume,0) cnt,nvl(aa.amount,0)+ nvl(bb.amount,0) amt
from test1 aa left join test2 bb on aa.reason = bb.reason

--right join
select * from test1 aa ,test2 bb
where  aa.reason(+) =bb.reason

select * from test1 aa right join test2 bb on aa.reason = bb.reason

--full join
select * from test1 aa ,test2 bb   where   aa.reason=bb.reason(+)
union
select * from test1 aa ,test2 bb   where   aa.reason(+)=bb.reason;