SHARE
TWEET

Untitled

a guest Oct 23rd, 2019 89 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. /* (i)*/
  3. SELECT
  4.     resort_name,
  5.     (resort_street_address ||' '|| town_name ||' '||resort_pcode) as "RESORT ADDRESS",
  6.     manager_name,
  7.     manager_phone
  8.     FROM resort r  
  9.     JOIN manager m on r.manager_id = m.manager_id
  10.     JOIN town t on r.town_id=t.town_id
  11.     WHERE r.resort_star_rating is null and r.RESORT_LIVEIN_MANAGER='Y'
  12.     ORDER BY r.resort_pcode DESC;
  13.  
  14.  
  15.  
  16.  
  17.  
  18.  
  19. /* (ii)*/
  20. select r.resort_id,
  21.         r.resort_name,
  22.         r.resort_street_address,
  23.         r.resort_pcode,
  24.         t.town_name,
  25.         t.town_state,
  26.         b.booking_charge AS total_booking_charge  
  27. from resort r
  28. join booking b on r.resort_id = b.resort_id
  29. join town t on t.town_id = r.town_id
  30. where booking_charge>(select avg(booking_charge) from booking)
  31. GROUP BY
  32. r.resort_id,r.resort_name,r.resort_street_address,r.resort_pcode,t.town_name,t.town_state,b.booking_charge;
  33.  
  34.    
  35. /* (iii)*/
  36. select  
  37.     re.review_id,
  38.     g.guest_no,
  39.     g.guest_name,
  40.     r.resort_id,
  41.     r.resort_name,
  42.     re.review_comment,
  43.     re.review_date as date_reviewed
  44.  
  45. from review re
  46.     join guest g on re.guest_no=g.guest_no
  47.     join resort r on re.resort_id=r.resort_id
  48.     join booking b on re.guest_no=b.guest_no and re.resort_id=b.resort_id
  49.    
  50. where re.review_date <> b.booking_to
  51. order by re.review_id;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top