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 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
Report the distinct advisors who have more than 2 students.
assistant
SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING count(*) > 2
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 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
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
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" ;