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 that have at least a member with advisor '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
human
Find the names of all the clubs that have at least a member from the city with city code 'BAL'.
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.city_code = 'BAL'
human
Find the name of all the clubs at 'AKW'.
assistant
SELECT clubname FROM club WHERE clublocation = 'AKW'
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
Give me the name of each club.
assistant
SELECT clubname FROM club ;