/* (i)*/
SELECT
    resort_name,
    (resort_street_address ||' '|| town_name ||' '||resort_pcode) as "RESORT ADDRESS",
    manager_name,
    manager_phone
    FROM resort r
    JOIN manager m on r.manager_id = m.manager_id
    JOIN town t on r.town_id=t.town_id
    WHERE r.resort_star_rating is null and r.RESORT_LIVEIN_MANAGER='Y'
    ORDER BY r.resort_pcode DESC;






/* (ii)*/
select r.resort_id,
        r.resort_name,
        r.resort_street_address,
        r.resort_pcode,
        t.town_name,
        t.town_state,
        b.booking_charge AS total_booking_charge
from resort r
join booking b on r.resort_id = b.resort_id
join town t on t.town_id = r.town_id
where booking_charge>(select avg(booking_charge) from booking)
GROUP BY
r.resort_id,r.resort_name,r.resort_street_address,r.resort_pcode,t.town_name,t.town_state,b.booking_charge;


/* (iii)*/
select
    re.review_id,
    g.guest_no,
    g.guest_name,
    r.resort_id,
    r.resort_name,
    re.review_comment,
    re.review_date as date_reviewed

from review re
    join guest g on re.guest_no=g.guest_no
    join resort r on re.resort_id=r.resort_id
    join booking b on re.guest_no=b.guest_no and re.resort_id=b.resort_id

where re.review_date <> b.booking_to
order by re.review_id;