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 of all students living in the each city.
assistant
SELECT avg(age) , city_code FROM student 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
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 number of students for the cities where have more than one student.
assistant
SELECT count(*) , city_code FROM student GROUP BY city_code HAVING count(*) > 1
human
List the first and last name of students who are not living in the city with code HKG, and sorted the results by their ages.
assistant
SELECT fname , lname FROM student WHERE city_code != 'HKG' ORDER BY age
human
Find the code of city where most of students are living in.
assistant
SELECT city_code FROM student GROUP BY city_code ORDER BY COUNT( * ) DESC LIMIT 1 ;