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
Can you list the number of amenity ids associated with each distinct dorm id and display both of those results (dorm id and number of amenity ids)?
assistant
SELECT T3.dormid , COUNT( T4.amenid ) FROM dorm AS T3 JOIN has_amenity AS T4 ON T3.dormid = T4.dormid JOIN dorm_amenity AS T5 ON T4.amenid = T5.amenid GROUP BY T3.dormid;
human
Wonderful, can you remove that information and instead display every student id that has a dorm id of 160?
assistant
SELECT T1.stuid FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid = T2.stuid WHERE T2.dormid = 160 ;