Practice: three SELECT problems

select p.player_name, t.team_code, p.bowling_style
from   cr_players p
join   cr_teams   t on t.team_id = p.team_id
where  p.bowling_style like '%spin%'
order  by p.bowling_style, p.player_name;

select p.player_name, t.team_code, p.bowling_style
from   cr_players p
join   cr_teams   t on t.team_id = p.team_id
where  instr(p.bowling_style, 'spin') > 0
order  by p.bowling_style, p.player_name;

select match_id, season,
       case win_type when 'TIE' then 'tie' else lower(win_type) end as result,
       nvl(to_char(win_margin), 'tie')                               as margin
from   cr_matches
where  (win_type = 'RUNS'    and win_margin < 10)
or     (win_type = 'WICKETS' and win_margin <= 2)
or      win_type = 'TIE'
order  by match_id;

select match_id, season,
       decode(win_type, 'TIE', 'tie', lower(win_type))   as result,
       coalesce(to_char(win_margin), 'tie')              as margin
from   cr_matches
where  win_type = 'TIE'
or     win_margin < case win_type when 'RUNS' then 10 when 'WICKETS' then 3 end
order  by match_id;

NVL(TO_CHAR(win_margin), 'tie'): the margin is a number, so it's turned into text first; NVL needs both arguments to be the same type.

select player_name, extract(year from born) as birth_year
from   cr_players
where  born >= date '2000-01-01'
order  by born desc;

select player_name, to_number(to_char(born, 'YYYY')) as birth_year
from   cr_players
where  to_char(born, 'YYYY') >= '2000'
order  by born desc;

The first way is better: born >= date '2000-01-01' can use an index on born; wrapping the column in TO_CHAR usually can't. Same rows today, different speed on a big table.