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
Report all majors that have less than 3 students.
assistant
SELECT Major FROM STUDENT GROUP BY Major HAVING COUNT(*) < 3
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
Find the number of students in total.
assistant
SELECT count(*) FROM STUDENT
human
Report the distinct advisors who have more than 2 students.
assistant
SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING count(*) > 2
human
Report all advisors that advise more than 2 students.
assistant
SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING COUNT(*) > 2
human
For each advisor, report the total number of students advised by him or her.
assistant
SELECT Advisor , COUNT( * ) FROM STUDENT GROUP BY Advisor ;