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
How many dorms are there?
assistant
SELECT count(*) FROM dorm
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
How many different genders are there in the dorms?
assistant
SELECT COUNT( DISTINCT gender ) FROM dorm ;