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 dorms and total capacity for each gender.
assistant
SELECT count(*) , sum(student_capacity) , gender FROM dorm GROUP BY gender
human
Find the name of dorms only for female (F gender).
assistant
SELECT dorm_name FROM dorm WHERE gender = 'F'
human
Find the first name of students who are living in the Smith Hall.
assistant
SELECT T1.fname 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.dorm_name = 'Smith Hall'
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 number of distinct gender for dorms.
assistant
SELECT count(DISTINCT gender) FROM dorm
human
Find the number of female students (with F sex) living in Smith Hall
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.dorm_name = "Smith Hall" AND T1.sex = "F" ;