CREATE TABLE Ref_Hotel_Star_Ratings (
    star_rating_code TEXT PRIMARY KEY,
    star_rating_description TEXT
);
CREATE TABLE Locations (
    Location_ID NUMBER PRIMARY KEY,
    Location_Name TEXT,
    Address TEXT,
    Other_Details TEXT
);
CREATE TABLE Ref_Attraction_Types (
    Attraction_Type_Code TEXT PRIMARY KEY,
    Attraction_Type_Description TEXT
);
CREATE TABLE Visitors (
    Tourist_ID NUMBER PRIMARY KEY,
    Tourist_Details TEXT
);
CREATE TABLE Features (
    Feature_ID NUMBER PRIMARY KEY,
    Feature_Details TEXT
);
CREATE TABLE Hotels (
    hotel_id NUMBER PRIMARY KEY,
    star_rating_code TEXT,
    pets_allowed_yn TEXT,
    price_range NUMBER,
    other_hotel_details TEXT,
    FOREIGN KEY ("star_rating_code") REFERENCES Ref_Hotel_Star_Ratings("star_rating_code")
);
CREATE TABLE Tourist_Attractions (
    Tourist_Attraction_ID NUMBER PRIMARY KEY,
    Attraction_Type_Code TEXT,
    Location_ID NUMBER,
    How_to_Get_There TEXT,
    Name TEXT,
    Description TEXT,
    Opening_Hours TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Attraction_Type_Code") REFERENCES Ref_Attraction_Types("Attraction_Type_Code"),
    FOREIGN KEY ("Location_ID") REFERENCES Locations("Location_ID")
);
CREATE TABLE Street_Markets (
    Market_ID NUMBER PRIMARY KEY,
    Market_Details TEXT,
    FOREIGN KEY ("Market_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);
CREATE TABLE Shops (
    Shop_ID NUMBER PRIMARY KEY,
    Shop_Details TEXT,
    FOREIGN KEY ("Shop_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);
CREATE TABLE Museums (
    Museum_ID NUMBER PRIMARY KEY,
    Museum_Details TEXT,
    FOREIGN KEY ("Museum_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);
CREATE TABLE Royal_Family (
    Royal_Family_ID NUMBER PRIMARY KEY,
    Royal_Family_Details TEXT,
    FOREIGN KEY ("Royal_Family_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);
CREATE TABLE Theme_Parks (
    Theme_Park_ID NUMBER PRIMARY KEY,
    Theme_Park_Details TEXT,
    FOREIGN KEY ("Theme_Park_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);
CREATE TABLE Visits (
    Visit_ID NUMBER PRIMARY KEY,
    Tourist_Attraction_ID NUMBER,
    Tourist_ID NUMBER,
    Visit_Date TIME,
    Visit_Details TEXT,
    FOREIGN KEY ("Tourist_ID") REFERENCES Visitors("Tourist_ID"),
    FOREIGN KEY ("Tourist_Attraction_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);
CREATE TABLE Photos (
    Photo_ID NUMBER PRIMARY KEY,
    Tourist_Attraction_ID NUMBER,
    Name TEXT,
    Description TEXT,
    Filename TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Tourist_Attraction_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);
CREATE TABLE Staff (
    Staff_ID NUMBER PRIMARY KEY,
    Tourist_Attraction_ID NUMBER,
    Name TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Tourist_Attraction_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);
CREATE TABLE Tourist_Attraction_Features (
    Tourist_Attraction_ID NUMBER PRIMARY KEY,
    Feature_ID NUMBER,
    FOREIGN KEY ("Feature_ID") REFERENCES Features("Feature_ID"),
    FOREIGN KEY ("Tourist_Attraction_ID") REFERENCES Tourist_Attractions("Tourist_Attraction_ID")
);

Question: What are the names and ids of the tourist attractions that are visited at most once?
SQL: 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

Question: What are the names of tourist attractions that can be reached by walk or is at address 660 Shea Crescent?
SQL: SELECT T2.Name FROM Locations AS T1 JOIN Tourist_Attractions AS T2 ON T1.Location_ID  =  T2.Location_ID WHERE T1.Address  =  "660 Shea Crescent" OR T2.How_to_Get_There  =  "walk"

Question: Find the names of the tourist attractions that is either accessible by walk or at address 660 Shea Crescent.
SQL: SELECT T2.Name FROM Locations AS T1 JOIN Tourist_Attractions AS T2 ON T1.Location_ID  =  T2.Location_ID WHERE T1.Address  =  "660 Shea Crescent" OR T2.How_to_Get_There  =  "walk"

Question: What are the names of the tourist attractions that have parking or shopping as their feature details?
SQL: 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'

Question: Find the tourist attractions that have parking or shopping as their feature details. What are the names of the attractions?
SQL: 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'

Question: What are the names of tourist attractions that can be reached by bus or is at address 254 Ottilie Junction?
SQL: SELECT T2.Name FROM Locations AS T1 JOIN Tourist_Attractions AS T2 ON T1.Location_ID  =  T2.Location_ID WHERE T1.Address  =  "254 Ottilie Junction" OR T2.How_to_Get_There  =  "bus"

Question: Find the names of the tourist attractions that is either accessible by bus or at address 254 Ottilie Junction.
SQL: SELECT T2.Name FROM Locations AS T1 JOIN Tourist_Attractions AS T2 ON T1.Location_ID  =  T2.Location_ID WHERE T1.Address  =  "254 Ottilie Junction" OR T2.How_to_Get_There  =  "bus"

Question: What are the names of the tourist attractions Vincent and Marcelle visit?
SQL: 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"

Question: Which tourist attractions do the tourists Vincent and Marcelle visit? Tell me the names of the attractions.
SQL: 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"

Question: What are the names of tourist attraction that Alison visited but Rosalind did not visit?
SQL: 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  =  "Alison" EXCEPT 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  =  "Rosalind"