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 last names of all the students who have president votes and whose advisor is 8741.
assistant
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'
human
Which major has the most students?
assistant
SELECT Major FROM STUDENT GROUP BY major ORDER BY count(*) DESC LIMIT 1
human
Find the distinct last names of all the students who have president votes and whose advisor is not 2192.
assistant
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'
human
Find the distinct Advisor of students who have treasurer votes in the spring election cycle.
assistant
SELECT DISTINCT T1.Advisor FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID = T2.Treasurer_Vote WHERE T2.Election_Cycle = 'Spring'
human
For each advisor, report the total number of students advised by him or her.
assistant
SELECT Advisor , count(*) FROM STUDENT GROUP BY Advisor
human
Which advisors have more than two students?
assistant
SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING COUNT( * ) > 2 ;