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 maximum age of all the students.
assistant
SELECT max(Age) FROM STUDENT
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 distinct first names of all the students who have vice president votes and whose city code is not PIT.
assistant
SELECT DISTINCT T1.Fname FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID = T2.VICE_PRESIDENT_Vote EXCEPT SELECT DISTINCT Fname FROM STUDENT WHERE city_code = 'PIT'
human
Find the number of students in total.
assistant
SELECT count(*) FROM STUDENT
human
Find the number of students whose city code is NYC and who have class senator votes in the spring election cycle.
assistant
SELECT count(*) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID = Class_Senator_Vote WHERE T1.city_code = 'NYC' AND T2.Election_Cycle = 'Spring'
human
Return the code of the city that has the most students.
assistant
SELECT city_code FROM STUDENT GROUP BY city_code ORDER BY COUNT( * ) DESC LIMIT 1 ;