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 average age and number of male students (with sex M) from each city.
assistant
SELECT count(*) , avg(age) , city_code FROM student WHERE sex = 'M' GROUP BY city_code
human
Find the name of dorms only for female (F gender).
assistant
SELECT dorm_name FROM dorm WHERE gender = 'F'
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
human
Find the number of distinct gender for dorms.
assistant
SELECT count(DISTINCT gender) FROM dorm
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" ;