CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);
CREATE TABLE Voting_record (
    StuID NUMBER,
    Registration_Date TEXT,
    Election_Cycle TEXT,
    President_Vote NUMBER,
    Vice_President_Vote NUMBER,
    Secretary_Vote NUMBER,
    Treasurer_Vote NUMBER,
    Class_President_Vote NUMBER,
    Class_Senator_Vote NUMBER,
    FOREIGN KEY ("Class_Senator_Vote") REFERENCES Student("StuID"),
    FOREIGN KEY ("Class_President_Vote") REFERENCES Student("StuID"),
    FOREIGN KEY ("Treasurer_Vote") REFERENCES Student("StuID"),
    FOREIGN KEY ("Secretary_Vote") REFERENCES Student("StuID"),
    FOREIGN KEY ("Vice_President_Vote") REFERENCES Student("StuID"),
    FOREIGN KEY ("President_Vote") REFERENCES Student("StuID"),
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);

Question: Count the number of male students who had class senator votes in the fall election cycle.
SQL: SELECT count(*) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  Class_Senator_Vote WHERE T1.Sex  =  "M" AND T2.Election_Cycle  =  "Fall"

Question: Find the number of students whose city code is NYC and who have class senator votes in the spring election cycle.
SQL: 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"

Question: Which students live in the city with code "NYC" and have class senator votes in the spring election cycle? Count the numbers.
SQL: 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"

Question: Find the average age of students who live in the city with code "NYC" and have secretary votes in the spring election cycle.
SQL: 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"

Question: What is the average age of students who have city code "NYC" and have secretary votes for the spring election cycle?
SQL: 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"

Question: Find the average age of female (sex is F) students who have secretary votes in the spring election cycle.
SQL: 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"

Question: What is the average age of the female students with secretary votes in the spring election cycle?
SQL: 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"

Question: Find the distinct first names of all the students who have vice president votes and whose city code is not PIT.
SQL: 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"

Question: What are the distinct first names of the students who have vice president votes and reside in a city whose city code is not PIT?
SQL: 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"

Question: Find the distinct last names of all the students who have president votes and whose advisor is not 2192.
SQL: SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote EXCEPT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "2192"

Question: What are the distinct last names of the students who have president votes but do not have 2192 as the advisor?
SQL: SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote EXCEPT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "2192"

Question: Find the distinct last names of all the students who have president votes and whose advisor is 8741.
SQL: SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote INTERSECT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "8741"

Question: What are the distinct last names of the students who have president votes and have 8741 as the advisor?
SQL: SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote INTERSECT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "8741"

Question: For each advisor, report the total number of students advised by him or her.
SQL: SELECT Advisor ,  count(*) FROM STUDENT GROUP BY Advisor

Question: How many students does each advisor have?
SQL: SELECT Advisor ,  count(*) FROM STUDENT GROUP BY Advisor

Question: Report all advisors that advise more than 2 students.
SQL: SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING COUNT(*)  >  2

Question: Which advisors have more than two students?
SQL: SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING COUNT(*)  >  2

Question: Report all majors that have less than 3 students.
SQL: SELECT Major FROM STUDENT GROUP BY Major HAVING COUNT(*)  <  3

Question: What are the majors only less than three students are studying?
SQL: SELECT Major FROM STUDENT GROUP BY Major HAVING COUNT(*)  <  3

Question: For each election cycle, report the number of voting records.
SQL: SELECT Election_Cycle ,  count(*) FROM VOTING_RECORD GROUP BY Election_Cycle

Question: Count the number of voting records for each election cycle.
SQL: SELECT Election_Cycle ,  count(*) FROM VOTING_RECORD GROUP BY Election_Cycle

Question: Which major has the most students?
SQL: SELECT Major FROM STUDENT GROUP BY major ORDER BY count(*) DESC LIMIT 1

Question: Find the major that is studied by the largest number of students.
SQL: SELECT Major FROM STUDENT GROUP BY major ORDER BY count(*) DESC LIMIT 1

Question: What is the most common major among female (sex is F) students?
SQL: SELECT Major FROM STUDENT WHERE Sex  =  "F" GROUP BY major ORDER BY count(*) DESC LIMIT 1

Question: Find the major that is studied by the most female students.
SQL: SELECT Major FROM STUDENT WHERE Sex  =  "F" GROUP BY major ORDER BY count(*) DESC LIMIT 1

Question: What is the city_code of the city that the most students live in?
SQL: SELECT city_code FROM STUDENT GROUP BY city_code ORDER BY count(*) DESC LIMIT 1

Question: Return the code of the city that has the most students.
SQL: SELECT city_code FROM STUDENT GROUP BY city_code ORDER BY count(*) DESC LIMIT 1

Question: Report the distinct advisors who have more than 2 students.
SQL: SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING count(*)  >  2

Question: Which advisors are advising more than 2 students?
SQL: SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING count(*)  >  2