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 numbers of different majors and cities.
assistant
SELECT count(DISTINCT major) , count(DISTINCT city_code) FROM student
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
Find the first name of students living in city PHL whose age is between 20 and 25.
assistant
SELECT fname FROM student WHERE city_code = 'PHL' AND age BETWEEN 20 AND 25
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 number and average age of students living in each city.
assistant
SELECT count(*) , avg(age) , city_code FROM student GROUP BY city_code
human
What is the average age for each city and what are those cities?
assistant
SELECT avg ( age ) , city_code FROM student GROUP BY city_code ;