human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Voting_record (StuID, Registration_Date, Election_Cycle, President_Vote, Vice_President_Vote, Secretary_Vote, Treasurer_Vote, Class_President_Vote, Class_Senator_Vote)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the average age of students who live in the city with code 'NYC' and have secretary votes in the spring election cycle.
assistant
SELECT avg(T1.Age) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID = SECRETARY_Vote WHERE T1.city_code = 'NYC' AND T2.Election_Cycle = 'Spring'
human
Find the first names of students with age above 22.
assistant
SELECT Fname FROM STUDENT WHERE Age > 22
human
Find the number of students in total.
assistant
SELECT count(*) FROM STUDENT
human
Find the maximum age of all the students.
assistant
SELECT max(Age) FROM STUDENT
human
Find the average age of female (sex is F) students who have secretary votes in the spring election cycle.
assistant
SELECT avg(T1.Age) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID = SECRETARY_Vote WHERE T1.Sex = 'F' AND T2.Election_Cycle = 'Spring'
human
Find the average age of female students.
assistant
SELECT avg ( Age ) FROM STUDENT WHERE Sex = "F" ;