oracle - Create view by joining three tables in SQL -

i have 3 tables students, subjects, rank ,with data -

1) students [name(primary)]

name -------- alex greg 

2) subjects [id(primary)]:

id -------- 100 101 102 

3) rank [seq(primary), name, id, rank]

seq    name    id     rank ------ ------- ------ ------ 1      alex    100     2      greg    100     3      greg    101    b  

i want create view should display data as

name    id     rank ------- ------ ------ alex    100    alex    101    z alex    102    z greg    100    greg    101    b greg    102    z 

so, every student , every subject, view should display rank if present in rank table, else replace null 'z'.

i'm newbie sql. in forming query appreciated!

cross join student , subject left outer join result rank ranks (student, subject) combination. selecting column nvl or coalesce replace null 'z'.

select st.name,        su.id,        nvl(ra.rank,'z')           rank, --coalesce(ra.rank,'z') rank   student st        cross join subject su        left outer join rank ra                     on ra.name = st.name                        , ra.id = su.id order  st.name,su.id  

note : order can removed above query if don't need.

fiddle