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 students who is older than 20 in each dorm.
assistant
SELECT count(*) , T3.dorm_name FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid = T2.stuid JOIN dorm AS T3 ON T3.dormid = T2.dormid WHERE T1.age > 20 GROUP BY T3.dorm_name
human
Find the number of students whose age is older than the average age for each gender.
assistant
SELECT count(*) , sex FROM student WHERE age > (SELECT avg(age) FROM student) GROUP BY sex
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'
human
Find the last name of students who is either female (sex is F) and living in the city of code BAL or male (sex is M) and in age of below 20.
assistant
SELECT lname FROM student WHERE sex = 'F' AND city_code = 'BAL' UNION SELECT lname FROM student WHERE sex = 'M' AND age < 20
human
Find the average and oldest age for students with different sex.
assistant
SELECT avg(age) , max(age) , sex FROM student GROUP BY sex
human
How many female students (sex is F) whose age is below 25?
assistant
SELECT COUNT( * ) FROM student WHERE sex = "F" AND age < 25 ;