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 name and gender type of the dorms whose capacity is greater than 300 or less than 100.
assistant
SELECT dorm_name , gender FROM dorm WHERE student_capacity > 300 OR student_capacity < 100
human
Find the total number of students living in the male dorm (with gender M).
assistant
SELECT count(*) FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid = T2.stuid JOIN dorm AS T3 ON T3.dormid = T2.dormid WHERE T3.gender = 'M'
human
Find the total capacity of all dorms.
assistant
SELECT sum(student_capacity) FROM dorm
human
Find the number of distinct gender for dorms.
assistant
SELECT count(DISTINCT gender) FROM dorm
human
Find the average and total capacity of dorms for the students with gender X.
assistant
SELECT avg(student_capacity) , sum(student_capacity) FROM dorm WHERE gender = 'X'
human
Find the number of dorms and total capacity for each gender.
assistant
SELECT COUNT( * ) , SUM( student_capacity ) , gender FROM dorm GROUP BY gender ;