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
List the clubs having 'Davis Steven' as a member.
assistant
SELECT DISTINCT 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.fname = 'Davis' AND t3.lname = 'Steven'
human
How many members does the club 'Tennis Club' has?
assistant
SELECT 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 t1.clubname = 'Tennis Club'
human
What are the names of all clubs?
assistant
SELECT clubname FROM club
human
Which club has the most members majoring in '600'?
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 ORDER BY count(*) DESC LIMIT 1
human
How many clubs are there?
assistant
SELECT count(*) FROM club
human
Which clubs have one or more members whose advisor is '1121'?
assistant
SELECT DISTINCT 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.advisor = 1121 ;