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: How many available hotels are there in total?
SQL: SELECT count(*) FROM HOTELS

Question: Find the total number of available hotels.
SQL: SELECT count(*) FROM HOTELS

Question: What are the price ranges of hotels?
SQL: SELECT price_range FROM HOTELS

Question: Tell me the price ranges for all the hotels.
SQL: SELECT price_range FROM HOTELS

Question: Show all distinct location names.
SQL: SELECT DISTINCT Location_Name FROM LOCATIONS

Question: What are the distinct location names?
SQL: SELECT DISTINCT Location_Name FROM LOCATIONS

Question: Show the names and details of all the staff members.
SQL: SELECT Name ,  Other_Details FROM Staff

Question: What is the name and detail of each staff member?
SQL: SELECT Name ,  Other_Details FROM Staff

Question: Show details of all visitors.
SQL: SELECT Tourist_Details FROM VISITORS

Question: What is the detail of each visitor?
SQL: SELECT Tourist_Details FROM VISITORS

Question: Show the price ranges of hotels with 5 star ratings.
SQL: SELECT price_range FROM HOTELS WHERE star_rating_code  =  "5"

Question: What are the price ranges of five star hotels?
SQL: SELECT price_range FROM HOTELS WHERE star_rating_code  =  "5"

Question: Show the average price range of hotels that have 5 star ratings and allow pets.
SQL: SELECT avg(price_range) FROM HOTELS WHERE star_rating_code  =  "5" AND pets_allowed_yn  =  1

Question: What is the average price range of five star hotels that allow pets?
SQL: SELECT avg(price_range) FROM HOTELS WHERE star_rating_code  =  "5" AND pets_allowed_yn  =  1

Question: What is the address of the location "UK Gallery"?
SQL: SELECT Address FROM LOCATIONS WHERE Location_Name  =  "UK Gallery"

Question: Find the address of the location named "UK Gallery".
SQL: SELECT Address FROM LOCATIONS WHERE Location_Name  =  "UK Gallery"

Question: What is the detail of the location UK Gallery?
SQL: SELECT Other_Details FROM LOCATIONS WHERE Location_Name  =  "UK Gallery"

Question: Return the detail of the location named "UK Gallery".
SQL: SELECT Other_Details FROM LOCATIONS WHERE Location_Name  =  "UK Gallery"

Question: Which location names contain the word "film"?
SQL: SELECT Location_Name FROM LOCATIONS WHERE Location_Name LIKE "%film%"

Question: Find all the locations whose names contain the word "film".
SQL: SELECT Location_Name FROM LOCATIONS WHERE Location_Name LIKE "%film%"

Question: How many distinct names are associated with all the photos?
SQL: SELECT count(DISTINCT Name) FROM PHOTOS

Question: Count the number of distinct names associated with the photos.
SQL: SELECT count(DISTINCT Name) FROM PHOTOS

Question: What are the distinct visit dates?
SQL: SELECT DISTINCT Visit_Date FROM VISITS

Question: Find all the distinct visit dates.
SQL: SELECT DISTINCT Visit_Date FROM VISITS

Question: What are the names of the tourist attractions that can be accessed by bus?
SQL: SELECT Name FROM TOURIST_ATTRACTIONS WHERE How_to_Get_There  =  "bus"

Question: Which tourist attractions can we get to by bus? Tell me the names of the attractions.
SQL: SELECT Name FROM TOURIST_ATTRACTIONS WHERE How_to_Get_There  =  "bus"

Question: What are the names and opening hours of the tourist attractions that can be accessed by bus or walk?
SQL: SELECT Name ,  Opening_Hours FROM TOURIST_ATTRACTIONS WHERE How_to_Get_There  =  "bus" OR How_to_Get_There  =  "walk"

Question: Find the names and opening hours of the tourist attractions that we get to by bus or walk.
SQL: SELECT Name ,  Opening_Hours FROM TOURIST_ATTRACTIONS WHERE How_to_Get_There  =  "bus" OR How_to_Get_There  =  "walk"

Question: What are the star rating descriptions of the hotels with price above 10000?
SQL: SELECT T2.star_rating_description FROM HOTELS AS T1 JOIN Ref_Hotel_Star_Ratings AS T2 ON T1.star_rating_code  =  T2.star_rating_code WHERE T1.price_range  >  10000

Question: Give me the star rating descriptions of the hotels that cost more than 10000.
SQL: SELECT T2.star_rating_description FROM HOTELS AS T1 JOIN Ref_Hotel_Star_Ratings AS T2 ON T1.star_rating_code  =  T2.star_rating_code WHERE T1.price_range  >  10000

Question: What are the details and opening hours of the museums?
SQL: SELECT T1.Museum_Details ,  T2.Opening_Hours FROM MUSEUMS AS T1 JOIN TOURIST_ATTRACTIONS AS T2 ON T1.Museum_ID  =  T2.Tourist_Attraction_ID

Question: Give me the detail and opening hour for each museum.
SQL: SELECT T1.Museum_Details ,  T2.Opening_Hours FROM MUSEUMS AS T1 JOIN TOURIST_ATTRACTIONS AS T2 ON T1.Museum_ID  =  T2.Tourist_Attraction_ID

Question: What is the name of the tourist attraction that is associated with the photo "game1"?
SQL: SELECT T2.Name FROM PHOTOS AS T1 JOIN TOURIST_ATTRACTIONS AS T2 ON T1.Tourist_Attraction_ID  =  T2.Tourist_Attraction_ID WHERE T1.Name  =  "game1"