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 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
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 first and last name of students whose age is younger than the average age.
assistant
SELECT fname , lname FROM student WHERE age < (SELECT avg(age) FROM student)
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 first name of students who is older than 20.
assistant
SELECT fname FROM student WHERE age > 20
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 ;