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 average price of hotels for each star rating code.
assistant
SELECT star_rating_code , avg(price_range) FROM HOTELS GROUP BY star_rating_code
human
Show the id and star rating of each hotel, ordered by its price from low to high.
assistant
SELECT hotel_id , star_rating_code FROM HOTELS ORDER BY price_range ASC
human
What are the star rating descriptions of the hotels with price above 10000?
assistant
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
human
Show the price ranges of hotels with 5 star ratings.
assistant
SELECT price_range FROM HOTELS WHERE star_rating_code = '5'
human
Show the details of the top 3 most expensive hotels.
assistant
SELECT other_hotel_details FROM HOTELS ORDER BY price_range DESC LIMIT 3
human
Show the details and star ratings of the 3 least expensive hotels.
assistant
SELECT other_hotel_details , star_rating_code FROM HOTELS ORDER BY price_range ASC LIMIT 3 ;