human
Schema:
Ref_Hotel_Star_Ratings (star_rating_code, star_rating_description)
Locations (Location_ID, Location_Name, Address, Other_Details)
Ref_Attraction_Types (Attraction_Type_Code, Attraction_Type_Description)
Visitors (Tourist_ID, Tourist_Details)
Features (Feature_ID, Feature_Details)
Hotels (hotel_id, star_rating_code, pets_allowed_yn, price_range, other_hotel_details)
Tourist_Attractions (Tourist_Attraction_ID, Attraction_Type_Code, Location_ID, How_to_Get_There, Name, Description, Opening_Hours, Other_Details)
Street_Markets (Market_ID, Market_Details)
Shops (Shop_ID, Shop_Details)
Museums (Museum_ID, Museum_Details)
Royal_Family (Royal_Family_ID, Royal_Family_Details)
Theme_Parks (Theme_Park_ID, Theme_Park_Details)
Visits (Visit_ID, Tourist_Attraction_ID, Tourist_ID, Visit_Date, Visit_Details)
Photos (Photo_ID, Tourist_Attraction_ID, Name, Description, Filename, Other_Details)
Staff (Staff_ID, Tourist_Attraction_ID, Name, Other_Details)
Tourist_Attraction_Features (Tourist_Attraction_ID, Feature_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names and ids of tourist attractions that are visited at most once.
assistant
SELECT T1.Name , T1.Tourist_Attraction_ID FROM Tourist_Attractions AS T1 JOIN VISITS AS T2 ON T1.Tourist_Attraction_ID = T2.Tourist_Attraction_ID GROUP BY T2.Tourist_Attraction_ID HAVING count(*) <= 1
human
What are the names of the tourist attractions that have parking or shopping as their feature details?
assistant
SELECT T1.Name FROM Tourist_Attractions AS T1 JOIN Tourist_Attraction_Features AS T2 ON T1.tourist_attraction_id = T2.tourist_attraction_id JOIN Features AS T3 ON T2.Feature_ID = T3.Feature_ID WHERE T3.feature_Details = 'park' UNION SELECT T1.Name FROM Tourist_Attractions AS T1 JOIN Tourist_Attraction_Features AS T2 ON T1.tourist_attraction_id = T2.tourist_attraction_id JOIN Features AS T3 ON T2.Feature_ID = T3.Feature_ID WHERE T3.feature_Details = 'shopping'
human
What are the visit date and details of the visitor whose detail is 'Vincent'?
assistant
SELECT T2.Visit_Date , T2.Visit_Details FROM VISITORS AS T1 JOIN VISITS AS T2 ON T1.Tourist_ID = T2.Tourist_ID WHERE T1.Tourist_Details = 'Vincent'
human
What are the names of the tourist attractions and the dates when the tourists named Vincent or Vivian visited there?
assistant
SELECT T1.Name , T3.Visit_Date FROM Tourist_Attractions AS T1 JOIN VISITORS AS T2 JOIN VISITS AS T3 ON T1.Tourist_Attraction_ID = T3.Tourist_Attraction_ID AND T2.Tourist_ID = T3.Tourist_ID WHERE T2.Tourist_Details = 'Vincent' OR T2.Tourist_Details = 'Vivian'
human
Which tourist attractions does the visitor with detail 'Vincent' visit?
assistant
SELECT T1.Name FROM Tourist_Attractions AS T1 JOIN VISITS AS T2 ON T1.Tourist_Attraction_ID = T2.Tourist_Attraction_ID JOIN VISITORS AS T3 ON T2.Tourist_ID = T3.Tourist_ID WHERE T3.Tourist_Details = 'Vincent'
human
Which tourist attractions do the tourists Vincent and Marcelle visit? Tell me the names of the attractions.
assistant
SELECT T1.Name FROM Tourist_Attractions AS T1 JOIN VISITORS AS T2 JOIN VISITS AS T3 ON T1.Tourist_Attraction_ID = T3.Tourist_Attraction_ID AND T2.Tourist_ID = T3.Tourist_ID WHERE T2.Tourist_Details = "Vincent" INTERSECT SELECT T1.Name FROM Tourist_Attractions AS T1 JOIN VISITORS AS T2 JOIN VISITS AS T3 ON T1.Tourist_Attraction_ID = T3.Tourist_Attraction_ID AND T2.Tourist_ID = T3.Tourist_ID WHERE T2.Tourist_Details = "Marcelle" ;