SQL_QUERIES_2025_09_21.sql

--  #1

DROP VIEW IF EXISTS VIEW_01_HOST_ALL ;
CREATE VIEW IF NOT EXISTS  VIEW_01_HOST_ALL  AS

SELECT 

HostAccount.host_id,
HostAccount.host_profile_name,
HostAccount.host_profile_data,
HostAccount.account_status ,
HostAccount.ts_created,

-- HOST DATA FROM PARENT GUEST ACCOUNT
GuestAccount.guest_id ,
GuestAccount.cust_number,
GuestAccount.first_name, 
GuestAccount.last_name,
GuestAccount.email ,
GuestAccount.phone ,
GuestAccount.guest_profile_data ,
GuestAccount.identity_verified,
GuestAccount.avail_addresses ,

-- HOST ADDRESS DATA FROM BRIDGING TABLE 
GuestID_AddrID_AddrType.guest_address_ID ,
GuestID_AddrID_AddrType.is_resid_address  ,
GuestID_AddrID_AddrType.is_post_address  , 
GuestID_AddrID_AddrType.is_bill_address  ,

-- ADDRSS INFORMAION
AddressIT.address_id,
AddressIT.street,
AddressIT.unit_identifier,
AddressIT.postal_code,
AddressIT.city,

-- BASIC LISTING INFORMAION
Listing.listing_id ,
Listing.listing_name 

FROM HostAccount 
LEFT OUTER JOIN GuestAccount ON HostAccount.guest_id = GuestAccount.guest_id
LEFT OUTER JOIN GuestID_AddrID_AddrType  ON GuestAccount.guest_id = GuestID_AddrID_AddrType.guest_id
LEFT OUTER JOIN AddressIT  ON GuestID_AddrID_AddrType.address_id  = AddressIT.address_id
LEFT OUTER JOIN Listing ON HostAccount.host_id  = Listing.host_id
;

SELECT * FROM  VIEW_01_HOST_ALL   ;



-- #2

DROP VIEW IF EXISTS VIEW_02_REV_ON_HOST ;
CREATE VIEW IF NOT EXISTS  VIEW_02_REV_ON_HOST AS

SELECT 
Review_On_Host.review_on_host_id, 
-- Review_On_Host.booking_id,

Booking.booking_id,
Booking.stay_success_status,
Booking.guest_id,

-- GUEST PROFILE NAME FROM GuestAccount
GA1.guest_profile_name,

Listing.listing_id,
Listing.listing_name,
Review_On_Host.num_stars_listing ,

Listing.host_id,
Review_On_Host.num_stars_host  ,

-- HOST ACCOUNT PROFILE NAME 
HostAccount.host_profile_name,

--  PARENT GUEST ACCOUT OF HOST 
GA2.guest_id AS host_guest_id 


FROM Review_On_Host
LEFT OUTER JOIN Booking ON Review_On_Host.booking_id = Booking.booking_id
LEFT OUTER JOIN GuestAccount AS GA1 ON  Booking.guest_id = GA1.guest_id
LEFT OUTER JOIN Listing ON Booking.listing_id = Listing.listing_id
LEFT OUTER JOIN HostAccount ON Listing.host_id = HostAccount.host_id
LEFT OUTER JOIN GuestAccount AS GA2 ON HostAccount.guest_id = GA2.guest_id 
;

SELECT * FROM VIEW_02_REV_ON_HOST;



-- #3

DROP VIEW IF EXISTS VIEW_03_SEARCH_ALL_DATA;
CREATE VIEW IF NOT EXISTS  VIEW_03_SEARCH_ALL_DATA  AS

SELECT

-- LISTING DATA 
Listing.listing_id,
Listing.listing_name,
Listing.accomod_type,
Listing.listing_descript,
Listing.num_2bed_rooms,
Listing.num_1bed_rooms,
Listing.total_accomm_size_qm,
Listing.foot_path_beach_km,
Listing.foot_path_shop_km,
Listing.foot_path_trainst_km,
Listing.listing_status,
Listing.ts_created,

-- DATA FROM Availability_And_Price TABLE
Availability_And_Price.avail_and_price_id,
Availability_And_Price.start_date,
Availability_And_Price.end_date,
Availability_And_Price.avail_status ,
Availability_And_Price.base_price_day ,

-- RELEVANT HOST ACCOUNT DATA
HostAccount.host_id,
HostAccount.host_profile_name,
HostAccount.host_profile_data,
HostAccount.guest_id

FROM Listing 
LEFT OUTER JOIN Availability_And_Price ON Listing.listing_id = Availability_And_Price.listing_id   
LEFT OUTER JOIN HostAccount ON Listing.host_id = HostAccount.host_id
;

SELECT * FROM VIEW_03_SEARCH_ALL_DATA ;



-- #4

DROP VIEW IF EXISTS VIEW_04_SEARCH_MY;
CREATE VIEW IF NOT EXISTS  VIEW_04_SEARCH_MY AS

SELECT * FROM VIEW_03_SEARCH_ALL_DATA

WHERE 

( start_date >= '2025-09-01' )
AND (avail_status = 'AVAIL' ) 

AND ( accomod_type LIKE '%APARTM%'  )
AND (num_2bed_rooms >=2 )

AND 
(
( (listing_descript LIKE '%Anzio%' )  AND  (listing_descript LIKE '%Centre%' ) )
OR 
( ( listing_descript LIKE '%Nettuno%' ) AND   (listing_descript LIKE '%Centre%' ) )
OR 
( ( listing_descript LIKE '%Nettuno%' ) AND  (listing_descript LIKE '%Borghese%' )  )
)
;

SELECT * FROM VIEW_04_SEARCH_MY;


-- #5

DROP VIEW IF EXISTS VIEW_05_SEARCH_GEN  ;
CREATE VIEW IF NOT EXISTS  VIEW_05_SEARCH_GEN   AS

SELECT * FROM  VIEW_03_SEARCH_ALL_DATA  

WHERE 

(start_date >= '2025-09-01' ) 
AND (avail_status = 'AVAIL' ) 

AND (accomod_type LIKE '%APARTM%' ) 
AND (num_2bed_rooms >=1 )
AND (total_accomm_size_qm >= 56.0 )
AND (foot_path_beach_km <= 1.0 )
AND ( foot_path_trainst_km <= 1.0 )

AND 
(
( ( listing_descript LIKE '%Sud %' ) OR (listing_descript LIKE '%laziale%' ) )
)
;

SELECT * FROM VIEW_05_SEARCH_GEN ;



-- #6

 DROP VIEW IF EXISTS VIEW_06_PAYMENT ;
CREATE VIEW IF NOT EXISTS  VIEW_06_PAYMENT AS

SELECT 

Payment.payment_id ,	
Payment.receipt_id ,
Payment.amount_paid ,
Payment.currency ,

Payment.payment_type ,
Payment.payment_method ,
Payment.transaction_id ,
Payment.ts_paid,

Booking.booking_id,
Booking.listing_id,
Listing.listing_name,

Booking.guest_id,
GA1.guest_profile_name ,

Listing.host_id,

HostAccount.host_profile_name,

GA2.guest_id AS host_guest_id

FROM Payment 

LEFT OUTER JOIN Booking ON Payment.booking_id = Booking.booking_id

-- JOIN WITH GUEST ACCOUNT TO GET BASIC GUEST DATA
LEFT OUTER JOIN GuestAccount AS GA1 ON  Booking.guest_id = GA1.guest_id
LEFT OUTER JOIN Listing ON Booking.listing_id = Listing.listing_id
LEFT OUTER JOIN HostAccount ON Listing.host_id = HostAccount.host_id

-- JOIN WITH GUEST ACCOUNT TO GET INFORMATION ON HOST'S PARENT GUEST ACCOUNT
LEFT OUTER JOIN GuestAccount AS GA2 ON HostAccount.guest_id = GA2.guest_id
;

SELECT * FROM  VIEW_06_PAYMENT;