human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Dorm (dormid, dorm_name, student_capacity, gender)
Dorm_amenity (amenid, amenity_name)
Has_amenity (dormid, amenid)
Lives_in (stuid, dormid, room_number)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the capacity and gender type of the dorm whose name has substring Donor .
assistant
SELECT student_capacity , gender FROM dorm WHERE dorm_name LIKE '%Donor%'
human
Find the number of distinct amenities.
assistant
SELECT count(*) FROM dorm_amenity
human
Find the name of amenities Smith Hall dorm have.
assistant
SELECT T3.amenity_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid = T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid = T3.amenid WHERE T1.dorm_name = 'Smith Hall'
human
Find the name of amenities Smith Hall dorm have. ordered the results by amenity names.
assistant
SELECT T3.amenity_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid = T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid = T3.amenid WHERE T1.dorm_name = 'Smith Hall' ORDER BY T3.amenity_name
human
List in alphabetic order all different amenities.
assistant
SELECT amenity_name FROM dorm_amenity ORDER BY amenity_name
human
What are the amenities in alphabetical order that Anonymous Donor Hall has?
assistant
SELECT T1.amenity_name FROM dorm_amenity AS T1 JOIN has_amenity AS T2 ON T2.amenid = T1.amenid JOIN dorm AS T3 ON T2.dormid = T3.dormid WHERE T3.dorm_name = "Anonymous Donor Hall" ORDER BY T1.amenity_name ;