human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Club (ClubID, ClubName, ClubDesc, ClubLocation)
Member_of_club (StuID, ClubID, Position)
Please answer with SQL.
assistant
Sure, I understand.
human
How many students are majoring in '600'?
assistant
SELECT COUNT( stuid ) FROM student WHERE major = 600;
human
Can you show me only those students?
assistant
SELECT stuid FROM student WHERE major = 600;
human
Can you also show me what clubs they are in?
assistant
SELECT t1.clubname FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid = t2.clubid JOIN student AS t3 ON t2.stuid = t3.stuid WHERE t3.major = '600' GROUP BY t1.clubname;
human
How many students who are majoring in 600 are in each of those clubs?
assistant
SELECT t1.clubname , COUNT( * ) FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid = t2.clubid JOIN student AS t3 ON t2.stuid = t3.stuid WHERE t3.major = "600" GROUP BY t1.clubname ORDER BY COUNT( * ) ;