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: Show the average price of hotels for each star rating code.
SQL: SELECT star_rating_code ,  avg(price_range) FROM HOTELS GROUP BY star_rating_code

Question: What is the average price range of hotels for each each star rating code?
SQL: SELECT star_rating_code ,  avg(price_range) FROM HOTELS GROUP BY star_rating_code

Question: Show the average price of hotels for different pet policy.
SQL: SELECT pets_allowed_yn ,  avg(price_range) FROM HOTELS GROUP BY pets_allowed_yn

Question: What are the average prices of hotels grouped by their pet policy.
SQL: SELECT pets_allowed_yn ,  avg(price_range) FROM HOTELS GROUP BY pets_allowed_yn

Question: Show the id and star rating of each hotel, ordered by its price from low to high.
SQL: SELECT hotel_id ,  star_rating_code FROM HOTELS ORDER BY price_range ASC

Question: Find the id and star rating of each hotel and sort them in increasing order of price.
SQL: SELECT hotel_id ,  star_rating_code FROM HOTELS ORDER BY price_range ASC

Question: Show the details of the top 3 most expensive hotels.
SQL: SELECT other_hotel_details FROM HOTELS ORDER BY price_range DESC LIMIT 3

Question: What are the details of the three most expensive hotels?
SQL: SELECT other_hotel_details FROM HOTELS ORDER BY price_range DESC LIMIT 3

Question: Show the details and star ratings of the 3 least expensive hotels.
SQL: SELECT other_hotel_details ,  star_rating_code FROM HOTELS ORDER BY price_range ASC LIMIT 3

Question: What are the details and star ratings of the three hotels with the lowest price ranges?
SQL: SELECT other_hotel_details ,  star_rating_code FROM HOTELS ORDER BY price_range ASC LIMIT 3

Question: Show the transportation method most people choose to get to tourist attractions.
SQL: SELECT How_to_Get_There FROM Tourist_Attractions GROUP BY How_to_Get_There ORDER BY COUNT(*) DESC LIMIT 1

Question: Which transportation method is used the most often to get to tourist attractions?
SQL: SELECT How_to_Get_There FROM Tourist_Attractions GROUP BY How_to_Get_There ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the description and code of the attraction type most tourist attractions belong to.
SQL: SELECT T1.Attraction_Type_Description ,  T2.Attraction_Type_Code FROM Ref_Attraction_Types AS T1 JOIN Tourist_Attractions AS T2 ON T1.Attraction_Type_Code  =  T2.Attraction_Type_Code GROUP BY T2.Attraction_Type_Code ORDER BY COUNT(*) DESC LIMIT 1

Question: Which attraction type does the most tourist attractions belong to? Tell me its  attraction type description and code.
SQL: SELECT T1.Attraction_Type_Description ,  T2.Attraction_Type_Code FROM Ref_Attraction_Types AS T1 JOIN Tourist_Attractions AS T2 ON T1.Attraction_Type_Code  =  T2.Attraction_Type_Code GROUP BY T2.Attraction_Type_Code ORDER BY COUNT(*) DESC LIMIT 1

Question: Show different ways to get to attractions and the number of attractions that can be accessed in the corresponding way.
SQL: SELECT How_to_Get_There ,  COUNT(*) FROM Tourist_Attractions GROUP BY How_to_Get_There

Question: List all the possible ways to get to attractions, together with the number of attractions accessible by these methods.
SQL: SELECT How_to_Get_There ,  COUNT(*) FROM Tourist_Attractions GROUP BY How_to_Get_There

Question: Show different tourist attractions' names, ids, and the corresponding number of visits.
SQL: SELECT T1.Name ,  T2.Tourist_Attraction_ID ,   COUNT(*) FROM Tourist_Attractions AS T1 JOIN VISITS AS T2 ON T1.Tourist_Attraction_ID  =  T2.Tourist_Attraction_ID GROUP BY T2.Tourist_Attraction_ID

Question: What are the name, id and the corresponding number of visits for each  tourist attraction?
SQL: SELECT T1.Name ,  T2.Tourist_Attraction_ID ,   COUNT(*) FROM Tourist_Attractions AS T1 JOIN VISITS AS T2 ON T1.Tourist_Attraction_ID  =  T2.Tourist_Attraction_ID GROUP BY T2.Tourist_Attraction_ID

Question: Show the names and ids of tourist attractions that are visited at least two times.
SQL: SELECT T1.Name ,  T2.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(*)  >=  2

Question: Which tourist attractions are visited at least twice? Give me their names and ids.
SQL: SELECT T1.Name ,  T2.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(*)  >=  2

Question: Show the names and ids of 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