how to give comments to column data in oracle?

Question

Guest wants to display Resort Id, Name and Comments of the resort based on it's star rating.

Comments of the resort displays as follows

If rating is between 5.0 to 4.5 then display the comments as 'Excellent Resort'

If rating is between 4.4 to 4.0 then display the comments as 'Very Good Resort'

else display 'Good Resort' . Give alias name to this result as Comments.

Sort the result based on resort id.

Answer 1

If I read the model correctly, then

• calculate average rating
• join it with the resort table

---

with ratings as
  (select resort_id,
     round(avg(star_rating), 1) rating
   from resort
   group by resort_id
  )
select 
  r.resort_id,
  r.resortname,
  case when c.rating between 4.5 and 5.0 then 'Excellent'
       when c.rating between 4.0 and 4.4 then 'Very good'
       else 'Good'
  end comment
from resort r join ratings c on r.resort_id = c.resort_id