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 number of amenities for each of the dorms that can accommodate more than 100 students.
assistant
SELECT count(*) , T1.dormid FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid = T2.dormid WHERE T1.student_capacity > 100 GROUP BY T1.dormid
human
Find the first name of students who are living in the dorm that has most number of amenities.
assistant
SELECT T1.fname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid = T2.stuid WHERE T2.dormid IN (SELECT T2.dormid 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 ORDER BY count(*) DESC LIMIT 1)
human
Find the number of dorms that have some amenity.
assistant
SELECT count(DISTINCT dormid) FROM has_amenity
human
Find the total capacity of all dorms.
assistant
SELECT sum(student_capacity) FROM dorm
human
Find the name of the dorm with the largest capacity.
assistant
SELECT dorm_name FROM dorm ORDER BY student_capacity DESC LIMIT 1
human
Find the name and capacity of the dorm with least number of amenities.
assistant
SELECT T1.dorm_name , T1.student_capacity FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid = T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid = T3.amenid GROUP BY T2.dormid ORDER BY COUNT( * ) LIMIT 1 ;