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 number of students in total.
assistant
SELECT count(*) 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
Which major has the most students?
assistant
SELECT Major FROM STUDENT GROUP BY major ORDER BY count(*) DESC LIMIT 1
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
Who are the advisors for students that live in a city with city code 'BAL'?
assistant
SELECT Advisor FROM STUDENT WHERE city_code = 'BAL'
human
What is the city_code of the city that the most students live in?
assistant
SELECT city_code FROM STUDENT GROUP BY city_code ORDER BY COUNT( * ) DESC LIMIT 1 ;